Oracle AQ是Oracle数据库中集成的一种消息队列机制,可以用于不同应用程序间的消息交互。
下面讲如何具体的实现。
1、为用户赋权
GRANT RESOURCE TO datatest;
GRANT CONNECT TO datatest;
GRANT EXECUTE ANY PROCEDURE TO datatest;
GRANT aq_administrator_role TO datatest;
GRANT aq_user_role TO datatest;
GRANT EXECUTE ON dbms_aqadm TO datatest;
GRANT EXECUTE ON dbms_aq TO datatest;
GRANT EXECUTE ON dbms_aqin TO datatest;
2、创建一个payload类型
payload数据载体,可以理解为运送货物的货车。出队列的数据都从这个创建的类型进行获取。
create or replace TYPE data_queue_payload_type AS OBJECT
( /* TODO enter attribute and method declarations here */
ctype number,pid varchar2(200),ctime VARCHAR2(30)
)
3、创建队列表、队列、启动队列
create or replace PROCEDURE CREATE_DATA_AQ AS
BEGIN
----创建队列表以允许多个消费者(consumers)
DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table => 'data_queue_table',
queue_payload_type => 'data_queue_payload_type',
multiple_consumers => TRUE
);
----创建队列
DBMS_AQADM.CREATE_QUEUE (
queue_name => 'data_queue',
queue_table => 'data_queue_table'
);
---启动队列
DBMS_AQADM.START_QUEUE (
queue_name => 'data_queue'
);
END CREATE_DATA_AQ;
4、消息入队列
本人在实际应用中使用的是触发器来进行入队列的操作,所以不需进行手动commit,如果是单独进行调用需要手动进行commit操作;
PROCEDURE input_queue(ctype number,pid varchar2,ctime VARCHAR2) AS
r_enqueue_options dbms_aq.enqueue_options_t;
r_message_properties dbms_aq.message_properties_t;
v_message_handle RAW(16);
o_payload data_queue_payload_type;
BEGIN
-- TODO: PROCEDURE DATA_AQ_DEAL.input_queue所需的实施
--DBMS_OUTPUT.PUT_LINE(ctype||','||pid||','||ctime);
o_payload := data_queue_payload_type(ctype,pid,ctime);
dbms_aq.enqueue(queue_name => 'data_queue', enqueue_options => r_enqueue_options, message_properties => r_message_properties
, payload => o_payload, msgid => v_message_handle);
--commit;
END input_queue;
5、消息出队列
PROCEDURE data_queue_callback_procedure (
context RAW,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload RAW,
payloadl NUMBER
) AS
r_dequeue_options dbms_aq.dequeue_options_t;
r_message_properties dbms_aq.message_properties_t;
v_message_handle RAW(16);
o_payload data_queue_payload_type;
BEGIN
-- TODO: PROCEDURE DATA_AQ_DEAL.data_queue_callback_procedure所需的实施
r_dequeue_options.msgid := descr.msg_id;
r_dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(queue_name => descr.queue_name, dequeue_options => r_dequeue_options, message_properties => r_message_properties
, payload => o_payload, msgid => v_message_handle);
---消息体 o_payload.ctype,o_payload.pid,o_payload.ctime
--消息处理程序
data_deal.deal_data(o_payload.ctype, o_payload.pid, o_payload.ctime);
END data_queue_callback_procedure;
6、通知
向队列增加一个订阅者(subsriber)并注册订阅者接到通知时的动作,这里接到通知时通过存储过程进行处理。
如下操作完成后,队列data_queue的consumer_name即为data_queue_subscriber
create or replace PROCEDURE DATA_SUBSCRIPT_AQ AS
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER (
queue_name => 'data_queue',
subscriber => SYS.AQ$_AGENT(
'data_queue_subscriber',
NULL,
NULL )
);
DBMS_AQ.REGISTER (
SYS.AQ$_REG_INFO_LIST(
SYS.AQ$_REG_INFO(
'data_queue:data_queue_subscriber',
DBMS_AQ.NAMESPACE_AQ,
'plsql://DATA_AQ_DEAL.data_queue_callback_procedure',
HEXTORAW('FF')
)
),
1
);
END DATA_SUBSCRIPT_AQ;