使用DBMS_ALERT實現會話間的非同步警示通知-雲原生資料庫 PolarDB-阿里雲

DBMS_ALERT包提供了註冊、發送和接收警示的能力。

表 1. DBMS ALERT 函數/預存程序

函數/預存程序	傳回型別	說明
REGISTER(name)	N/A	使當前會話可以接收指定警示的通知資訊。
REMOVE(name)	N/A	取消接收指定警示的通知資訊。
REMOVEALL	N/A	從註冊列表中刪除此會話的所有警示。
SIGNAL(name, message)	N/A	產生指定名稱的警示。
WAITANY(name OUT, message OUT, status OUT, timeout)	N/A	等待產生登入的警示。
WAITONE(name, message OUT, status OUT, timeout)	N/A	等待產生指定的警示。

使用說明

REGISTER

當前會話可以接收指定警示的通知資訊。

文法

REGISTER(name VARCHAR2)

參數

參數名稱	描述
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

取消接收指定警示的通知資訊。

文法

REMOVE(name VARCHAR2)

參數

參數名稱	描述
name	被取消註冊的警示名稱。

REMOVEALL

從註冊列表中刪除此會話的所有警示。

文法

REMOVEALL

SIGNAL

產生指定名稱的警示。

文法

SIGNAL(name VARCHAR2, message VARCHAR2)

參數

參數名稱	描述
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)

參數

參數名稱	描述
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)

參數

參數名稱	描述
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

綜合性樣本

以下樣本中，當表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