多層資料庫開發十:存儲過程

第十章存儲過程

　　這一章介紹怎樣使用存儲過程。存儲過程是資料庫伺服器端的一段程式，它有兩種類型。一種類似于SELECT查詢，用于檢索資料，檢索到的資料能夠以資料集的形式傳回給客戶。另一種類似于INSERT或DELETE查詢，它不傳回資料，隻是執行一個動作。有的伺服器允許同一個存儲過程既可以傳回資料又可以執行動作。

10.1 概述

　　在不同類型的伺服器上，存儲過程的工作方式是不同的。例如，對于InterBase伺服器來說，它能夠以輸出參數的形式傳回資料，而對于其他伺服器如MicrosoftSQL Server和Sybase，能夠以資料集的形式傳回資料和資訊。

　　在Delphi 4中，要通路和操縱伺服器上的存儲過程，可以使用TStoredProc構件或TQuery構件。至于到底選擇哪一個，取決于存儲過程本身是怎樣編寫的、資料怎樣傳回和使用哪一種伺服器。TStoredProc構件和TQuery構件都是從TDataSet繼承下來的。

　　TStoredProc構件适合于執行那些不需要傳回資料，并且通過輸出參數來傳回資訊的存儲過程。TStoredProc構件的Params屬性用于管理這些參數，同時，TStoredProc構件的GetResults函數可以顯式地申請傳回結果。總之，TStoredProc構件适合于執行那些不需要傳回結果或者隻是通過輸出參數傳回結果的存儲過程。

　　TQuery構件适合于執行那些能夠傳回資料集的存儲過程，包括InterBase伺服器上通過輸出參數傳回資料集的存儲過程。當然，TQuery構件也适合于執行那些不需要傳回結果或者隻是通過輸出參數傳回結果的存儲過程。

　　參數既可以由存儲過程傳遞給客戶程式，也可以由客戶程式傳遞給存儲過程，前者稱為輸出參數，後者稱為輸入參數。對于有的伺服器來說，輸出參數隻能傳遞一個值，而有的伺服器允許輸出參數傳遞一個資料集。

10.2 什麼時候需要用存儲過程

　　如果伺服器定義了存儲過程，應當根據需要決定是否要用存儲過程。存儲過程通常是一些經常要執行的任務，這些任務往往是針對大量的記錄而進行的。在伺服器上執行存儲過程，可以改善應用程式的性能。這是因為：

.伺服器往往具有強大的計算能力和速度。

.避免把大量的資料下載下傳到用戶端，減少網絡上的傳輸量。

　　例如，假設一個應用程式需要計算一個資料，這個資料需要涉及到許多記錄。如果不使用存儲過程的話，把這些資料下載下傳到用戶端，導緻網絡上的流量劇增。

　　不僅如此，用戶端可能是一台老掉牙的計算機，它的運算速度很慢。而改用存儲過程後，伺服器會很快地把資料計算出來，并且隻需傳遞一個資料給用戶端，其效率之高是非常明顯的。

10.3 怎樣使用存儲過程

　　應用程式怎樣使用存儲過程，取決于存儲過程本身是怎樣編寫的、資料怎樣傳回和使用哪一種伺服器。

10.3.1 使用存儲過程的一般步驟

　　要通路伺服器上的存儲過程，一般是這麼幾個步驟：

　　第一步，把一個TStoredProc構件放到窗體或資料子產品上。

　　第二步，設定DatabaseName屬性指定一個資料庫，可以設為BDE别名或者應用程式專用的别名(如果用TDatabase構件連接配接資料庫的話)。

　　第三步，設定StoredProcName屬性指定存儲過程的名稱。如果前面正确設定了DatabaseName屬性，就可以從一個下拉清單中選擇一個存儲過程。由于經常要在運作期執行不同的存儲過程，是以，StoredProcName屬性一般是在運作期設定的。

　　第四步，單擊Params邊上的省略号按鈕打開一個編輯器。如果第二步和第三步設定正确的話，在這個編輯器中将顯示所有的輸入和輸出參數，否則，這個編輯器就是空的。

　　要說明的是，并不是所有的伺服器都能夠提供有關的參數的資訊。如果伺服器沒有提供有關參數的資訊，就得自己建立這些參數。

10.3.2 準備和執行存儲過程

　　在執行存儲過程之前，最好先通知伺服器準備好，這就要調用TStoredProc構件的Prepare函數，例如：

　　StoredProc1.Prepare;

　　注意：如果應用程式在運作期改變了參數的資訊，必須重新調用Prepare函數。要執行存儲過程，可以調用TStoredProc構件的ExecProc函數，程式示例如下：

StoredProc1.Params[0].AsString := Edit1.Text;

StoredProc1.Prepare;

StoredProc1.ExecProc;

　　注意：如果在調用ExecProc之前沒有調用Prepare，TStoredProc構件會自動把參數準備好，存儲過程執行完畢後，再自動取消準備。不過，如果一個存儲過程要反複執行多次的話，最好顯式地調用Prepare，不再需要執行存儲過程時調用UnPrepare函數。

　　執行了存儲過程後，它有可能傳回這樣幾種資料：

.一是資料集，可以用标準的資料控件顯示其中的資料。

.二是輸出參數。

.三是狀态資訊。

10.4 建立一個存儲過程

　　存儲過程一般是用專門的工具編寫的。不過，這裡要介紹的是怎樣用SQL語句在運作期動态地建立存儲過程。對于不同的伺服器來說，即使是相同功能的存儲過程，SQL語句也有可能是不同的，是以，必須事先查閱伺服器的文檔。

10.4.1 使用SQL語句建立存儲過程

　　要使用SQL語句建立存儲過程，就要用到TQuery構件的SQL屬性。如果存儲過程中要用到參數的話，必須把TQuery構件的ParamCheck屬性設為False。

　　下面的例子示範了怎樣用SQL語句建立一個存儲過程：

With Query1 Do

Begin

　 ParamCheck := False;

With SQL Do

Clear;

Add('CREATE PROCEDURE GET_MAX_EMP_NAME');

Add('RETURNS (Max_Name CHAR(15))');

Add('AS');Add('BEGIN');

Add('SELECT MAX(LAST_NAME)');

Add('FROM EMPLOYEE');

Add('INTO :Max_Name;');

Add('SUSPEND;');

Add('END');End;

ExecSQL;

End;

　　當然，也可以用SQL Explorer來建立存儲過程。

10.4.2 用TQuery構件檢索資料集

　　要用TQuery構件從存儲過程中檢索資料集，必須正确設定SQL屬性。在SELECT語句中，要用存儲過程的名稱代替表格的名稱。如果存儲過程需要傳遞輸入參數的話，要仿照Object Pascal語言的過程那樣，在存儲過程後面用一對圓括号把參數的值括起來。如果有多個輸入參數，彼此之間要用逗号隔開。

　　例如，InterBase伺服器上有一個存儲過程叫GET_EMP_PROJ，它需要傳遞一個輸入參數叫EMP_NO，并且通過一個輸出參數叫PROJ_ID來傳遞執行結果。下面是這個存儲過程的代碼：

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)

　 RETURNS (PROJ_ID CHAR(5))

　　BEGIN

　　 FOR SELECT PROJ_ID

　　 FROM EMPLOYEE_PROJECT

　　 WHERE EMP_NO = :EMP_NO

　　 INTO :PROJ_ID

　　 SUSPEND;

　 END

　　相應地，要通過上面這個存儲過程檢索資料集，SQL語句可以這樣寫：

SELECT *

　 FROM GET_EMP_PROJ(52)

10.4.3 用TStoredProc構件檢索資料集

　　要用TStoredProc構件從存儲過程中檢索資料集，必須設定StoredProcName屬性指定一個存儲過程的名稱。如果存儲過程需要傳遞輸入參數的話，可以通過Params屬性或ParamByName函數提供參數。

　　例如，Sybase伺服器上有一個存儲過程叫GET_EMPLOYEES，它有個輸入參數叫@EMP_NO。下面是這個存儲過程的代碼：

　　CREATE PROCEDURE GET_EMPLOYEES @EMP_NO SMALLINT

　　 AS SELECT EMP_NAME, EMPLOYEE_NO FROM EMPLOYEE_TABLE

　　 WHERE (EMPLOYEE_NO = @EMP_NO)

　　相應地，要通過上面這個存儲過程檢索資料集，程式應當這樣寫：

With StoredProc1 Do

Close;

ParamByName('@EMP_NO').AsSmallInt := 52;

Active := True;

10.4.4 用TQuery構件通過參數檢索資料

　　用TQuery構件通過參數傳回的資料是一條記錄，即使存儲過程隻有一個輸出參數。是以，應用程式需要從傳回的資料中檢索出每一個字段的值。

　　首先，要在SELECT語句中用存儲過程的名稱代替表格的名稱。

　　如果有多個輸出參數的話，您可以選擇其中部分輸出參數，也可以用星号表示選擇所有輸出參數。

　　如果存儲過程需要傳遞輸入參數的話，在存儲過程後面用一對圓括号把參數的值括起來。如果有多個輸入參數，彼此之間要用逗号隔開。

　　例如，InterBase伺服器上有一個存儲過程叫GET_HIGH_EMP_NAME，通過一個輸出參數叫High_Last_Name來傳回EMPLOYEE表的LAST_NAME字段。

　　下面是這個存儲過程的代碼：

CREATE PROCEDURE GET_HIGH_EMP_NAME

RETURNS (High_Last_Name CHAR(15))

BEGIN

SELECT MAX(LAST_NAME)

FROM EMPLOYEE

INTO :High_Last_Name;

SUSPEND;

END

　　相應地，SQL語句應當這樣寫：

SELECT High_Last_Name

　 FROM GET_HIGH_EMP_NAME

10.4.5 用TStoredProc構件通過參數檢索資料

　　要用TStoredProc構件通過參數檢索資料，首先要設定StoredProcName屬性指定一個存儲過程。如果存儲過程需要傳遞輸入參數的話，可以通過Params屬性或ParamByName函數提供參數。

　　調用ExecProc執行了存儲過程後，可以通過Params屬性或ParamByName函數通路輸出參數。

　　例如，InterBase伺服器上有一個存儲過程叫GET_HIGH_EMP_NAME，通過一個輸出參數叫High_Last_Name傳回EMPLOYEE表的LAST_NAME字段。

SELECT MAX(LAST_NAME)FROM EMPLOYEE

　　相應地，要通過上面這個存儲過程檢索資料，程式應當這樣寫：

StoredProcName := 'GET_HIGH_EMP_NAME'ExecProc;

Edit1.Text := ParamByName('High_Last_Name').AsString;

10.4.6 用TQuery構件執行一個動作

　　有的存儲過程并不傳回資料，它們隻是執行一些動作。例如，要删除一條記錄，既可以用DELETE語句直接删除記錄，也可以執行一個存儲過程。

　　要用TQuery構件執行一個動作，需在SQL語句中包含要執行的存儲過程的名稱。如果存儲過程需要傳遞輸入參數的話，在存儲過程後面用一對圓括号把參數的值括起來。如果有多個輸入參數，彼此之間要用逗号隔開。

　　例如，InterBase伺服器上有一個存儲過程叫ADD_EMP_PROJ，用于向EMPLOYEE_PROJECT表中增加一條記錄。

CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))

INSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID)

VALUES (:EMP_NO, :PROJ_ID);

WHEN SQLCODE -530 DO

EXCEPTION UNKNOWN_EMP_ID;

EXECUTE PROCEDURE ADD_EMP_PROJ(20, 'GUIDE');

10.4.7 用TStoredProc構件執行一個動作

　　要用TStoredProc構件執行一個動作，首先要設定StoredProcName屬性指定一個存儲過程。可以通過Params屬性或ParamByName函數提供輸入參數(如果需要的話)。

　　例如，InterBase伺服器上有一個存儲過程叫ADD_EMP_PROJ，用于向EMPLOYEE_PROJECT表中增加一條記錄。它的代碼請參見上一小節。

　　要執行這個存儲過程，程式應當這樣寫：

StoredProcName := 'ADD_EMP_PROJ';

ExecProc;

10.5 存儲過程的參數

　　要執行伺服器上的存儲過程，往往要傳遞一些參數。這些參數分為四種類型：

　　第一種稱為輸入參數，由客戶程式向存儲過程傳遞值。

　　第二種稱為輸出參數，由存儲過程向客戶程式傳回結果。

　　第三種稱為輸入/輸出參數，既可以由客戶程式向存儲過程傳遞值，也可以由存儲過程向客戶程式傳回結果。

　　第四種稱為狀态參數，由存儲過程向客戶程式傳回錯誤資訊。

　　要說明的是，并不是所有的伺服器都支援上述四種類型的參數，例如，InterBase就不支援狀态參數。

　　可以通過TStoredProc構件的Params屬性通路存儲過程的參數(TParam對象)。如果在設計期正确設定了StoredProcName屬性，Params屬性中将自動包含存儲過程的參數，否則，需要自己建立參數。

10.5.1 輸入參數

　　輸入參數用于由客戶程式向存儲過程傳遞值，值實際上是傳遞給存儲過程中的SQL語句。如果一個存儲過程有輸入參數，一定要在執行該存儲過程之前對輸入參數指派。

　　如果用TQuery構件執行存儲過程，可以把輸入參數用一對圓括号括起來，彼此之間用逗号隔開，就像調用Object Pascal的過程一樣。例如，假設要執行一個存儲過程叫GET_EMP_PROJ，它需要傳遞一個輸入參數，其值為52，SQL語句如下：

　SELECT PROJ_ID

　　FROM GET_EMP_PROJ(52)

　　如果用TStoredProc構件執行存儲過程，可以通過Params屬性或ParamByName函數來通路每一個輸入參數。要在執行存儲過程前對輸入參數指派。例如，假設要執行一個存儲過程叫GET_EMP_PROJ，它需要傳遞一個輸入參數叫EMP_NO，其資料類型為SMALLINT，其值為52，相應地程式代碼應當這樣寫：

ParamByName('EMP_NO').AsSmallInt := 52;

10.5.2 輸出參數

　　輸出參數用于由存儲過程向客戶程式傳遞結果。輸出參數是由存儲過程指派的，客戶程式隻能在執行了存儲過程以後，才能通路輸出參數的值。

　　要通路輸出參數的值，可以通過TStoredProc構件的Params屬性或ParamByName函數。例如，下面的代碼把輸出參數的值顯示到一個編輯框中：

Edit1.Text := Params[0].AsString;

　　大多數存儲過程都有一個或幾個輸出參數，輸出參數既可以傳回一個單獨的值，也可以傳回一個資料集。

　　注意：有的伺服器如InFormix可能不提供參數的資訊，隻能從存儲過程的代碼中檢視它有無輸出參數。

10.5.3 輸入/輸出參數

　　輸入/輸出參數既可以用于由客戶程式向存儲過程傳遞值，也可以由存儲過程向客戶程式傳回結果，也就是說，同一個參數兼具兩種角色。作為輸入參數，必須在執行存儲過程之前對它指派。作為輸出參數，隻能在執行了存儲過程後通路它的值。

　　例如，Oracle伺服器中有一個存儲過程，它的IN_OUTVAR參數就是一個輸入/輸出參數。這個存儲過程的代碼如下：

CREATE OR REPLACE PROCEDURE UPDATE_THE_TABLE (IN_OUTVAR IN OUT INTEGER)

UPDATE ALLTYPETABLE

SET NUMBER82FLD = IN_OUTVAR

WHERE KEYFIELD = 0;

IN_OUTVAR:=1;

UPDATE_THE_TABLE;

　　相應地，要執行上面這個存儲過程，程式代碼應當這樣寫：

ParamByName('IN_OUTVAR').AsInteger := 103;

ExecProc;IntegerVar := ParamByName('IN_OUTVAR').AsInteger;

10.5.4 狀态參數

　　除了傳回資料集或輸出參數外，有的存儲過程還可以傳回一個狀态參數。狀态參數不需要事先指派，隻有在執行了存儲過程之後才能通路它的值。

　　要通路輸出參數的值，可以通過TStoredProc構件的Params屬性或ParamByName函數。例如，下面的代碼通路ByOutputParam參數：

　　DateVar := StoredProc1.ParamByName('ByOutputParam').AsDate;

10.5.5 怎樣在設計期通路參數

　　如果在設計期正确設定了DatabaseName和StoredProcName屬性，就可以在設計期看到這些參數，對于其中的輸入參數，可以設定它們的值。不過，有的資料庫伺服器不提供存儲過程的參數資訊，這種情況下，隻能使用SQLExplorer去檢視存儲過程的代碼，從中找出參數的名稱和類型，然後在對象觀察器中手動建立這些參數。

　　要在設計期通路參數，可以單擊Params屬性邊上的省略号按鈕打開如圖10.1所示的編輯器：

　　圖10.1 存儲過程的參數

　　單擊工具欄上的按鈕可以建立一個新的參數，單擊按鈕可以删除一個參數，單擊按鈕可以把參數的順序上移，單擊按鈕可以把參數的順序下移。

　　選擇其中一個參數，對象觀察器将同步顯示該參數的屬性。其中，ParamType屬性必須設定，以指定參數的使用類型，可以設為Input、Output、Input/Output或Result。DataType屬性也必須設定，以指定參數的資料類型。注：對于Oracle的存儲過程來說，要傳回資料集，必須把DataType屬性設為ftCursor。對于輸入參數或輸入/輸出來說，必須設定Value屬性給參數指派。不能對輸出參數和狀态參數指派。

10.5.6 怎樣在運作期通路參數

　　如果伺服器沒有提供有關參數的資訊，就必須自己建立參數。在運作期，可以通過TParam的Create或TParams的AddParam來建立一個參數。

　　例如，InterBase伺服器上有一個存儲過程叫GET_EMP_PROJ，這個存儲過程有一個輸入參數叫EMP_NO和一個輸出參數叫PROJ_ID。這個存儲過程的代碼如下：

RETURNS (PROJ_ID CHAR(5))

FOR SELECT PROJ_IDFROM EMPLOYEE_PROJECT

WHERE EMP_NO = :EMP_NO

INTO :PROJ_ID

下面通過程式設計動态地建立這兩個參數：

var

P1, P2: TParam;

...

StoredProcName := 'GET_EMP_PROJ';

Params.Clear;

P1 := TParam.Create(Params, ptInput);

P2 := TParam.Create(Params, ptOutput);

TryParams[0].Name := 'EMP_NO';Params[1].Name := 'PROJ_ID';

ParamByname('EMP_NO').AsSmallInt := 52;

ExecProc;Edit1.Text := ParamByname('PROJ_ID').AsString;

FinallyP1.Free;

P2.Free;

10.5.7 ParamBindMode屬性

　　這個屬性用于設定Params屬性中的每一個參數與存儲過程的參數怎樣比對。

　　如果ParamBindMode屬性設為pbByName(預設)，表示Params屬性中的參數按名稱與存儲過程的參數比對。

　　如果ParamBindMode設為pbByNumber，表示Params屬性中的參數按序号與存儲過程的參數比對。

　　建議把ParamBindMode屬性設為pbByName，因為按名稱比對不需要參數的順序，而按序号比對往往容易搞錯。不過，有的情況下可能需要按序号比對，因為有的伺服器并沒有提供參數的名稱。

多層資料庫開發十:存儲過程

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List

多層資料庫開發十:存 儲 過 程

繼續閱讀

多層資料庫開發十:存儲過程