SQL Server存储过程的基本概念以及语法汇总Word格式文档下载.docx
《SQL Server存储过程的基本概念以及语法汇总Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《SQL Server存储过程的基本概念以及语法汇总Word格式文档下载.docx（6页珍藏版）》请在冰豆网上搜索。
下面介绍使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程
创建存储过程前,应该考虑下列几个事项:
①不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。
②存储过程可以嵌套使用,嵌套的最大深度不能超过32层。
③创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。
④存储过程是数据库对象,其名称必须遵守标识符规则。
⑤只能在当前数据库中创建存储过程。
⑥一个存储过程的最大尺寸为128M。
使用CREATEPROCEDURE创建存储过程的语法形式如下:
QUOTE:
CREATEPROC[EDURE]procedure_name[;
number][;
number]
[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]
WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
ASsql_statement[...n]
用CREATEPROCEDURE创建存储过程的语法参数的意义如下:
procedure_name:
用于指定要创建的存储过程的名称。
number:
该参数是可选的整数,它用来对同名的存储过程分组,以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。
@parameter:
过程中的参数。
在CREATEPROCEDURE语句中可以声明一个或多个参数。
data_type:
用于指定参数的数据类型。
VARYING:
用于指定作为输出OUTPUT参数支持的结果集。
Default:
用于指定参数的默认值。
OUTPUT:
表明该参数是一个返回参数。
例如:
下面创建一个简单的存储过程productinfo,用于检索产品信息。
USENorthwind
ifexists（selectnamefromsysobjects
wherename='
productinfo'
andtype='
p'
dropprocedureproductinfo
GO
createprocedureproductinfo
as
select*fromproducts
通过下述sql语句执行该存储过程:
executeproductinfo
即可检索到产品信息。
执行存储过程
直接执行存储过程可以使用EXECUTE命令来执行,其语法形式如下:
[[EXEC[UTE]]
{[@return_status=]
{procedure_name[;
number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,...n]
[WITHRECOMPILE]
使用EXECUTE命令传递单个参数,它执行showind存储过程,以titles为参数值。
showind存储过程需要参数（@tabname,它是一个表的名称。
其程序清单如下:
EXECshowindtitles
当然,在执行过程中变量可以显式命名:
EXECshowind@tabname=titles
如果这是isql脚本或批处理中第一个语句,则EXEC语句可以省略:
showindtitles或者showind@tabname=titles
下面的例子使用了默认参数
CREATEPROCEDUREinsert_Products_1
（@SupplierID_2int,
@CategoryID_3int,
@ProductName_1nvarchar（40='
无'
ASINSERTINTOProducts
（ProductName,SupplierID,CategoryID
VALUES
（@ProductName_1,@SupplierID_2,@CategoryID_3
execinsert_Products_11,1
Select*fromProductswhereSupplierID=1andCategoryID=1
下面的例子使用了返回参数
CREATEPROCEDUREquery_products
（@SupplierID_1int,
@ProductName_2nvarchar（40output
AS
select@ProductName_2=ProductNamefromproducts
whereSupplierID=@SupplierID_1
执行该存储过程来查询SupplierID为1的产品名:
declare@productnvarchar（40
execquery_products1,@productoutput
select'
产品名'
=@product
go
查看存储过程
存储过程被创建之后,它的名字就存储在系统表sysobjects中,它的源代码存放在系统表syscomments中。
可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。
使用企业管理器查看用户创建的存储过程
在企业管理器中,打开指定的服务器和数据库项,选择要创建存储过程的数据库,单击存储过程文件夹,此时在右边的页框中显示该数据库的所有存储过程。
用右键单击要查看的存储过程,从弹出的快捷菜单中选择属性选项,此时便可以看到存储过程的源代码。
使用系统存储过程来查看用户创建的存储过程
可供使用的系统存储过程及其语法形式如下:
sp_help:
用于显示存储过程的参数及其数据类型
sp_help[[@objname=]name]
参数name为要查看的存储过程的名称。
sp_helptext:
用于显示存储过程的源代码
sp_helptext[[@objname=]name]
sp_depends:
用于显示和存储过程相关的数据库对象
sp_depends[@objname=]’object’
参数object为要查看依赖关系的存储过程的名称。
sp_stored_procedures:
用于返回当前数据库中的存储过程列表
修改存储过程
存储过程可以根据用户的要求或者基表定义的改变而改变。
使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程,但不会更改权限,也不影响相关的存储过程或触发器。
其语法形式如下:
ALTERPROC[EDURE]procedure_name[;
[{@parameterdata_type}
[VARYING][=default][OUTPUT]][,...n][WITH
sql_statement[...n]
重命名和删除存储过程
1.重命名存储过程
修改存储过程的名称可以使用系统存储过程sp_rename,其语法形式如下:
sp_rename原存储过程名称,新存储过程名称
另外,通过企业管理器也可以修改存储过程的名称。
删除存储过程
删除存储过程可以使用DROP命令,DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除,其语法形式如下:
dropprocedure{procedure}[,…n]
当然,利用企业管理器也可以很方便地删除存储过程。
存储过程的重新编译
在我们使用了一次存储过程后,可能会因为某些原因,必须向表中新增加数据列或者为表新添加索引,从而改变了数据库的逻辑结构。
这时,需要对存储过程进行重新编译,SQLServer提供三种重新编译存储过程的方法:
1、在建立存储过程时设定重新编译
语法格式:
CREATEPROCEDUREprocedure_nameWITHRECOMPILEASsql_statement
2、在执行存储过程时设定重编译
EXECUTEprocedure_nameWITHRECOMPILE
3、通过使用系统存储过程设定重编译
语法格式为:
EXECsp_recompileOBJECT
系统存储过程与扩展存储过程
1.系统存储过程
系统存储过程存储在master数据库中,并以sp_为前缀,主要用来从系统表中获取信息,为系统管理员管理SQLServer提供帮助,为用户查看数据库对象提供方便。
比如用来查
看数据库对象信息的系统存储过程sp_help、显示存储过程和其它对象的文本的存储过程sp_helptext等。
2.扩展存储过程：
扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库（DLL文件所包含的函数中实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用Transact-SQL过程那样从Transact-SQL语句调用这些函数。
例:
利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。
执行代码：
usemasterexecxp_cmdshell'
dir*.exe'
执行结果返回系统目录下的文件内容文本信息。
最后给大家举一个例子：
QUOTE:
/**1、在Northwind数据库中，创建一个带查询参数的存储过程，要求在输入一个定购金额总额＠total时，查询超出该值的所有产品的相关信息，包括产品名称和供应商名称、单位数量、单价、以及该产品的定购金额总额，并通过一个输出参数返回满足查询条件的产品数**/IFexists（select*fromSysObjectswherename='
more_than_total'
andtype='
dropproceduremore_than_totalgoCREATEPROCEDUREMore_Than_Total@totalmoney=0ASDeclare@amountsmallintBEGINselectdistinctP.productName,S.contactName,P.UnitPricefromProductsPinnerjoin[orderDetails]O
onp.productID=o.productIDinnerjoinsupplierssonp.supplierID=s.SupplierIDwhereO.productIDin（selectproductIDfrom[orderDetails]groupbyproductIdhavingsum（quantity*unitprice>
@totalENDGO
升级会员 