sql server 存储过程使用游标记录

sql server 存储过程使用游标记录--方便下次参考使用

游标的组成：

声明游标
打卡游标
从一个游标中查找信息
关闭游标
释放游标

游标类型：

静态游标
动态游标
只进游标
键集驱动游标

静态游标：静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。

静态游标在滚动期间很少或根本监测不到变化，虽然在tempdb中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb的程度最低，在滚动期间它能够监测到所有变化，单消耗的资源也更多。

键集驱动游标介于二者之间，它能够监测到大部分的变化，但比动态游标消耗更少的资源。

动态游标：与静态游标相对。当滚动游标时，动态游标反映结果集中所作的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部update、insert、delete语句均通过游标可见。

只进游标：只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能进行检索。对所有由当前用户发出或其他用户提交，并影响结果集中的行的insert、update、delete语句，其效果在这些行从游标中提取时是可见的。

键集驱动游标：打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的唯一标识符(键)控制。键由以唯一方式在结果集中标识行的列构成。键集是游标打开时来自所有适合select语句的行中的一系列键值，键集驱动

游标打开时建立在tempdb中。对非键集列中的数据值所做的更改(由游标所有者更改或其他用户提交)在用户滚动游标时是可见的，在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。

参考实例如下：

100

101

102

103

104

105

106

107

108

109

110

111

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

posted on 2018-09-21 11:39 sunwugang 阅读(353) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

欢迎莅临 SUN WU GANG 的园子！！！

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论