以前学习Oracle10g下面高级队列的测试例子，记下。
这里假设一个简单的需求, 某单位有三种角色, Developer, Leader, PM, 消息也分三种类型, normal, urgent, secret. 不同角色仅仅关心某些信息,关系矩阵如下
|
Normal |
Urgent |
Secret |
|
|
Dev |
X |
X |
|
|
Leader |
|
X |
X |
|
PM |
|
|
X |
下面我写了几个demo来实现这个需求, 流程如下, 很简单
- 建立消息载体, 这里使用object
- 建立queue table和queue
- 建立 subscriber (即consumer)
- Enqueue, dequeue
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
首先需要特定的权限
------------------------------------------------
-- needed privileges
grant aq_administrator_role to t2_1_12_1_d;
grant execute on dbms_aqadmin to t2_1_12_1_d;
grant execute on dbms_aq to t2_1_12_1_d;
用一个Object来保存event消息. AQ还支持文本消息和LOB类型的消息,这里只用object来做demo
------------------------------------------------
-- object which holds event message
create or replace type evt_msg_typ as object
(
evt_time varchar2(50),
evt_type varchar2(10),
evt_content varchar2(1000)
);
------------------------------------------------
-- clean up queue if exists
declare
l_qt varchar2(30):= 'evt_qt';
l_q varchar2(30):='evt_q';
begin
dbms_aqadm.stop_queue(queue_name => l_q);
dbms_aqadm.drop_queue(queue_name => l_q);
dbms_aqadm.drop_queue_table(queue_table => l_qt);
end;
/
建立一个queue table,然后在上面建立一个queue
我们需要一个支持多消费者的队列,所以multiple_consumers => true
(如果一个消息需要被多个消费者获取,必须使用这个参数)
------------------------------------------------
-- create queue table and queue
declare
l_qt varchar2(30) := 'evt_qt';
l_q varchar2(30) := 'evt_q';
l_payload_typ varchar2(30) := 'evt_msg_typ';
begin
dbms_aqadm.create_queue_table(queue_table => l_qt,
queue_payload_type => l_payload_typ,
multiple_consumers => true);
dbms_aqadm.create_queue(queue_name => l_q,queue_table => l_qt);
dbms_aqadm.start_queue(queue_name => l_q);
end;
/
根据不同需求建立订阅关系, 实现了需求中的矩阵
------------------------------------------------
-- add subscribers
declare
l_q varchar2(30) := 'evt_q';
l_dev sys.aq$_agent := sys.aq$_agent('dev', null, null);
l_ldr sys.aq$_agent := sys.aq$_agent('ldr', null, null);
l_pm sys.aq$_agent := sys.aq$_agent('pm', null, null);
begin
-- remove them
begin
dbms_aqadm.remove_subscriber(queue_name => l_q, subscriber => l_dev);
dbms_aqadm.remove_subscriber(queue_name => l_q, subscriber => l_ldr);
dbms_aqadm.remove_subscriber(queue_name => l_q, subscriber => l_pm);
exception
when others then
null;
end;
dev无条件收取所有消息(仅仅是demo,实际也是应该设置条件)
-- developer receives all events (not including those with specified recipents)
dbms_aqadm.add_subscriber(queue_name => l_q, subscriber => l_dev);
ldr需要关心urgent和secret类型的消息, 所以设置了一个基于消息类型内容的规则
-- leader is a rule-based subscriber, who concerns URGENT and SECRET
dbms_aqadm.add_subscriber(queue_name => l_q,
subscriber => l_ldr,
rule => 'tab.user_data.evt_type in (''SECRET'',''URGENT'')');
pm也设置了一个rule, 仅仅关心secret
-- pm is a rule-based subscriber, who just concerns SECRET
dbms_aqadm.add_subscriber(queue_name => l_q,
subscriber => l_pm,
rule => 'tab.user_data.evt_type in (''SECRET'')');
end;
/
生成消息, 并且入队
------------------------------------------------
-- enqueue different events
declare
l_q varchar2(30) := 'evt_q';
l_opt dbms_aq.enqueue_options_t;
l_prop dbms_aq.message_properties_t;
l_msgid raw(16);
l_payload evt_msg_typ;
l_rcpt_list dbms_aq.aq$_recipient_list_t;
begin
-- normal event
l_payload := evt_msg_typ(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
'NORMAL',
'normal');
dbms_aq.enqueue(queue_name => l_q,
enqueue_options => l_opt,
message_properties => l_prop,
payload => l_payload,
msgid => l_msgid);
-- urgent event
l_payload := evt_msg_typ(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
'URGENT',
'urgent');
dbms_aq.enqueue(queue_name => l_q,
enqueue_options => l_opt,
message_properties => l_prop,
payload => l_payload,
msgid => l_msgid);
这里有个特殊处理:
因为dev没有设置条件,所以dev可以收取secret消息. 并且ldr也可以收取secret
但是我只想pm收到这个消息,所以这消息入队的时候指定了recipient
-- top secret event only visible to PM, by using speicified recipients
l_payload := evt_msg_typ(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
'SECRET',
'top secret');
l_rcpt_list(0) := sys.aq$_agent('pm', null, null);
l_prop.recipient_list := l_rcpt_list;
dbms_aq.enqueue(queue_name => l_q,
enqueue_options => l_opt,
message_properties => l_prop,
payload => l_payload,
msgid => l_msgid);
commit;
end;
/
这段代码收取一个指定Consumer的所有消息. 这是一个主动获取的过程(Pull), 订阅者并没有得到通知
------------------------------------------------
-- dequeue by a consumer
declare
l_q varchar2(30) := 'evt_q';
l_opt dbms_aq.dequeue_options_t;
l_prop dbms_aq.message_properties_t;
l_msgid raw(16);
l_payload evt_msg_typ;
l_consumer varchar2(30) := 'pm';
l_more boolean := true;
no_messages exception;
pragma exception_init(no_messages, -25228);
begin
l_opt.consumer_name := l_consumer;
l_opt.navigation := dbms_aq.first_message;
l_opt.wait := dbms_aq.no_wait;
while (l_more) loop
begin
dbms_aq.dequeue(queue_name => l_q,
dequeue_options => l_opt,
message_properties => l_prop,
payload => l_payload,
msgid => l_msgid);
commit;
dbms_output.put_line(l_payload.evt_time ||' ' ||l_payload.evt_type);
l_opt.navigation := dbms_aq.next_message;
exception
when no_messages then
l_more := false;
end;
end loop;
end;
/
在实际运行中,我们更多的需要Push模式, 即当特定消息发生的时候, 订阅者得到通知. Oracle AQ提供了三种类型的通知: HTTP, Email和PL/SQL
我们用PL/SQL来进行demo, 当消息发生的时候, 用一个sp来自动处理
-------------------------------------------------------------------
-- used to demo pub/sub and notification
-------------------------------------------------------------------
drop table pm_evt;
create table pm_evt(occur_time varchar2(50),notify_time varchar2(50), msg varchar2(1000));
truncate table pm_evt;
select * from pm_evt;
这就是用来处理事务的callback, 他使用特定的接口,不要试图改参数名称
得到消息通知把内容放到特定表里面做一个log
-- callback sp
create or replace procedure plsqlnotif
(
context raw,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload raw,
payloadl number
) as
l_opt DBMS_AQ.dequeue_options_t;
l_prop DBMS_AQ.message_properties_t;
l_msgid raw(16);
l_payload evt_msg_typ;
begin
从自动通知的描述参数可以得到consumer name等信息
在mulit consumer模式中, 必须指定consumer name才能dequeue
-- get the consumer name and message id from the descriptor
l_opt.msgid := descr.msg_id;
l_opt.consumer_name := descr.consumer_name;
-- dequeue the message
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => l_opt,
message_properties => l_prop,
payload => l_payload,
msgid => l_msgid);
-- audit it
insert into pm_evt
(occur_time, notify_time, msg)
values
(l_payload.evt_time,
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
l_payload.evt_type || ' ' || l_payload.evt_content);
commit;
end;
/
使用刚才我们写的sp进行注册,绑定了 queue/subscriber/callback 的关系
---- register for notification
declare
l_q varchar2(30) := 'evt_q';
l_pm varchar2(10) := 'pm';
l_sp varchar2(30) := 'plsqlnotif';
l_reginfolist sys.aq$_reg_info_list;
begin
l_reginfolist := sys.aq$_reg_info_list(sys.aq$_reg_info(l_q || ':' ||l_pm,
DBMS_AQ.NAMESPACE_AQ,
'plsql://' || l_sp,
null));
dbms_aq.register(l_reginfolist, 1);
end;
/