Oracle實時程式通訊(轉)

由於Oracle不提供用於實時輸出訊息的工具, Oracle資料庫開發者總是要面臨實時監視他們的儲備過程執行的挑戰。他們必須使用dbms_output.put_line呼叫，這個呼叫直到過程完成才返回結果。
　　在本文中，我想演示如何從Oracle8i資料庫直接傳送電子郵件，作為一種實時通訊解決方案。這樣我們要監視儲存過程就不再需要等待它們完成了，這樣的方法還為開發者提供了其他的一些好處：

　　　. 可以在幾分鐘內除錯一些很長的批處理過程，而不需要等幾個小時；

　　　. 計算用於指定程式碼塊所需的執行時間；

　　這就需要解決一個問題，我們如何從執行的儲存過程中輸出訊息以便我們可以即時檢查它們，即使我們不在辦公場所？我們的做法是把所有必需的過程與函式包裝在自定義的包中，然後使用Oracle8i UTL_SMTP包直接地從Oracle資料庫中傳送電子郵件。下面我將詳細講解一些這個過程。

　　Oracle的UTL_SMTP包

　　在Oracle8i中引入了UTL_SMTP包（SMTP代表Simple Mail Transfer Protocol簡單郵件傳送協議，使用TCP埠25在客戶機和伺服器之間建立通訊聯絡)，使開發者能夠從資料庫傳送電子郵件。

　　只有安裝帶有Java虛擬機器(JVM)的8i或更高的版本才能使用UTL_SMTP。 此外還必須把plsql.jar載入資料庫中。否則，當呼叫UTL_SMTP API來傳送電子郵件的時候我們將得到下面的異常：ORA - 29540 : class oracle/plsql/net/TCPConnection does not exist。

　　預設的$ORACLE_HOME/javavm/install/initjvm.sql指令碼（安裝了JVM）不執行把plsql.jar載入資料庫的initplsj.sql指令碼。系統使用者或者內部使用者可以手工執行$ORACLE_HOME/RDBMS/ADMIN/initplsj.sql指令碼以解決這個問題。 如果你沒有可用的指令碼，你要麼可以從Oracle支援那裡得到它，要麼可以簡單地直接使用loadjava載入實用程式plsql.jar：

　　loadjava -user sys/password@database -resolve plsql/jlib/plsql.jar

　　UTL_SMTP API:

　　本文的程式碼中使用了下列UTL_SMTP包中的API：

　　　OPEN_CONNECTION()：開啟到簡單郵件傳送協議伺服器的連線。

　　　HELO()：執行連線之後建立與簡單郵件傳送協議伺服器初始的收發關係功能，它能識別傳送到伺服器的“信使”。

　　　MAIL()：初始化與伺服器的郵件交換，但是事實上不傳送訊息。

　　　RCPT()：識別訊息的接受者。為了把一條訊息傳送到多個接受者，你必須多次呼叫這個過程。

　　　DATA()：指定電子郵件的內容。

　　　QUIT()：終止一個SMTP會話並且斷開與伺服器的連線。

　　為了利用應用程式程式設計介面，把下面的呼叫按照給定的順序放入程式中：

　　　呼叫 OPEN_CONNECTION

　　　呼叫 HELO

　　　呼叫 MAIL

　　　呼叫 RCPT for each recipient

　　　格式化電子郵件的內容然後呼叫MAIL

　　　呼叫 QUIT

　　EmailUtils包規範

　　 EmailUtils包包括下列API：

　　　SetSender/GetSender-設定/取得傳送者

　　　SetRecipient/GetRecipient -設定/取得接受者

　　　SetCcrecipient/GetCcrecipient -設定/取得抄件接受者

　　　SetMailHost/GetMailHost -設定/取得郵件主機

　　　SetSubject/GetSubject -設定/取得主題

　　　Send-傳送郵件

　　程式碼1說明了EmailUtils包的規範：

create or replace package EmailUtils as

procedure SetSender(pSender in varchar2);
function GetSender
return varchar2;

procedure SetRecipient(pRecipient in varchar2);
function GetRecipient
return varchar2;

procedure SetCcRecipient(pCcRecipient in varchar2);
function GetCcRecipient
return varchar2;

procedure SetMailHost(pMailHost in varchar2);
function GetMailHost
return varchar2;

procedure SetSubject(pSubject in varchar2);
function GetSubject
return varchar2;

procedure Send(pMessage in varchar2);

procedure Send(pSender in varchar2,
pRecipient in varchar2,
pMailHost in varchar2,
pCcRecipient in varchar2 := null,
pSubject in varchar2 := null,
pMessage in varchar2 := null);

end EmailUtils;
/

　　可以看出，Send過程是過載過程：包規範中包括這個過程的兩個版本。 一個版本當至少三個強制性引數要規定的時候引用，pSender，pRecipient和pMailHost：

procedure Send(pSender in varchar2,
pRecipient in varchar2,
pMailHost in varchar2,
pCcRecipient in varchar2 := null,
pSubject in varchar2 := null,
pMessage in varchar2 := null);

　 另一個版本只有當提供pMessage引數值時執行：

　　　procedure Send(pMessage in varchar2);

　　第二個版本是用作除錯的版本。 所有的電子郵件訊息共用同樣的傳送者、接受者、郵件主機、抄送接受者和主題資訊，這些都是我在會話的開始的時候設定好的。 下面是一個PL/SQL程式塊的例子：

begin

EmailUtils.SetSender('WayneZ@MyCompany.com');
EmailUtils.SetRecipient('waynezheng@vip.sina.com');
EmailUtils.SetCcRecipient('WayneZ@MyCompany.com');
EmailUtils.SetMailHost('MyServer.MyCompany.com');
EmailUtils.SetSubject('DeleteClassifications procedure: Run 1');

end;
/

　　一個實際的電子郵件訊息將在每個Send過程呼叫中被指定。 我們可以把所用的EmailUtils.Send()呼叫插入到我們除錯的程式碼中，我們以前為了得到同樣的除錯結果使用的是DBMS_OUTPUT.PUT_LINE()呼叫。:

vMessage := 'Point 1.' || utl_tcp.crlf ||
'Rows processed: ' || to_char(vRows) || utl_tcp.crlf ||
'Elapsed time: ' || vTime;

EmailUtils.Send(vMessage);

vMessage := 'Point 3.' || utl_tcp.crlf ||
'Rows processed: ' || to_char(vRows) || utl_tcp.crlf ||
'Elapsed time: ' || vTime;

EmailUtils.Send(vMessage);

　　程式碼2顯示帶有過載Send過程的EmailUtils規格。 我們可以看到，Send過程的程式碼相當簡單。 UTL_SMTP包不提供用於格式化訊息內容的應用程式設計介面。 而是由使用者負責格式化訊息。 這就是為什麼下列程式塊要被包含到每個Send過程中以便格式化電子郵件的頭部。

vMessage := 'Date: ' ||
to_char(sysdate, 'fmDy, DD Mon YYYY fxHH24:MI:SS') ||
utl_tcp.crlf ||
'From: ' || pSender || utl_tcp.crlf ||
'Subject: ' || pSubject || utl_tcp.crlf ||
'To: ' || pRecipient || utl_tcp.crlf;

　　同時,如果訊息長度超過2000字元的話，你可能得到一個錯誤( ORA - 06502 : PL/SQL : numeric or value error)。 所以為了避免出現這個錯誤，我們使用下面的程式塊,不允許訊息超過2000個字元：

if length(vMessage) > 2000
then
vMessage := substr(vMessage, 1, 2000);
end if;

　　如果需要傳送帶有超過2000字的電子郵件，那麼可以使用另三個UTL_SMTP應用程式程式設計介面，提供比DATA()過程更加精細的控制。 首先, OPEN_DATA()傳送資料命令。 然後WRITE_DATA()新增資料到你要傳送的字串中。 你可以呼叫WRITE_DATA()任意多次，這樣你就可以一次寫2000個字元以克服字數的限制。 最後, CLOSE_DATA()通過傳送一個封裝在CRLF中的終止週期結束電子郵件訊息。

　　實時訊息使你的生活更加舒適

　　從資料庫傳送電子郵件就是那麼容易。 一旦你試用這個簡單的操作，我相信你會發現它很有用，便於你的資料庫操作,例如除錯、遠端的資料庫監控和輸出資料庫資料。

　　每個資料庫開發者都有在程式碼中使用大量的DBMS_OUTPUT呼叫的除錯經歷。 在開始一個SQL * Plus會話之後,輸入SET SERVEROUTPUT ON然後執行這個過程。 放進DBMS_OUTPUT.PUT_LINE呼叫的訊息顯示在螢幕上--但是隻有在過程完成以後才能顯示出來。 這個過程極端地麻煩，尤其在除錯長的批處理時通常是要執行整晚。 你可以等待10到12小時僅僅是為了查出錯誤的程式碼，然後修改，再去等待下一個10到12個小時？ 然而，如果你有訪問訊息的實時的辦法，那麼你可以在頭5到10分鐘內捕捉到問題。

　　DBMS_OUTPUT包也有其他的缺點。 例如,它不接受可變的布林型別以及它有255字元每行的限制（如果你想輸出一個長的訊息的話，那麼你會得到這個異常：ORA - 20000 : ORU - 10028 : line length overflow, limit of 255 bytes per line）。 把它的缺點全部列出這已經超出本文的範圍了，但是重要結論就是DBMS_OUTPUT包不許資料庫開發者實時的看到訊息。

　　談到伺服器上的OS檔案，你會不會喜歡把選定的資料從伺服器中輸出到一個Excel電子表格呢？ 一個辦法就是使用Oracle的UTL_FILE包,它提供了一個標準OS流文件輸入/輸出的限制級版本。 然而, PL/SQL程式只能訪問在初始化檔案INIT.ORA的UTL_FILE_DIR引數規定的目錄。 這個引數大多數情況下是空的。為了使這個目錄可用來進行檔案訪問，必須請資料庫管理者修改初始化檔案。 這有些麻煩。使用EmailUtils包,你可以簡簡單單的把資料寫入一個電子郵件中，傳送給自己，然後收到它的副本貼上到自己喜歡的文件編輯器中。

PL/SQL是基於Oracle的一個主流應用程式程式語言，它的特點是將SQL語句與過程化程式開發語言相結合，以實現更為複雜的商業邏輯。本文主要就其中多程式通訊進行討論。

　　顯然，多程式技術是用來提高應用的併發性，進而提高整個系統的執行效率，那麼如何在PL/SQL中實現多程式的通訊呢？其實，PL/SQL其設計的初衷主要是增強SQL語句的功能，而沒有考慮到其他程式語言的高階功能，所以在PL/SQL中實現多程式通訊只能藉助於Oracle提供的兩個開發包：DBMS_PIPE和DBMS_ALERT。

　　1． DBMS_PIPE

　　該包提供多程式之間管道通訊的方法，比如連線到同一個資料庫的兩個獨立會話之間可以通過管道進行通訊，另外也可以在儲存過程和Pro*C之間進行通訊，這樣就大大地增強了PL/SQL的處理能力。該包主要提供兩對函式：

　　pack_message(v_msg varchar2)-----將v_msg資訊打包放入到緩衝器中，準備傳送；

　　send_message(v_pipename varchar2)-----傳送名為v_pipename的管道的緩衝器；

　　unpack_message(v_msg varchar2)-----將資訊解析到v_msg中；

　　receive_message(v_pipename varchar2)----接受名為v_pipename的管道的緩衝器；

　　其執行的原理是：首先建立有名管道（這點熟悉unix很清楚），管道的傳送端和接受端都有相應的緩衝器進行接受和傳送處理，要注意的是，文字資訊必須打包來傳送，通過解析來讀取資訊。

　　為了理解前面的描述，下面列舉一個兩個會話之間通訊的例項：

　　傳送程式：

　　declare
v_pipename varchar2(30):='pipe1';
v_status integer;
begin
dbms_pipe.pack_message(' hello,this is sending process!');
v_status:=dbms_pipe.send_message(v_pipename);
if v_status !=0 then
dbms_output.put_line('error!');
end if;
end;
/

　　接受程式：

　　declare
v_pipename varchar2(30):='pipe1';
v_status integer;
v_msg varchar2(20);
begin
v_status:=dbms_pipe.receive_message(v_pipename);
if v_status !=0 then
dbms_output.put_line('error');
end if;
dbms_pipe.unpack_message(v_msg);
dbms_output.put_line(v_msg);
end;
/

　　2． DBMS_ALERT

　　與DBMS_PIPE類似，DBMS_ALERT可以實現多個程式（會話）之間的通訊。其基本的執行過程為：首先建立一個報警通道，然後通過報警通道來傳送報警訊號；在接收端需要先註冊該報警通道，對該通道進行監聽，然後等待報警訊號的到來。下面是該包中的主要函式說明：

　　dbms_alert.signal(報警管道名,待傳送訊息)---傳送報警資訊；

　　dbms_alert.register（報警管道名）------註冊報警管道；

　　dbms_alert.waitone（報警管道名,接受訊息值，返回狀態值）-------對報警管道進行監聽，等待訊息的到來；

　　同樣，這裡也給出使用DBMS_ALERT的一個例項：

　　傳送程式：

　　declare
v_alertName varchar2(30):='alert1';
begin
dbms_alert.signal(v_alertName,' hello,this is sending process!');
commit;
end;
/

　　接受程式：

　　declare
v_alertName varchar2(30):='alert1';
v_status integer;
v_msg varchar2(20);
begin
dbms_alert.register(v_alertName);
dbms_alert.waitone(v_alertName,v_msg,v_status);
if v_status !=0 then
dbms_output.put_line('error');
end if;
dbms_output.put_line(v_msg);
end;
/

　　3． 若干說明

　　DBMS_PIPE和DBMS_ALERT這兩個包都可以進行多程式間的通訊，但兩者任然是有一些區別：

　　1) 報警訊號是同步的。報警訊號直到會話發出Commit時才被髮出；如果它所處的事務回滾，則該訊號就不傳送了。但是管道訊號是非同步的，而且其通訊過程不受事務提交和回滾的影響。

　　2)　沿著管道進行傳遞的訊息只能被一個程式進行處理，其訊息的接收方式是拷貝後刪除，但是報警訊號可以被多個程式所獲得，即訊息的接收方式僅僅是拷貝。

　　3) 管道訊息不僅可以傳遞文字資訊，還可以傳遞其他資訊，比如物件等，這一好處得益於其對訊息的打包預處理；而報警訊息只能是文字，不能是其他型別。

　　4． 小結

　　近年來，雖然多程式應用有逐漸被多執行緒應用取代的趨勢，但是多程式仍然有其適用的空間，希望讀者通過本文初淺的介紹，對基於PL/SQL的多程式通訊這一技術有一個初步的認識，這樣為以後深入的開發打下一定的基礎。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242333/，如需轉載，請註明出處，否則將追究法律責任。