幾例在ASP預存程序的使用方法

一、使用Command對象和Parameter對象傳遞參數 本講將主要使用Microsoft SQL Server7.0資料庫，先建立一個串連檔案AdoSQL7.asp備用，以後用到時不再特別說明。

% ''AdoSQL7.asp

Option Explicit

Response.Expires = 0

''第一部分: 建立串連

Dim Cnn, StrCnn

Set Cnn = Server.CreateObject("ADODB.Connection")

StrCnn = "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"

Cnn.Open StrCnn

%>

注意：自己使用時要將Data Source設為你的資料庫伺服器所在的機器名。

另外，以前使用Access資料庫時，用Microsoft Access97可以很方便的查看欄位及資料，而使用SQL Server資料庫，尤其是並不在資料庫伺服器，而是在另一台機器上調試ASP指令碼時，要查看欄位及資料便需另外安裝工具，這裡向你提供一個工具：Msqry32.exe（Microsoft Query），這個檔案隨Office97安裝，一般位於目錄“Microsoft Office\Office”下。

例wuf70.asp：

%@ LANGUAGE="VBSCRIPT" %>

!--#include file="AdoSQL7.asp"-->

!--#include file="adovbs.inc"-->

% '' wuf70.asp

Dim cmdTest, prmTest, rsTest

''建立 Command 對象

Set cmdTest = Server.CreateObject("ADODB.Command")

‘Recordset、Command對象都可以通過ActiveConnection屬性來串連Connection對象

cmdTest.ActiveConnection = Cnn

''SQL命令 - 含兩個參數， 用 ？ 表示

cmdTest.CommandText = "Update jobs Set job_desc = ? Where job_id = ?"

''設命令類型為 SQL 陳述式

cmdTest.CommandType = adCmdText

''Prepared 屬性決定是否將 SQL 命令先行編譯，將其設為True，可以加快運行

cmdTest.Prepared = True

''建立 Parameter 對象

Set prmTest=cmdTest.CreateParameter("job_desc",adVarChar,adParamInput,50,"網路")

''將資料追加到 Parameters 資料集合中

cmdTest.Parameters.Append prmTest

Set prmTest = cmdTest.CreateParameter("job_id",adSmallInt,adParamInput,,"12")

cmdTest.Parameters.Append prmTest

''執行修改 – 不需要返回結果，簡單的使用cmdTest.Execute 就可以了

cmdTest.Execute

''重新設定參數運行 – 便可修改另一條資料

cmdTest.Parameters("job_id") = "1"

cmdTest.Parameters("job_desc") = "測試"

cmdTest.Execute

''重新設定參數運行

cmdTest("job_id") = "14"

cmdTest("job_desc") = "金融"

cmdTest.Execute

Set rsTest = Cnn.Execute("Select job_id,job_desc From jobs")

While Not rsTest.EOF

Response.Write rsTest(0) & rsTest(1) & " "

rsTest.MoveNext

Wend

Cnn.close : Set prmTest = Nothing

Set cmdTest = Nothing: Set Cnn = Nothing

%>

分析：

1．Command對象的CreateParameter方法用來為SQL命令或預存程序建立參數對象，共有五個參數（五個參數都是可選項）：

第一個參數：參數對象的名稱；

第二個參數：參數對象的資料類型，種類太多，還是參考ADO協助吧，這裡adVarChar（字串值）、adSmallInt（2 位元組帶符號整型）；

第三個參數：參數類型。可以是：adParamInput（指示輸入參數）、adParamOutput（ 指示為輸出參數）、adParamReturnValue（指示為傳回值）、adParamUnknown（指示參數類型無法確定）、adParamInputOutput（指示為輸入/輸出參數）；

第四個參數：參數的資料長度，最好指定為與資料庫中對應欄位長度相等，以免使用時出錯，尤其資料類型為VarChar時，如果是整型或日期型則不必提供該值；

第五個參數：參數的設定初值。

2．cmdTest.Parameters.Append方法增加一個Parameter對象到Parameters資料集合中，從本例中也可看到如何使用多個參數。

3．從本例可以看到，只需要重新設定輸入參數，便可執行對其他資料的修改，非常方便，這一思路在編程時也是最常用的方法之一。

4．重設參數，既可以使用cmdTest.Parameters，也可以省略為cmdTest("job_id")。

二、在ASP中使用預存程序

什麼是預存程序（預存程序位於資料庫伺服器中，是一個SQL語句的集合，可包含一個或多個SQL語句）、怎樣建立預存程序不屬於本講座的內容，本講座主要是舉例說明在ASP中如何調用預存程序。

使用預存程序的好處是大大的，預存程序比在ASP指令碼中運行SQL命令效率更高；可以提高整體效能並減輕網路負載（減少了在網路伺服器和資料服務器之間的互動）；可以最佳化ASP代碼和增強代碼靈活性等等。

（一）在預存程序中使用輸入參數

本例中使用的預存程序為SQL Server7.0內建的“byroyalty”，裡面的一條SQL語句非常簡單，無非是多了一個CREATE PROCEDURE byroyalty，並且有一個輸入參數為@percentage：

CREATE PROCEDURE byroyalty @percentage int

AS

select au_id from titleauthor

where titleauthor.royaltyper = @percentage

serve by www.cidu.net

例wuf71.asp

% @LANGUAGE = VBScript %>

!--#include file="AdoSQL7.asp"-->

!--#include file="adovbs.inc"-->

% '' wuf71.asp

Dim cmdTest, prmTest, rsTest

Set cmdTest = Server.CreateObject("ADODB.Command")

cmdTest.CommandText = "byroyalty" ''預存程序名

''設命令類型為 預存程序

cmdTest.CommandType = adCmdStoredProc

''建立 Parameter 對象

Set prmTest = Server.CreateObject("ADODB.Parameter")

‘Type屬性對應wuf70.asp中的第二個參數

prmTest.Type = adInteger ''4 位元組的帶符號整型

‘Direction屬性對應wuf70.asp中的第三個參數

prmTest.Direction = adParamInput

‘Value屬性對應wuf70.asp中的第五個參數

prmTest.Value = 30

cmdTest.Parameters.Append prmTest

Set cmdTest.ActiveConnection = Cnn

‘需要返回一個記錄集，故使用Set rsTest = cmdTest.Execute

Set rsTest = cmdTest.Execute

While Not rsTest.EOF

Response.Write rsTest(0) & " "

rsTest.MoveNext

Wend

Cnn.close

Set rsTest = Nothing : Set prmTest = Nothing

Set cmdTest = Nothing: Set Cnn = Nothing

%>

CommandText屬性既可以指定SQL命令，也可以指定為預存程序或者是表名稱。

在本例中，建立Parameter對象與wuf70.asp略有不同，其實仔細看看，意思也是差不多的，本例還有兩個屬性未用：prmTest.Name、prmTest.Size，加上Type、Direction和Value，對應於wuf70.asp中的五個參數。

（二）使用輸出參數

從資料庫表中取得一個記錄或計算一個值時，需要使用返回輸出參數的預存程序。為了舉例，先在SQL Server的pubs庫中建立一預存程序OUTemploy，該預存程序需要輸入兩個日期，然後輸出一個最大值。

CREATE PROCEDURE OUTemploy

(

@job_lvl tinyint OUTPUT,

@hire_date1 datetime,

@hire_date2 datetime

)

AS

select @job_lvl = MAX(job_lvl) from employee

where hire_date >= @hire_date1 and hire_date = @hire_date2

有多種方法可以建立預存程序：

1．使用Microsoft SQL Server的Enterprise Manager，開啟後在左邊的樹目錄中依次開啟：Console Root – Microsoft SQL Servers – SQL Server Group – ICBCZJP(Windows NT) – databases – pubs – stored procedure – New stored procedure，輸入預存程序後，還可對其進行文法檢測；

2．使用Microsoft SQL Server的Query Analyzer，先串連資料庫伺服器並選擇pubs資料庫。輸入上面的預存程序並點擊Execute Query(或按F5)；

3．使用VB6.0，開啟菜單“視圖”/“資料檢視視窗”後，按右鍵“資料連結”/“建立資料連結”；

4．使用ASP指令碼建立預存程序，例wuf75.asp：

% @LANGUAGE = VBScript %>

!--#include file="AdoSQL7.asp"-->

!--#include file="adovbs.inc"-->

% '' wuf75.asp

Dim StrSQL

''注: & Chr(10) & Chr(13) 完全可以不要, 主要是為了好看

StrSQL="CREATE PROCEDURE OUTemploy ( @job_lvl tinyint OUTPUT, " & Chr(10) & Chr(13) &_

"@hire_date1 datetime, @hire_date2 datetime) AS " & Chr(10) & Chr(13) &_

"select @job_lvl = MAX(job_lvl) from employee " &_

"where hire_date >= @hire_date1 and hire_date = @hire_date2"

Cnn.Execute StrSQL

Response.Write "建立預存程序成功"

Cnn.close: Set Cnn = Nothing

%>

預存程序建立後，除了使用菜單，你還可以使用SQL語句“Drop Procedure OUTemploy”刪除它。

例wuf72.asp – 把所需要的輸入參數送入預存程序並取得輸出結果。

% @LANGUAGE = VBScript %>

!--#include file="AdoSQL7.asp"-->

!--#include file="adovbs.inc"-->

% '' wuf72.asp

Dim cmdTest, prmTest

Set cmdTest = Server.CreateObject("ADODB.Command")

cmdTest.ActiveConnection = Cnn

cmdTest.CommandText = "OUTemploy" ''預存程序名

cmdTest.CommandType = adCmdStoredProc

''建立 Parameter 對象

Set prmTest = cmdTest.CreateParameter("job_lvl",adTinyInt,adParamOutput)

cmdTest.Parameters.Append prmTest

''adTinyInt - 1 位元組帶符號整型

''adDbDate - 日期值 (yyyymmdd)

Set prmTest = cmdTest.CreateParameter("hiredate1",adDBDate,adParamInput,,"1993-05-09")

cmdTest.Parameters.Append prmTest

Set prmTest = cmdTest.CreateParameter("hiredate2",adDBDate,adParamInput,,"1994-02-01")

cmdTest.Parameters.Append prmTest

cmdTest.Execute

‘下面三種表達方式意思一樣

Response.Write cmdtest("job_lvl") & " "

Response.Write cmdTest.Parameters("job_lvl") & " "

Response.Write cmdTest.Parameters("job_lvl").Value

Cnn.close

Set prmTest = Nothing

Set cmdTest = Nothing: Set Cnn = Nothing

%>

（三）使用傳回碼參數

用Return語句可以從預存程序返回不同的傳回碼，如下面預存程序先取得一個記錄集，然後，若有叫Margaret的僱員則返回1，否則返回0。

Create Procedure Returnemploy

AS

select emp_id, fname from employee

If Exists(Select fname From employee Where fname=''Margaret'')

Return(1)

Else

Return(0)

例wuf73.asp

% @LANGUAGE = VBScript %>

!--#include file="AdoSQL7.asp"-->

!--#include file="adovbs.inc"-->

% '' wuf73.asp

Dim cmdTest, prmTest, rsTest

Set cmdTest = Server.CreateObject("ADODB.Command")

cmdTest.ActiveConnection = Cnn

cmdTest.CommandText = "Returnemploy" ''預存程序名

cmdTest.CommandType = adCmdStoredProc

Set prmTest = cmdTest.CreateParameter("ReturnValue",adInteger,adParamReturnValue)

cmdTest.Parameters.Append prmTest

Set rsTest = cmdTest.Execute()

While Not rsTest.EOF

Response.Write rsTest(0) & " ][ " & rsTest(1) & " "

rsTest.MoveNext

Wend

rsTest.Close: Set rsTest = Nothing

''返回 cmdtest("ReturnValue") 之前,必須先關閉rsTest, 否則結果錯誤

If cmdtest("ReturnValue") = 1 Then

Response.Write "有該僱員"

Else

Response.Write "無該僱員"

End If

Cnn.close

Set prmTest = Nothing

Set cmdTest = Nothing: Set Cnn = Nothing

%>

三、如何處理大資料

這裡的“大資料”主要是指Text（大文本）和image（映像）欄位，採用前面所述的方法無法正確擷取其資料。必須首先使用Size = rsTest(0).ActualSize擷取欄位值的實際長度，然後再使用rsTest(0).GetChunk(Size)取得資料。在實際使用過程中，由於這些欄位都比較大，為了節省、合理使用伺服器資源，一般均採取分段讀取的方法。例wuf74.asp:

% @LANGUAGE = VBScript %>

!--#include file="AdoSQL7.asp"-->

!--#include file="adovbs.inc"-->

% '' wuf74.asp

Dim StrSQL, rsTest

‘pr_info 是一個文字欄位

StrSQL = "Select pr_info,pub_id From pub_info"

Set rsTest = Cnn.Execute(StrSQL)

Dim BasicSize, BeginSize, LText

Do While Not rsTest.EOF

Response.Write rsTest(1) & " "

‘每次讀取 1024位元組

BasicSize = 1024

BeginSize = 0

While BeginSize rsTest(0).ActualSize

LText = rsTest(0).GetChunk(BasicSize)

BeginSize = BeginSize + BasicSize

‘逐段輸出到用戶端

Response.Write LText

Wend

Response.Write ""

rsTest.MoveNext

Loop

Cnn.close

Set rsTest = Nothing: Set Cnn = Nothing

%>

在本例中，每次最多讀取1024位元組，分多次讀完。反之，如果將大資料寫至資料庫中，方法類似上面，不過不是使用GetChunk方法，而是使用AppendChunk方法：

rsTest(0).AppendChunk Ltext

注意：最後介紹一個關於SQL Server資料庫的小技巧，如果你遇到過這種情況：資料庫的中文資料顯示出來均是亂碼，請不要驚慌。你只要去我的網站下載sqlsrv32.dll覆蓋“C:\Windows\System”下的同名檔案就可以了。問題發生的源頭是SQL Server驅動程式，典型的情況發生在Windows98第二版（SQL Server驅動程式的版本號碼為3.70.06.23）或Windows2000中或者安裝了MDAC2.5（版本號碼為3.70.08.20）。