详细实例全⾯解析SQL存储过程
全⾯解析SQL存储过程
　存储过程(Stored Procedure)，是⼀组为了完成特定功能的SQL 语句，类似⼀门程序设计语⾔，也包括了数据类型、流程控制、输⼊和输出和它⾃⼰的函数库。存储过程可以说是⼀个记录集，它是由⼀些T-SQL语句组成的代码块，这些T-SQL语句代码像⼀个⽅法⼀样实现⼀些功能(对单表或多表的增删改查)，然后再给这个代码块取⼀个名字，在⽤到这个功能的时候调⽤他就⾏了。不过SQL存储过程对于⼀些初学者来说还是⽐较抽象难理解的，因此本⽂将由浅⾄深地剖析SQL存储过程，帮助你学习它。
存储过程的优点
1. 存储过程只在创造时进⾏编译，以后每次执⾏存储过程都不需再重新编译，⽽⼀般SQL语句每执⾏⼀次就编译⼀次，所以使⽤存储过程
可提⾼数据库执⾏速度，效率要⽐T-SQL语句⾼。
2. 当对数据库进⾏复杂操作时，可将此复杂操作⽤存储过程封装起来与数据库提供的事务处理结合⼀起使⽤。
3. ⼀个存储过程在程序在⽹络中交互时可以替代⼤堆的T-SQL语句，所以也能降低⽹络的通信量，提⾼通信速率。
4. 存储过程可以重复使⽤，可减少数据库开发⼈员的⼯作量。
5. 安全性⾼，可设定只有某些⽤户才具有对指定存储过程的使⽤权
存储过程基本语法
--------------创建存储过程-----------------
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
--------------调⽤存储过程-----------------
EXECUTE Procedure_name '' --存储过程如果有参数，后⾯加参数格式为：@参数名=value，也可直接为参数值value --------------删除存储过程----------------- 创建存储过程的参数
1. procedure_name ：存储过程的名称，在前⾯加#为局部临时存储过程，加##为全局临时存储过程。
2. number：是可选的整数，⽤来对同名的过程分组，以便⽤⼀条 DROP PROCEDURE 语句即可将同组的过程⼀起除去。例如，名为
orders 的应⽤程序使⽤的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。
如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使⽤适当的定界符。
3. @parameter：存储过程的参数。可以有⼀个或多个。⽤户必须在执⾏过程时提供每个所声明参数的值(除⾮定义了该参数的默认值)。
存储过程最多可以有 2100 个参数。
使⽤ @ 符号作为第⼀个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅⽤于该过程本⾝；相同的参数名称可以⽤在其它过程中。默认情况下，参数只能代替常量，⽽不能⽤于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。
4. data_type：参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以⽤作存储过程的参数。不过，cursor 数据类型只能
⽤于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。
说明对于可以是 cursor 数据类型的输出参数，没有最⼤数⽬的限制。
5. VARYING：指定作为输出参数⽀持的结果集(由存储过程动态构造，内容可以变化)。仅适⽤于游标参数。
6. default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执⾏过程。默认值必须是常量或 NULL。如果过程将对该参数
使⽤ LIKE 关键字，那么默认值中可以包含通配符(%、_、[] 和 [^])。
7. OUTPUT：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使⽤ OUTPUT 参数可将信息返回给调⽤过程。Text、ntext
和 image 参数可⽤作 OUTPUT 参数。使⽤ OUTPUT 关键字的输出参数可以是游标占位符。
8. RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运⾏时重新编译。在使⽤⾮典型值或临时值⽽不希望覆盖缓存
在内存中的执⾏计划时，请使⽤ RECOMPILE 选项。
9. ENCRYPTION: 表⽰ SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句⽂本的条⽬。使⽤ ENCRYPTION
可防⽌将过程作为 SQL Server 复制的⼀部分发布。 说明在升级过程中，SQL Server 利⽤存储在 syscomments 中的加密注释来重新创建加密过程。
10. FOR REPLICATION：指定不能在订阅服务器上执⾏为复制创建的存储过程。.使⽤ FOR REPLICATION 选项创建的存储过程可⽤作存
储过程筛选，且只能在复制过程中执⾏。本选项不能和 WITH RECOMPILE 选项⼀起使⽤。
11. AS：指定过程要执⾏的操作。
12. sql_statement：过程中要包含的任意数⽬和类型的 Transact-SQL 语句。但有⼀些限制。
实例操作学习
　下⾯通过表Student来具体了解⼀下存储过程，因为是要了解存储过程的简单⽤法，所以例⼦很简单。
⽆参数存储过程
　选出Student表中的所有信息
create proc StuProc
as      //此处 as 不可以省略不写
begin  //begin 和 end 是⼀对，不可以只写其中⼀个，但可以都不写 select S#,Sname,Sage,Ssex from student end go
有参数存储过程
　全局变量
　全局变量也称为外部变量，是在函数的外部定义的，它的作⽤域为从变量定义处开始，到本程序⽂件的末尾。
　选出指定姓名的学⽣信息:
create proc StuProc
@sname varchar(100)
as
begin select S#,Sname,Sage,Ssex from student where sname=@sname end go exec StuProc '赵雷' //执⾏语句
　上⾯是在外部给变量赋值，也可以在内部直接给变量设置默认值
create proc StuProc
@sname varchar(100)='赵雷'
as begin select S#,Sname,Sage,Ssex from student where sname=@sname end go exec StuProc
　也可以把变量的内容输出，使⽤output
create proc StuProc
@sname varchar(100),
@IsRight int  output //传出参数 as if exists (select S#,Sname,Sage,Ssex from student where sname=@sname) set @IsRight =1 else set @IsRight=0 go declare @
　以上是全局变量，下⾯来了解局部变量
　局部变量
　局部变量也称为内部变量。局部变量是在函数内作定义说明的。其作⽤域仅限于函数内部，离开该函数后再使⽤这种变量是⾮法的。
　局部变量的定义
　必须先⽤Declare命令定以后才可以使⽤，declare{@变量名 数据类型}
　局部变量的赋值⽅法
　set{@变量名=表达式}或者select{@变量名=表达式}
　局部变量的显⽰
create proc StuProc
as
declare @sname varchar(100) set @sname='赵雷' select S#,Sname,Sage,Ssex from student where sname=@sname go exec StuProc
　那如果是要把局部变量的数据显⽰出来怎么办呢？
create proc StuProc
as
declare @sname varchar(100) set @sname=(select Sname from student where S#=01) select @sname go exec StuProc
更详细的实例操作学习
　⽐如，在SQL Server查询编辑器窗⼝中⽤CREATE PROCEDURE语句创建存储过程PROC_InsertEmployee，⽤于实现向员⼯信息表
(tb_Employee)中添加信息，同时⽣成⾃动编号。其SQL语句如下：
IF EXISTS (SELECT name
FROM  sysobjects
WHERE  name = 'Proc_InsertEmployee' AND type = 'P') DROP PROCEDURE Proc_InsertEmployee GO CREATE PROCEDURE Proc_InsertEmployee @PName 存储过程的修改
　创建完存储过程之后，如果需要重新修改存储过程的功能及参数，可以在SQL Server 2005中通过以下两种⽅法进⾏修改：⼀种是⽤
Microsoft SQL Server Mangement修改存储过程；另外⼀种是⽤T-SQL语句修改存储过程。
　使⽤Microsoft SQL Server Mangement修改存储过程，步骤如下：
　(1)在SQL Server Management Studio的“对象资源管理器”中，选择要修改存储过程所在的数据库(如：db_18)，然后在该数据库
下，选择“可编程性”。
　(2)打开“存储过程”⽂件夹，右键单击要修改的存储过程(如：PROC_SEINFO)，在弹出的快捷菜单中选择“修改”命令，将会出现查
询编辑器窗⼝。⽤户可以在此窗⼝中编辑T-SQL代码，完成编辑后，单击⼯具栏中的“执⾏(X)”按钮，执⾏修改代码。⽤户可以在查询编
辑器下⽅的Message窗⼝中看到执⾏结果信息。
　使⽤Transact-SQL修改存储过程：
　使⽤ALTER PROCEDURE语句修改存储过程，它不会影响存储过程的权限设定，也不会更改存储过程的名称。
　语法：
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION  }
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
参数说明
　procedure_name：是要更改的存储过程的名称。
　交叉链接：关于ALTER PROCEDURE语句的其他参数与CREATE PROCEDURE语句相同，可参见上⾯的“创建存储过程的参数”。
　例如，修改存储过程PROC_SEINFO，⽤于查询年龄⼤于35的员⼯信息。SQL语句如下：
ALTER PROCEDURE [dbo].[PROC_SEINFO]
write的返回值
AS
BEGIN
SELECT * FROM tb_Employee where 员⼯年龄>35 END
存储过程的删除
　使⽤Microsoft SQL Server Mangement删除存储过程，步骤如下：
　(1)在SQL Server Management Studio的“对象资源管理器”中，选择要删除存储过程所在的数据库(如：db_student)，然后在该数据库下选择“可编程性”。
　(2)打开“存储过程”⽂件夹，右键单击要删除的存储过程(如：PROC_SEINFO)，在弹出的快捷菜单中选择“删除”命令。
　(3)单击“确定”按钮，即可删除所选定的存储过程。
注意：删除数据表后，并不会删除相关联的存储过程，只是其存储过程⽆法执⾏。
　使⽤T-SQL删除存储过程：
　DROP PROCEDURE语句⽤于从当前数据库中删除⼀个或多个存储过程或过程组。
　语法：
DROP PROCEDURE { procedure } [ ,...n ]
　参数说明：
Procedure：是要删除的存储过程或存储过程组的名称。过程名称必须符合标识符规则。可以选择是否指定过程所有者名称，但不能指定服务器名称和数据库名称。
n：是表⽰可以指定多个过程的占位符。
　例如删除PROC_SEINFO存储过程的SQL语句如下。
DROP PROCEDURE PROC_SEINFO
　例如，删除多个存储过程proc10、proc20和proc30。
DROP PROCEDURE proc10, proc20, proc30
  例如，删除存储过程组procs(其中包含存储过程proc1、proc2、proc3)。
DROP PROCEDURE procs
注意：SQL语句DROP不能删除存储过程组中的单个存储过程。
应⽤存储过程验证⽤户登录⾝份：
　⽬前，验证⽤户登录⾝份的⽅法有多种，⽽通过调⽤存储过程来实现⽤户⾝份验证是⽬前最好的解决⽅案之⼀。因为存储过程在创建时即
在服务器上进⾏编译，所以执⾏起来⽐单个SQL语句要快得多。
　本例是通过调⽤存储过程来验证⽤户登录的⽤户名和密码是否正确。运⾏本实例，在“⽤户名”和“密码”⽂本框中输⼊相应的⽤户名和
密码，单击“登录”按钮即可。
　程序开发步骤：
　(1)新建⼀个⽹站，将其命名为"index"，默认主页名为Default.aspx。
　(2)Default.aspx页⾯涉及到的控件如表1所⽰。
　(3)主要程序代码如下。
　打开SQL Server Management Studio，并连接到SQL Server2005中的数据库。单击⼯具栏中“ ”按钮，新建查询编辑器。在该查
询编辑器中，创建验证登录⽤户⾝份的存储过程PROC_EXISTS，具体的SQL语句如下：
CREATE PROC PROC_EXISTS
(
@UserName NVARCHAR(20),
@PassWord NVARCHAR(20), @ReturnValue int OUTPUT ) AS IF EXISTS(select * from tb_member where userName=@UserName AND passWord=@PassWord
　在"登录"按钮的Click事件下，执⾏验证登录⽤户⾝份的存储过程，如果输⼊的⽤户名和密码正确，则弹出对话框提⽰⽤户登录成功，代码
如下：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。