内容

oracle xeでキュー機能(AQ)を使ってみました。

やりたかったことはセッションAで実行した処理結果を自動でセッションBに返す
ということがしたくてchatGPTに質問したらAQを使えばできるとのことでした。
結局やりたかったことはできなかったのですが、データが登録されたら検知してデータを取得する
というのはできたので記載しておきます。

AQ使ってみた

親DB(CDB: systemユーザで接続できるDB)に接続してからメインで使用しているDBに対して権限を付与します。

alter session set container = サービス名;
grant execute on dbms_aq to ユーザ名; -- PDB: メインで使ってるDBのユーザ名

ペイロードのタイプを定義します。

CREATE OR REPLACE TYPE my_payload_type AS OBJECT (
    column1 NUMBER,
    column2 VARCHAR2(100)
);

キュー関連を作成します。

BEGIN
  -- キューテーブルの作成
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table => 'my_queue_table',
    queue_payload_type => 'my_payload_type',
    multiple_consumers => TRUE
  );

  -- キューの作成
  DBMS_AQADM.CREATE_QUEUE(
    queue_name => 'my_queue',
    queue_table => 'my_queue_table'
  );

  -- キューの使用を開始
  DBMS_AQADM.START_QUEUE('my_queue');
END;
/

サブスクライバを登録します。
サブスクライバとは受信者のことだと思います。

BEGIN
    DBMS_AQADM.ADD_SUBSCRIBER(
          queue_name  => 'my_queue',
          subscriber  => SYS.AQ$_AGENT('SUBSCR',NULL,NULL),
          rule        => NULL
    );
END;
/

テスト用のテーブルを作成します。

CREATE TABLE test_table (
    column1 NUMBER,
    column2 VARCHAR2(100)
);

PLSQLでデキュー処理(キューから値を取り出す)を作成します。
AUTO_RECVに引数が5つありますがPLSQL内では使っていません。
ですが後述のPLSQLの自動化設定のときに引数がないと動かないです。
理由は不明です。

-- デキュー
CREATE OR REPLACE PROCEDURE AUTO_RECV(
             context    IN RAW,
             reginfo    IN SYS.AQ$_REG_INFO,
             descr      IN SYS.AQ$_DESCRIPTOR,
             payload    IN VARCHAR2,
             payloadl   IN NUMBER
) IS
  dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  mesg   my_payload_type;  
  message_handle    RAW(16);
BEGIN
  DBMS_OUTPUT.ENABLE;
  
  -- メッセージをデキュー
  dequeue_options.CONSUMER_NAME := 'SUBSCR';
  DBMS_AQ.DEQUEUE(
    queue_name         => 'my_queue',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => mesg,
    msgid              => message_handle
  );

  DBMS_OUTPUT.PUT_LINE(mesg.column1 || ' ' || mesg.column2);
  insert into test_table values (mesg.column1, mesg.column2);
  COMMIT;
END AUTO_RECV;
/

デキューをするPLSQLを自動化します。

DECLARE
         REGINFO  SYS.AQ$_REG_INFO;
         REGINFOS SYS.AQ$_REG_INFO_LIST;
BEGIN
     REGINFO := SYS.AQ$_REG_INFO(
                 'my_queue:SUBSCR',
                  DBMS_AQ.NAMESPACE_AQ,
                  'plsql://AUTO_RECV?PR=1',
                  NULL
                );
     REGINFOS := SYS.AQ$_REG_INFO_LIST(REGINFO);
     DBMS_AQ.REGISTER( REGINFOS,1);
END;
/

エンキュー(キューに値を入れる)をします。

DECLARE
  enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  payload my_payload_type;
  message_handle RAW(16);
BEGIN
  payload := my_payload_type(123, 'Hello, World!');

  DBMS_AQ.ENQUEUE(
    queue_name => 'my_queue',
    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => payload,
    msgid => message_handle
  );

  COMMIT;
END;
/

作成したものを削除するには

指定したキュー関連を削除できます。
サブスクライバも消えますがタイプは消えないです。

BEGIN
  DBMS_AQADM.DROP_QUEUE_TABLE(
    queue_table => 'my_queue_table',
    force => TRUE -- force:trueにすることでキューを強制的に一時停止→削除→キューテーブルを削除できます。
  );
END;
/

指定したタイプを削除できます。

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE MY_PAYLOAD_TYPE';
END;
/

トリガー機能について

以降はchatGPTに教えてもらったけど使っていないコードです。
下記コードを実行するとmy_queueというテーブルまたはビューがないためエラーになります。
結局使わなかったので深堀はしてないですが、トリガーという機能は
テーブルなどに何らかのアクションがあると自動で何らかの処理を実行する機能みたいです。

下記の場合だとmy_queueというテーブルにデータが登録されたらそれを検知して
my_queueというキューからデータを取得するという処理です。

キューテーブルの作成でできたAQ$MY_QUEUE_TABLEというキューのデータを保持してるビューを
対象にすべきだと思ったのですがこのタイプではトリガーの作成はできないとエラーがでました。

-- トリガーを作成する
-- DECLAREからはデキューの処理
CREATE OR REPLACE TRIGGER my_queue_trigger
AFTER INSERT ON my_queue
FOR EACH ROW
DECLARE
  dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  payload   my_payload_type;  
  message_handle    RAW(16);
BEGIN
  DBMS_OUTPUT.ENABLE;
  
  -- メッセージをデキュー
  DBMS_AQ.DEQUEUE(
    queue_name         => 'my_queue',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => payload,
    msgid              => message_handle
  );
  
  DBMS_OUTPUT.PUT_LINE(payload.column1 || ' ' || payload.column2);
END;
/

参考

デキューの自動化関連は下記サイトを参考にさせていただきました。
https://qiita.com/kjmtgm/items/bb31794a7e780c853e29