彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 彩世界开奖app苹果下载 > sqlserver 存款和储蓄进度重临游标的拍卖

sqlserver 存款和储蓄进度重临游标的拍卖

发布时间:2019-09-22 06:12编辑:彩世界开奖app苹果下载浏览(139)

    创建表:

    1.存储过程

    图片 1图片 2

    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 ;
    
    1 1 create table tb1(
    2 2 
    3 3 id int ,
    4 4 
    5 5 name nvarchar(20)
    6 6 
    7 7 )
    

    2.定义函数

    View Code

    一.
    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 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

    图片 3图片 4

    三.

     1 create proc tb1_proc (
     2 
     3 @cur cursor varying output
     4 
     5 )
     6 
     7 as
     8 
     9 begin
    10 
    11   set @cur=cursor for
    12 
    13   select * from tb1
    14 
    15 end
    16 
    17 open @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

    本文由彩世界平台发布于彩世界开奖app苹果下载,转载请注明出处:sqlserver 存款和储蓄进度重临游标的拍卖

    关键词: