oracle订阅发布,Oracle Advanced Queuing 之03(订阅&通知)

最新推荐文章于 2022-12-15 11:26:11 发布

DigitalElusion

最新推荐文章于 2022-12-15 11:26:11 发布

阅读量364

点赞数

文章标签： oracle订阅发布

本文详细介绍了如何在Oracle Advanced Queuing (AQ)中创建队列、队列表，启动队列，并配置订阅及回调。首先创建了一个队列表和测试队列，然后启动了队列并赋予用户权限。接着，定义了订阅者和注册了回调过程，实现了消息的入队。最后，验证了回调过程成功处理了消息，展示了队列中消息的状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.創建隊列及隊列表且启动之 aq_admin login

begin

--Create a table for queues

dbms_aqadm.create_queue_table (queue_table=>'aq_msg_qtab2',queue_payload_type=>'AQ_ADMIN.AQ_MSG_TYPE',multiple_consumers => true);

--Create a test queue

dbms_aqadm.create_queue (queue_name => 'aq_msg_queue2', queue_table => 'aq_msg_qtab2');

--Start the queue

dbms_aqadm.start_queue (queue_name => 'aq_msg_queue2');

end;

begin

dbms_aqadm.grant_queue_privilege (privilege => 'ALL',queue_name => 'AQ_ADMIN.AQ_MSG_QUEUE2',grantee => 'AQ_USER',grant_option => FALSE);

end;

2.创建订阅&注册回调(回调过程的创建参照上节) aq_admin login

begin

-- Configure the demo subscriber.

dbms_aqadm.add_subscriber (queue_name => 'aq_msg_queue2',subscriber => sys.aq$_agent(name => 'subscriber_xag',address => null,protocol => 0));

-- Register the procedure for dequeuing the messages received.

dbms_aq.register(

sys.aq$_reg_info_list(

sys.aq$_reg_info('AQ_ADMIN.AQ_MSG_QUEUE2:SUBSCRIBER_XAG',dbms_aq.namespace_aq,

'plsql://AQ_USER.CALL_BACK_PCK.AQ_MSG_QUEUE1',hextoraw('FF'))

1);

end;

3.入队 aq_user login

select count(1) from aq_admin.aq_msg_qtab2;

-------------------------------------------------------

declare

v_queue_name varchar2(50);

v_enqueue_options dbms_aq.enqueue_options_t;

v_message_properties dbms_aq.message_properties_t;

v_message_handle raw(16);

v_aq_msg aq_admin.aq_msg_type;

begin

v_queue_name:='aq_admin.aq_msg_queue2';

v_aq_msg := aq_admin.aq_msg_type (3,'none','none','a','content 3',sysdate);

dbms_aq.enqueue(queue_name => v_queue_name,enqueue_options => v_enqueue_options,message_properties => v_message_properties,payload => v_aq_msg,msgid => v_message_handle);

commit;

end;

select count(1) from aq_admin.aq_msg_qtab2;

-------------------------------------------------------

4.验证回调结果

select t.queue_name,t.received_time,t.consumer_name,t.content.msg_content,t.content.msg_create_time

from aq_msg_received t where 'content 3'=t.content.msg_content

-----------------------------------------------------------------------------------

QUEUE_NAME RECEIVED_TIME CONSUMER_NAMECONTENT.MSG_CONTENTCONTENT.MSG_CREATE_TIME

"AQ_ADMIN"."AQ_MSG_QUEUE2"2020/11/11 23:16:38SUBSCRIBER_XAGcontent 3 2020/11/11 23:16:38