Oracle RESULT_CACHE "ऑफ-लेबल" का उपयोग करना

set echo off set verify off set linesize 192 set trim on set trims on spool script.log DROP TABLE EVT_TBL PURGE; DROP TABLE EVT_LOG PURGE; DROP SEQUENCE EVT_SEQ; DROP FUNCTION EVT_CHECK_JOB; DROP PROCEDURE EVT_CREATE_RECORD; DROP PROCEDURE EVT_PRECESS_JOB; -- CREATE TABLE EVT_TBL ( N NUMBER PRIMARY KEY ,V VARCHAR2(100) ); CREATE SEQUENCE EVT_SEQ; CREATE TABLE EVT_LOG ( N NUMBER ,V VARCHAR2(100) ,D DATE DEFAULT SYSDATE ); CREATE OR REPLACE PROCEDURE EVT_CREATE_RECORD IS BEGIN INSERT INTO EVT_TBL SELECT EVT_SEQ.NEXTVAL, 'ID: '||EVT_SEQ.CURRVAL FROM DUAL; END EVT_CREATE_RECORD; / BEGIN FOR I IN 1..9 LOOP EVT_CREATE_RECORD; END LOOP; COMMIT; END; / CREATE OR REPLACE FUNCTION EVT_CHECK_JOB RETURN BOOLEAN RESULT_CACHE IS V_JOB INTEGER; PROCEDURE START_JOB IS PRAGMA AUTONOMOUS_TRANSACTION; C_LOCKHANDLE CONSTANT NUMBER := 13617637; V_RESULT NUMBER; BEGIN V_RESULT := DBMS_LOCK.REQUEST ( id => C_LOCKHANDLE ,lockmode => DBMS_LOCK.X_MODE ,release_on_commit => true ); SELECT MAX(J.JOB) INTO V_JOB FROM DBA_JOBS J WHERE J.LOG_USER = USER AND J.WHAT LIKE '%EVT_PRECESS_JOB;%'; IF V_JOB IS NULL THEN DBMS_JOB.submit(job => V_JOB, what => 'EVT_PRECESS_JOB;',next_date => SYSDATE+2/24/3600); INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'START JOB: "EVT_PRECESS_JOB;"', SYSDATE FROM DUAL; COMMIT; V_RESULT := NULL; END IF; IF V_RESULT = 0 THEN V_RESULT := DBMS_LOCK.RELEASE(lockhandle => C_LOCKHANDLE); END IF; EXCEPTION WHEN OTHERS THEN IF V_RESULT = 0 THEN V_RESULT := DBMS_LOCK.RELEASE(lockhandle => C_LOCKHANDLE); END IF; RAISE; END; PROCEDURE LOG_EXECUTE IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'EXECUTE: "EVT_CHECK_JOB;"', SYSDATE FROM DUAL; COMMIT; END; BEGIN LOG_EXECUTE; START_JOB; RETURN TRUE; END EVT_CHECK_JOB; / CREATE OR REPLACE PROCEDURE EVT_PRECESS_JOB IS C_MAX_INTERVAL CONSTANT INTEGER := 5; V_INTERVAL PLS_INTEGER := 0; V_REC EVT_TBL%ROWTYPE := NULL; V_ROWID UROWID := NULL; BEGIN WHILE V_ROWID IS NOT NULL OR V_INTERVAL < C_MAX_INTERVAL LOOP V_ROWID := NULL; BEGIN SELECT E.*, E.ROWID INTO V_REC.N, V_REC.V, V_ROWID FROM EVT_TBL E WHERE ROWNUM = 1 ORDER BY EN FOR UPDATE NOWAIT; V_INTERVAL := 1; EXCEPTION WHEN NO_DATA_FOUND THEN V_REC := NULL; V_INTERVAL := V_INTERVAL + 1; END; IF V_ROWID IS NOT NULL THEN INSERT INTO EVT_LOG (N, V, D) VALUES (V_REC.N, V_REC.V, SYSDATE); DELETE FROM EVT_TBL WHERE ROWID = V_ROWID; ELSE INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'SLEEP('||V_INTERVAL||'): "EVT_PRECESS_JOB;"', SYSDATE FROM DUAL; END IF; COMMIT; --      DBMS_LOCK.SLEEP(seconds => V_INTERVAL); END LOOP; INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'EXIT JOB: "EVT_PRECESS_JOB;"', SYSDATE FROM DUAL; DBMS_RESULT_CACHE.Invalidate(owner => USER, name => 'EVT_CHECK_JOB'); COMMIT; END EVT_PRECESS_JOB; / CREATE OR REPLACE TRIGGER EVT_TBL_TRG AFTER INSERT OR UPDATE ON EVT_TBL BEGIN IF EVT_CHECK_JOB THEN NULL; END IF; END; / select * from EVT_TBL; select N, V, TO_CHAR(D, 'HH24:MI:SS') AS D from EVT_LOG order by D desc; select job, what from user_jobs; begin EVT_CREATE_RECORD; commit; DBMS_LOCK.SLEEP(20); EVT_CREATE_RECORD; EVT_CREATE_RECORD; commit; DBMS_LOCK.SLEEP(30); EVT_CREATE_RECORD; commit; DBMS_LOCK.SLEEP(30); end; / select N, V, TO_CHAR(D, 'HH24:MI:SS') AS D from EVT_LOG order by D desc; spool off 

  .  .  .  .  .  .  .  .  .  .  PL/SQL  .  .  .  . NV ---------- -------------------------------------------------- 1 ID: 1 2 ID: 2 3 ID: 3 4 ID: 4 5 ID: 5 6 ID: 6 7 ID: 7 8 ID: 8 9 ID: 9 9  .        PL/SQL  . NVD ---------- -------------------------------------------------- -------- -30 EXIT JOB: "EVT_PRECESS_JOB;" 15:06:45 -29 SLEEP(5): "EVT_PRECESS_JOB;" 15:06:40 -28 SLEEP(4): "EVT_PRECESS_JOB;" 15:06:36 -27 SLEEP(3): "EVT_PRECESS_JOB;" 15:06:33 -26 SLEEP(2): "EVT_PRECESS_JOB;" 15:06:31 23 ID: 23 15:06:30 -25 START JOB: "EVT_PRECESS_JOB;" 15:06:27 -24 EXECUTE: "EVT_CHECK_JOB;" 15:06:27 -22 EXIT JOB: "EVT_PRECESS_JOB;" 15:06:15 -21 SLEEP(5): "EVT_PRECESS_JOB;" 15:06:10 -20 SLEEP(4): "EVT_PRECESS_JOB;" 15:06:06 NVD ---------- -------------------------------------------------- -------- -19 SLEEP(3): "EVT_PRECESS_JOB;" 15:06:03 -18 SLEEP(2): "EVT_PRECESS_JOB;" 15:06:01 17 ID: 17 15:06:00 16 ID: 16 15:05:59 -15 SLEEP(4): "EVT_PRECESS_JOB;" 15:05:55 -14 SLEEP(3): "EVT_PRECESS_JOB;" 15:05:52 -13 SLEEP(2): "EVT_PRECESS_JOB;" 15:05:50 10 ID: 10 15:05:49 9 ID: 9 15:05:48 8 ID: 8 15:05:47 7 ID: 7 15:05:46 NVD ---------- -------------------------------------------------- -------- 6 ID: 6 15:05:45 5 ID: 5 15:05:44 4 ID: 4 15:05:43 3 ID: 3 15:05:42 2 ID: 2 15:05:41 1 ID: 1 15:05:40 -12 START JOB: "EVT_PRECESS_JOB;" 15:05:37 -11 EXECUTE: "EVT_CHECK_JOB;" 15:05:36 30  .