ORACLE的預存程序的非同步呼叫

http://tech.ccidnet.com/art/294/20030526/47427_1.html

ORACLE的預存程序的非同步呼叫
作者：王輝 發文時間：2003.05.26

　　1. 關於作者

　　王輝　從1994年開始工作，曾擔任教師、資料庫管理員、主程式員、專案經理，現在深圳一家公司擔任專案經理。可以通過ddxxkk@21cn.com聯絡。

　　2. 前言

　　在ＯＲＡＣＬＥ資料庫實際應用過程中，經常把業務處理邏輯的放在預存程序，用戶端通過ＡＤＯ進行調用。有些商務邏輯處理量大並且複雜，使用戶端調用預存程序後，長時間沒有反應，也不知預存程序運行狀態，本文講述如何在ＯＲＡＣＬＥ通過任務和管道的應用，非同步呼叫預存程序的方法。

　　3. 基本原理

　　　　1.使用DBMS_JOB包將主處理預存程序作為任務提交到任務隊列中

　　　　2.主處理預存程序在運行過程中通過DBMS_PIPE包將處理情況放在管道中

　　　　3.監控儲存區過程讀取管道，從而瞭解處理情況

　　　　4.本文沒有描述雙向通訊的方式，監控儲存區過程可以通過管道發送資訊給主處理預存程序

　　4. 具體實現測試

　　建立測試環境

　　4.1. 對資料庫的ini進行相關修改，使系統的任務隊列功能開啟

　　對於ORACLE9I，只需修設定資料庫的INI內的"JOB_QUEUE_PROCESSES=非0值"，如 "JOB_QUEUE_PROCESSES=10"，對於ORACLE8I除設定執行個體ini檔案中的"JOB_QUEUE_PROCESSES=非0值"外，還需設定"job_queue_interval=1"

　　4.2. 設用使用者權限

　　由於使用ORACLE中特定的程式包，所以要注意要明文給於使用者系統許可權。對目前使用者（測試使用者），賦於以下權力

EXECUTE DBMS_PIPE
EXECUTE DBMS_LOCK
EXECUTE DBMS_JOB

　　4.3. 建立類比大業務處理預存程序

　　本預存程序通過迴圈30次，調用DBMS_LOCK.SLEEP(1)（體眠1秒）和寫管道類比大的處理過程。

　　具體代碼如下：

CREATE OR REPLACE PROCEDURE maxpro AS
n integer;
status NUMBER;
BEGIN
FOR N IN 1..30 LOOP
DBMS_PIPE.PURGE('maxpro'); --清除原管道資訊
DBMS_PIPE.PACK_MESSAGE(N); --把資訊放入緩衝區
status:=DBMS_PIPE.SEND_MESSAGE('maxpro');
DBMS_LOCK.SLEEP(1);
END LOOP;
DBMS_PIPE.PURGE('maxpro'); --清除原管道資訊
DBMS_PIPE.PACK_MESSAGE(999999); --把資訊放入緩衝區,用999999 表示過程完成
status:=DBMS_PIPE.SEND_MESSAGE('maxpro');
END maxpro;

　　4.4. 建立讀取管道動態瞭解處理狀態的預存程序

　　以下代碼是對於maxpro預存程序的狀態進行讀取的過程，主要使用讀取管道的方法，注意：

status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);

　　中的"0"參數是很關鍵的，說明接受等待時間為0秒，表示,調用時如果沒有資訊不等待直接返回。

　　具體代碼如下：

CREATE OR REPLACE PROCEDURE readmaxpro AS
n integer;
status integer;
begin
--接受等待時間為1秒,可以為0,立即調用或DBMS_PIPE.RECEIVE_MESSAGE('mypipe') 等待100天
status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);
--status為0為成功可以UNPACK_MESSAGE，1為逾時沒有資料，2為資訊太大，3為內部錯誤
IF status <> 0 THEN
DBMS_OUTPUT.PUT_LINE('管道中現在沒有資訊返回');
return;
END IF;
DBMS_PIPE.UNPACK_MESSAGE(n);
DBMS_OUTPUT.PUT_LINE('maxpro 的當前進行狀態為'|| n);
end;

　　4.5. -起動類比大業務處理預存程序為後台進程的預存程序（BEGINMAXPRO）

　　通過DBMS_JOB.SUBMIT(JOB,'maxpro;',sysdate,NULL,FALSE);使"maxpro"為一個任務，在目前時間（sysdate）後執行,當然如果把sysdate改一具體時間，那就在具體時間執行

　　具休代碼如下：

CREATE OR REPLACE PROCEDURE beginmaxpro AS
JOB BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(JOB,'maxpro;',sysdate,NULL,FALSE);
DBMS_OUTPUT.PUT_LINE('隊列號碼JOB=' || JOB);
COMMIT;
end;

　　5. 代碼執行

　　5.1. 執行過程

　　註：黑體字是螢幕輸出，底線字是輸入內容
　　在CMD行輸入 sqlplus 根據提示登入
　　設定DBMS_OUTPUT可見
　　SQL> set serveroutput ON
　　以下是執行過程和相應的返回資訊
　　SQL> call beginmaxpro();
　　隊列號碼JOB=21
　　調用完成。
　　SQL> call readmaxpro();
　　maxpro 的當前進行狀態為14
　　調用完成。
　　SQL> call readmaxpro();
　　maxpro 的當前進行狀態為16
　　調用完成。
　　SQL> call readmaxpro();
　　maxpro 的當前進行狀態為23
　　調用完成。
　　SQL> call readmaxpro();
　　maxpro 的當前進行狀態為999999
　　調用完成。
　　SQL> call readmaxpro();
　　管道中現在沒有資訊返回
　　調用完成。
　　SQL>

　　5.2. 執行分析

　　beginmaxpro()，將主處理預存程序放入隊列的過程，執行後儲存程式馬上返回，maxpro預存程序放在任務隊列中，並設定為在當前系統時間執行（馬上執行）

　　maxpro為主處理預存程序，開始執行類比大業務處理，處理過程中將處理進度資訊寫入管道

　　readmaxpro();為檢查狀態預存程序，首先讀取資訊，如果資訊不存在，說明管道內沒有資訊可讀而退出；如果存在資訊則讀出資訊

　　本例子可以進行改進，輸出不通過DBMS_OUTPUT.PUT_LINE而是用傳回值，那麼就可以在前端用ADO調用，發布任務，任務過程中從用戶端讀取進度。代碼如下：

CREATE OR REPLACE PROCEDURE readmaxpro(p out intergr) AS
n integer;
status integer;
begin
status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);
IF status <> 0 THEN
P:=-1;
return;
END IF;
DBMS_PIPE.UNPACK_MESSAGE(p);
end;

　　6. 附錄：

　　6.1. 相關程式包說明

1
DBMS_JOB.SUBMIT(
JOB OUT BINARY_INTEGER,
WHAT IN VARCHAR2,
NEXT_DATE IN DATE DEFAULTSYSDATE,
INTERVAL IN VARCHAR2 DEFAULT 'NULL',
NO_PARSE IN BOOLEAN DEFAULT FALSE,
INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
FORCE IN BOOLEAN DEFAULT FALSE)
功能：提交作業入作業隊列
參數說明：
JOB:作業的作業號，輸出參數
WHAT：作業對應的PL/SQL代碼（一般用於過程名)
NEXT_DATE:下一次作業啟動並執行時間
INTERVAL:作業執行間隔時間，預設是NULL,只執行一次
NO_PARSE:是否對WHAT語句，在提交時進行分析，預設是進行分析。
INSTANCE：作業提交時，指明運行作業的執行個體，預設是0，任何的資料庫全可以
FORCE：是否檢查INSTANCE指定的資料庫在運行，預設是FALSE，不檢查
例子：
declare
jobn number;
begin
DBMS_JOB.SUBMIT(jobn,'maxpro;',sysdate);
DBMS_OUTPUT.PUT_LINE('JOB=' || jobn);
COMMIT;
end;

　　注意：

　　設定資料庫參數JOB_QUEUE_PROCESSES = 非0值（最大隊列數最大60）

　　對於9I前設定資料庫參數JOB_QUEUE_INTERVAL = 非0值（隊列掃描周期1-3600秒）

　　2

DBMS_PIPE.PACK_MESSAGE (item IN VARCHAR2);
DBMS_PIPE.PACK_MESSAGE (item IN NCHAR);
DBMS_PIPE.PACK_MESSAGE (item IN NUMBER);
DBMS_PIPE.PACK_MESSAGE (item IN DATE);
DBMS_PIPE.PACK_MESSAGE_RAW (item IN RAW);
DBMS_PIPE.PACK_MESSAGE_ROWID (item IN ROWID);
功能：把一個資料項目放到私人資料緩衝區

3

DBMS_PIPE.SEND_MESSAGE (
pipename IN VARCHAR2,
timeout IN INTEGER DEFAULT MAXWAIT,
maxpipesize IN INTEGER DEFAULT 8192)
RETURN INTEGER;
功能：發送私人資料緩衝區的資訊到具名管道，如果管道不存在就建立

4

DBMS_PIPE.RECEIVE_MESSAGE (
pipename IN VARCHAR2,
timeout IN INTEGER DEFAULT maxwait) RETURN INTEGER;
功能：從管道到接受資訊放到資料緩衝區

5

DBMS_PIPE.UNPACK_MESSAGE (item OUT VARCHAR2);
DBMS_PIPE.UNPACK_MESSAGE (item OUT NCHAR);
DBMS_PIPE.UNPACK_MESSAGE (item OUT NUMBER);
DBMS_PIPE.UNPACK_MESSAGE (item OUT DATE);
DBMS_PIPE.UNPACK_MESSAGE_RAW (item OUT RAW);
DBMS_PIPE.UNPACK_MESSAGE_ROWID (item OUT ROWID);
功能：從資料緩衝區接收下一個資料項目

6

DBMS_PIPE.PURGE ( pipename IN VARCHAR2);
功能：清除管道內的資訊