基于应用抛出事件的 Jobs

最新推荐文章于 2020-04-04 12:15:06 发布

jgmydsai

最新推荐文章于 2020-04-04 12:15:06 发布

阅读量535

点赞数

分类专栏： DBMS_SCHEDULER 文章标签： Job Events Raised Application

本文链接：https://blog.csdn.net/jgmydsai/article/details/38614285

版权

DBMS_SCHEDULER 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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 completed

SQL> select * from t_job_test;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-16 16:21:40  event_by_app_based_job
1 row selected

SQL> exec p_send_msg;
PL/SQL procedure successfully completed

SQL> select * from t_job_test;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-16 16:21:40  event_by_app_based_job
2014-08-16 16:22:07  event_by_app_based_job
2 rows selected