grant aq_administrator_role to test;
begin
dbms_aqadm.grant_type_access('test');
end;
create or replace type messageobj as object
(
title varchar2(30),
data1 number,
data2 varchar2(100),
data3 date,
member procedure print(v_Message in varchar2)
);
create or replace type body MessageObj as
member procedure print(v_Message in varchar2) is
begin
dbms_output.put_line(v_Message || ':'||title);
dbms_output.put_line('Data 1:' || data1);
dbms_output.put_line('Data 2:' || data2);
dbms_output.put_line('Data 3:' || data3);
end print;
end;
-- prompt SimpleQ...
begin
--Create a simple table,with all of the defaults.This will allow
--FIFO queues,with no message grouping or multiple consumers.
dbms_aqadm.create_queue_table(
queue_table=>'SimpleQTab',
queue_payload_type=>'MessageObj',
comment=>'Simple Queue Table');
dbms_aqadm.create_queue(
queue_name=>'SimpleQ',
queue_table=>'SimpleQTab',
comment=>'Simple Queue');
--Enable enqueue and dequeue within operations simpleQ.
dbms_aqadm.create_queue(
queue_name=>'ExceptionQ',
queue_table=>'SimpleQTab',
queue_type=>dbms_aqadm.exception_queue,
comment=>'Exception Queue');
--Enable dequeue operations for exceptionQ.
dbms_aqadm.start_queue('ExceptionQ',False,true);
end;
begin
--create a priority queue table,by sqecitying the sort order.
--this queue has no message grouping or multiple consumers.
dbms_aqadm.create_queue_table(
queue_table=>'PriorityQTab',
queue_payload_type=>'MessageObj',
sort_list=>'priority,enq_time',
comment=>'Priority queue table');
dbms_aqadm.create_queue(
queue_name=>'PriorityQ',
queue_table=>'PriorityQTab',
comment=>'Priority Queue');
--Enable enqueue and dequeue operations for priorityq.
dbms_aqadm.start_queue('PriorityQ');
end;
--enqueue and dequeue opertion
declare
v_Message MessageObj;
v_EnqueueOptions dbms_aq.enqueue_options_t;
v_DequeueOptions dbms_aq.dequeue_options_t;
v_MessageProperties dbms_aq.message_properties_t;
v_msgid raw(16);
c_numMessages constant integer:=10;
e_QTimeOut exception;
pragma exception_init(e_QTimeOut,-25228);
begin
for v_Counter in 1..c_NumMessages loop
--Create a message to enqueue.
v_message := messageobj('Message'||v_counter,v_counter*10,'abcdefghijklmnopqrstuvwxyz',sysdate+v_counter);
--enqueue it with the default options.
dbms_aq.enqueue(
queue_name => 'SimpleQ',
enqueue_options => v_enqueueOptions,
message_properties => v_messageproperties,
payload =>v_message,
msgid =>v_msgid);
end loop;
--commit all the enqueue.
commit;
--loop until there are no more message to dequeue.
begin
loop
--dequeue the first message into v_message,waiting a maximum
--of 1 second.
v_dequeueOptions.wait :=1;
dbms_aq.dequeue(
queue_name =>'SimpleQ',
dequeue_options =>v_dequeueOptions,
message_properties =>v_messageProperties,
payload =>v_message,
msgid => v_msgid);
--and print it.
v_message.print('after dequeue');
end loop;
exception
when e_QTimeOut then
--end of the queue reached
null;
end;
--commit all the dequeue.
commit;
end;
输出结果:
after dequeue:Message1
Data 1:10
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:27-11月-07
after dequeue:Message2
Data 1:20
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:28-11月-07
after dequeue:Message3
Data 1:30
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:29-11月-07
after dequeue:Message4
Data 1:40
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:30-11月-07
after dequeue:Message5
Data 1:50
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:01-12月-07
after dequeue:Message6
Data 1:60
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:02-12月-07
after dequeue:Message7
Data 1:70
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:03-12月-07
after dequeue:Message8
Data 1:80
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:04-12月-07
after dequeue:Message9
Data 1:90
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:05-12月-07
after dequeue:Message10
Data 1:100
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:06-12月-07
--priority enqueue and dequeue opertion
declare
v_Message MessageObj;
v_EnqueueOptions dbms_aq.enqueue_options_t;
v_DequeueOptions dbms_aq.dequeue_options_t;
v_MessageProperties dbms_aq.message_properties_t;
v_msgid raw(16);
c_numMessages constant integer:=10;
e_QTimeOut exception;
pragma exception_init(e_QTimeOut,-25228);
begin
for v_Counter in 1..c_NumMessages loop
--Create a message to enqueue.
v_message := messageobj('Message'||v_counter,v_counter*10,'abcdefghijklmnopqrstuvwxyz',sysdate+v_counter);
--enqueue it with priority equal to v_counter.thus the last message to be enqueueed will have highest priority
v_MessageProperties.priority := -v_counter;
dbms_aq.enqueue(
queue_name => 'PriorityQ',
enqueue_options => v_enqueueOptions,
message_properties => v_messageproperties,
payload =>v_message,
msgid =>v_msgid);
end loop;
--commit all the enqueue.
commit;
--loop until there are no more message to dequeue.
begin
loop
--dequeue the first message into v_message,waiting a maximum
--of 1 second.
v_dequeueOptions.wait :=1;
dbms_aq.dequeue(
queue_name =>'PriorityQ',
dequeue_options =>v_dequeueOptions,
message_properties =>v_messageProperties,
payload =>v_message,
msgid => v_msgid);
--and print it.
v_message.print('after dequeue');
end loop;
exception
when e_QTimeOut then
--end of the queue reached
null;
end;
--commit all the dequeue.
commit;
end;
输出结果:
after dequeue:Message10
Data 1:100
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:06-12月-07
after dequeue:Message9
Data 1:90
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:05-12月-07
after dequeue:Message8
Data 1:80
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:04-12月-07
after dequeue:Message7
Data 1:70
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:03-12月-07
after dequeue:Message6
Data 1:60
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:02-12月-07
after dequeue:Message5
Data 1:50
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:01-12月-07
after dequeue:Message4
Data 1:40
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:30-11月-07
after dequeue:Message3
Data 1:30
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:29-11月-07
after dequeue:Message2
Data 1:20
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:28-11月-07
after dequeue:Message1
Data 1:10
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:27-11月-07
--簡單的enqueue和dequeue操作
DECLARE
v_Message MessageObj;
v_EnqueueOptions dbms_aq.enqueue_options_t;
v_DequeueOptions dbms_aq.dequeue_options_t;
v_messageProperties dbms_aq.message_properties_t;
v_MsgID RAW(16);
c_numMessages CONSTANT INTEGER :=10;
e_QTimeOut EXCEPTION;
PRAGMA EXCEPTION_INIT(e_QTimeOut,-25228);
BEGIN
FOR v_Counter IN 1..c_NumMessages LOOP
--Create a message to enqueue.
v_Message :=MessageObj('Message'||v_Counter,v_Counter*10,'abcdefghijklmnopqrstuvwxyz',SYSDATE+v_Counter);
--Enqueue it with the default options.
dbms_aq.enqueue(queue_name => 'SimpleQ',
enqueue_options => v_enqueueoptions,
message_properties => v_messageProperties,
payload => v_Message,
msgid => v_MsgID);
END LOOP;
--commit all the enqueue.
COMMIT;
--loop until there are no more message to dequeue.
BEGIN
LOOP
--dequeue the first message into v_message,waiting a maximum of 1 second.
v_DequeueOptions.WAIT := 1 ;
dbms_aq.dequeue(queue_name => 'SimpleQ',
dequeue_options => v_DequeueOptions,
message_properties => v_messageProperties,
payload => v_message,
msgid => v_MsgID);
--and print it.
v_message.print('after dequeue');
END LOOP;
EXCEPTION
WHEN e_QTimeOut THEN
--end of the queue reached
NULL;
END;
--commit all the dequeue.
COMMIT;
END;
輸出結果
after dequeue:Message1
Data 1:10
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:02-12月-07
after dequeue:Message2
Data 1:20
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:03-12月-07
after dequeue:Message3
Data 1:30
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:04-12月-07
after dequeue:Message4
Data 1:40
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:05-12月-07
after dequeue:Message5
Data 1:50
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:06-12月-07
after dequeue:Message6
Data 1:60
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:07-12月-07
after dequeue:Message7
Data 1:70
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:08-12月-07
after dequeue:Message8
Data 1:80
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:09-12月-07
after dequeue:Message9
Data 1:90
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:10-12月-07
after dequeue:Message10
Data 1:100
Data 2:abcdefghijklmnopqrstuvwxyz
Data 3:11-12月-07