Sybase或SQLServer数据库分批归档方案

IF OBJECT_ID ('dbo.ARCHIVE_LCHIS_TASK') IS NOT NULL
    DROP PROCEDURE dbo.ARCHIVE_LCHIS_TASK
GO

--EXEC ARCHIVE_LCHIS_TASK
CREATE PROCEDURE ARCHIVE_LCHIS_TASK
AS
BEGIN 
    DECLARE @V_MON INT
    DECLARE @VSQL VARCHAR(8000)
    DECLARE @VSQL_DEL VARCHAR(8000)
    DECLARE @VSQL_UPDATE VARCHAR(8000)
    DECLARE @tab_name VARCHAR(80)
    DECLARE @S_MON CHAR(4)
    DECLARE @error INT
    DECLARE @V_AR_MON CHAR(4)
    DECLARE @V_ROWCOUNT INT
    DECLARE @his_tab_name VARCHAR(80)
    SET @V_MON = 12

    WHILE @V_MON > 0
    BEGIN
        SELECT @S_MON = CASE 
                            WHEN @V_MON >= 10 
                                THEN CONVERT(VARCHAR(2), @V_MON)
                            ELSE 
                                '0' + CONVERT(VARCHAR(2), @V_MON) 
                        END 

        DECLARE Stb_name SCROLL CURSOR FOR 
            SELECT 'INSERT INTO lchis.dbo.' + TAB_NAME + '_' + @S_MON + 
                   ' SELECT * FROM ' + TAB_NAME + 
                   ' WHERE REPORT_DATE <= CONVERT(CHAR, DATEADD(MM, -' + AR_MONTH + ', GETDATE()), 111) 
                    AND ARCHIVE_STATUS<>''SUCCESS'' AND MONTH(REPORT_DATE) = ' + CONVERT(VARCHAR(2), @V_MON),
                   TAB_NAME, 
                   AR_MONTH
            FROM ARCHIVE_TABLE 
            WHERE ENABLE_FLAG = 'YES'

        OPEN Stb_name
        FETCH Stb_name INTO @VSQL, @tab_name, @V_AR_MON

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @error = 0
            SET @V_ROWCOUNT = 1
            SET ROWCOUNT 100000

            WHILE @V_ROWCOUNT > 0
            BEGIN
                BEGIN TRANSACTION
                SELECT @his_tab_name=@tab_name+ '_' + @S_MON
                 EXECUTE(@VSQL)
                SELECT @error = @@ERROR, @V_ROWCOUNT = @@ROWCOUNT
 
                IF @V_ROWCOUNT = 1
                BEGIN
                    ROLLBACK TRANSACTION
                    BREAK
                END
                
               SET @VSQL_UPDATE = 'UPDATE ' + @tab_name + 
                                ' SET ARCHIVE_STATUS=''SUCCESS'' WHERE REPORT_DATE <= CONVERT(CHAR, DATEADD(MM, -' + @V_AR_MON + ', GETDATE()), 111) 
                                AND ARCHIVE_STATUS<>''SUCCESS'' AND MONTH(REPORT_DATE) = ' + CONVERT(VARCHAR(2), @V_MON)    
                                     
                 EXECUTE(@VSQL_UPDATE)
                 
                SELECT @error = @error + @@ERROR, @V_ROWCOUNT = @@ROWCOUNT
              
                IF @error = 0 
                BEGIN
                 INSERT INTO ARCHIVE_TOLCHIS_LOG(ARCHIVE_TIME,TABLE_NAME,ARCHIVE_COUNTS) 
                 VALUES(getdate(),@his_tab_name,@V_ROWCOUNT)
                 COMMIT TRANSACTION
                 END
                ELSE
                BEGIN
                    ROLLBACK TRANSACTION
                 END
            END

            FETCH Stb_name INTO @VSQL, @tab_name, @V_AR_MON
        END

        CLOSE Stb_name
        DEALLOCATE Stb_name
        SET @V_MON = @V_MON - 1
    END
    
END
GO

 

ARCHIVE_TABLE是 自建的归档配置表，可以配置需要归档的表名称，归档多久之前的旧数据

删除已归档数据的存储过程

IF OBJECT_ID ('dbo.ARCHIVE_DEL_TASK') IS NOT NULL
    DROP PROCEDURE dbo.ARCHIVE_DEL_TASK
GO

CREATE PROCEDURE ARCHIVE_DEL_TASK
AS
BEGIN 
    DECLARE @V_MON INT
    DECLARE @VSQL_DEL VARCHAR(8000)
    DECLARE @tab_name VARCHAR(80)
    DECLARE @S_MON CHAR(4)
    DECLARE @error INT
    DECLARE @V_AR_MON CHAR(4)
    DECLARE @V_ROWCOUNT INT
    DECLARE @his_tab_name VARCHAR(80)
   

        DECLARE Stb_name SCROLL CURSOR FOR 
            SELECT 
                   TAB_NAME, 
                   AR_MONTH
            FROM ARCHIVE_TABLE 
            WHERE ENABLE_FLAG = 'YES'

        OPEN Stb_name
        FETCH Stb_name INTO @tab_name, @V_AR_MON

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @error = 0
            SET @V_ROWCOUNT = 1
            SET ROWCOUNT 100000

            WHILE @V_ROWCOUNT > 0
            BEGIN
                BEGIN TRANSACTION

               SET @VSQL_DEL = 'DELETE FROM ' + @tab_name + 
                                ' WHERE REPORT_DATE <= CONVERT(CHAR, DATEADD(MM, -' + @V_AR_MON + ', GETDATE()), 111) 
                                AND ARCHIVE_STATUS=''SUCCESS'''    
                                     
                 EXECUTE(@VSQL_DEL)
                 
                SELECT @error = @error + @@ERROR, @V_ROWCOUNT = @@ROWCOUNT
              
                IF @error = 0 
                BEGIN

                 COMMIT TRANSACTION
                 END
                ELSE
                BEGIN
                    ROLLBACK TRANSACTIO
                 END
            END

            FETCH Stb_name INTO @tab_name, @V_AR_MON
        END

        CLOSE Stb_name
        DEALLOCATE Stb_name

    
END
GO