Oracle10g下面AQ(高级队列)的测试

以前学习Oracle10g下面高级队列的测试例子，记下。

 

这里假设一个简单的需求, 某单位有三种角色, Developer, Leader, PM, 消息也分三种类型, normal, urgent, secret. 不同角色仅仅关心某些信息,关系矩阵如下

	Normal	Urgent	Secret
Dev	X	X
Leader		X	X
PM			X

下面我写了几个demo来实现这个需求, 流程如下, 很简单

1. 建立消息载体, 这里使用object
2. 建立queue table和queue
3. 建立 subscriber (即consumer)
4. 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;
/