job3/job4

botang · 发表于 2017-10-14 17:15:07

job复杂时间类型：

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'HR.SCHEDULE3'
,repeat_interval =>'freq=YEARLY;bydate=20171021,20171117,20171231'
,comments => 'schedule3'
);
END;
----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'HR.SCHEDULE31'
,repeat_interval =>'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
,comments => 'SCHEDULE31'
);
END;
---
declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=>'freq=YEARLY;bydate=1021,1117,1231;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31' ,
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
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'HR.SCHEDULE32'
,repeat_interval =>'freq=YEARLY;bydate=1021,1117,1231;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31'
,comments => 'schedule32'
);
END;
---
declare
v_1 timestamp;
begin
for i in 1..10
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=>'HR.SCHEDULE32' ,
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;

复制代码

sys的窗口 job4:

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)
);
---
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');
----
select * from dba_tab_privs p where p.grantee='HR';
----
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;
----
select * from dba_tab_privs p where p.grantee='HR';
-----

复制代码

hr的窗口job4：

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;

复制代码

grant execute on sys.t_event_queue_payload to hr;

SELECT * FROM scheduler_test order by id;

---
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;