ORACLE的存儲(chǔ)過程的異步調(diào)用－－任務(wù)和管道的應(yīng)用
2024-08-29 13:38:09
供稿：網(wǎng)友
1. 前言
在ＯＲＡＣＬＥ數(shù)據(jù)庫實(shí)際應(yīng)用過程中，經(jīng)常把業(yè)務(wù)處理邏輯的放在存儲(chǔ)過程，客戶端通過ＡＤＯ進(jìn)行調(diào)用。 有些業(yè)務(wù)邏輯處理量大并且復(fù)雜，使客戶端調(diào)用存儲(chǔ)過程后，長(zhǎng)時(shí)間沒有反應(yīng)，也不知存儲(chǔ)過程運(yùn)行狀態(tài)，本文講述如何在ＯＲＡＣＬＥ通過任務(wù)和管道的應(yīng)用，異步調(diào)用存儲(chǔ)過程的方法。
2. 基本原理
1.使用DBMS_JOB包將主處理存儲(chǔ)過程作為任務(wù)提交到任務(wù)隊(duì)列中
2.主處理存儲(chǔ)過程在運(yùn)行過程中通過DBMS_PipE包將處理情況放在管道中
3.監(jiān)控存儲(chǔ)過程讀取管道，從而了解處理情況
4.本文沒有描述雙向通信的方式，監(jiān)控存儲(chǔ)過程可以通過管道發(fā)送信息給主處理存儲(chǔ)過程
3. 具體實(shí)現(xiàn)測(cè)試
建立測(cè)試環(huán)境
3.1. 對(duì)數(shù)據(jù)庫的ini進(jìn)行相關(guān)修改，使系統(tǒng)的任務(wù)隊(duì)列功能打開
對(duì)于Oracle9I，只需修設(shè)定數(shù)據(jù)庫的INI內(nèi)的"JOB_QUEUE_PROCESSES=非0值"，如 "JOB_QUEUE_PROCESSES=10"，對(duì)于ORACLE8I除設(shè)定實(shí)例ini文件中的"JOB_QUEUE_PROCESSES=非0值"外，還需設(shè)定"job_queue_interval=1"
3.2. 設(shè)用用戶權(quán)限
由于使用ORACLE中特定的程序包，所以要注重要明文給于用戶系統(tǒng)權(quán)限。對(duì)當(dāng)前用戶（測(cè)試用戶），賦于以下權(quán)力
EXECUTE DBMS_PIPE
EXECUTE DBMS_LOCK
EXECUTE DBMS_JOB
3.3. 建立模擬大業(yè)務(wù)處理存儲(chǔ)過程
本存儲(chǔ)過程通過循環(huán)30次，調(diào)用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); --把信息放入緩沖區(qū)
status:=DBMS_PIPE.SEND_MESSAGE('maxpro');
DBMS_LOCK.SLEEP(1);
END LOOP;
DBMS_PIPE.PURGE('maxpro'); --清除原管道信息
DBMS_PIPE.PACK_MESSAGE(999999); --把信息放入緩沖區(qū),用999999 表示過程完成
status:=DBMS_PIPE.SEND_MESSAGE('maxpro');
END maxpro;
3.4. 建立讀取管道動(dòng)態(tài)了解處理狀態(tài)的存儲(chǔ)過程
以下代碼是對(duì)于maxpro存儲(chǔ)過程的狀態(tài)進(jìn)行讀取的過程，主要使用讀取管道的方法，注重：
status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);
中的"0"參數(shù)是很要害的，說明接受等待時(shí)間為0秒，表示,調(diào)用時(shí)假如沒有信息不等待直接返回。
具體代碼如下：
CREATE OR REPLACE PROCEDURE readmaxpro AS
n integer;
status integer;
begin
--接受等待時(shí)間為1秒,可以為0,立即調(diào)用或DBMS_PIPE.RECEIVE_MESSAGE('mypipe') 等待100天
status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);
--status為0為成功可以UNPACK_MESSAGE，1為超時(shí)沒有數(shù)據(jù)，2為信息太大，3為內(nèi)部錯(cuò)誤
IF status <> 0 THEN
DBMS_OUTPUT.PUT_LINE('管道中現(xiàn)在沒有信息返回');
return;
END IF;
DBMS_PIPE.UNPACK_MESSAGE(n);
DBMS_OUTPUT.PUT_LINE('maxpro 的當(dāng)前進(jìn)行狀態(tài)為' n);
end;
3.5. -起動(dòng)模擬大業(yè)務(wù)處理存儲(chǔ)過程為后臺(tái)進(jìn)程的存儲(chǔ)過程（BEGINMAXPRO）
通過DBMS_JOB.SUBMIT(JOB,'maxpro;',sysdate,NULL,FALSE);使"maxpro"為一個(gè)任務(wù)，在當(dāng)前時(shí)間（sysdate）后執(zhí)行,當(dāng)然假如把sysdate改一具體時(shí)間，那就在具體時(shí)間執(zhí)行
具休代碼如下：
CREATE OR REPLACE PROCEDURE beginmaxpro AS
JOB BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(JOB,'maxpro;',sysdate,NULL,FALSE);
DBMS_OUTPUT.PUT_LINE('隊(duì)列號(hào)碼JOB=' JOB);
COMMIT;
end;
4. 代碼執(zhí)行
4.1. 執(zhí)行過程
注：黑體字是屏幕輸出，下劃線字是輸入內(nèi)容
在CMD行輸入 sqlplus 根據(jù)提示登錄
設(shè)定DBMS_OUTPUT可見
SQL> set serveroutput ON
以下是執(zhí)行過程和相應(yīng)的返回信息
SQL> call beginmaxpro();
隊(duì)列號(hào)碼JOB=21
調(diào)用完成。
SQL> call readmaxpro();
maxpro 的當(dāng)前進(jìn)行狀態(tài)為14
調(diào)用完成。
SQL> call readmaxpro();
maxpro 的當(dāng)前進(jìn)行狀態(tài)為16
調(diào)用完成。
SQL> call readmaxpro();
maxpro 的當(dāng)前進(jìn)行狀態(tài)為23
調(diào)用完成。
SQL> call readmaxpro();
maxpro 的當(dāng)前進(jìn)行狀態(tài)為999999
調(diào)用完成。
SQL> call readmaxpro();
管道中現(xiàn)在沒有信息返回
調(diào)用完成。
SQL>
4.2. 執(zhí)行分析
beginmaxpro()，將主處理存儲(chǔ)過程放入隊(duì)列的過程，執(zhí)行后存儲(chǔ)程序馬上返回，maxpro存儲(chǔ)過程放在任務(wù)隊(duì)列中，并設(shè)定為在當(dāng)前系統(tǒng)時(shí)間執(zhí)行（馬上執(zhí)行）
maxpro為主處理存儲(chǔ)過程，開始執(zhí)行模擬大業(yè)務(wù)處理，處理過程中將處理進(jìn)度信息寫入管道
readmaxpro();為檢查狀態(tài)存儲(chǔ)過程，首先讀取信息，假如信息不存在，說明管道內(nèi)沒有信息可讀而退出；假如存在信息則讀出信息
本例子可以進(jìn)行改進(jìn)，輸出不通過DBMS_OUTPUT.PUT_LINE而是用返回值，那么就可以在前端用ADO調(diào)用，發(fā)布任務(wù)，任務(wù)過程中從客戶端讀取進(jìn)度。代碼如下：
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;
5. 附錄：
5.1. 相關(guān)程序包說明
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)
功能：提交作業(yè)入作業(yè)隊(duì)列
參數(shù)說明：
JOB:作業(yè)的作業(yè)號(hào)，輸出參數(shù)
WHAT：作業(yè)對(duì)應(yīng)的PL/SQL代碼（一般用于過程名)
NEXT_DATE:下一次作業(yè)運(yùn)行的時(shí)間
INTERVAL:作業(yè)執(zhí)行間隔時(shí)間，缺省是NULL,只執(zhí)行一次
NO_PARSE:是否對(duì)WHAT語句，在提交時(shí)進(jìn)行分析，缺省是進(jìn)行分析。
INSTANCE：作業(yè)提交時(shí)，指明運(yùn)行作業(yè)的實(shí)例，缺省是0，任何的數(shù)據(jù)庫全可以
FORCE：是否檢查INSTANCE指定的數(shù)據(jù)庫在運(yùn)行，缺省是FALSE，不檢查
例子：
declare
jobn number;
begin
DBMS_JOB.SUBMIT(jobn,'maxpro;',sysdate);
DBMS_OUTPUT.PUT_LINE('JOB=' jobn);
COMMIT;
end;
注重：
設(shè)定數(shù)據(jù)庫參數(shù)JOB_QUEUE_PROCESSES = 非0值（最大隊(duì)列數(shù)最大60）
對(duì)于9I前設(shè)定數(shù)據(jù)庫參數(shù)JOB_QUEUE_INTERVAL = 非0值（隊(duì)列掃描周期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);
功能：把一個(gè)數(shù)據(jù)項(xiàng)放到私有數(shù)據(jù)緩沖區(qū)
3
DBMS_PIPE.SEND_MESSAGE (
pipename IN VARCHAR2,
timeout IN INTEGER DEFAULT MAXWAIT,
maxpipesize IN INTEGER DEFAULT 8192)
RETURN INTEGER;
功能：發(fā)送私有數(shù)據(jù)緩沖區(qū)的信息到命名管道，假如管道不存在就建立
4
DBMS_PIPE.RECEIVE_MESSAGE (
pipename IN VARCHAR2,
timeout IN INTEGER DEFAULT maxwait) RETURN INTEGER;
功能：從管道到接受信息放到數(shù)據(jù)緩沖區(qū)
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);
功能：從數(shù)據(jù)緩沖區(qū)接收下一個(gè)數(shù)據(jù)項(xiàng)
6
DBMS_PIPE.PURGE ( pipename IN VARCHAR2);
功能：清除管道內(nèi)的信息
