第76次：2014-12-22

botang · 发表于 2014-12-24 11:36:08

本帖最后由 botang 于 2014-12-30 12:44 编辑

2014-12-22-A.sql:

CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
event_name VARCHAR2(30)
);
select * from dba_objects o where o.object_name=upper('t_event_queue_payload');
select * from dba_tab_privs tp where tp.grantee='HR';
grant execute on t_event_queue_payload to hr;
select * from dba_tab_privs tp where tp.grantee='HR';
----------------------------
select * from dba_queues ;
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 ;
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;
----HR
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_date DATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
drop sequence hr.scheduler_test_seq;
CREATE SEQUENCE hr.scheduler_test_seq order ;
----
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'event_based_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
INSERT INTO scheduler_test (id, created_date)
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
COMMIT;
END;',
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => 'event_queue',
enabled => TRUE);
END;
----
----SYS
SELECT * FROM hr.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_job_run_details d where d.job_name='JOB5';

复制代码

2014-12-22-B.sql

SELECT * FROM hr.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;
begin
DBMS_SCHEDULER.SET_ATTRIBUTE('HR.JOB4', 'raise_events', DBMS_SCHEDULER.JOB_SUCCEEDED);
end;
select j.raise_events from dba_scheduler_jobs j where j.job_name='JOB4';
declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=> 'hr.schedule5' ,
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=> 'FREQ=YEARLY;BYDATE=0101,0404;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=hr.schedule5' ,
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;
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=YEARLY;BYDATE=0101,0404;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=hr.schedule5',
start_date => systimestamp at time zone '+8:00',
comments => 'schedule_comphc',
schedule_name => 'HR.SCHEDULE6');
END;
declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=> 'hr.schedule6' ,
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;

复制代码