DBMS_ALERTを使用したアラートの登録、送信、受信 - PolarDB

DBMS_ALERT パッケージは、アラートを登録、送信、および受信する機能を提供しています。

表 1. DBMS_ALERT関数とストアドプロシージャ

関数またはストアドプロシージャ	戻り値のデータ型	説明
REGISTER(name)	非該当	現在のセッションを使用して、指定されたアラートの通知を受信します。
REMOVE(name)	非該当	指定されたアラートの通知の受信を中止します。
REMOVEALL	非該当	現在のセッションのすべてのアラートを登録リストから削除します。
SIGNAL(name, message)	非該当	指定された名前で設定されたアラートを生成します。
WAITANY(name OUT, message OUT, status OUT, timeout)	非該当	登録されたアラートが生成されるのを待ちます。
WAITONE(name, message OUT, status OUT, timeout)	非該当	指定されたアラートが生成されるのを待ちます。

使用量

登録

この関数またはストアドプロシージャは、現在のセッションを使用して、指定されたアラートの通知を受け取ります。

構文

REGISTER(name VARCHAR2)

Parameters

パラメーター	説明
name	登録するアラートの名前。

例

次の匿名ブロックは、alert_testという名前のアラートに登録するために使用されます。

DECLARE
    v_name           VARCHAR2(30) := 'alert_test';
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 120;
BEGIN
    DBMS_ALERT.REGISTER(v_name);
    DBMS_OUTPUT.PUT_LINE('Registered for alert ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    DBMS_ALERT.WAITONE(v_name,v_msg,v_status,v_timeout);
    DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
    DBMS_ALERT.REMOVE(v_name);
END;

サンプル結果:

Registered for alert alert_test
Waiting for signal...

削除

この関数またはストアドプロシージャは、指定されたアラートの通知の受信をキャンセルします。

構文

REMOVE(name VARCHAR2)

Parameters

パラメーター	説明
name	登録を解除するアラートの名前。

削除

この関数またはストアドプロシージャは、現在のセッションのすべてのアラートを登録リストから削除します。

構文

REMOVEALL

信号

この関数またはストアドプロシージャは、指定された名前で設定されたアラートを生成します。

構文

SIGNAL(name VARCHAR2, message VARCHAR2)

Parameters

パラメーター	説明
name	アラートの名前。
message	アラートに関連するメッセージ。

例

次の匿名ブロックは、alert_testという名前のアラートを送信するために使用されます。

DECLARE
    v_name   VARCHAR2(30) := 'alert_test';
BEGIN
    DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END;

サンプル結果:

Issued alert for alert_test

WAITANY

この関数またはストアドプロシージャは、登録されたアラートが生成されるのを待ちます。

構文

WAITANY(name OUT VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout NUMBER)

Parameters

パラメーター	説明
name	アラートの名前。
message	アラートに関連するメッセージ。
status	返された状態コード。有効な値： 0: システムがアラートを生成します。 1: アラートがタイムアウトします。
timeout	アラートが生成されるまで待機する最大時間。単位は秒です。

例

次の匿名ブロックは、WAITANY ストアドプロシージャを使用して、alert_test または any_alert という名前のアラートを受信します。

DECLARE
    v_name           VARCHAR2(30);
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 120;
BEGIN
    DBMS_ALERT.REGISTER('alert_test');
    DBMS_ALERT.REGISTER('any_alert');
    DBMS_OUTPUT.PUT_LINE('Registered for alert alert_test and any_alert');
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    DBMS_ALERT.WAITANY(v_name,v_msg,v_status,v_timeout);
    DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
    DBMS_ALERT.REMOVEALL;
END;

サンプル結果:

Registered for alert alert_test and any_alert
Waiting for signal...

次の匿名ブロックは、any_alertのシグナルを発行するために使用されます。

DECLARE
    v_name   VARCHAR2(30) := 'any_alert';
BEGIN
    DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END;

サンプル結果:

Issued alert for any_alert

次の出力は、制御プロセスが最初の匿名ブロックを返し、残りのコードが実行されることを示しています。

Registered for alert alert_test and any_alert
Waiting for signal...
Alert name   : any_alert
Alert msg    : This is the message from any_alert
Alert status : 0
Alert timeout: 120 seconds

WAITONE

この関数またはストアドプロシージャは、指定されたアラートが生成されるのを待ちます。

構文

WAITONE(name VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout NUMBER)

Parameters

パラメーター	説明
name	アラートの名前。
message	SIGNALストアドプロシージャによって送信されたメッセージ。
status	返された状態コード。有効な値： 0: システムがアラートを生成します。 1: アラートがタイムアウトします。
timeout	アラートが生成されるまで待機する最大時間。単位は秒です。

例

次の匿名ブロックは、WAITONEストアドプロシージャを使用してalert_testという名前のアラートを受信することを除いて、WAITANYの例で使用されているものと似ています。

DECLARE
    v_name           VARCHAR2(30) := 'alert_test';
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 120;
BEGIN
    DBMS_ALERT.REGISTER(v_name);
    DBMS_OUTPUT.PUT_LINE('Registered for alert ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    DBMS_ALERT.WAITONE(v_name,v_msg,v_status,v_timeout);
    DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
    DBMS_ALERT.REMOVE(v_name);
END;

サンプル結果:

Registered for alert alert_test
Waiting for signal...

次の匿名ブロックは、alert_test にシグナルを発行します。

DECLARE
    v_name   VARCHAR2(30) := 'alert_test';
BEGIN
    DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END;

サンプル結果:

Issued alert for alert_test

次の出力は、最初のセッションが警告され、制御フローが最初の匿名ブロックを返し、残りのコードが実行されることを示しています。
```
Registered for alert alert_test
Waiting for signal...
Alert name   : alert_test
Alert msg    : This is the message from alert_test
Alert status : 0
Alert timeout: 120 seconds
```

Comprehensive example

次の例では、2つのトリガーを使用して、deptまたはempテーブルが変更されたときにアラートを送信します。匿名ブロックはこれらのアラートをリッスンし、アラートが受信されるとメッセージを表示します。

基本データを準備します。

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
    loc             VARCHAR2(13)
);

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

GRANT ALL ON emp TO PUBLIC;
GRANT ALL ON dept TO PUBLIC;

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);

CREATE USER mary IDENTIFIED BY password;
CREATE USER john IDENTIFIED BY password;

次のステートメントを実行して、deptテーブルとempテーブルのトリガーを定義します。

CREATE OR REPLACE TRIGGER dept_alert_trig
    AFTER INSERT OR UPDATE OR DELETE ON dept
DECLARE
    v_action        VARCHAR2(25);
BEGIN
    IF INSERTING THEN
        v_action := ' added department(s) ';
    ELSIF UPDATING THEN
        v_action := ' updated department(s) ';
    ELSIF DELETING THEN
        v_action := ' deleted department(s) ';
    END IF;
    DBMS_ALERT.SIGNAL('dept_alert',USER || v_action || 'on ' ||
        SYSDATE);
END;

CREATE OR REPLACE TRIGGER emp_alert_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    v_action        VARCHAR2(25);
BEGIN
    IF INSERTING THEN
        v_action := ' added employee(s) ';
    ELSIF UPDATING THEN
        v_action := ' updated employee(s) ';
    ELSIF DELETING THEN
        v_action := ' deleted employee(s) ';
    END IF;
    DBMS_ALERT.SIGNAL('emp_alert',USER || v_action || 'on ' ||
        SYSDATE);
END;

セッションで次の匿名ブロックを実行します。

DECLARE
    v_dept_alert     VARCHAR2(30) := 'dept_alert';
    v_emp_alert      VARCHAR2(30) := 'emp_alert';
    v_name           VARCHAR2(30);
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 60;
BEGIN
    DBMS_ALERT.REGISTER(v_dept_alert);
    DBMS_ALERT.REGISTER(v_emp_alert);
    DBMS_OUTPUT.PUT_LINE('Registered for alerts dept_alert and emp_alert');
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    LOOP
        DBMS_ALERT.WAITANY(v_name,v_msg,v_status,v_timeout);
        EXIT WHEN v_status != 0;
        DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
        DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
        DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
        DBMS_OUTPUT.PUT_LINE('------------------------------------' ||
            '-------------------------');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_ALERT.REMOVEALL;
END;

サンプル結果:

Registered for alerts dept_alert and emp_alert
Waiting for signal...

他のセッションでdeptテーブルとempテーブルを更新します。
- ユーザーMaryは、deptテーブルとempテーブルに対して次の操作を実行します。
```
INSERT INTO dept VALUES (50, 'FINANCE', 'CHICAG0');
INSERT INTO emp (empno,ename,deptno) VALUES (9001,'J0NES',50);
INSERT INTO emp (empno,ename,deptno) VALUES (9002,'ALICE',50);
```
- ユーザーJohnは、deptテーブルに対して次の操作を実行します。
```
INSERT INTO dept VALUES (60,'HR','L0S ANGELES');
```

この場合、ステップ3のセッションでは、トリガーから信号を受信する匿名ブロックによって生成された出力が表示されます。

Registered for alerts dept_alert and emp_alert
Waiting for signal...
Alert name   : dept_alert
Alert msg    : mary added department(s) on 25-OCT-07 16:41:01
Alert status : 0
-------------------------------------------------------------
Alert name   : emp_alert
Alert msg    : mary added employee(s) on 25-OCT-07 16:41:02
Alert status : 0
-------------------------------------------------------------
Alert name   : dept_alert
Alert msg    : john added department(s) on 25-OCT-07 16:41:22
Alert status : 0
-------------------------------------------------------------
Alert status : 1