利用DBMS_SCHEDULER、DBMS_AQ、DBMS_ADADM實現基於時間和基於事件的自動執行任務

一、建立基於時間的作業

1）建立表t1

SQL> create table t1 (id number(10));

表已建立。

2）建立儲存過程tt

SQL> create procedure tt as

  2  begin

  3  insert into t1 values(2);

  4  commit;

  5  end;

  6  /

過程已建立。

3）建立程式use_tt

SQL>  begin

  2   dbms_scheduler.create_program(

  3   program_name=>'SCOTT.use_tt',

  4   program_action=>'SCOTT.TT',

  5   program_type=>'STORED_PROCEDURE',

  6   number_of_arguments=>0,

  7   comments=>'',

  8   enabled=>TRUE);

  9   end;

  10  /

PL/SQL 過程已成功完成。

4）建立計劃use_tt_1

SQL> begin

  2  dbms_scheduler.create_schedule(

  3  repeat_interval=>'FREQ=SECONDLY;INTERVAL=5',

  4  start_date=>systimestamp at time zone '+6:00',

  5  schedule_name=>'"SCOTT"."USE_TT_1"');

  6  end;

  7  /

PL/SQL 過程已成功完成。

5）建立作業tt_job

SQL> begin

  2  dbms_scheduler.create_job(

  3  job_name=>'"SCOTT"."TT_JOB"',

  4  program_name=>'SCOTT.USE_TT',

  5  schedule_name=>'SCOTT.USE_TT_1',

  6  job_class=>'DEFAULT_JOB_CLASS',

  7  auto_drop=>FALSE,

  8  enabled=>TRUE);

  9  end;

 10  /

PL/SQL 過程已成功完成。

6）檢視job資訊

SQL> col job_name format a15;

SQL> col program_name format a15;

SQL> col schedule_name format a15;

SQL> select job_name, program_name, schedule_name from  user_scheduler_jobs;

JOB_NAME        PROGRAM_NAME    SCHEDULE_NAME

--------------- --------------- ---------------

TT_JOB          USE_TT          USE_TT_1

檢視執行job的動態資訊，發現被執行了140次

SQL>SELECT COUNT(1) FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='TT_JOB';

  COUNT(1)

----------

       140

檢視t1表，發現不斷有資料插入

SQL> select count(*) from t1;

  COUNT(*)

----------

       145

二、建立基於事件的作業

1）建立表event_t

SQL> create table event_t(event_ varchar2(100));

表已建立。

2）建立型別t_event_q，用於說明訊息在佇列中存放的格式

SQL> create or replace type t_event_q as object(

  2  object_owner varchar2(50),event_name varchar2(50));

  3  /

型別已建立。

3）建立佇列表event_q

SQL>  begin

  2  dbms_aqadm.create_queue_table(

  3  queue_table=>'event_q',

  4  queue_payload_type=>'t_event_q',

  5  multiple_consumers=>true);

  6 end;

  7 /

PL/SQL 過程已成功完成。

4）建立佇列e_q，並啟動佇列

SQL> begin

  2  dbms_aqadm.create_queue(

  3  queue_name=>'e_q',

  4  queue_table=>'event_q');

  5  end;

  6  /

PL/SQL 過程已成功完成。

SQL> exec dbms_aqadm.start_queue(queue_name=>'e_q');

PL/SQL 過程已成功完成。

5）建立儲存過程t2

SQL>create or replace procedure t2 as

2 begin

3 insert into event_t values('event has recevie');

4 commit;

5 end;

6 /

過程已建立。

6）建立程式use_t2

SQL>  begin

  2   dbms_scheduler.create_program(

  3   program_name=>'SCOTT.use_t2',

  4   program_action=>'SCOTT.T2',

  5   program_type=>'STORED_PROCEDURE',

  6   number_of_arguments=>0,

  7   comments=>'',

  8   enabled=>TRUE);

  9   end;

  10  /

PL/SQL 過程已成功完成。

7）建立作業event_job

SQL> begin

2 dbms_scheduler.create_job(

3 job_name=>'"SCOTT"."EVENT_JOB"',

4 program_name=>'SCOTT.USE_T2',

5 event_condition=>'tab.user_data.object_owner=''SCOTT'' 

6 and tab.user_data.event_name=''give_me_an_event''',

7 queue_spec=>'SCOTT.E_Q',

8 job_class=>'DEFAULT_JOB_CLASS',

9 auto_drop=>FALSE,

10 enabled=>TRUE);

11  end;

12 /

PL/SQL 過程已成功完成。

8）發出事件

SQL>  declare

  2  aa dbms_aq.enqueue_options_t;

  3  bb dbms_aq.message_properties_t;

  4  cc raw(16);

  5  dd t_event_q;

  6  begin

  7  dd:=t_event_q('SCOTT','give_me_an_event');

  8  dbms_aq.enqueue(

  9  queue_name=>'e_q',

 10  enqueue_options=>aa,

 11  message_properties=>bb,

 12  payload=>dd,

 13  msgid=>cc);

 14  end;

 15  /

PL/SQL 過程已成功完成。

9）檢視結果

SQL> commit;

提交完成。

SQL> select * from event_t;

EVENT_

----------------------------------------

event has recevie