sql server 存储过程使用游标记录--方便下次参考使用
游标的组成：
- 声明游标
- 打卡游标
- 从一个游标中查找信息
- 关闭游标
- 释放游标
游标类型：
- 静态游标
- 动态游标
- 只进游标
- 键集驱动游标
静态游标：静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。
静态游标在滚动期间很少或根本监测不到变化，虽然在tempdb中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb的程度最低，在滚动期间它能够监测到所有变化，单消耗的资源也更多。
键集驱动游标介于二者之间，它能够监测到大部分的变化，但比动态游标消耗更少的资源。
动态游标：与静态游标相对。当滚动游标时，动态游标反映结果集中所作的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部update、insert、delete语句均通过游标可见。
只进游标：只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能进行检索。对所有由当前用户发出或其他用户提交，并影响结果集中的行的insert、update、delete语句，其效果在这些行从游标中提取时是可见的。
键集驱动游标：打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的唯一标识符(键)控制。键由以唯一方式在结果集中标识行的列构成。键集是游标打开时来自所有适合select语句的行中的一系列键值，键集驱动
游标打开时建立在tempdb中。对非键集列中的数据值所做的更改(由游标所有者更改或其他用户提交)在用户滚动游标时是可见的，在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。
参考实例如下：
USE [hhris]
GO
/****** Object: StoredProcedure [dbo].[INIT_DICT_QUEUECODE] Script Date: 2018-09-21 17:12:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--if (exists (select * from sys.objects where name = 'INIT_DICT_QUEUECODE'))
--drop proc INIT_DICT_QUEUECODE
--go
--create procedure INIT_DICT_QUEUECODE
-- =============================================
-- Author: <sunwugang>
-- ALTER date: <2018-09-21>
-- Description: <添加参数维护>
-- =============================================
ALTER procedure [dbo].[INIT_DICT_QUEUECODE]
(
@p_QUEUEID int,
@p_QUEUENAME varchar(100),
@p_DEVICECOUNT int,
@p_CHECKDURATION int,
@p_BEGINTIMEPART varchar(50),
@p_ENDTIMEPART varchar(50),
@p_CODECOUNT int,
@p_QUEUESIGN varchar(50),
@p_ADDRESS varchar(50),
@p_ofdepart varchar(30),
@p_PMBEGINTIME varchar(50),
@p_PMENDTIME varchar(50),
@p_CodeCoefficient varchar(50),
@p_TIMEPART varchar(50),
@p_PARAMTYPE varchar(50),
@p_CALLTYPE varchar(50),
@p_result int output
)
as
insert into QS_PARAM (QUEUEID, QUEUENAME, DEVICECOUNT, CHECKDURATION, BEGINTIMEPART, ENDTIMEPART, CODECOUNT,QUEUESIGN,ADDRESS,ofdepart,PMBEGINTIME, PMENDTIME, CodeCoefficient, TIMEPART,PARAMTYPE,CALLTYPE)
values(@p_QUEUEID, @p_QUEUENAME, @p_DEVICECOUNT, @p_CHECKDURATION, @p_BEGINTIMEPART, @p_ENDTIMEPART, @p_CODECOUNT,@p_QUEUESIGN,@p_ADDRESS,@p_ofdepart,@p_PMBEGINTIME, @p_PMENDTIME, @p_CodeCoefficient, @p_TIMEPART,@p_PARAMTYPE,@p_CALLTYPE)
declare
@loopNum int,--循环次数
@codeNum int,--号源编号
@codeFirstAm int,
@codeFirstPm int
DECLARE paramDetails cursor
for
select queuename,paramtype,begintimepart,endtimepart,ofdepart,calltype,codecount from qs_param a
where a.queueid=@p_QUEUEID and a.paramtype=@p_PARAMTYPE order by convert(float,endtimepart)
begin try
set @loopNum=1
set @codeNum=1
set @codeFirstAm=0--未进行初始化
set @codeFirstPm=0--未进行初始化
--删除号源字典
delete from dict_queuecode where queuename=@p_QUEUENAME and codeparamtype=@p_PARAMTYPE and ofdepart=@p_ofdepart
begin tran --当前事务点，rollback、commit都从这里开始
DECLARE
@v_QUEUEID int,
@v_QUEUENAME varchar(100),
@v_DEVICECOUNT int,
@v_CHECKDURATION int,
@v_BEGINTIMEPART varchar(50),
@v_ENDTIMEPART varchar(50),
@v_CODECOUNT int,
@v_QUEUESIGN varchar(50),
@v_ADDRESS varchar(50),
@v_ofdepart varchar(30),
@v_PMBEGINTIME varchar(50),
@v_PMENDTIME varchar(50),
@v_CodeCoefficient varchar(50),
@v_TIMEPART varchar(50),
@v_PARAMTYPE varchar(50),
@v_CALLTYPE varchar(50)
open paramDetails
fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount
WHILE @@FETCH_STATUS =0
begin
--循环获取qs_param表中维护的某个时间段
set @loopNum=1
while @loopNum <= @v_codecount
begin
--上午
if @codeFirstAm=0 and @v_calltype='上午'
begin
set @codeNum=1
set @codeFirstAm=1
end
--下午
if @codeFirstPm=0 and @v_calltype='下午'
begin
set @codeNum=1
set @codeFirstPm=1
end
--添加号源信息
insert into DICT_QUEUECODE (queuename,codeparamtype,codevalue,timepart,hintinfo,OFDEPART,calltype,queueid)
values (@v_queuename,@v_paramtype,@codeNum,@v_begintimepart+'~'+@v_endtimepart,'',@v_ofdepart,@v_calltype,@p_QUEUEID)
--重新赋值
set @loopNum=@loopNum+1
set @codeNum=@codeNum+1
end
fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount
end
close paramDetails
DEALLOCATE paramDetails
set @p_result=1
commit
end try
begin catch
set @p_result=-1
rollback
end catch
实例二
USE [MES30]
GO
/****** Object: StoredProcedure [dbo].[sp_WM_DeliveryCommit] Script Date: 2018/9/20 16:21:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- ALTER date: <2017-06-13>
-- Description: <发货单提交>
-- =============================================
ALTER procedure [dbo].[sp_WM_DeliveryCommit]
(
@DeliveryCode NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ERROR_MESSAGE NVARCHAR(4000);
DECLARE @Flag NVARCHAR(50)
SET @Flag='0';
BEGIN TRY
BEGIN TRAN;
DECLARE DeliveryCursor CURSOR
FOR SELECT MatCode,DeliveryQty FROM t_WM_DeliveryDtl WHERE _DeliveryCode=@DeliveryCode
OPEN DeliveryCursor
DECLARE @MatCode nvarchar(50),@DeliveryQty decimal(22,6)
FETCH NEXT FROM DeliveryCursor INTO @MatCode,@DeliveryQty
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE @tmpDeliveryQty decimal(22,6),@tmpRequireQty decimal(22,6),@tmpReturnQty decimal(22,6)
DECLARE @tmpStockQty decimal(22,6),@tmpQty decimal(22,6)
SELECT @tmpDeliveryQty=SUM(isnull(DeliveryQty,0)) FROM t_WM_DeliveryDtl,t_WM_Delivery
WHERE DeliveryCode=_DeliveryCode and (DeliverySts=2 OR DeliverySts=3) and MatCode=@MatCode
SELECT @tmpRequireQty=SUM(isnull(RequireQty,0)) FROM t_WM_RequireDtl,t_WM_Require
WHERE RequireCode=_RequireCode and (RequireSts=2 OR RequireSts=3) and MatCode=@MatCode
SELECT @tmpRequireQty=SUM(isnull(ReOutQty,0)) FROM t_WM_ReturnOutDtl,t_WM_ReturnOut
WHERE ReOutCode=_ReOutCode and (ReOutSts=2 OR ReOutSts=3) and MatCode=@MatCode
SELECT @tmpStockQty=SUM(isnull(ValidQty,0)) FROM t_WM_InDtl,t_WM_In
WHERE InCode=_InCode and MatCode=@MatCode
SET @tmpQty=isnull(@tmpStockQty,0)-isnull(@tmpDeliveryQty,0)-isnull(@tmpRequireQty,0)-isnull(@tmpReturnQty,0)-isnull(@DeliveryQty,0)
if(@tmpQty<0)
BEGIN
SET @Flag=@MatCode;
BREAK;;
END
FETCH NEXT FROM DeliveryCursor INTO @MatCode,@DeliveryQty
END
CLOSE DeliveryCursor
DEALLOCATE DeliveryCursor
if @Flag='0'
UPDATE t_WM_Delivery SET DeliverySts=2 WHERE DeliveryCode=@DeliveryCode
COMMIT TRAN
SELECT @Flag;
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = ERROR_MESSAGE();
RAISERROR (@ERROR_MESSAGE , 16, 1);
SELECT -1;
END CATCH
END
实例三(该实例来自网络)：
---游标循环遍历--
begin
declare @a int,@error int
declare @temp varchar(50)
set @a=1
set @error=0
begin tran --申明事务
--申明游标为Uid
declare order_cursor cursor
for (select [Uid] from Student)
--打开游标--
open order_cursor
--开始循环游标变量--
fetch next from order_cursor into @temp
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
update Student set Age=20+@a,demo=@a where Uid=@temp
set @a=@a+1
set @error= @error + @@ERROR --记录每次运行sql后是否正确，0正确
fetch next from order_cursor into @temp --转到下一个游标
end
if @error=0
begin
commit tran --提交事务
end
else
begin
rollback tran --回滚事务
end
close order_cursor --关闭游标
deallocate order_cursor --释放游标
end