sqlserver 游标的运用

by admin on 2019年10月2日

上面包车型大巴理念是在接纳游标的经过中做的日志。我也是第二次利用,尽管有啥样狼狈的地方请议论指正,我们一块使劲。

declare @temp_temp uniqueidentifier--临时变量    
DECLARE aaa CURSOR for select Id from A
-------------------打开游标
open aaa
--先查询一次再循环,防止有多个游标时@@FETCH_STATUS=-1不能进入下个游标循环的情况
fetch next from aaa into @temp_temp
-------------------循环取数据
while @@FETCH_STATUS=0
begin
print @temp_temp
fetch next from aaa into @temp_temp

end
----------------------------------- 关闭游标    
Close aaa    
----------------------------------- 删除游标    
Deallocate aaa
declare @temp_temp uniqueidentifier--临时变量    
DECLARE aaa CURSOR for select Id from A
-------------------打开游标
open aaa
--先查询一次再循环,防止有多个游标时@@FETCH_STATUS=-1不能进入下个游标循环的情况
fetch next from aaa into @temp_temp
-------------------循环取数据
while @@FETCH_STATUS=0
begin
print @temp_temp
fetch next from aaa into @temp_temp

end
----------------------------------- 关闭游标    
Close aaa    
----------------------------------- 删除游标    
Deallocate aaa
/***
游标的使用
 讲了这个多游标的优点,现在我们就亲自来揭开游标的神秘的面纱。
 使用游标的顺序: 声名游标、打开游标、读取数据、关闭游标、删除游标。
1.3.1声明游标
最简单游标声明:DECLARE <游标名>CURSOR FOR<SELECT语句>;
其中select语句可以是简单查询,也可以是复杂的接连查询和嵌套查询
例子:[已表2 AddSalary为例子]
Declare mycursor cursor for select * from AddSalary
这样我就对表AddSalary申明了一个游标mycursor 
【高级备注】
DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>
这里我说一下游标中级应用中的[INSENSITIVE]和[SCROLL]
INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内(建立在tempdb 数据库下)。对该游标的读取操作皆由临时表来应答。因此,对基本表的修改并不影响游标提取的数据,即游标不会随着基本表内容的改变而改变,同时也无法通过游标来更新基本表。如果不使用该保留字,那么对基本表的更新、删除都会反映到游标中。
另外应该指出,当遇到以下情况发生时,游标将自动设定INSENSITIVE 选项。
a.在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句;
b.使用OUTER JOIN;
c.所选取的任意表没有索引;
d.将实数值当作选取的列。
SCROLL
表明所有的提取操作(如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE)都可用。如果不使用该保留字,那么只能进行NEXT 提取操作。由此可见,SCROLL 极大地增加了提取数据的灵活性,可以随意读取结果集中的任一行数据记录,而不必关闭再
重开游标。 
1.3.2 打开游标
非常简单,我们就打开刚才我们声明的游标mycursor
OPEN mycursor 
1.3.3读取数据
FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名  | @游标变量名 } [ INTO @变量名 [,…] ]
参数说明:
NEXT   取下一行的数据,并把下一行作为当前行(递增)。由于打开游标后,行指针是指向该游标第1行之前,所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。
INTO @变量名[,…]  把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。
现在我们就取出mycursor游标的数据吧! 
当游标被打开时,行指针将指向该游标集第1行之前,如果要读取游标集中的第1行数据,必须移动行指针使其指向第1行。就本例而言,可以使用下列操作读取第1行数据:
Eg: Fetch next from mycursor 或则 Fetch first from mycursor
这样我就取出了游标里的数据,但是光光这样可不够,我们还需要将取出的数据赋给变量
--声明2个变量
declare @O_ID NVARCHAR(20)
declare @A_Salary float
--将取出的值传入刚才声明的2个变量
Fetch next from mycursor into @ O_ID,@ A_Salary

1.3.4关闭游标
CLOSE mycursor   

1.3.5删除游标
DEALLOCATE mycursor         

1.3.6 实例训练
**/
CREATE PROCEDURE PK_Test
AS
    --声明2个变量
    declare @O_ID nvarchar(20)  
    declare @A_Salary float  
    --声明一个游标mycursor,select语句中参数的个数必须要和从游标取出的变量名相同
    declare mycursor cursor for select O_ID,A_Salary from AddSalary  
    --打开游标
    open mycursor    
    --从游标里取出数据赋值到我们刚才声明的2个变量中
    fetch next from mycursor into @O_ID,@A_Salary    
    --判断游标的状态
    -- 0 fetch语句成功    
    ---1 fetch语句失败或此行不在结果集中    
    ---2 被提取的行不存在
    while (@@fetch_status=0)
    begin    
    --显示出我们每次用游标取出的值
       print '游标成功取出一条数据'
       print @O_ID
       print @A_Salary   
    --用游标去取下一条记录
       fetch next from mycursor into @O_ID,@A_Salary
    end
    --关闭游标
    close mycursor
    --撤销游标
    DEALLOCATE mycursor 
GO

1.积存进程

1.

  

  

图片 1

CREATE PROCEDURE [dbo].[pro_init_dzz_dy_exception] AS
DECLARE @v_uuid VARCHAR (40) ;
DECLARE @v_operatetime datetime ;
DECLARE @v_userid VARCHAR (40) ;
DECLARE @v_zjhm VARCHAR (30) ;
DECLARE @v_csrq Date ;
DECLARE @v_rdsj Date ;
DECLARE @v_zzsj Date ;
DECLARE @v_idcardmult VARCHAR (1);
DECLARE @v_idcardvalidity VARCHAR (1) ;
DECLARE @v_subzjhmstr VARCHAR (50) ;
DECLARE @v_csrqstr VARCHAR (50) ;
DECLARE @dbname VARCHAR (100) ;
DECLARE @insertSqlStrStart VARCHAR (5000) ;
DECLARE @insertSqlStrMiddle VARCHAR (5000) ;
DECLARE @insertSqlStrEnd VARCHAR (5000) ;
DECLARE @insertSqlStr VARCHAR (5000) ;
DECLARE @querysql VARCHAR (5000) ;
DECLARE @deletesql VARCHAR (5000) ;
DECLARE @yearInterval INT ;
DECLARE @mm INT ; --DECLARE v_all_dy sys_refcursor;
--DECLARE @v_dy_info TABLE T_DZZ_DY_EXCEPTION_INFO_TEMP;
DECLARE allSche CURSOR FOR SELECT
    schemaname
FROM
    s_qkdzzinfo ;
BEGIN

--清空异常信息表
SET @deletesql = 'delete from t_dzz_dy_exception_info';
BEGIN
    TRAN ; EXEC (@deletesql) ; COMMIT TRAN ;

SET @insertSqlStrStart = 'insert into t_dzz_dy_exception_info 
                         (uuid,dzz_dy_id,datatype,errorlevel,errortype,operatetime) 
                         values(' ;
SET @insertSqlStrEnd = ')' ; OPEN allSche FETCH NEXT
FROM
    allSche INTO @dbname ;
WHILE (@@FETCH_STATUS = 0)
BEGIN

--SET @querysql = 'select * from ' + dbname + '.t_dy_info where delflag =1' ;
--FAST_FORWARD 
exec('DECLARE v_all_dy CURSOR FOR select userid, zjhm, csrq, rdsj, zzsj, idcardmult, idcardvalidity from '+@dbname+'.t_dy_info where delflag = 1 and dylb in (1,2) and dyzt = 1');
--DECLARE v_all_dy CURSOR FOR select REPLACE(userid, ' ', ''), REPLACE(zjhm, ' ', ''), csrq, rdsj, zzsj, idcardmult, idcardvalidity from @dbname.t_dy_info where delflag = 1;
OPEN v_all_dy ; FETCH NEXT
FROM
    v_all_dy INTO @v_userid, @v_zjhm, @v_csrq, @v_rdsj, @v_zzsj, @v_idcardmult, @v_idcardvalidity;
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @v_operatetime =  CONVERT(datetime, GETDATE(), 20);
SET @v_userid = REPLACE(@v_userid, ' ', '');
SET @v_zjhm = REPLACE(@v_zjhm, ' ', '')
SET @v_csrq = CONVERT (DATE,@v_csrq,23);
SET @v_rdsj = CONVERT (DATE,@v_rdsj,23);
SET @v_zzsj = CONVERT (DATE,@v_zzsj,23);
--SET @v_rdsj = @v_dy_info.rdsj ;
--SET @v_zzsj = @v_dy_info.zzsj ;
--SET @v_idcardmult = @v_dy_info.idcardmult ;
--SET @v_idcardvalidity = @v_dy_info.idcardvalidity ;
IF @v_zjhm IS NOT NULL
AND @v_csrq IS NOT NULL
BEGIN
SET @v_subzjhmstr = SUBSTRING (@v_zjhm, 7, 4) + SUBSTRING (@v_zjhm, 11, 2) + SUBSTRING (@v_zjhm, 13, 2) ;
SET @v_csrqstr = CONVERT (
    VARCHAR (100),
    CONVERT (
        DATE,
        CONVERT (VARCHAR(100), @v_csrq, 23),
        20
    ),
    112
)
IF @v_subzjhmstr != @v_csrqstr
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,1,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
IF @v_rdsj IS NOT NULL
AND @v_zzsj IS NOT NULL
BEGIN
SET @yearInterval = dbo.FUNC_getYearsToDates (@v_rdsj ,@v_zzsj) ;
SET @mm = dbo.FUNC_getMonthsToDates (@v_rdsj ,@v_zzsj) ;
IF (
    DateDiff(DAY, '1921-07-01' ,@v_rdsj) >= 0
    AND DateDiff(DAY, '1923-06-09' ,@v_rdsj) <= 0
)
OR (
    DateDiff(DAY, '1928-06-18' ,@v_rdsj) >= 0
    AND DateDiff(DAY, '1945-04-22' ,@v_rdsj) <= 0
)
OR (
    DateDiff(DAY, '1969-04-01' ,@v_rdsj) >= 0
    AND DateDiff(DAY, '1977-08-11' ,@v_rdsj) <= 0
)
BEGIN
IF DateDiff(DAY ,@v_zzsj ,@v_rdsj) != 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
ELSE

IF DateDiff(DAY, '1923-06-10' ,@v_rdsj) >= 0
AND DateDiff(DAY, '1927-04-26' ,@v_rdsj) <= 0
BEGIN
IF @yearInterval > 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
ELSE
BEGIN
IF @mm != 6
AND @mm != 3
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
END
ELSE

IF DateDiff(DAY, '1927-04-27' ,@v_rdsj) >= 0
AND DateDiff(DAY, '1928-06-17' ,@v_rdsj) <= 0
BEGIN
IF DateDiff(DAY ,@v_zzsj ,@v_rdsj) != 0
BEGIN
IF @mm != 3
AND @yearInterval != 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
END
ELSE

IF DateDiff(DAY, '1945-04-23' ,@v_rdsj) >= 0
AND DateDiff(DAY, '1956-09-14' ,@v_rdsj) <= 0
BEGIN
IF @mm != 6
AND @yearInterval != 0
AND @mm != - 1
AND @mm != 12 
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
ELSE
IF @yearInterval != 1
AND @yearInterval != 2
AND (@mm != 6 AND @yearInterval != 0)
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
ELSE
BEGIN
IF @yearInterval != 1
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
END
IF @v_idcardvalidity IS NOT NULL
BEGIN
IF @v_idcardvalidity = 1
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,3,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END

IF @v_idcardmult IS NOT NULL
BEGIN
IF @v_idcardmult = 1 
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,4,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END

IF @v_rdsj IS NOT NULL
AND @v_csrq IS NOT NULL
BEGIN
SET @yearInterval = dbo.FUNC_getYearsDifference (@v_csrq ,@v_rdsj) ;
IF @yearInterval < 19
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,5,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
IF DateDiff(DAY ,@v_csrq ,@v_rdsj) < 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,2,6,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END

END
IF @v_rdsj IS NOT NULL 
BEGIN
IF DateDiff(DAY, '1921-07-01' ,@v_rdsj) < 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,2,7,' ;
SET @insertSqlStr = @insertSqlStrStart + '''' +@v_uuid + '''' + ',' + '''' +@v_userid + '''' +@insertSqlStrMiddle +''''+cast(@v_operatetime as varchar)+''''+@insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ; 
END
END
FETCH NEXT
FROM
    v_all_dy INTO @v_userid, @v_zjhm, @v_csrq, @v_rdsj, @v_zzsj, @v_idcardmult, @v_idcardvalidity;
END ; CLOSE v_all_dy ; DEALLOCATE v_all_dy ; FETCH NEXT
FROM
    allSche INTO @dbname ;
END ; CLOSE allSche ; DEALLOCATE allSche ;
END ;

  消息 16951,级别 16,状态 1,过程
usp_proc,第 16 行
      变量 ‘@myref’
无法用作参数,因为在进行该进程前,不得为 CULX570SORubicon OUTPUT
参数分配游标。

游标的嵌套

游标的嵌套

2.概念函数

  那个难点是自己在调用一个递归的、输出cursor output
的蕴藏进程

declare @temp_temp uniqueidentifier--临时变量    
DECLARE aaa CURSOR for select Id from A
-------------------打开游标
open aaa
--先查询一次再循环,防止有多个游标时@@FETCH_STATUS=-1不能进入下个游标循环的情况
fetch next from aaa into @temp_temp
-------------------循环取数据
while @@FETCH_STATUS=0
begin
print @temp_temp
    --===========================游标嵌套 
    DECLARE bbb CURSOR for select Id from B
    -------------------打开游标
    open bbb
    --先查询一次再循环,防止有多个游标时@@FETCH_STATUS=-1不能进入下个游标循环的情况
    fetch next from bbb into @temp_temp
    -------------------循环取数据
    while @@FETCH_STATUS=0
    begin
    print @temp_temp
    fetch next from bbb into @temp_temp

    end
    ----------------------------------- 关闭游标    
    Close bbb    
    ----------------------------------- 删除游标    
    Deallocate bbb
    --===========================游标嵌套
fetch next from aaa into @temp_temp

end
----------------------------------- 关闭游标    
Close aaa    
----------------------------------- 删除游标    
Deallocate aaa
declare @temp_temp uniqueidentifier--临时变量    
DECLARE aaa CURSOR for select Id from A
-------------------打开游标
open aaa
--先查询一次再循环,防止有多个游标时@@FETCH_STATUS=-1不能进入下个游标循环的情况
fetch next from aaa into @temp_temp
-------------------循环取数据
while @@FETCH_STATUS=0
begin
print @temp_temp
    --===========================游标嵌套 
    DECLARE bbb CURSOR for select Id from B
    -------------------打开游标
    open bbb
    --先查询一次再循环,防止有多个游标时@@FETCH_STATUS=-1不能进入下个游标循环的情况
    fetch next from bbb into @temp_temp
    -------------------循环取数据
    while @@FETCH_STATUS=0
    begin
    print @temp_temp
    fetch next from bbb into @temp_temp

    end
    ----------------------------------- 关闭游标    
    Close bbb    
    ----------------------------------- 删除游标    
    Deallocate bbb
    --===========================游标嵌套
fetch next from aaa into @temp_temp

end
----------------------------------- 关闭游标    
Close aaa    
----------------------------------- 删除游标    
Deallocate aaa
一.
CREATE FUNCTION [dbo].[FUNC_getMonthsToDates] (@v_rdsj DATE, @v_zzsj DATE) RETURNS INT AS
BEGIN

DECLARE @beginYear INT ;
DECLARE @endYear INT ;
DECLARE @beginMonth INT ;
DECLARE @endMonth INT ;
DECLARE @beginDay INT ;
DECLARE @endDay INT ;
DECLARE @beginBool INT ;
DECLARE @endBool INT ;
DECLARE @yearInterval INT ;
DECLARE @monthInterval INT ;
DECLARE @mm INT ;
SET @beginYear = DatePart(YEAR ,@v_rdsj) ;
SET @endYear = DatePart(YEAR ,@v_zzsj) ;
SET @beginMonth = DatePart(MONTH ,@v_rdsj) ;
SET @endMonth = DatePart(MONTH ,@v_zzsj) ;
SET @beginDay = DatePart(DAY ,@v_rdsj) ;
SET @endDay = DatePart(DAY ,@v_zzsj) ;
SET @yearInterval = @endYear - @beginYear ; --为1说明是当月最后一天喂0说明不是
SET @beginBool = datediff(
    MONTH ,@v_rdsj,
    dateadd(DAY, 1 ,@v_rdsj)
) ;
SET @endBool = datediff(
    MONTH ,@v_zzsj,
    dateadd(DAY, 1 ,@v_zzsj)
) ;
IF DateDiff(DAY ,@v_rdsj ,@v_zzsj) >= 0
BEGIN

IF (
    @yearInterval = 0
    OR @yearInterval = 1
)
BEGIN

IF (
    @endMonth < @beginMonth
    OR @endMonth = @beginMonth
    AND (
        (@endDay < @beginDay)
        AND (@beginBool = 0 AND @endBool = 0)
    )
)
BEGIN

SET @yearInterval = @yearInterval - 1;
END
SET @monthInterval = (@endMonth + 12) - @beginMonth ;
IF (
    @endDay < @beginDay
    AND (@beginBool = 0 AND @endBool = 0)
)
BEGIN

SET @monthInterval = @monthInterval - 1 ;
END
SET @monthInterval = @monthInterval % 12 ;
SET @mm = @yearInterval * 12 + @monthInterval ;
IF (@beginBool = 1 AND @endBool = 1)
BEGIN

SET @mm = @mm ;
END
ELSE

IF (
    @beginBool = 0
    AND @endBool = 1
    AND @endDay <= @beginDay
)
BEGIN

SET @mm = @mm ;
END
ELSE

IF (
    @beginBool = 0
    AND @endBool = 0
    AND @endDay = @beginDay
)
BEGIN

SET @mm = @mm ;
END
ELSE

BEGIN

SET @mm = -1 ;
END
END
ELSE

BEGIN

SET @mm = -1 ;
END
END
ELSE

BEGIN

SET @mm = -1 ;
END RETURN @mm ;
END

 
create proc usp_proc(
@level int
@myref cursor varying output
)
as
begin
    if @level=3
        begin
             set @myref=cursor local static for
            select * from table
            open @myref
        end
     if @level<3
        begin
        declare @cur cursor
        exec usp_proc 2 @cur output --递归
        --
        --对输出游标@cur做一些操作
        --
        --使用完游标
        close @cur  --关闭游标
        deallocate @cur --删除游标
        end
end            

  

  

二.

如果未有对出口的游标做close、deallocate管理就能够并发下面错误。

CREATE function [dbo].[FUNC_getYearsDifference](@v_begin DATE,
@v_end DATE)
returns int
as
BEGIN
DECLARE @beginYear INT;
DECLARE @endYear INT;
DECLARE @beginMonth INT;
DECLARE @endMonth INT;
DECLARE @beginDay INT;
DECLARE @endDay INT;
DECLARE @yearInterval INT;
DECLARE @num INT;
SET @num = -1;
SET @beginYear = DatePart (year,@v_begin);
SET @endYear = DatePart (year,@v_end);
SET @beginMonth = DatePart (month,@v_begin);
SET @endMonth = DatePart (month,@v_end);
SET @beginDay = DatePart (day,@v_begin);
SET @endDay = DatePart (day,@v_end);
SET @yearInterval = @endYear – @beginYear;
if DateDiff(day,@v_begin,@v_end) >= 0
begin
if(@endMonth < @beginMonth)
begin
SET @yearInterval = @yearInterval – 1;
end
if(@endMonth = @beginMonth)
begin
if(@endDay < @beginDay)
BEGIN
SET @yearInterval = @yearInterval – 1;
END
else if(@endDay = @beginDay)
BEGIN
SET @yearInterval = @yearInterval;
END
else
begin
SET @yearInterval = @yearInterval;
end
end
else
begin
SET @yearInterval = @yearInterval;
end
end
else
begin
SET @yearInterval = -1;
end
return @yearInterval;
END

2.

三.

  未有为@cur,分配游标

CREATE function [dbo].[FUNC_getYearsToDates](@v_rdsj DATE,
@v_zzsj DATE)
returns int
as
begin
DECLARE @beginYear INT;
DECLARE @endYear INT;
DECLARE @beginMonth INT;
DECLARE @endMonth INT;
DECLARE @beginDay INT;
DECLARE @endDay INT;
DECLARE @beginBool INT;
DECLARE @endBool INT;
DECLARE @yearInterval INT;
DECLARE @num INT;
SET @num = -1;
SET @beginYear = DatePart (year,@v_rdsj);
SET @endYear = DatePart (year,@v_zzsj);
SET @beginMonth = DatePart (month,@v_rdsj);
SET @endMonth = DatePart (month,@v_zzsj);
SET @beginDay = DatePart (day,@v_rdsj);
SET @endDay = DatePart (day,@v_zzsj);
SET @yearInterval = @endYear – @beginYear;
–为1申明是前些日子最后一天喂0表达不是
SET @beginBool = datediff(month,@v_rdsj,dateadd(day,1,@v_rdsj));
SET @endBool = datediff(month,@v_zzsj,dateadd(day,1,@v_zzsj));
if DateDiff(day,@v_rdsj,@v_zzsj) >= 0
begin
if(@endMonth < @beginMonth)
begin
SET @yearInterval = @yearInterval – 1;
end
if(@endMonth = @beginMonth)
begin
if(@beginBool = 1 and @endBool = 1)
BEGIN
SET @yearInterval = @yearInterval;
END
else if(@beginBool = 0 and @endBool = 1 and @endDay <= @beginDay)
BEGIN
SET @yearInterval = @yearInterval;
END
else if(@beginBool = 0 and @endBool = 0 and @endDay = @beginDay)
BEGIN
SET @yearInterval = @yearInterval;
END
else
begin
SET @yearInterval = -1;
end
end
else
begin
SET @yearInterval = -1;
end
end
else
begin
SET @yearInterval = -1;
end
return @yearInterval;
end

  那些标题是本身在动用存款和储蓄进程再次来到的游标 cursor
output 产生的

 

  

create proc myproc(
@mycur cursor varying output
)
as
begin
set @mycur=cursor local static  for
select * from table

open @mycur --打开游标
end

--调用myproc
declare @cur cursor
exec myproc @cur output
fetch next from @cur
while @@fetch_status=0
    begin
    --使用游标
    fetch next from @cur
    end 

并发上述错的由来正是概念游标后供给开荒 open @mycur

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图