（九）存储过程
1、 概述
存储过程是可以一次执行的SQL代码块，就像执行C#中的方法一样。存储过程在很多方面和视图很类似，二者的最重要的区别是存储过程可以使用参数。
使用存储过程有很多优点，此外列出三个：
1) 语法比直接使用查询更简单；
2) DBMS优化存储过程的行为，因此操作速度可能更快（但实际上这种差别可能不明显）；
3) 通过使用存储过程，实际处理的数据封装在数据库中，这意味着如果要修改数据库结构，可同时修改存储过程而不必重新编译代码——如果修改不是很大。
在存储过程中，可使用变量、分支、布尔逻辑和游标来读取内部查询结果，并进行串行处理。实际上，在很多方面，存储过程编程更类似于C#等语言编程，而不是典型的数据库查询。另外，SQL Server 2005及其后续版本支持使用C#编写存储过程。如果愿意，甚至可以完全在应用程序中使用存储过程调用来代替更新、插入和删除操作。
2、 创建存储过程
2.1 Visual C# 的模板：
Code
分为五个部分：
1) 第一行定义了存储过程的名称dbo.StoreProcedure1，dbo为database owner；
2) 存储过程的参数规范；
3) AS关键字表示参数规范的结束，后面的SQL代码指定了存储过程的操作；
4) 存储过程的主体，在此模板中由一个被注释掉的命令组成；
5) RETURN关键字结果存储过程。
2.2 SQL Server 2005中的模板：
Code
按如下操作：查询-->指定模板的参数。得到：
Code说明：
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>。例如：
Code6.6 高级存储过程
例子：
Code
