基于事件的schedule

botang · 发表于 2020-12-6 15:01:44

--SYS:
队列
select * from dba_queues q where q.QUEUE_TYPE='NORMAL_QUEUE' ;
select * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
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;
需要AQ_ADMINISTRATOR_ROLE
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' ;
select * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
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;
select * from dba_tab_privs p where p.grantee='HR';
grant execute on dbms_aq to hr;
grant create job to hr;
--HR:
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_date DATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
select * from scheduler_test;
CREATE SEQUENCE scheduler_test_seq;

复制代码

BEGIN
sys.dbms_scheduler.create_event_schedule(
event_condition => 'tab.user_data.event_name=''give_me_a_prod''',
queue_spec => '"SYS"."EVENT_QUEUE"',
start_date => systimestamp at time zone 'Asia/Shanghai',
schedule_name => '"HR"."SCHEDULE4"');
END;

复制代码

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"HR"."PROGRAM4"',
program_action=>'BEGIN
INSERT INTO scheduler_test (id, created_date)
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
COMMIT;
END;',
program_type=>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'',
enabled=>TRUE);
END;

复制代码

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB4"',
program_name => '"HR"."PROGRAM4"',
schedule_name => '"HR"."SCHEDULE4"',
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => TRUE);
END;

复制代码

显式打ADT信号:

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;

复制代码

--------------------------------
job chain:

--hr:
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
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;
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_program_1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step2');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_2',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step3');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_3',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step4');
END;
--SYS
em做
如果HR本人，需要以下权限
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;
另外：
analyze_chain：
dbms_scheduler.analyze_chain(chain_name => ,rules => ,steps => ,step_pairs => );
evaluate_running_chain：
dbms_scheduler.evaluate_running_chain(job_name => );
规则制定：
每一条规则都要有“condition”和“action”。
如果condition为TRUE, the action执行。 Conditions are usually based on the outcome of one or more previous steps.
A condition accepts Boolean and numeric integer values in an expression. The entire expression must evaluate to a Boolean value.
The simplified syntax of a chain condition is as follows:
'factor|NOT(factor)[AND|OR factor]'
factor:
stepname ERROR_CODE number|[NOT]step_condition
When creating a rule condition using the simplified syntax:
You specify one or more factors, and a Boolean operator (AND, OR, or NOT).
A factor can be either a simple Boolean value (TRUE or FALSE) or a chain condition. A chain condition describes the condition of another step in the job chain. You can use the following to describe the chain condition:
The current state of the chain step:
SUCCEEDED
FAILED
STOPPED
COMPLETED
The error code returned by the chain step. The error is a numeric value, and can be:
Evaluated within a numeric clause
Compared to a list of values using an IN clause
You can use negative factors, by enclosing the factor in parentheses and prefixing the factor with the NOT operator.
Examples:
'step1 SUCCEEDED AND step2 ERROR_CODE = 3'
'TRUE'
'step3 NOT COMPLETED AND NOT (step1 SUCCEEDED)'
'step2 ERROR_CODE NOT IN (1,2,3)'
You can also refer to attributes of chain steps of the chain (this is called bind-variable syntax). The syntax is as follows:
STEP_NAME.ATTRIBUTE
Possible attributes are: completed, state, start_date, end_date, error_code, and duration.
Possible values for the state attribute include: 'NOT_STARTED', 'SCHEDULED', 'RUNNING', 'PAUSED', 'SUCCEEDED', 'FAILED', and 'STOPPED'.
If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE'; otherwise, completed is 'FALSE'.
Some examples of the bind variable syntax are:
':step1.state=''SUCCEEDED'' and :step2.error_code=3'
'1=1'
':step3.state != ''COMPLETED'''
':step2.error_code not in (1,2,3)'
':step1.state = ''NOT_STARTED'''
The rule action specifies what is to be done as a result of the rule being triggered. A typical action is to run a specified step. Possible actions include:
START step_1[,step_2...]
STOP step_1[,step_2...]
END [{end_value | step_name.error_code}]
When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. You can also configure rules to be evaluated at regular intervals by using the EVALUATION_INTERVAL attribute of the chain.
You add a rule to a chain with the DEFINE_CHAIN_RULE procedure:
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'TRUE', -- starting step
ACTION => 'START load_data_evt,stop_when_disk_full_evt',
Rule_Name => 'dataload_rule1',
COMMENTS => 'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'load_data_evt COMPLETED',
ACTION => 'START do_bulk_load',
RULE_NAME => 'dataload_rule2');
END;
又比如：
BEGIN
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'TRUE',
action => 'START chain_step_1',
rule_name => 'chain_rule_1',
comments => 'First link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_1 completed',
action => 'START chain_step_2',
rule_name => 'chain_rule_2',
comments => 'Second link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_2 completed',
action => 'START chain_step_3',
rule_name => 'chain_rule_3',
comments => 'Third link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_3 completed',
action => 'END',
rule_name => 'chain_rule_4',
comments => 'End of the chain.');
END;
/
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN rule_owner FORMAT A10
COLUMN rule_name FORMAT A15
COLUMN condition FORMAT A25
COLUMN action FORMAT A20
COLUMN comments FORMAT A25
SELECT owner,
chain_name,
rule_owner,
rule_name,
condition,
action,
comments
FROM dba_scheduler_chain_rules
ORDER BY owner, chain_name, rule_owner, rule_name;
/
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN job_name FORMAT A20
COLUMN chain_owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN step_name FORMAT A25
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;
BEGIN
DBMS_SCHEDULER.run_chain (
chain_name => 'test_chain_1',
job_name => 'test_chain_1_run_job',
start_steps => 'chain_step_1, chain_step_2, chain_step_3');
END;
/
/*
EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');
EXEC DBMS_SCHEDULER.drop_chain (chain_name => 'test_chain_1');
EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_1');
EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_2');
EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_3');
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
PURGE RECYCLEBIN;
*/
--hr:
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;

复制代码