(九)存储过程
1、 概述
存储过程是可以一次执行的SQL代码块,就像执行C#中的方法一样。存储过程在很多方面和视图很类似,二者的最重要的区别是存储过程可以使用参数。
使用存储过程有很多优点,此外列出三个:
1) 语法比直接使用查询更简单;
2) DBMS优化存储过程的行为,因此操作速度可能更快(但实际上这种差别可能不明显);
3) 通过使用存储过程,实际处理的数据封装在数据库中,这意味着如果要修改数据库结构,可同时修改存储过程而不必重新编译代码——如果修改不是很大。
在存储过程中,可使用变量、分支、布尔逻辑和游标来读取内部查询结果,并进行串行处理。实际上,在很多方面,存储过程编程更类似于C#等语言编程,而不是典型的数据库查询。另外,SQL Server 2005及其后续版本支持使用C#编写存储过程。如果愿意,甚至可以完全在应用程序中使用存储过程调用来代替更新、插入和删除操作。
2、 创建存储过程
2.1 Visual C# 的模板:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
分为五个部分:
1) 第一行定义了存储过程的名称dbo.StoreProcedure1,dbo为database owner;
2) 存储过程的参数规范;
3) AS关键字表示参数规范的结束,后面的SQL代码指定了存储过程的操作;
4) 存储过程的主体,在此模板中由一个被注释掉的命令组成;
5) RETURN关键字结果存储过程。
2.2 SQL Server 2005中的模板:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
按如下操作:查询-->指定模板的参数。得到:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
说明:
1) SET NOCOUNT ON :
该选项在存储过程中经常使用,因为存储过程中执行的大多数SQL语句都返回受影响的行数。存储过程可能执行很多SQL语句,因此返回这个数据可能会对存储过程要返回的数据造成干扰。除非真的需要这些信息,否则应在存储过程中包含这条命令。
2) 存储过程的名称:
前面两个模板有一个区别:Visual C# 的包含存储过程所属的架构dbo(对于小型应用程序或用户不多的应用程序,可能所有的数据库对象都属于同一所有者,即dbo——database owner)。通常应在名称中包含架构,通过指定SQL Server对象的所有者,可以通过每个所有者实施安全设置。
名称后面可以包含一个分号和一个编号,例如:
CREATE PROCEDRUE dbo.ProcedureName ; 1
可以创建多个名称相同而编号不同的存储过程。如此可将这些存储过程组合在一起,可以使用一个命令一次性删除:DROP PROCEDURE dbo.ProcedureName
关键字PROCEDURE 可简写为PROC。
3) 参数规范:
参数是可选的,参数放在括号中。参数以下列形式指定:
<Parameter Name> <Parameter Data Type>
参数名必须以@打头,数据类型是SQL Server数据类型,且可以包含其他规范。多个参数间用逗号分隔。
也可以指定下列信息:
Ø 对于游标返回类型,必须使用VARYING 关键字。它表示返回类型是非标量值,因此可以返回多个值。这样就可以通过参数返回结果集ADO.NET中,不能使用返回游标的存储过程,而只能在其他SQL脚本中使用。
Ø 可以为参数指定默认值,使得使用存储过程时可以不指定参数,即使默认值为NULL。为此,只需要在参数后面加上 = 号和要指定的值。
Ø 用OUTPUT 关键字标记输出参数。
例子:
(
@myIdentifier int = -2,
@mySearch varchar ( 50 ) = ’ % ‘ ,
@ExtendedInfo CURSOR VARYING OUTPUT
)
在存储过程的参数规范末尾,也可使用一些高级选项,用于指定存储过程如何执行。用AS结束参数规范。
4) 存储过程的主体:
首先要注意参数的用法。
Ø 设置参数的值,可用SET关键字:如SET < parameter name > = < value >
Ø 局部变量,用DECLARE : 如 DECLARE @myTempInt int。注意声明和设置游标类型变量时要使用不同的代码。
Ø 游标类型变量:DECLARE @myCursorVariable CURSOR
RETURN 关键字终止存储过程的执行,它只能返回整形值的结果,且它是可选的。它通常作为状态码返回。不干扰SELECT语句返回的结果,而是被独立解释。
存储过程在遇到结果时才返回结果。例如,每次执行SELECT语句时都将返回结果,除非将结果存储在变量中。因此存储过程可能返回多个结果集。这时,可使用SqlDataReader对象读取查询结果,并使用SqlDataReader.NextResult()方法从一个返回的结果移到下一个。
5) 关键字GO :表示执行到这里为止的所有SQL语句。用于分隔不同功能的代码块或者不同的存储过程。
6) 附一实例:
3、 使用存储过程
3.1 概述
使用存储过程有两种方式:在C#应用程序中使用ADO.NET,通过脚本执行。存储过程可以调用其他存储过程和用户自定义函数。
3.2 脚本执行存储过程
Ø 例子:
DECLARE @RC int
DECLARE @EndingType varchar(50)
SET @EndingType = ‘sad’
EXECUTE @RC = [FolkTaleDB].[dbo].[GetStoriesByEnding] @EndingType
Ø 或者:
EXECUTE [FolkTaleDB].[dbo].[GetStoriesByEnding] ‘sad’
Ø 可以使用关键字NULL或DEFAULT来传递空值或默认值。输出参数必须先定义一个变量,并包含OUTPUT关键字,如下所示:
DECLARE @OutputParam int
EXECUTE [FolkTaleDB].[dbo].[SprocWithOutput] 5 , @OutputParam OUTPUT
Ø 也可以通过名称而不是位置来指定任意数量的参数,甚至可以省略定义了默认值的参数。例如:
EXECUTE [FolkTaleDB].[dbo].[GetStoriesByEnding] @EndingType = ‘ sad ’
4、 通过存储过程更新数据
使用存储过程可以方便地更新、插入和删除数据库中的数据。
5、 条件逻辑与循环
下面列出可用于SQL 的逻辑和分支结构:
Ø 使用GOTO的简单分支:有着在编程语言中的一切问题,应避免使用。
Ø 使用WHILE 的循环:SQL中唯一的循环。通过使用计数器,可实现类似于C#风格的for…next循环。
Ø 使用IF和ELSE的分支:ELSE为可选项。
5.1 WHILE 循环
语法:
WHILE <condition>
<statement to execute>
如果要执行的语句是代码块,其必须放在BEGIN和END之间。例如:
DECLARE @Counter int
SET @Counter = 0
WHILE @Counter < 10
BEGIN
SELECT @Counter AS Counter
SET @Counter=@Counter + 1
END
代码返回10个结果集,值为0-9。
关键字BREAK可退出循环,或使用CONTINUE关键字来退出当前循环周期并开始下一个循环周期(如果条件的值仍为true的话)。
5.2 IF…ELSE分支
语法:
IF <condition>
< statement to execute if true >
ELSE
< statement to execute if false >
在IF语句中,常用一种技术是,使用EXISTS运算符来判断行是否存在,例如:
IF EXISTS (SELECT EndingID FROM Ending WHERE EndingType = ‘sad’)
SELECT ‘ Yep. ’ AS Result
ELSE
SELECT ‘Nope. ’ AS Result
6、 游标
6.1 概述
游标使得能够在SQL代码中处理结果集中的行,因此它与数据阅读器类类似。游标是一种高级技术,大多数应用程序不需要使用游标。然而,游标可以完成一些以其他方法难以完成的功能。
6.2 游标的基本用法
要创建游标,必须声明它并指定它要使用的结果集。语法如下:
DECLARE MyCursor CURSOR FOR <select statement>
<select statement >是返回游标要使用的结果集的SQL SELECT 语句。也可以声明游标变量,并使用游标给这些变量赋值:
DELARE @MyCursorVal CURSOR
SET @MyCursorVal = MyCursor
创建游标后,必须打开它才能读取行数据。方法:OPEN MyCursor。用完游标后,关闭它:CLOSE MyCursor。如果游标不再需要了,可释放它:DEALLOCATE Mycursor。释放后不能再打开,但是游标的名称还可以用来创建一个新游标。
6.3 游标选项
用法:DECLARE MyCursor CURSOR <key> <key> FOR <select statement>
下面解释这些选项:
Ø 游标的作用域:GLOBAL(默认值),LOCAL。
Ø 游标的读取行为:FORWARD_ONLU ,SCROLL。
Ø 游标类型:STATIC,KEYSET, DYNAMIC, FAST_FORWARD。
Ø 游标数据修改行为:READ_ONLY,SCROLL_LOCKS,OPTIMISTIC。
Ø 转换警告:在有些复杂的情况下,可将游标(其select语句)转换为不同的类型,这样它返回的列将可以不同。在这样情况下,可使用TYPE_WARNING关键字收到通知。
6.4 使用游标导航结果
下面的语句将返回一个只包含一行的结果集,并将结果集中的游标位置设置为返回的行:
FETCH FROM MyCursor
也可以用INTO将获得的结果放在变量中:
FETCH FROM MyCursor INTO @myVar1,@myVar2
这里按照列在结果中的顺序将它们的值复制给变量。
如果游标中包含SCOROLL选项,则可以和导航选项在数据中以其他方式移动:
FETCH < navigation option > FROM MyCursor
< navigation option >可为下表关键字之一:
关键字 | 说明 |
NEXT | 默认行为,将游标移到结果集中的下一行 |
PRIOR | 将游标移到上一行 |
FIRST | 将游标移到结果集中的第一行 |
LAST | 将游标移到结果集中的最后一行 |
ABSOLUTE n | 返回结果集中的第n行;如果n为负数,则返回结果集中的倒数第n行。如果n为0,则不返回任何行。如果游标使用了DYNAMIC选项,则不能使用此选项 |
RELATIVE n | 返回从游标当前位置开始的第n行;如果n为负数,则返回从游标当前位置往前数第n行。如果n为0,则返回当前行;如果还没有获取行,则不返回任何行 |
全局变量FETCH_STATUS可用于检测没有返回任何行的情况。其可能的取值有3个:
Ø 0 :FETCH操作成功;
Ø 1 :将游标移到上一行。FETCH操作失败,因为请求的行超出结果集的范围;
Ø 2 :FETCH操作失败,因为不存在请求的行(只适用于KEYSET游标)。
此变量常见用途是,在WHILE循环中使用游标来遍历整个结果集:
FETCH NEXT FROM MyCursor
WHILE @@FETCH_STATUS = 0
BEGIN
--Operate on current row.
FETCH NEXT FROM MyCursor
END
6.5 使用游标修改数据
可以使用游标返回的主键值来修改底层数据表的行。如果要这样做,可以使用READ_ONLY游标,但如果要使修改反映在游标数据中,应使用DYNAMIC 或 KEYSET游标。在有些情况下,只能使用获得的主键值来修改数据。
另外,也可以直接通过游标访问当前行以编辑数据。为此,不能使用READ_ONLY游标,且只能编辑游标声明中FOR UPDATE OF 子句指定的列。在遵循这些的规则的情况下,可以使用标准的UPDATE 或DELETE语法来更新或删除行,但必须在语句后面的WHERE子句中指定一个特定的值。WHERE子句如下:WHERE CURRENT OF <cursor name or cursor variable>。例如:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
6.6 高级存储过程
例子:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)