Bo's Oracle Station › 博客首页 ›Oracle技术› 开发和基础管理 › 查看内容

【活动演讲2015】“作为2015年Oracle技术嘉年华演讲加冰之一”：《Oracle Scheduler作业链规则编程在工控响应系统中的应用》和《我的DBA之路》

2015-11-4 10:21| 发布者: admin| 查看: 9359| 评论: 0|原作者: Bo Tang

摘要: Oracle Scheduler CHAIN，也就是作业链，是将一串需要完成的作业逻辑编程连在一起，根据每一个步骤完成的不同返回结果来确定下面的哪一个动作需要被完成。链的第一步一般是监听接收ADT的基于事件的schedule，其他步骤可能是基于事件的schedule或program。整个chain，可以设计多个exit，但每次特定执行，只会根据当次规则满足情况从一个出口退出。CHAIN的主要步骤包括定义程序(做什么)、定义CHAIN步骤(总步骤)，以及CHAIN的规则(如何做)。

Oracle Scheduler作业链规则编程在工控响应系统中的应用

Author: Bo Tang

1. 活动介绍：

2. “作为演讲加冰之一”：

http://otn.itpub.net/jiabin.html

3. 演讲材料：

3.1 演讲中的用到的唯一脚本：

下载

--SYS:
--查询队列
select * from dba_queues   q where q.QUEUE_TYPE='NORMAL_QUEUE' order by name ;
select * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE' order by name;
select * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
--创建类型
CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
event_name VARCHAR2(30)
);
grant execute on t_event_queue_payload to hr;
--创建队列
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_queue',
    queue_table => 'event_queue_tab');
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => 'event_queue');
END;
/
--再次查询队列
select * from dba_queues   q where q.QUEUE_TYPE='NORMAL_QUEUE' order by name;
select * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE' order by name;
select * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
--授权队列入队
begin
   dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
end;
/
--授权队列出队
begin
   dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
end;
/
--授权规则编写
BEGIN
DBMS_RULE_ADM.grant_system_privilege(
     privilege    => DBMS_RULE_ADM.create_rule_set_obj,
     grantee      => 'HR',
     grant_option => FALSE);

DBMS_RULE_ADM.grant_system_privilege(
     privilege    => DBMS_RULE_ADM.create_evaluation_context_obj,
     grantee      => 'HR',
     grant_option => FALSE);

DBMS_RULE_ADM.grant_system_privilege(
     privilege    => DBMS_RULE_ADM.create_rule_obj,
     grantee      => 'HR',
     grant_option => FALSE);
END;
/
--授权
grant execute on dbms_aq to hr;
grant create job to hr;

--HR:
--查询授权
select * from user_tab_privs;
select * from user_sys_privs;
--创建实验用表
CREATE TABLE scheduler_test (
id            NUMBER(10)    NOT NULL,
description   VARCHAR2(20) NOT NULL,
created_date DATE          NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
--创建实验用序列
CREATE SEQUENCE scheduler_test_seq nocache;
--创建实验用基于事件的Schedule
begin
   dbms_scheduler.create_event_schedule(schedule_name => 'Step1',
                                 queue_spec => 'SYS.event_queue',
                                 event_condition =>
                               'tab.user_data.event_name = ''give_me_a_prod''',
                                start_date      => SYSTIMESTAMP);
end;
/
--创建实验用Program
BEGIN
DBMS_SCHEDULER.create_program (
    program_name   => 'Step2',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         INSERT INTO scheduler_test (id, description, created_date)
                         VALUES (scheduler_test_seq.NEXTVAL, ''Step2'', SYSDATE);
                         COMMIT;
                       END;',
    enabled        => TRUE,
    comments       => 'Step2');

DBMS_SCHEDULER.create_program (
    program_name   => 'Step3',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         INSERT INTO scheduler_test (id, description, created_date)
                         VALUES (scheduler_test_seq.NEXTVAL, ''Step3'', SYSDATE);
                         COMMIT;
                       END;',
    enabled        => TRUE,
    comments       => 'Step3');

DBMS_SCHEDULER.create_program (
    program_name   => 'Step4',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         INSERT INTO scheduler_test (id, description, created_date)
                         VALUES (scheduler_test_seq.NEXTVAL, ''Step4'', SYSDATE);
                         COMMIT;
                       END;',
    enabled        => TRUE,
    comments       => 'Step4');
END;
/
--创建实验用Chain
begin
   dbms_scheduler.create_chain(chain_name => 'chain1');
end;
/
--定义Chain步骤
BEGIN
DBMS_SCHEDULER.define_chain_event_step(chain_name => 'Chain1',
                                step_name => 'Step1',
                                event_schedule_name => 'Step1');
end;
Begin
DBMS_SCHEDULER.define_chain_step(chain_name => 'Chain1',
                              step_name => 'Step2',
                              program_name => 'Step2');

    DBMS_SCHEDULER.define_chain_step(chain_name => 'Chain1',
                              step_name => 'Step3',
                              program_name => 'Step3');

    DBMS_SCHEDULER.define_chain_step(chain_name => 'Chain1',
                              step_name => 'Step4',
                              program_name => 'Step4');
end;
/
--第一种编程Chain规则
BEGIN
DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'TRUE',
    action     => 'START Step1',
    rule_name => 'Rule1',
    comments   => 'Rule1');

DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step1 completed',
    action     => 'START Step2',
    rule_name => 'Rule2',
    comments   => 'Rule2');

DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step2 completed',
    action     => 'START Step3',
    rule_name => 'Rule3',
    comments   => 'Rule3');

DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step3 completed',
    action     => 'START Step4',
    rule_name => 'Rule4',
    comments   => 'Rule4');

   DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step4 completed',
    action     => 'End',
    rule_name => 'Rule5',
    comments   => 'Rule5');
END;
/
--查询第一种编程Chain规则

SELECT owner,
       chain_name,
       rule_owner,
       rule_name,
       condition,
       action,
       comments
FROM   dba_scheduler_chain_rules；
查询chain
select * from dba_scheduler_chains;
--Enable Chain
begin
dbms_scheduler.enable(name => 'CHAIN1');
end;

--创建实验用作业job1
begin
   dbms_scheduler.create_job(job_name => 'JOB1',
                               job_type => 'CHAIN',
                                job_action => 'CHAIN1',
                                 start_date => systimestamp,
                                 enabled => true,
                                 repeat_interval => 'freq=minutely; bysecond=0; interval=2');
end;
/
--查看job1
select * from user_scheduler_jobs;
select * from user_scheduler_running_jobs;
--查看job1里的chain
SELECT owner,
       job_name,
       chain_owner,
       chain_name,
       step_name,
       state
FROM   dba_scheduler_running_chains
ORDER BY owner, job_name, chain_name, step_name;
--发消息
DECLARE
l_enqueue_options     DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle      RAW(16);
l_queue_msg           sys.t_event_queue_payload;
BEGIN
l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
                  enqueue_options     => l_enqueue_options,
                  message_properties => l_message_properties,
                  payload             => l_queue_msg,
                  msgid               => l_message_handle);
COMMIT;
END;
/
--查看结果
SELECT * FROM scheduler_test order by id;

--SYS:
--删除job1
begin
dbms_scheduler.stop_job(job_name => 'HR.JOB1',
                          force => true);
dbms_scheduler.drop_job(job_name => 'HR.JOB1');
end;
/

--HR:
--第二种编程Chain规则

begin
   dbms_scheduler.drop_chain_rule('CHAIN1','RULE1');
   dbms_scheduler.drop_chain_rule('CHAIN1','RULE2');
   dbms_scheduler.drop_chain_rule('CHAIN1','RULE3');
   dbms_scheduler.drop_chain_rule('CHAIN1','RULE4');
   dbms_scheduler.drop_chain_rule('CHAIN1','RULE5');
end;
/

BEGIN
DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'TRUE',
    action     => 'START Step1',
    rule_name => 'Rule1',
    comments   => 'Rule1');

DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step1 completed',
    action     => 'START Step2',
    rule_name => 'Rule2',
    comments   => 'Rule2');

DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step2 failed',
    action     => 'START Step3',
    rule_name => 'Rule3',
    comments   => 'Rule3');

DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step2 completed',
    action     => 'START Step4',
    rule_name => 'Rule4',
    comments   => 'Rule4');

   DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step4 completed',
    action     => 'End',
    rule_name => 'Rule5',
    comments   => 'Rule5');

   DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'Chain1',
    condition => 'Step3 completed',
    action     => 'End',
    rule_name => 'Rule6',
    comments   => 'Rule6');
END;
/
--查询第二种编程Chain规则

SELECT owner,
       chain_name,
       rule_owner,
       rule_name,
       condition,
       action,
       comments
FROM   dba_scheduler_chain_rules；
--查询chain
select * from dba_scheduler_chains;
--Enable Chain
begin
dbms_scheduler.enable(name => 'CHAIN1');
end;
/
--创建实验用作业job2
begin
   dbms_scheduler.create_job(job_name => 'JOB2',
                               job_type => 'CHAIN',
                                job_action => 'CHAIN1',
                                 start_date => systimestamp,
                                 enabled => true,
                                 repeat_interval => 'freq=minutely; bysecond=0; interval=2'

                                             );
end;
/
--查看job2
select * from user_scheduler_jobs;
select * from user_scheduler_running_jobs;
--查看job2里的chain
SELECT owner,
       job_name,
       chain_owner,
       chain_name,
       step_name,
       state
FROM   dba_scheduler_running_chains
ORDER BY owner, job_name, chain_name, step_name;
--发消息
DECLARE
l_enqueue_options     DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle      RAW(16);
l_queue_msg           sys.t_event_queue_payload;
BEGIN
l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
                  enqueue_options     => l_enqueue_options,
                  message_properties => l_message_properties,
                  payload             => l_queue_msg,
                  msgid               => l_message_handle);
COMMIT;
END;
/
--查看结果
SELECT * FROM scheduler_test order by id;

--SYS:
--删除job2
begin
dbms_scheduler.stop_job(job_name => 'HR.JOB2',
                          force => true);
dbms_scheduler.drop_job(job_name => 'HR.JOB2');
end;
/
--测试chain
BEGIN
DBMS_SCHEDULER.run_chain (
    chain_name    => 'Chain1',
    job_name      => 'Job3',
    start_steps   => 'Step3');
END;
/
SELECT * FROM scheduler_test order by id;

3.2 演讲的PPT：

Oracle Scheduler作业链规则编程在工控响应系统中的应用：

scheduler.pdf

我的DBA之路：

botang.pdf

4. 技术内容介绍：

Oracle Scheduler CHAIN，也就是作业链，是将一串需要完成的作业逻辑编程连在一起，根据每一个步骤完成的不同返回结果来确定下面的哪一个动作需要被完成。链的第一步一般是监听接收ADT的基于事件的schedule，其他步骤可能是基于事件的schedule或program。整个chain，可以设计多个exit，但每次特定执行，只会根据当次规则满足情况从一个出口退出。CHAIN的主要步骤包括定义程序(做什么)、定义CHAIN步骤(总步骤)，以及CHAIN的规则(如何做)。

5. 现场录屏：