Oracle高级队列(Advanced Queue)简单实例_Oracle

Oracle高级队列(Advanced Queue)简单实例

2020-01-10 18:08lqh Oracle

这篇文章主要介绍了Oracle高级队列(Advanced Queue)简单实例的相关资料,需要的朋友可以参考下

Oracle高级队列(Advanced Queue)简单实例

最简单的高级队列的练习，基本上都使用了默认参数。

				?

									-- Create Type

									create or replace type note as Object(

									 subject varchar2(100),

									 Content varchar2(2000),CreateTime date

									);

									-- Create queue table

									begin

									 sys.dbms_aqadm.create_queue_table(

									  queue_table => 'NOTETAB',

									  queue_payload_type => 'TEST.NOTE'

									end;

									--create queue

									begin

									 sys.dbms_aqadm.create_queue(

									  queue_name => 'NOTEQ',

									  queue_table => 'NOTETAB',

									  queue_type => sys.dbms_aqadm.normal_queue,

									  max_retries => 5,

									  retry_delay => 0,

									  retention_time => 0);

									end;

									--enqueue

									declare

									v_Message note;

									v_MsgId RAW(16);

									v_options DBMS_AQ.ENQUEUE_OPTIONS_T;

									v_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

									v_Recipients DBMS_AQ.AQ$_RECIPIENT_LIST_T;

									begin

									   v_Message:=note(subject => 'note3',Content => 'content3',createTime => sysdate);

									--   v_Recipients(0) := sys.aq$_agent('NOTE','MTQ',0);

									--   v_properties.recipient_list := v_Recipients;

									   v_options.visibility :=DBMS_AQ.IMMEDIATE;

									   dbms_aq.enqueue(queue_name => 'noteq',enqueue_options => v_options,message_properties => v_properties,payload => v_Message,msgid => v_MsgId);

									   dbms_output.put_line('encode success,msgid is '||v_MsgId);

									end;

									--dequeue

									declare

									v_Message note;

									v_MsgId RAW(16);

									v_options DBMS_AQ.DEQUEUE_OPTIONS_T;

									v_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

									v_Recipients DBMS_AQ.AQ$_RECIPIENT_LIST_T;

									begin

									--   v_Recipients(0) := sys.aq$_agent('NOTE','MTQ',0);

									--   v_properties.recipient_list := v_Recipients;

									   v_options.visibility :=DBMS_AQ.IMMEDIATE;

									   dbms_aq.dequeue(queue_name => 'noteq',dequeue_options => v_options,message_properties => v_properties,payload => v_Message,msgid => v_MsgId);

									   dbms_output.put_line('decode success,msgid is '||v_MsgId);

									   dbms_output.put_line('subject is '||v_Message.subject);

									   dbms_output.put_line('Content is '||v_Message.Content);

									   dbms_output.put_line('createTime is '||to_char(v_Message.createTime,'yyyy-mm-dd hh24:mi:ss'));

									end;