Sybase或SQLServer資料庫分批歸檔方案

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

CREATE PROCEDURE ARCHIVE_LCHIS_TASK
AS
BEGIN 
    DECLARE @V_MON INT
    DECLARE @VSQL VARCHAR(8000)
    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

    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 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
                EXECUTE(@VSQL)
                SELECT @error = @@ERROR, @V_ROWCOUNT = @@ROWCOUNT

                IF @V_ROWCOUNT = 0
                BEGIN
                    ROLLBACK TRANSACTION
                    BREAK
                END

                SET @VSQL_DEL = 'DELETE FROM ' + @tab_name + 
                                ' WHERE REPORT_DATE <= CONVERT(CHAR, DATEADD(MM, -' + @V_AR_MON + ', GETDATE()), 111) ' + 
                                ' AND MONTH(REPORT_DATE) = ' + CONVERT(VARCHAR(2), @V_MON)
                EXECUTE(@VSQL_DEL)
                SELECT @error = @error + @@ERROR, @V_ROWCOUNT = @@ROWCOUNT

                IF @error = 0
                    COMMIT TRANSACTION
                ELSE
                    ROLLBACK TRANSACTION
            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是 自建的歸檔配置表，可以配置需要歸檔的表名稱，歸檔多久之前的舊資料