SqlServer中的存储过程和游标操作

[size=medium]--------------开篇讲述-----------------------------
首先介绍一下什么是存储过程：
存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，
还可以根据条件执行不同SQL语句， 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。
---------------注意事项----------SQL2008 R2------
*修改存储过程和重新创建存储过程的区别：
如果需要更改存储过程中的语句或参数，可以删除并重新创建该存储过程，也可以通过一个步骤更改该存储过程。
删除并重新创建存储过程时，与该存储过程关联的所有权限都将丢失。更改存储过程时，将更改过程或参数定义，
但为该存储过程定义的权限将保留，并且不会影响任何相关的存储过程或触发器。还可以修改存储过程以加密其定
义或使该过程在每次执行时都得到重新编译。
*修改存储过程出错：
更改存储过程的名称或定义可能会导致所有相关对象在执行时失败，如果这些相关对象未进行更新以反映对该存储过程所做的更改。

1、----------------------------------SQLserver存储过程的语法------------------------------

Create PROC [ EDURE ] procedure_name [ number ] [ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]

简单一点就是：Create PROCEDURE procedure_name [参数个数] [有参数/没有参数 ] AS 过程体 GO
存储过程的调用：execute procedure [参数]

2、---------------------------------------查看存储过程-------------------------
-- 若要查看存储过程的定义
sys.sql_modules (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)
sp_helptext (Transact-SQL)
-- 查看有关存储过程的信息
sys.objects (Transact-SQL)
sys.procedures (Transact-SQL)
sys.parameters (Transact-SQL)
sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL)
sp_help (Transact-SQL)
-- 查看存储过程的依赖关系
sys.sql_expression_dependencies (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
-- 查看有关扩展存储过程的信息
sp_helpextendedproc (Transact-SQL)

3、------------------------存储过程示例：--------------------------
----------示例1：--------------简单数据查询------------------
create proc query_book
as
select * from book
go
--调用存储过程
exec query_book
----------示例2：--------------存储过程参数传递------------------
create procedure dbo.Get_operator
@Czy_code varchar(10),

@Czy_password varchar(10)
as
declare Czy_name varchar(10)
declare Czy_in_station varchar(10)

begin transaction
select @Czy_name = name,@Czy_in_station = in_station
from dbo.User where code = @Czy_code and password =@Czy_password
commit
select @Czy_code,@Czy_name,@Czy_in_station

GO
----------示例3：--------------分页存储过程------------------
create proc commonPagination
@columns varchar(500), --要显示的列名，用逗号隔开
@tableName varchar(100), --要查询的表名
@orderColumnName varchar(100), --排序的列名
@order varchar(50), --排序的方式，升序为asc,降序为 desc
@where varchar(100), --where 条件，如果不带查询条件，请用 1=1
@pageIndex int, --当前页索引
@pageSize int, --页大小(每页显示的记录条数)
@pageCount int output --总页数，输出参数
as
begin
declare @sqlRecordCount nvarchar(1000) --得到总记录条数的语句
declare @sqlSelect nvarchar(1000) --查询语句
set @sqlRecordCount=N'select @recordCount=count(*) from '
+@tableName + ' where '+ @where
declare @recordCount int --保存总记录条数的变量
exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output

--动态 sql 传参
if( @recordCount % @pageSize = 0) --如果总记录条数可以被页大小整除
set @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
else --如果总记录条数不能被页大小整除
set @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1
set @sqlSelect =
N'select '+@columns+' from (
select row_number() over (order by '
+@orderColumnName+' '+@order
+') as tempid,* from '
+@tableName+' where '+ @where
+') as tempTableName where tempid between '
+str((@pageIndex - 1)*@pageSize + 1 )
+' and '+str( @pageIndex * @pageSize)
exec (@sqlSelect) --执行动态Sql
end
go
--以下是调用示例
use pubs
go
declare @pageCount int
exec commonPagination 'job_id,job_desc','jobs','job_id', 'asc','1=1',2,2,@pageCount output
select '总页数为：' + str(@pageCount)

4、 ----------------------------------存储过程输入输出演示------------------------------------------------
存储过程的3种传回值（方便正在看这个例子的朋友不用再去查看语法内容）:
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别: output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。

实例1：只返回单一记录集的存储过程。

要求：查询表bankMoney的内容的存储过程
create procedure sp_query_bankMoney as
select * from bankMoney
go
exec sp_query_bankMoney
注* 在使用过程中只需要把中的SQL语句替换为存储过程名，就可以了很方便吧！

实例2（向存储过程中传递参数）：

加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总金额。
Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output with encryption ---------加密 as
insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan' go
在SQL Server查询分析器中执行该存储过程的方法是： declare @total_price int
exec insert_bank '004','Zhangsan','男',100,@total_price output print '总余额为'+convert(varchar,@total_price) go

实例3：使用带有复杂 Select 语句的简单过程

下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。 USE pubs
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
au_info_all 存储过程可以通过以下方法执行： EXECUTE au_info_all -- or
EXEC au_info_all
如果该过程是批处理中的第一条语句，则可使用： au_info_all

实例4：使用带有参数的简单过程

Create PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) 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 Where au_fname = @firstname AND au_lname = @lastname GO
au_info 存储过程可以通过以下方法执行： EXECUTE au_info 'Dull', 'Ann' -- or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' -- or
EXEC au_info 'Dull', 'Ann' -- or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull' 如果该过程是批处理中的第一条语句，则可使用： au_info 'Dull', 'Ann' -- or
au_info @lastname = 'Dull', @firstname = 'Ann' -- or
au_info @firstname = 'Ann', @lastname = 'Dull'

实例5：使用带有通配符参数的简单过程

Create PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' 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 Where au_fname LIKE @firstname AND au_lname LIKE @lastname GO
au_info2 存储过程可以用多种组合执行。下面只列出了部分组合： EXECUTE au_info2 -- or
EXECUTE au_info2 'Wh%' -- or
EXECUTE au_info2 @firstname = 'A%' -- or
EXECUTE au_info2 '[CK]ars[OE]n' -- or
EXECUTE au_info2 'Hunter', 'Sheryl' -- or
EXECUTE au_info2 'H%', 'S%'
5、--------------------------------存储过程中游标的使用--------------------------------------------------
(1)、需要游标的数据操作

当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组
活动集：select语句返回的元组的集合
当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。
(2)、游标分类

滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。
非滚动游标：只能在活动集中顺序地取下一个元组。
更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。
(3)、定义与使用游标的语句

declare ：
declare 游标名[scroll] cursor for select语句[for update [of列表名]] 定义一个游标，使之对应一个select语句
for update任选项，表示该游标可用于对当前行的修改与删除
open
打开一个游标，执行游标对应的查询，结果集合为该游标的活动集
open 游标名
fetch
在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中
fetch [next | prior | first | last | current | relative n | absolute m] 游标名into [变量表]
close
关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句
close 游标名
deallocate
删除游标，以后不能再对该游标执行open语句
deallocate 游标名
@@FETCH_STATUS
返回被FETCH 语句执行的最后游标的状态.
0 fetch语句成功
-1 fetch语句失败
-2 被提取的行不存在
(4)、游标实例
例:查询电子商务系学生信息,性别为女输出为female,否则输出为male？
declare c1 cursor for select sno,sname,ssex from student where sdept='ec'
declare @sno char(10),@sname char(10),@ssex char(2)
Open c1
Fetch c1 into @sno,@sname,@ssex
While @@fetch_status==0
Begin
if @ssex='女'
begin set @ssex='female' end
else
begin set @ssex='male' end
Select @sno,@sname ,@ssex
Fetch c1 into @sno,@sname,@ssex
end

(5)修改存储过程
例： ALTER PROC [dbo].[dnt_UserRecoveryByUserName] @username NVARCHAR(50) AS
BEGIN
DECLARE @uid INT; DECLARE @tid INT; DECLARE @replies INT;
DECLARE @temp varchar(50);
SET @uid = (SELECT TOP(1) uid FROM dnt_users WHERE username = '@username');
SET @tid = 0; SET @replies = 0;
UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid;
UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid; UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid;
-- 定义一游标
DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only -- FOR UPDATE
OPEN Ctemp
FETCH next FROM Ctemp INTO @tid; WHILE (@@fetch_status = 0) BEGIN
SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0);
UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid;
FETCH next FROM Ctemp INTO @tid; END
CLOSE Ctemp;
DEALLOCATE Ctemp;[/size]