存儲(chǔ)過(guò)程介紹及asp+存儲(chǔ)過(guò)程的使用

一、先介紹一下什么是存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是利用SQL Server所提供的Tranact-SQL語(yǔ)言所編寫的程序。Tranact-SQL語(yǔ)言是SQL Server提供專為設(shè)計(jì)數(shù)據(jù)庫(kù)應(yīng)用程序的語(yǔ)言，它是應(yīng)用程序和SQL Server數(shù)據(jù)庫(kù)間的主要程序式設(shè)計(jì)界面。它好比Oracle數(shù)據(jù)庫(kù)系統(tǒng)中的Pro-SQL和Informix的數(shù)據(jù)庫(kù)系統(tǒng)能夠中的Informix-4GL語(yǔ)言一樣。這類語(yǔ)言主要提供以下功能，讓用戶可以設(shè)計(jì)出符合引用需求的程序：
1)、變量說(shuō)明
2)、ANSI兼容的SQL命令(如Select,Update….)
3)、一般流程控制命令(if…else…、while….)
4)、內(nèi)部函數(shù)

二、存儲(chǔ)過(guò)程的書寫格

CREATE PROCEDURE [擁有者.]存儲(chǔ)過(guò)程名[;程序編號(hào)]
[(參數(shù)#1,…參數(shù)#1024)]
[WITH
 {RECOMPILE ENCRYPTION RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行

其中存儲(chǔ)過(guò)程名不能超過(guò)128個(gè)字。每個(gè)存儲(chǔ)過(guò)程中最多設(shè)定1024個(gè)參數(shù)
(SQL Server 7.0以上版本),參數(shù)的使用方法如下:

@參數(shù)名 數(shù)據(jù)類型 [VARYING] [=內(nèi)定值] [OUTPUT]

每個(gè)參數(shù)名前要有一個(gè)“@”符號(hào),每一個(gè)存儲(chǔ)過(guò)程的參數(shù)僅為該程序內(nèi)部使用,參數(shù)的類型除了IMAGE外，其他SQL Server所支持的數(shù)據(jù)類型都可使用。
[=內(nèi)定值]相當(dāng)于我們?cè)诮�立�?shù)據(jù)庫(kù)時(shí)設(shè)定一個(gè)字段的默認(rèn)值，這里是為這個(gè)參數(shù)設(shè)定默認(rèn)值。[OUTPUT]是用來(lái)指定該參數(shù)是既有輸入又有輸出值的，也就是在調(diào)用了這個(gè)存儲(chǔ)過(guò)程時(shí)，如果所指定的參數(shù)值是我們需要輸入的參數(shù)，同時(shí)也需要在結(jié)果中輸出的，則該項(xiàng)必須為OUTPUT，而如果只是做輸出參數(shù)用，可以用CURSOR，同時(shí)在使用該參數(shù)時(shí)，必須指定VARYING和OUTPUT這兩個(gè)語(yǔ)句。

 例子:
 CREATE PROCEDURE order_tot_amt @o_id int,@p_tot int output AS
 SELECT @p_tot = sum(Unitprice*Quantity)
 FROM orderdetails
 WHERE ordered=@o_id
 
 例子說(shuō)明:
該例子是建立一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程order_tot_amt,這個(gè)存儲(chǔ)過(guò)程根據(jù)用戶輸入的定單ID號(hào)碼(@o_id),由定單明細(xì)表(orderdetails)中計(jì)算該定單銷售總額[單價(jià)(Unitprice)*數(shù)量(Quantity)],這一金額通過(guò)@p_tot這一參數(shù)輸出給調(diào)用這一存儲(chǔ)過(guò)程的程序

三、在SQL Server中執(zhí)行存儲(chǔ)過(guò)程

在SQL Server的查詢分析器中，輸入以下代碼:
declare @tot_amt int
execute order_tot_amt 1,@tot_amt output
select @tot_amt

以上代碼是執(zhí)行order_tot_amt這一存儲(chǔ)過(guò)程，以計(jì)算出定單編號(hào)為1的定單銷售金額，我們定義@tot_amt為輸出參數(shù)，用來(lái)承接我們所要的結(jié)果

四、在ASP中調(diào)用存儲(chǔ)過(guò)程

<!--           必須加載adovbs.inc文件，否則將出錯(cuò)        -->
<!--#include file="adovbs.inc"-->
<%
dim objCnn
dim objCmd
dim Rs
const o_id=112

'-----建立Connection對(duì)象----------
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "driver={sql server};server=localhost;uid=sa;pwd=cncanet;database=check;"
'-----建立Command對(duì)象-----------
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="order_tot_amt" '指定存儲(chǔ)過(guò)程名稱
objCmd.CommandType=adCmdStoredProc '其為Stored Procedure
'-----準(zhǔn)備stored procedure 的參數(shù)-------
objCmd.Parameters.Append _
 objCmd.CreateParameter("o_id",adInteger,adParamInput,,o_id)
objCmd.Parameters.Append _
 objCmd.CreateParameter("p_tot",adBigInt,adParamOutput,,0)
'-----執(zhí)行存儲(chǔ)過(guò)程----------------------
objCmd.Execute

'-----輸出參數(shù)以及處理結(jié)果--------------
for each parm in objCmd.Parameters
 Response.Write parm.name &"="& trim(parm) &"<br>"
next
%>

以上代碼在Win2000+IIS5.0+SQL Server2000中通過(guò)