Lock Handle

範例 : 

[建立Package Body]

CREATE OR REPLACE PACKAGE BODY cux_lock AS

  FUNCTION getHandle

  (

    pLockName       IN VARCHAR2,

    pExpirationSecs IN NUMBER DEFAULT 60

  ) RETURN VARCHAR2 IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    vLockHandle VARCHAR2(128);

  BEGIN

    dbms_lock.allocate_unique(lockname => pLockName,

                              lockhandle => vLockHandle,

                              expiration_secs => pExpirationSecs);

    RETURN vLockHandle;

  END getHandle;

  PROCEDURE RequestLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  ) IS

    vLockStatus NUMBER;

  BEGIN

    vLockstatus := dbms_lock.request(lockhandle => getHandle(pLockName),

                                     lockmode => dbms_lock.x_mode,

                                     timeout => dbms_lock.maxwait,--wait forever

                                     release_on_commit => FALSE);

    CASE vLockstatus

      WHEN 0 THEN

        vMessage := NULL;

      WHEN 2 THEN

        vMessage := 'Dead lock - Detected';

      WHEN 4 THEN

        vMessage := 'Lock already obtained';

      ELSE

        vMessage := 'Request lock failed : ' || vlockstatus;

    END CASE;

  END requestLock;

  PROCEDURE ReleaseLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  ) IS

    vLockStatus NUMBER;

  BEGIN

    vLockstatus := dbms_lock.release(lockhandle => getHandle(pLockName));

    IF vLockStatus > 0 THEN 

       vMessage := 'Release lock failed : ' || vLockstatus;

    END IF;

  END ReleaseLock;

END cux_lock;

[建立Package]

CREATE OR REPLACE PACKAGE cux_lock AS

  PROCEDURE RequestLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  );

  PROCEDURE ReleaseLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  );

END cux_lock;

=> Session 1 

DECLARE

 xMessage VARCHAR2(2000);

BEGIN

 cux_lock.RequestLock(pLockName => 'MyLock1',vMessage => xMessage);    

 dbms_output.put_line(xMessage);          

END;

=> Session 2 

DECLARE

 xMessage VARCHAR2(2000);

BEGIN

 cux_lock.RequestLock(pLockName => 'MyLock1',vMessage => xMessage);    

 dbms_output.put_line(xMessage);          

END;

=> Session  1 

DECLARE

 xMessage VARCHAR2(2000);

BEGIN

 cux_lock.ReleaseLock(pLockName => 'MyLock1',vMessage => xMessage);    

 dbms_output.put_line(xMessage);          

END;