基于应用抛出事件的 Jobs

来源：互联网发布：聚合数据平台编辑：程序博客网时间：2024/06/11 22:32

define payload

CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (  事件  VARCHAR2(30));

创建队queue_table、queue，并启动queue

BEGIN  -- Create a queue table to hold the event queue.  DBMS_AQADM.create_queue_table(    queue_table        => 'event_queue_tab',    queue_payload_type => 't_event_queue_payload',    multiple_consumers => TRUE,    comment            => 'Queue Table For Event Messages');  -- Create the event queue.  DBMS_AQADM.create_queue (    queue_name  => 'event_by_app_queue',    queue_table => 'event_queue_tab');  -- Start the event queue.  DBMS_AQADM.start_queue (queue_name => 'event_by_app_queue');END;

注意：这儿使用了前面创建的类型：t_event_queue_payload

建立基于事件的job

BEGIN   DBMS_SCHEDULER.create_job (      job_name        => 'event_by_app_based_job',      job_type        => 'PLSQL_BLOCK',      job_action      => 'begin INSERT INTO t_job_test VALUES (SYSDATE, ''event_by_app_based_job''); end;',                                  start_date      => SYSTIMESTAMP,      event_condition => 'tab.user_data.事件 = ''天王盖地虎''',      queue_spec      => 'event_by_app_queue',      enabled         => TRUE);END;

建立proc来enqueue

CREATE OR REPLACE PROCEDURE p_send_msg AS  l_enqueue_options     DBMS_AQ.enqueue_options_t;  l_message_properties  DBMS_AQ.message_properties_t;  l_message_handle      RAW(16);  l_queue_msg           t_event_queue_payload;BEGIN  l_queue_msg := t_event_queue_payload('天王盖地虎');  DBMS_AQ.enqueue(queue_name          => 'event_by_app_queue',                  enqueue_options     => l_enqueue_options,                  message_properties  => l_message_properties,                  payload             => l_queue_msg,                  msgid               => l_message_handle);  COMMIT;END;

测试：

SQL> exec p_send_msg;PL/SQL procedure successfully completedSQL> select * from t_job_test;CREATED              DES-------------------- --------------------------------------------------2014-08-16 16:21:40  event_by_app_based_job1 row selectedSQL> exec p_send_msg;PL/SQL procedure successfully completedSQL> select * from t_job_test;CREATED              DES-------------------- --------------------------------------------------2014-08-16 16:21:40  event_by_app_based_job2014-08-16 16:22:07  event_by_app_based_job2 rows selected

0 0