ASP中使用存储过程

　　学习使用存储过程(StoredProcedure)，是ASP程序员的必须课之一。

所有的大型数据库都支持存储过程，比如Oracle、MSSQL等，（但MSAccess不支持，不过，在Access里可以使用参数化的查询）。

存储过程是利用SQLServer所提供的Tranact-SQL语言所编写的程序。Tranact-SQL语言是SQLServer提供专为设计数据库应用程序的语言，它是应用程序和SQLServer数据库间的主要程序式设计界面。它好比Oracle数据库系统中的Pro-SQL和Informix的数据库系统能够中的Informix-4GL语言一样。这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：
1)、变量说明
2)、ANSI兼容的SQL命令(如Select,Update….)
3)、一般流程控制命令(if…else…、while….)
4)、内部函数

存储过程的书写格


CREATEPROCEDURE[拥有者.]存储过程名[;程序编号]
[(参数#1,…参数#1024)]
[WITH
{RECOMPILEENCRYPTIONRECOMPILE,ENCRYPTION}
]
[FORREPLICATION]
AS程序行


其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数(SQLServer7.0以上版本),参数的使用方法如下:


@参数名数据类型[VARYING][=内定值][OUTPUT]


每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQLServer所支持的数据类型都可使用。

[=内定值]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。


CREATEPROCEDUREorder_tot_amt@o_idint,@p_totintoutputAS
SELECT@p_tot=sum(Unitprice*Quantity)
FROMorderdetails
WHEREordered=@o_id



该例子是建立一个简单的存储过程order_tot_amt,这个存储过程根据用户输入的定单ID号码(@o_id),由定单明细表(orderdetails)中计算该定单销售总额[单价(Unitprice)*数量(Quantity)],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序

使用存储过程有许多好处，它可以封装复杂的数据逻辑，充分发挥大型数据库本身的优势。

我们知道，ASP并不适合做复杂的数据运算，而通过OLDDB访问数据库，由于数据需要在ASP和数据库之间传递，相当消耗系统资源。

事实上，如果数据库仅仅起着数据存储的作用，那么它的功能是远远没有得到利用的。

关于如何创建存储过程，请参考MSSQL的相关文档。

本文介绍存储过程如何在ASP中运用。

简单的一个SQL语句：


selectID,Name,Picture,Time,Dutyfromemploy


我们可以创建一个存储过程：


CREATEPROCEDUREsp_employ
AS
selectID,Name,Picture,Time,Dutyfromemploy
Go


而SQL语句：


selectID,Name,Picture,Time,DutyfromemploywhereID=10230


对应的存储过程是：(用Alter替换我们已有的存储过程)


ALTERPROCEDUREsp_employ
@inIDint
AS
selectID,Name,Picture,Time,DutyfromemploywhereID=@inID
Go



下面对比一下SQL和存储过程在ASP中的情况。首先看看直接执行SQL的情况：


<%
dimConn,strSQL,rs
Conn.OpenDSN=webData;uid=user;pwd=password
strSQL=selectID,Name,Picture,Time,Dutyfromemploy
Setrs=Conn.Execute(strSQL)
%>



再看看如何执行StoredProcedure：

<%
dimConn,strSQL,rs
setConn=Server.CreateObject(ADODB.Connection)
Conn.OpenDSN=webData;uid=user;pwd=password’makeconnection
strSQL=sp_employ
Setrs=Conn.Execute(strSQL)
%>

而执行带参数的StoredProcedure也是相当类似的：


<%
dimConn,strSQL,rs,myInt
myInt=1
setConn=Server.CreateObject(ADODB.Connection)
Conn.OpenDSN=webData;uid=user;pwd=password
strSQL=sp_myStoredProceduremyInt
Setrs=Conn.Execute(strSQL)
%>



在SQLServer中执行存储过程

在SQLServer的查询分析器中，输入以下代码:
declare@tot_amtint
executeorder_tot_amt1,@tot_amtoutput
select@tot_amt

以上代码是执行order_tot_amt这一存储过程，以计算出定单编号为1的定单销售金额，我们定义@tot_amt为输出参数，用来承接我们所要的结果


在ASP中调用存储过程


<!--必须加载文件，否则将出错-->
<!--#includefile=adovbs.inc-->
<%
dimobjCnn
dimobjCmd
dimRs
consto_id=112

-----建立Connection对象----------
setobjCnn=Server.CreateObject(Adodb.connection)
objCnn.Opendriver={sqlserver};server=localhost;uid=sa;pwd=cncanet;database=check;
-----建立Command对象-----------
objCmd.ActiveConnection=objCnn
objCmd.CommandText=order_tot_amt指定存储过程名称
objCmd.CommandType=adCmdStoredProc其为StoredProcedure
-----准备storedprocedure的参数-------
objCmd.Parameters.Append_
objCmd.CreateParameter(o_id,adInteger,adParamInput,,o_id)
objCmd.Parameters.Append_
objCmd.CreateParameter(p_tot,adBigInt,adParamOutput,,0)
-----执行存储过程----------------------
objCmd.Execute

-----输出参数以及处理结果--------------
foreachparminobjCmd.Parameters
next
%>