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

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

巴特西

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

最新文章

热门文章