Oracle存儲過程中異步調用的實際操作步驟

我們都知道在Oracle數據庫的實際應用的過程中，我們經常把相關的業務處理邏輯，放在Oracle存儲過程中，客戶端以通過ＡＤＯ來進行相關的調用。而有些相關的業務邏輯處理量大並且復雜。

使客戶端調用存儲過程後，長時間沒有反應，也不知Oracle存儲過程運行狀態，

本文講述如何在ＯＲＡＣＬＥ通過任務和管道的應用，異步調用存儲過程的方法。

 基本原理

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

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

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

4.本文沒有描述雙向通信的方式，監控Oracle存儲過程可以通過管道發送信息給主處理存儲過程

具體實現測試

建立測試環境

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. 建立模擬大業務處理存儲過程

本Oracle存儲過程通過循環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. 建立讀取管道動態了解處理狀態的Oracle存儲過程

以下代碼是對於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. -起動模擬大業務處理Oracle存儲過程為後台進程的存儲過程（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.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();為檢查狀態Oracle存儲過程，首先讀取信息，如果信息不存在，說明管道內沒有信息可讀而退出；如果存在信息則讀出信息

本例子可以進行改進，輸出不通過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); 

功能：清除管道內的信息

以上的相關內容就是對Oracle存儲過程的異步調用 的介紹，望你能有所收獲。