Oracle月度服务器计数表创建及自动调度任务实现咨询

一、表结构设计建议 #

强烈不建议动态新增MonthYear列，因为Oracle中动态DDL操作（ALTER TABLE ADD COLUMN）会锁表，影响业务可用性，且后续查询需动态拼接列名，维护成本极高。更符合关系型数据库规范的方案是采用行存储模式，将每个月的数据作为一行存储：

固定描述类型表结构（推荐） #

如果描述类型（如物理服务器、虚拟服务器）是固定的，使用该结构：

CREATE TABLE SERVER_MONTHLY_COUNT (
    MONTHYEAR VARCHAR2(6) PRIMARY KEY, -- 格式为YYYYMM，例如202405
    PHYSICAL_SERVER_COUNT NUMBER(10) DEFAULT 0, -- 物理服务器计数
    VIRTUAL_SERVER_COUNT NUMBER(10) DEFAULT 0, -- 虚拟服务器计数
    CLOUD_SERVER_COUNT NUMBER(10) DEFAULT 0, -- 云服务器计数
    CREATE_DATE DATE DEFAULT SYSDATE, -- 数据创建时间
    UPDATE_DATE DATE DEFAULT SYSDATE -- 数据更新时间
);

动态描述类型表结构（灵活扩展） #

如果描述类型会动态新增，采用键值对形式存储：

CREATE TABLE SERVER_MONTHLY_COUNT (
    MONTHYEAR VARCHAR2(6),
    CATEGORY VARCHAR2(50), -- 描述类型，例如'物理服务器'
    COUNT NUMBER(10) DEFAULT 0, -- 对应计数
    PRIMARY KEY (MONTHYEAR, CATEGORY), -- 联合主键避免重复
    CREATE_DATE DATE DEFAULT SYSDATE,
    UPDATE_DATE DATE DEFAULT SYSDATE
);

二、月度自动统计任务实现 #

使用Oracle自带的DBMS_SCHEDULER创建调度任务，每月自动统计并插入数据。

1. 创建统计存储过程 #

根据你的表结构选择对应的存储过程：

固定描述类型版

CREATE OR REPLACE PROCEDURE SP_SAVE_SERVER_MONTH_DATA AS
    V_CUR_MONTH VARCHAR2(6);
BEGIN
    -- 获取上月的年月标识（如需统计当月可去掉ADD_MONTHS）
    V_CUR_MONTH := TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM');
    
    -- 用MERGE实现新增或更新数据
    MERGE INTO SERVER_MONTHLY_COUNT t
    USING (SELECT V_CUR_MONTH AS MONTHYEAR FROM DUAL) s
    ON (t.MONTHYEAR = s.MONTHYEAR)
    WHEN MATCHED THEN UPDATE SET 
        PHYSICAL_SERVER_COUNT = (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'PHYSICAL' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH),
        VIRTUAL_SERVER_COUNT = (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'VIRTUAL' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH),
        CLOUD_SERVER_COUNT = (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'CLOUD' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH),
        UPDATE_DATE = SYSDATE
    WHEN NOT MATCHED THEN INSERT (MONTHYEAR, PHYSICAL_SERVER_COUNT, VIRTUAL_SERVER_COUNT, CLOUD_SERVER_COUNT, CREATE_DATE)
    VALUES (V_CUR_MONTH,
        (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'PHYSICAL' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH),
        (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'VIRTUAL' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH),
        (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'CLOUD' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH),
        SYSDATE);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- 抛出异常便于调度任务捕获告警
END;
/

动态描述类型版

CREATE OR REPLACE PROCEDURE SP_SAVE_SERVER_MONTH_DATA AS
    V_CUR_MONTH VARCHAR2(6);
BEGIN
    V_CUR_MONTH := TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM');
    
    -- 先删除当月已存在的数据（避免重复）
    DELETE FROM SERVER_MONTHLY_COUNT WHERE MONTHYEAR = V_CUR_MONTH;
    
    -- 插入各分类数据（此处可根据实际分类表循环，示例为手动插入）
    INSERT INTO SERVER_MONTHLY_COUNT (MONTHYEAR, CATEGORY, COUNT)
    VALUES (V_CUR_MONTH, '物理服务器', (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'PHYSICAL' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH));
    
    INSERT INTO SERVER_MONTHLY_COUNT (MONTHYEAR, CATEGORY, COUNT)
    VALUES (V_CUR_MONTH, '虚拟服务器', (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'VIRTUAL' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH));
    
    INSERT INTO SERVER_MONTHLY_COUNT (MONTHYEAR, CATEGORY, COUNT)
    VALUES (V_CUR_MONTH, '云服务器', (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = 'CLOUD' AND TO_CHAR(DEPLOY_DATE, 'YYYYMM') <= V_CUR_MONTH));
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

2. 创建调度任务 #

设置每月1号凌晨2点自动执行存储过程：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        JOB_NAME        => 'JOB_SERVER_MONTH_STAT',
        JOB_TYPE        => 'STORED_PROCEDURE',
        JOB_ACTION      => 'SP_SAVE_SERVER_MONTH_DATA',
        START_DATE      => TO_TIMESTAMP('2024-06-01 02:00:00', 'YYYY-MM-DD HH24:MI:SS'),
        REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        ENABLED         => TRUE,
        COMMENTS        => '每月自动统计并保存服务器月度计数'
    );
END;
/

三、（不推荐）动态新增列的实现方案 #

如果业务必须要动态新增MonthYear列，可参考以下代码，但需承担锁表、维护复杂等风险：

1. 创建新增列的存储过程 #

CREATE OR REPLACE PROCEDURE SP_ADD_MONTH_COLUMN AS
    V_COLUMN_NAME VARCHAR2(12);
    V_MONTH_VALUE VARCHAR2(6);
    V_EXIST_FLAG NUMBER;
BEGIN
    V_MONTH_VALUE := TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM');
    V_COLUMN_NAME := 'C_' || V_MONTH_VALUE; -- 列名格式如C_202405
    
    -- 检查列是否已存在
    SELECT COUNT(*) INTO V_EXIST_FLAG
    FROM USER_TAB_COLUMNS
    WHERE TABLE_NAME = 'SERVER_MONTHLY_COUNT'
    AND COLUMN_NAME = V_COLUMN_NAME;
    
    IF V_EXIST_FLAG = 0 THEN
        -- 新增列
        EXECUTE IMMEDIATE 'ALTER TABLE SERVER_MONTHLY_COUNT ADD ' || V_COLUMN_NAME || ' NUMBER(10) DEFAULT 0';
        -- 更新列数据（此处需根据描述类型调整，示例为更新物理服务器计数）
        EXECUTE IMMEDIATE 'UPDATE SERVER_MONTHLY_COUNT SET ' || V_COLUMN_NAME || ' = (SELECT COUNT(*) FROM SERVER_LIST WHERE TYPE = ''PHYSICAL'' AND TO_CHAR(DEPLOY_DATE, ''YYYYMM'') = ''' || V_MONTH_VALUE || ''') WHERE CATEGORY = ''物理服务器''';
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

2. 创建新增列的调度任务 #

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        JOB_NAME        => 'JOB_ADD_MONTH_COLUMN',
        JOB_TYPE        => 'STORED_PROCEDURE',
        JOB_ACTION      => 'SP_ADD_MONTH_COLUMN',
        START_DATE      => TO_TIMESTAMP('2024-06-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS'),
        REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=1; BYMINUTE=0; BYSECOND=0',
        ENABLED         => TRUE,
        COMMENTS        => '每月新增服务器月度计数列'
    );
END;
/

四、关键注意事项 #

• 权限配置：执行DBMS_SCHEDULER需要CREATE JOB权限，存储过程需拥有对应表的查询、插入、更新权限。
• 异常告警：可给调度任务配置失败告警，通过DBMS_SCHEDULER.SET_ATTRIBUTE设置FAILURE_ACTION触发邮件通知。
• 数据归档：若数据量较大，可将表设为分区表（按MONTHYEAR分区），定期归档历史分区提升性能。
• 统计逻辑：根据实际业务调整统计条件（比如是当月新增数量还是累计数量）。

内容的提问来源于stack exchange，提问作者codeplex