二、Oracle AQ

1、创建用户并赋予权限。

--创建用户、并赋予权限
create user devuser identified by devuser;
grant execute on dbms_aq to devuser;
grant execute on dbms_aqadm to devuser;
grant dba to devuser;
BEGIN
  DBMS_AQADM.grant_system_privilege('ENQUEUE_ANY','devuser',false);
   DBMS_AQADM.grant_system_privilege('DEQUEUE_ANY','devuser',false);
END;

2、创建对象类型

create type demo_queue_payload_type as object
(message varchar2(2000));

3、创建队列表

BEGIN
  dbms_aqadm.create_queue_table(queue_table        =>'demo_queue_table',
                                queue_payload_type => 'demo_queue_payload_type');
END;                                

4、创建队列并启动队列

BEGIN
  dbms_aqadm.create_queue(queue_name  => 'demo_queue',
                          queue_table => 'demo_queue_table');

  dbms_aqadm.start_queue(queue_name => 'demo_queue');
END;

5、入队

DECLARE
  r_enqueue_options    dbms_aq.enqueue_options_t;
  r_message_properties dbms_aq.message_properties_t;
  v_message_handle     RAW(16);
  o_payload            demo_queue_payload_type;
BEGIN
  o_payload := demo_queue_payload_type('这是第四条消息');

  dbms_aq.enqueue(queue_name         =>'demo_queue',
                  enqueue_options    =>r_enqueue_options,
                  message_properties =>r_message_properties,
                  payload            =>o_payload,
                  msgid              =>v_message_handle);

  

  COMMIT;

END;

6、出队

DECLARE
  r_dequeue_options    dbms_aq.dequeue_options_t;
  r_message_properties dbms_aq.message_properties_t;
  v_message_handle     RAW(16);
  o_payload            demo_queue_payload_type;
BEGIN
  --r_dequeue_options.dequeue_mode := dbms_aq.browse;

  dbms_aq.dequeue(queue_name         =>'demo_queue',
                  dequeue_options    =>r_dequeue_options,
                  message_properties =>r_message_properties,
                  payload            =>o_payload,
                  msgid              =>v_message_handle);

 

  --dbms_output.put_line('***浏览的消息是' || o_payload.MESSAGE || '***');
  
  COMMIT;
END;