第67次：2014-11-18

botang · 发表于 2014-11-19 16:37:04

本帖最后由 botang 于 2014-11-27 16:04 编辑

2014-11-18-CHAIN.sql：

create or replace procedure proc05317
is
begin
update employees set salary=salary+0.1 where employee_id=100;
commit;
end;
select * from user_scheduler_schedules;
select * from scheduler_test;
drop table scheduler_test;
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)
);
select * from scheduler_test;
select scheduler_test_seq.nextval from dual;
drop sequence scheduler_test_seq;
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 => 'Program for first link in the chain.');
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 => 'Program for second link in the chain.');
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 => 'Program for last link in the chain.');
END;
BEGIN
DBMS_SCHEDULER.create_chain (
chain_name => 'test_chain_1',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => 'A test chain.');
END;
SELECT
chain_name,
rule_set_owner,
rule_set_name,
number_of_rules,
number_of_steps,
enabled,
comments
FROM user_scheduler_chains;
BEGIN
DBMS_SCHEDULER.define_chain_event_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_1',
event_schedule_name => 'HR.SCHEDULE2');
end;
begin
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_2',
program_name => 'test_program_1');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_3',
program_name => 'test_program_2');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_4',
program_name => 'test_program_3');
END;
----------------------------------------------------------------
BEGIN
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '1=1',
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 => 'START chain_step_4',
rule_name => 'chain_rule_4',
comments => 'Fourth link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_4 completed',
action => 'END',
rule_name => 'chain_rule_5',
comments => 'End of the chain.');
END;
----
BEGIN
DBMS_SCHEDULER.ENABLE ('test_chain_1');
END;
SELECT *
FROM user_scheduler_running_chains;
select * from scheduler_test;
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 t_event_queue_payload;
BEGIN
l_queue_msg := t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select * from hr.scheduler_test;
SELECT *
FROM dba_scheduler_running_chains;

复制代码

2014-11-18-COMPSCHEDULER.sql:

declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=>'hr.schedule4' ,
start_date => sysdate,
return_date_after => v_1,
next_run_date => v_1 );
dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
end loop;
end;
'FREQ=YEARLY;BYDATE=20180101;BYHOUR=14;BYMINUTE=10;BYSECOND=0;intersect=hr.schedule3'
declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=> 'FREQ=YEARLY;BYDATE=0201;BYHOUR=14;BYMINUTE=10;BYSECOND=0;include=hr.schedule3' ,
start_date => sysdate,
return_date_after => v_1,
next_run_date => v_1 );
dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
end loop;
end;
declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=> 'hr.scheduler5' ,
start_date => sysdate,
return_date_after => v_1,
next_run_date => v_1 );
dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
end loop;
end;

复制代码

2014-11-18-EMAIL.sql

select * from dba_scheduler_notifications;
begin
dbms_scheduler.set_scheduler_attribute('email_server','station90.example.com:25');
end;
begin
dbms_scheduler.set_scheduler_attribute('email_sender','oracle@example.com');
end;
grant execute on utl_smtp to hr;
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'hr.job9',
recipients => 'oracle@example.com',
events => 'job_started, job_succeeded');
END;
select * from dba_scheduler_notifications;
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'hr.job10',
recipients => 'oracle@example.com',
events => 'job_failed');
END;

复制代码