創(chuàng)建存儲(chǔ)過程
create or replace procedure 過程名 is
cursor locked_orders is
select t.* from 表名 t where t.LOCK_STATE = '0' and t.LOCK_TIME < sysdate-24/24;
begin
for locked_order in locked_orders loop
begin
update 表名 t
set
t.LOCK_STATE = '1'
where t.LOCK_ID = locked_order.LOCK_ID;
end;
end loop;
commit;
end 過程名;
創(chuàng)建定時(shí)任務(wù)
以下命令是在PLSQL是命令窗口中執(zhí)行
variable job1 number;
begin
sys.dbms_job.submit(job => :job1,
what => '存儲(chǔ)過程名;',
next_date => sysdate,
interval => 'SYSDATE+1/24');
commit;
sys.dbms_job.run(:job1);
end;
/
