oracle|預存程序 夏毅
一、ASP動態網站開發技術
隨著人們對網際網路認識的加深和IT技術的發展，一成不變的靜態網頁已經越來越滿足不了資訊互動和電子商務的需求，因此以資料庫為核心開發能夠實現資訊互動和個人化服務的網頁已經成為一種潮流。為了迎合動態互動式網頁的開發趨勢，出現了可以與後台資料庫進行互動的Web開發技術，目前比較流行的一類是建立在微軟Windows平台IIS基礎上的ASP(Active Server Pages)技術。它是將VBscript、JavaScript等特定的指令碼語言利用特殊的標記嵌入到HTML中，當Browser端提出請求時，Web伺服器會使用相應的指令碼解釋引擎對指令碼解釋執行，完成資料庫的查詢、修改等任務，並把結果動態格式化成HTML形式送回到Browser請求端。
Oracle資料庫是目前國內外最常使用的資料庫，隨著Oracle 8i的發布，它又增添了對Java和對象的支援，大大方便了網路軟體的開發。因此目前ASP應用程式的開發在很多場合需要與後台Oracle資料庫打交道。下面，我們將以多個專題討論群組的應用為例，著重介紹ASP指令碼是如何利用ADO組件的Command對象訪問資料庫的預存程序的。
多專題討論群組使用者提交的資訊都統一儲存在以下結構的資料庫表中：
CREATE TABLE messages(
m_id NUMBER,
m_forumName varchar2(30) NOT NULL,
m_subject varchar2(30) NOT NULL,
m_username varchar2(30) NOT NULL,
m_email varchar2(70) NOT NULL,
m_entrydate date default sysdate,
m_message varchar2(200),
m_ordernum number,
m_reply number)；
其中各欄位的含義如下：
m_id：每條訊息唯一識別的標識號；
m_forumname：討論專題名稱；
m_subject：訊息主題；
m_username：使用者名稱；
m_email：E-mail地址；
m_entrydate：提交時間；
m_message：訊息內容；
m_ordernum：順序號；
m_reply：是否為回答訊息。
二、ASP指令碼調用Oracle資料庫包中的預存程序
1.建立資料庫包
假設現在資料庫表中已經存有多個專題討論的訊息，如果想要在網頁上分別顯示各專題的名稱及各專題的訊息數目，首先在Oracle資料庫中定義一個名為getForum的預存程序，放在名為forum_retrieve的包中。
如果預存程序返回的結果是多行資料集，此過程必須放在一個包中。包是Oracle資料庫的一種對象，它可以將資料類型、預存程序、函數、變數和常量封裝在一起，類似於VB中的標準代碼模組。包在結構上又分為包頭和包體兩部分分別儲存的，因此這兩部分也要分別建立，包頭用來定義可被外部參考的元素；包體定義實際代碼，即邏輯執行部分。
此例的包頭與包體分別定義如下：
CREATE OR REPLACE PACKAGE forum_retrieve
AS
TYPE tforumname IS TABLE of messages.m_forumname %TYPE INDEX BY BINARY_INTEGER;
TYPE tcount IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE getForums
(
forumname OUT tforumname,
theCount OUT tcount
);
以上是包頭的定義，首先對tforumname和tcount這兩個PL/SQL表類型進行聲明，然後對外部調用到的預存程序進行聲明。
CREATE OR REPLACE PACKAGE BODY forum_retrieve
AS
PROCEDURE getForums
(
forumname OUT tforumname,
theCount OUT tcount
)
AS
CURSOR c1 IS
SELECT m_forumname
FROM messages
GROUP by m_forumname
ORDER by m_forumname;
counter NUMBER DEFAULT 1;
BEGIN
FOR c IN c1 LOOP
forumname(counter):=c.m_forumname;
SELECT COUNT(*)
into thecount(counter)
FROM messages
WHERE m_forumname=forumname(counter);
counter :=counter+1;
END LOOP;
END;
以上是對包體的定義，它只包含了一個名為getforums的預存程序。
由於Oracle與SQL Server等其他資料庫不同，不能在預存程序中將查詢的多行結果直接返回到調用端，每次只能直接返回一行結果，但在這裡我們查詢的討論群組名稱和數目返回的是多行資料，因此我們使用了PL/SQL的游標c1，它首先查詢到不同專題名稱的結果集，然後建立遊標迴圈分別對每一個討論群組專題統計出各自的訊息數目，將專題名稱和訊息數目分別存到名為forumname和thecount的PL/SQL表變數中，並作為輸出變數返回到調用端。tforumname和tcount是PL/SQL的兩個表類型，這種類型類似於其他程式設計語言的數組，它們的定義在預存程序中是無法進行的，因此這也是必須將getforums預存程序放在一個包中的原因。
如果以上forum_retrieve包在建立過程沒有出現錯誤，那麼它就已經被編譯後儲存在Oracle的資料字典中了，在SQL*plus下鍵入以下命令：
SELECT * FROM user_objects WHERE object_name='forum_retrieve';
會看到名為forum_retrieve的包頭和包體的狀態，正常應是VALID狀態。
2.在ASP檔案中調用預存程序
下一步我們就可以在用戶端的ASP檔案中使用ADO Command對象來調用這個預存程序了。首先在IIS所在主機上要建立好對Oracle資料庫ODBC的DSN串連，或直接在ASP檔案中建立DSN-Less串連，這完全可以根據個人喜好和具體情況來選擇，代碼如下：
在以上例子中預存程序只有輸出變數。如果被調用的預存程序還包含輸入變數，SQL連接字串應修改如下：
SQLstr=｛call package.procedure(?,..,{resultset 100,output1,output2,...})｝
其中？是每個輸入變數的預留位置，與輸入變數的個數一一對應。另外，還要分別對每個輸入變數賦值，格式如下：
CMD.Parameters.Append CMD.CreateParameter("輸入變數名",adVarChar,adParamInput,30,賦值變數)
其中adVarChar指定變數類型是字串；adParamInput指定變數為輸入變數；30是指定字串的最大長度，如果輸入變數是整型或其他類型，則無須設定此項。所有這些常量的定義都包含在ADOVBS.INC檔案中，對於不同的參數類型可查看此檔案進行設定。
三、ASP指令碼調用Oracle資料庫中獨立的預存程序
所謂獨立的預存程序是指沒有放在包中，而是作為單獨的Object Storage Service在Oracle資料字典中的過程，ASP指令碼對這種預存程序的調用與上述方法略有不同，以下舉例說明。例如，要在ASP指令碼中調用獨立的名為getmessages的預存程序，代碼如下：
CREATE OR REPLACE PROCEDURE getmessages
(
forumname IN messages.m_forumname%TYPE,
lastmessage OUT messages.m_id%TYPE
)
AS
BEGIN
......‘內容省略
ENDgetmessages;
在ASP檔案中也是使用ADO Command對象對此過程進行調用，代碼如下：
四、為什麼要使用存
