SQL Server存储过程

协助一个VC++的项目，用的数据库是SQLServer，定义了很多存储过程。以后的对应要修改数据库结构，自然需要修改存储过程，所以先了解一下。

Sql Server的存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。存储过程相对于其他的数据库访问方法有以下的优点：
(1)重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
(2)提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。
(3)减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
(4)安全性。参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

创建存储过程的语句如下：

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ [ OUT [ PUT ] 
    ] [ ,n ] 
[ WITH <procedure_option> [ ,n ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

[schema_name]: 代表的是存储过程所属的架构的名称
[；Number]: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。
[@ parameter]: 存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为等于另一个参数，否则用户必须在调用存储过程的时候为参数赋值。存储过程最多有2100个参数。
[RECOMPILE]：指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。
[ENCRYPTION]：是一种加密的功能， 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。

例子：

IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'au_info_all' AND type = 'P')
   DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
GO

Transaction-SQL游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的 Transaction-SQL语句。在存储过程或触发器中使用Transaction-SQL游标的过程为：
(1)声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。
(2)使用Declare Cursor语句将Transaction-SQL游标与Select语句相关联。还可以利用Declare Cursor定义游标的只读、只进等特性。　
(3)使用Open语句执行Select语句填充游标。
(4)使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。 Transaction-SQL游标不支持提取行块。
(5)使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用 Deallocate语句才会完全释放。

游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。
声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select 语句。
  Declare 游标名称 Cursor 参数
声明游标的参数
(1)Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。
(2)Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。
(3)Static、Keyset与Dynamic: 第一个表示定义一个游标，其数据存放到一个临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不反映对基表所作的修改，并且该游标不允许修改。Keyset表示的是，当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic表示的是滚动游标时，动态游标反映对结果集内所有数据的更改。
(4)Read_only 、Scroll_Locks与Optimistic：第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。
标准游标：
  Declare MyCursor Cursor
    For Select * From Master_Goods
只读游标
  Declare MyCusror Cursor
    For Select * From Master_Goods
    For Read Only
可更新游标
  Declare MyCusror Cursor
    For Select * From Master_Goods
    For UpDate

打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。
打开游标
  全局游标：Open Global MyCursor            局部游标: Open MyCursor

读取游标数据：在打开游标以后，使用Fetch语句从 Transaction-SQL服务器游标中检索特定的一行。使用Fetch操作，可以使游标移动到下一个记录，并将游标返回的每个列得数据分别赋值给声明的本地变量。
  Fetch [Next | Prior | First | Last | Absolute　n　| Relative　n　]  From MyCursor
  Into @GoodsID,@GoodsName
其中:Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为Next
Prior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。
First表示返回结果集中的第一行，并且将其作为当前行。
Last表示返回结果集中的最后一行，并且将其作为当前行。
Absolute　n　如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。
Relative　n　如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。

关闭游标调用的是Close语句，方式如下：
  Close Global MyCursor               Close MyCursor

释放游标调用的是Deallocate语句，方法如下：
  Deallocate Glboal MyCursor       Deallocate MyCursor

游标实例:

Declare MyCusror Cursor Scroll
  For Select * From Master_Goods Order By GoodsID
Open MyCursor
Fetch next From MyCursor
Into @GoodsCode,@GoodsName
While(@@Fetch_Status = 0)
  Begin
    Begin
      Select @GoodsCode = Convert(Char(20),@GoodsCode)
      Select @GoodsName = Convert(Char(20),@GoodsName)
      PRINT @GoodsCode + ':' + @GoodsName
    End
    Fetch next From MyCursor
    Into @GoodsCode,@GoodsName
  End
Close MyCursor
Deallocate MyCursor

修改当前游标的数据方法如下:
  UpDate Master_Goods Set GoodsName = 'xxxx' Where Current Of MyCursor;
删除当前游标行数据的方法如下:
  Delete From Master_Goods Where Current Of MyCursor
Select @@CURSOR_ROWS 可以得到当前游标中存在的数据行数。注意：此变量为一个连接上的全局变量，因此只对应最后一次打开的游标。

【参考】
http://tech.ddvip.com/2008-08/121826978452413.html
http://www.pconline.com.cn/smb/yrjc/0701/955251.html
http://www.cnblogs.com/yangyang8848/archive/2009/07/03/1515731.html
http://www.cnblogs.com/yangyang8848/archive/2009/07/02/1514593.html