oracle自定义锁定

--oracle版本:11.1.0.7.0

--1.创建自定义锁定
--定义锁定
create or replace function get_lockhandle return varchar2
is
  printer_lockname varchar2(128) := 'printer_lock';
  printer_handle varchar2(128);
begin
  if printer_handle is null then
    dbms_lock.allocate_unique(lockname => printer_lockname,lockhandle => printer_handle);
  end if;
  return printer_handle;
end;

--申请锁定
create or replace procedure lock_printer(p_return_code out integer)
is
  v_temp_lockhandle varchar2(128);
  v_call_status integer;
begin
  v_temp_lockhandle := get_lockhandle;
  v_call_status := dbms_lock.request(
    lockhandle => v_temp_lockhandle,
    lockmode => dbms_lock.x_mode,
    timeout => 5,
    release_on_commit => true--表示commit或rollback会释放锁定
  );
  p_return_code := v_call_status;
end lock_printer;

--释放锁定
create or replace procedure release_printer(p_return_code out integer)
is
  v_temp_lockhandle varchar2(128);
  v_call_status integer;
begin
  v_temp_lockhandle := get_lockhandle;
  v_call_status := dbms_lock.release(lockhandle => v_temp_lockhandle);
  p_return_code := v_call_status;
end release_printer;

--2.公开权限
grant execute on get_lockhandle to public;
grant execute on lock_printer to public;
grant execute on release_printer to public;

--3.使用自定义锁定
declare
  v_code integer := -1;
  v_desc varchar2(128):= '';
begin
  sys.lock_printer(v_code);
  select decode(v_code,0,'申请锁定成功',1,'申请锁定超时',2,'申请锁定发生死锁',3,'传入参数错误',
                4,'已经获得了锁定，重复申请了锁',5,'传入的锁定句柄错误') into v_desc from dual;
  if v_code = 0 then
    --do you work
    dbms_output.put_line(v_desc);
    --commit;
  else
    rollback;
    return;
  end if;
end;
/

--4.查看自定义锁定
select * from v$lock where type = 'UL';