某客户遇到一个高级队列方面的问题，由于自己本身也不熟悉，所以进行了简单的测试，
虽然目前高级队列用的非常少，但是该特性其实已经跟streams集成到一起了，回头会写
几篇关于streams方面的文章。
'###### Create user and grant ######'
SQL> create user aq identified by aq;
User created.
SQL> grant connect, resource, aq_administrator_role to aq;
Grant succeeded.
SQL> grant execute on dbms_aqadm to aq;
Grant succeeded.
SQL> grant execute on dbms_aq to aq;
Grant succeeded.
SQL> alter user aq default tablespace roger;
User altered.
SQL> connect aq/aq
Connected.
SQL> create sequence aq_sequence start with 1 increment by 1;
Sequence created.
'###### Create type and queue table ######'
SQL> create type message as object (
2 city VARCHAR2(30)
3 );
Type created.
SQL> BEGIN
2 DBMS_AQADM.create_queue_table (queue_table => 'input_queue_table',
3 sort_list => 'priority',
4 multiple_consumers => TRUE,
5 queue_payload_type => 'message',
6 COMMENT => 'Creating input queue table'
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
'###### Create queue ######'
SQL> BEGIN
2 DBMS_AQADM.create_queue (queue_name => 'input_queue',
3 queue_table => 'input_queue_table',
4 COMMENT => 'Demo Queue'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
'###### Start queue ######'
SQL> BEGIN
2 DBMS_AQADM.start_queue (queue_name => 'input_queue');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 subscriber SYS.aq$_agent;
3 BEGIN
4 subscriber := SYS.aq$_agent ('prog1', NULL, NULL);
5 DBMS_AQADM.add_subscriber (queue_name => 'input_queue',
6 subscriber => subscriber
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
'###### Create enqueue procedure ######'
SQL> CREATE OR REPLACE PROCEDURE demo_enqueue (userinfo MESSAGE)
2 AS
3 enq_msgid RAW (16);
4 eopt DBMS_AQ.enqueue_options_t;
5 mprop DBMS_AQ.message_properties_t;
6 priority NUMBER;
7 BEGIN
8 SELECT aq_sequence.NEXTVAL
9 INTO priority
10 FROM DUAL;
11
12 mprop.priority := priority;
13 DBMS_AQ.enqueue (queue_name => 'input_queue',
14 enqueue_options => eopt,
15 message_properties => mprop,
16 payload => userinfo,
17 msgid => enq_msgid
18 );
19 COMMIT;
20 END demo_enqueue;
21 /
Procedure created.
SQL> DECLARE
2 payload1 MESSAGE;
3 payload2 MESSAGE;
4 payload3 MESSAGE;
5 payload4 MESSAGE;
6 BEGIN
7 payload1 := MESSAGE ('BELMONT');
8 payload2 := MESSAGE ('REDWOOD SHORES');
9 payload3 := MESSAGE ('SUNNYVALE');
10 payload4 := MESSAGE ('BURLINGAME');
11 demo_enqueue (payload1);
12 demo_enqueue (payload2);
13 demo_enqueue (payload3);
14 demo_enqueue (payload4);
15 END;
16 /
PL/SQL procedure successfully completed.
'###### Create Dequeue procedure ######'
SQL> CREATE OR REPLACE PROCEDURE demo_dequeue (appname VARCHAR2)
2 AS
3 deq_msgid RAW (16);
4 dopt DBMS_AQ.dequeue_options_t;
5 mprop DBMS_AQ.message_properties_t;
6 payload MESSAGE;
7 BEGIN
8 dopt.consumer_name := appname;
9 dopt.WAIT := DBMS_AQ.no_wait;
10 dopt.navigation := DBMS_AQ.first_message;
11 DBMS_AQ.dequeue (queue_name => 'input_queue',
12 dequeue_options => dopt,
13 message_properties => mprop,
14 payload => payload,
15 msgid => deq_msgid
16 );
17 COMMIT;
18 END demo_dequeue;
19 /
Procedure created.
SQL> BEGIN
2 demo_dequeue('prog1');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> set lines 200
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 1 PROCESSED
INPUT_QUEUE 03-jan-2012 01:38:38 2 READY
INPUT_QUEUE 03-jan-2012 01:38:38 3 READY
INPUT_QUEUE 03-jan-2012 01:38:38 4 READY
SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
2 priority
3 FROM input_queue_table;
Q_NAME ENQ_TIME STEP_NO PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE 03-jan-2012 09:38:38 0 2
INPUT_QUEUE 03-jan-2012 09:38:38 0 3
INPUT_QUEUE 03-jan-2012 09:38:38 0 4
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 2 READY
INPUT_QUEUE 03-jan-2012 01:38:38 3 READY
INPUT_QUEUE 03-jan-2012 01:38:38 4 READY
SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 step_no, priority
3 FROM input_queue_table;
Q_NAME ENQ_TIME STEP_NO PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE 03-jan-2012 09:38:38 0 2
INPUT_QUEUE 03-jan-2012 09:38:38 0 3
INPUT_QUEUE 03-jan-2012 09:38:38 0 4
SQL> DECLARE
2 payload1 MESSAGE;
3 payload2 MESSAGE;
4 payload3 MESSAGE;
5 payload4 MESSAGE;
6 BEGIN
7 payload1 := MESSAGE ('BELMONT');
8 payload2 := MESSAGE ('REDWOOD SHORES');
9 payload3 := MESSAGE ('SUNNYVALE');
10 payload4 := MESSAGE ('BURLINGAME');
11 demo_enqueue (payload1);
12 demo_enqueue (payload2);
13 demo_enqueue (payload3);
14 demo_enqueue (payload4);
15 END;
16 /
PL/SQL procedure successfully completed.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 2 READY
INPUT_QUEUE 03-jan-2012 01:38:38 3 READY
INPUT_QUEUE 03-jan-2012 01:38:38 4 READY
INPUT_QUEUE 03-jan-2012 01:49:28 5 READY
INPUT_QUEUE 03-jan-2012 01:49:28 6 READY
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
7 rows selected.
SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
2 priority
3 FROM input_queue_table;
Q_NAME ENQ_TIME STEP_NO PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE 03-jan-2012 09:49:28 0 7
INPUT_QUEUE 03-jan-2012 09:38:38 0 2
INPUT_QUEUE 03-jan-2012 09:38:38 0 3
INPUT_QUEUE 03-jan-2012 09:38:38 0 4
INPUT_QUEUE 03-jan-2012 09:49:28 0 5
INPUT_QUEUE 03-jan-2012 09:49:28 0 6
INPUT_QUEUE 03-jan-2012 09:49:28 0 8
7 rows selected.
SQL> CREATE OR REPLACE PROCEDURE demo_dequeue (appname VARCHAR2)
2 AS
3 deq_msgid RAW (16);
4 dopt DBMS_AQ.dequeue_options_t;
5 mprop DBMS_AQ.message_properties_t;
6 payload MESSAGE;
7 BEGIN
8 dopt.consumer_name := appname;
9 dopt.WAIT := DBMS_AQ.no_wait;
10 dopt.navigation := DBMS_AQ.first_message;
11 DBMS_AQ.dequeue (queue_name => 'input_queue',
12 dequeue_options => dopt,
13 message_properties => mprop,
14 payload => payload,
15 msgid => deq_msgid
16 );
17 COMMIT;
18 END demo_dequeue;
19 /
Procedure created.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 3 READY
INPUT_QUEUE 03-jan-2012 01:38:38 4 READY
INPUT_QUEUE 03-jan-2012 01:49:28 5 READY
INPUT_QUEUE 03-jan-2012 01:49:28 6 READY
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
6 rows selected.
SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
2 priority
3 FROM input_queue_table;
Q_NAME ENQ_TIME STEP_NO PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE 03-jan-2012 09:49:28 0 7
INPUT_QUEUE 03-jan-2012 09:38:38 0 3
INPUT_QUEUE 03-jan-2012 09:38:38 0 4
INPUT_QUEUE 03-jan-2012 09:49:28 0 5
INPUT_QUEUE 03-jan-2012 09:49:28 0 6
INPUT_QUEUE 03-jan-2012 09:49:28 0 8
6 rows selected.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 4 READY
INPUT_QUEUE 03-jan-2012 01:49:28 5 READY
INPUT_QUEUE 03-jan-2012 01:49:28 6 READY
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
2 priority
3 FROM input_queue_table;
Q_NAME ENQ_TIME STEP_NO PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE 03-jan-2012 09:49:28 0 7
INPUT_QUEUE 03-jan-2012 09:38:38 0 4
INPUT_QUEUE 03-jan-2012 09:49:28 0 5
INPUT_QUEUE 03-jan-2012 09:49:28 0 6
INPUT_QUEUE 03-jan-2012 09:49:28 0 8
SQL> DECLARE
2 payload1 MESSAGE;
3 payload2 MESSAGE;
4 payload3 MESSAGE;
5 payload4 MESSAGE;
6 BEGIN
7 payload1 := MESSAGE ('BELMONT');
8 payload2 := MESSAGE ('REDWOOD SHORES');
9 payload3 := MESSAGE ('SUNNYVALE');
10 payload4 := MESSAGE ('BURLINGAME');
11 demo_enqueue (payload1);
12 demo_enqueue (payload2);
13 demo_enqueue (payload3);
14 demo_enqueue (payload4);
15 END;
16 /
PL/SQL procedure successfully completed.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 4 READY
INPUT_QUEUE 03-jan-2012 01:49:28 5 READY
INPUT_QUEUE 03-jan-2012 01:49:28 6 READY
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
9 rows selected.
SQL> BEGIN
2 demo_dequeue('prog1');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:38:38 4 PROCESSED
INPUT_QUEUE 03-jan-2012 01:49:28 5 PROCESSED
INPUT_QUEUE 03-jan-2012 01:49:28 6 READY
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
9 rows selected.
SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
2 priority
3 FROM input_queue_table;
Q_NAME ENQ_TIME STEP_NO PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE 03-jan-2012 09:49:28 0 7
INPUT_QUEUE 03-jan-2012 09:57:38 0 9
INPUT_QUEUE 03-jan-2012 09:57:38 0 11
INPUT_QUEUE 03-jan-2012 09:38:38 0 4
INPUT_QUEUE 03-jan-2012 09:49:28 0 5
INPUT_QUEUE 03-jan-2012 09:49:28 0 6
INPUT_QUEUE 03-jan-2012 09:49:28 0 8
INPUT_QUEUE 03-jan-2012 09:57:38 0 10
INPUT_QUEUE 03-jan-2012 09:57:38 0 12
9 rows selected.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:49:28 6 READY
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
7 rows selected.
SQL> BEGIN
2 demo_dequeue('prog1');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
2 msg_priority, msg_state
3 FROM aq$input_queue_table;
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:49:28 6 PROCESSED
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
7 rows selected.
SQL> /
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:49:28 6 PROCESSED
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
7 rows selected.
SQL> /
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:49:28 6 PROCESSED
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
7 rows selected.
SQL> /
QUEUE ENQ_TIME MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE 03-jan-2012 01:49:28 7 READY
INPUT_QUEUE 03-jan-2012 01:49:28 8 READY
INPUT_QUEUE 03-jan-2012 01:57:38 9 READY
INPUT_QUEUE 03-jan-2012 01:57:38 10 READY
INPUT_QUEUE 03-jan-2012 01:57:38 11 READY
INPUT_QUEUE 03-jan-2012 01:57:38 12 READY
6 rows selected.补充：可以用event 10960 去trace AQ，对于group级别的请参考如下mos文档。
Procedure to Dequeue Messages from any Queue not using Message Grouping [ID 243665.1]
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
