ORACLE HANDBOOK系列之十二：進階隊列（Advanced Queuing）

Oracle Streams Advanced Queuing (AQ) is a robust and feature-rich message queuing system integrated with Oracle Database. When an organization has different systems that must communicate with each other, a messaging environment can provide a standard, reliable way to transport critical information between these systems. AQ is implemented in database tables.

Oracle AQ是Oracle資料庫中整合的一種訊息佇列機制，可以用於不同應用程式間的訊息互動，例如PL/SQL可以通過相應的Package訪問隊列、C#應該程式可以通過ODP.NET訪問隊列、Java應用程式則可以通過OJMS訪問隊列。AQ內部是通過資料庫表實現的（即訊息實際上是儲存在資料庫表中）。

隨筆展示了PL/SQL中使用AQ的基本用法，希望對各位有協助。樣本假設了一個情境：A是一個被頻繁調用的預存程序，每次調用A之前需要調用過程B，B消耗大量的時間，假設A的執行並不依賴於B的執行結果，我們可以把調用B的上下文先存入AQ中，而後非同步地進行處理，從而減小了B對應用程式效能的影響。

1.    建立AQ所需要的許可權

GRANT EXECUTE ON DBMS_AQ TO user1;
GRANT EXECUTE ON DBMS_AQADM TO user1;
BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('ENQUEUE_ANY', 'user1', FALSE);
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('DEQUEUE_ANY', 'user1', FALSE);
END;

需要使用sys或system使用者發起這些授權語句，欲建立及管理AQ，需要獲得兩個至關重要的包dbms_aq, dbms_aqadm的執行許可權。後兩個通過grant_system_privilege進行的授權是可選的，它們表示的是：

ENQUEUE_ANY means users granted this privilege are allowed to enqueue messages to any queues in the database. DEQUEUE_ANY means users granted this privilege are allowed to dequeue messages from any queues in the database.

2.    建立一個payload類型

CREATE OR REPLACE TYPE t_spl_queue_payload AS OBJECT
(
  ID             CHAR(36),
  EXEC_DATE     TIMESTAMP(6),
  PARAMETER1       NUMBER,
  PARAMETER2       VARCHAR2(500),
  FLAG           CHAR(1)
);

通常我們會定義一個對象，用於儲存將來需要放置在AQ隊列中的資訊。

3.    建立AQ相關表

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table        => 'user1. Spl_queue_table',
                                multiple_consumers => TRUE,
                                queue_payload_type => 'user1.t_spl_queue_payload');
END;

執行的結果是產生了表Spl_queue_table，以及若干個aq$_ spl_queue_table_表。表Spl_queue_table中除了AQ隊列自身需要的一些欄位外，有一個類型為t_spl_queue_payload的USER_DATA欄位，用於儲存隊列訊息，這也印證了上面說的：AQ內部是通過資料庫表實現的。

4.    建立及啟動AQ

BEGIN
  DBMS_AQADM.CREATE_QUEUE(queue_name  => 'user1.spl_aq',
                          queue_table => 'user1.spl_queue_table');
END;
--
BEGIN
  DBMS_AQADM.START_QUEUE(queue_name => 'user1.spl_aq');
END;

如何停止及刪除AQ：

BEGIN
  DBMS_AQADM.STOP_QUEUE (queue_name => 'user1.spl_aq'); 
  DBMS_AQADM.DROP_QUEUE (queue_name => 'user1.spl_aq'); 
  DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'user1.spl_queue_table');
END;

5.    訊息的入隊

PROCEDURE enqueue(p_payload IN t_spl_queue_payload) IS
--PRAGMA AUTONOMOUS_TRANSACTION;
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     RAW(16);
  recipients         DBMS_AQ.aq$_recipient_list_t;
BEGIN
  recipients(1) := sys.aq$_agent('someguy', 'user1.SPL_AQ', NULL);
  message_properties.recipient_list := recipients;
  message_properties.priority := -5;
  message_properties.delay := dbms_aq.no_delay;
  message_properties.expiration := dbms_aq.never;
--enqueue_options.visibility := dbms_aq.on_commit;
  enqueue_options.visibility := dbms_aq.immediate;
  enqueue_options.sequence_deviation := null;

  dbms_aq.enqueue(queue_name         => 'user1.SPL_AQ',
                  enqueue_options    => enqueue_options,
                  message_properties => message_properties,
                  payload            => p_payload,
                  msgid              => message_handle);

--COMMIT;
END enqueue;

(1) recipient，其中“someguy”指定的是訊息的接收者，出隊時你需要指定一樣的名字才能接收到訊息。(2)visibility，可以是on_commit或者immediate，如果使用on_commit，需要手工調用commit語句之後訊息才進入隊列（這種情況下，最好使用自治事務）；如果使用immediate，則dbms_aq.enqueue完成時訊息就進入隊列，不需commit，並且預設使用自治事務。

6.    訊息的出隊

PROCEDURE dequeue IS
  l_payload      t_spl_queue_payload;
  l_queue_record NUMBER;
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     RAW(16);
BEGIN
  dequeue_options.consumer_name := 'someguy';
  dequeue_options.dequeue_mode  := dbms_aq.remove;
  dequeue_options.navigation    := dbms_aq.next_message;
  dequeue_options.visibility    := dbms_aq.immediate;
--dequeue_options.wait          := dbms_aq.forever;
  dequeue_options.wait          := dbms_aq.no_wait;
  dequeue_options.msgid         := null;
--
  SELECT COUNT(*)
INTO l_queue_record
FROM AQ$SPL_QUEUE_TABLE
WHERE msg_state = 'READY';
--
  FOR i IN 1 .. l_queue_record LOOP
    dbms_aq.dequeue(queue_name         => 'user1.SPL_AQ',
                    dequeue_options    => dequeue_options,
                    message_properties => message_properties,
                    payload            => l_payload,
                    msgid              => message_handle);
--
  /*……………………………………….
  some time consuming calculation
  ………………………………………….*/
END LOOP;
END;

(1) consumer_name需要和前面在入隊時指定的recipient一致。(2)wait的兩個值forever和no_wait是指如果當前隊列中無訊息時，是否進行等待，預設等待。(3) navigation的兩個值first_message和next_message，一般出於效能考慮我們使用後者，或者在第一次出隊時使用前者而在隨後的出隊中使用後者：

The FIRST_MESSAGE navigation option performs a SELECT on the queue. The NEXT_ MESSAGE navigation option fetches from the results of the SELECT run in the FIRST_ MESSAGE navigation. Thus performance is optimized because subsequent dequeues need not run the entire SELECT again.