测试批量存储过程

1.新建测试表： 

DROP TABLE IF EXISTS query_exec_log;

CREATE TABLE query_exec_log (

    log_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',

    exec_start_time DATETIME NOT NULL COMMENT '查询开始时间',

    exec_end_time DATETIME NOT NULL COMMENT '查询结束时间',

    exec_duration_ms INT COMMENT '执行耗时(毫秒)',

    exec_result VARCHAR(255) COMMENT '执行结果（成功/失败）',

    error_msg TEXT COMMENT '错误信息（失败时填充）',

    query_rows INT COMMENT '查询返回行数'

)  COMMENT='12小时查询任务执行日志';

2. 编写测试存储过程： 

DELIMITER //  -- 临时修改语句结束符，避免存储过程内的;中断执行

DROP PROCEDURE IF EXISTS proc_continuous_query_12h;

CREATE PROCEDURE proc_continuous_query_12h(

    IN p_interval_seconds INT DEFAULT 300  -- 查询间隔（秒），默认5分钟

)

BEGIN

    -- 声明变量

    DECLARE v_start_datetime DATETIME;  -- 任务起始时间

    DECLARE v_current_datetime DATETIME;  -- 当前时间

    DECLARE v_continue BOOLEAN DEFAULT TRUE;  -- 是否继续执行

    DECLARE v_exec_start DATETIME;  -- 单次查询开始时间

    DECLARE v_exec_end DATETIME;  -- 单次查询结束时间

    DECLARE v_duration_ms INT;  -- 单次执行耗时

    DECLARE v_query_rows INT DEFAULT 0;  -- 查询返回行数

    DECLARE v_error_msg TEXT;  -- 错误信息

    DECLARE v_exception INT DEFAULT 0;  -- 异常标记

    -- 初始化任务起始时间

    SET v_start_datetime = NOW();

    SET v_current_datetime = v_start_datetime;

    -- 循环执行：直到超过12小时

    WHILE v_continue DO

        -- 1. 记录单次查询开始时间

        SET v_exec_start = NOW();

        SET v_exception = 0;

        SET v_error_msg = NULL;

        SET v_query_rows = 0;

        -- 2. 执行核心查询逻辑（替换为你的目标SQL）

        BEGIN

            -- 示例：执行存款余额分析查询（可替换为任意查询）

            CREATE TEMPORARY TABLE IF NOT EXISTS tmp_query_result AS

            WITH date_params AS (

                SELECT

                    cast( '2024-11-25' as date) as curr_dt , -- 2024-11-25  当天

            cast('2024-11-24' as date) as prev_day_dt ,  -- 2024-11-24 前一天

            (cast(substr('2024-11-25',1,8)||'01' as date) -30)  as last_month_day, -- 2024-10-02  距今30天的

            (cast(substr('2024-11-25',1,8)||'01' as date) -365) as last_year_day, -- 2023-11-02 距今1年的

            (cast(substr('2024-11-25',1,8)||'01' as date) -1)  as  last_month_end,   -- 上月底

            (cast(substr('2024-11-25',1,5)||'01-01' as date) -1)  as last_year_end -- 上年末

            ),

            base_agg AS (

                SELECT

                    t.PRDT_NO,

                    t.PRDT_NM,

                    t.CUST_TPCD,

                    t.OPACT_ORGNO,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.curr_dt THEN t.YACM_BAL ELSE 0 END) AS curr_yacm_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.curr_dt THEN t.ACCT_BAL ELSE 0 END) AS curr_acct_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_day_dt THEN t.YACM_BAL ELSE 0 END) AS prev_day_yacm_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_day_dt THEN t.ACCT_BAL ELSE 0 END) AS prev_day_acct_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_month_end_dt THEN t.YACM_BAL ELSE 0 END) AS last_month_yacm_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_month_end_dt THEN t.ACCT_BAL ELSE 0 END) AS last_month_acct_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_30day_dt THEN t.YACM_BAL ELSE 0 END) AS prev_30day_yacm_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_30day_dt THEN t.ACCT_BAL ELSE 0 END) AS prev_30day_acct_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_year_dt THEN t.YACM_BAL ELSE 0 END) AS prev_year_yacm_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_year_dt THEN t.ACCT_BAL ELSE 0 END) AS prev_year_acct_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_year_end_dt THEN t.YACM_BAL ELSE 0 END) AS last_year_yacm_bal,

                    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_year_end_dt THEN t.ACCT_BAL ELSE 0 END) AS last_year_acct_bal

                FROM dep_dpsit_acct_bal_info_test t

                CROSS JOIN date_params dp

                WHERE t.DDW_ETL_DT IN (dp.curr_dt, dp.prev_day_dt, dp.last_month_end_dt, dp.prev_30day_dt, dp.prev_year_dt, dp.last_year_end_dt)

                GROUP BY t.PRDT_NO, t.PRDT_NM, t.CUST_TPCD, t.OPACT_ORGNO

            )

            SELECT

                PRDT_NO, PRDT_NM, CUST_TPCD, OPACT_ORGNO,

                curr_yacm_bal AS 年累计余额, curr_acct_bal AS 账户余额,

                curr_yacm_bal - prev_day_yacm_bal AS 日增年累计余额,

                curr_acct_bal - prev_day_acct_bal AS 日增账户余额,

                curr_yacm_bal - last_month_yacm_bal AS 月增年累计余额,

                curr_acct_bal - last_month_acct_bal AS 月增账户余额

            FROM base_agg;

            -- 获取查询返回行数

            SELECT COUNT(*) INTO v_query_rows FROM tmp_query_result;

            -- 删除临时表（释放资源）

            DROP TEMPORARY TABLE IF EXISTS tmp_query_result;

        END;

        -- 3. 记录单次查询结束时间和耗时

        SET v_exec_end = NOW();

        SET v_duration_ms = TIMESTAMPDIFF(MILLISECOND, v_exec_start, v_exec_end);

        -- 4. 写入执行日志

        INSERT INTO query_exec_log (

            exec_start_time, exec_end_time, exec_duration_ms,

            exec_result, error_msg, query_rows

        )

        VALUES (

            v_exec_start, v_exec_end, v_duration_ms,

            IF(v_exception = 0, '成功', '失败'),

            v_error_msg,

            v_query_rows

        );

        -- 5. 检查是否超过12小时：终止循环

        SET v_current_datetime = NOW();

        IF TIMESTAMPDIFF(HOUR, v_start_datetime, v_current_datetime) >= 12 THEN

            SET v_continue = FALSE;

        ELSE

            -- 6. 未超时：等待指定间隔后继续执行

            DO SLEEP(p_interval_seconds);

        END IF;

    END WHILE;

    -- 任务结束：写入最终日志（可选）

    INSERT INTO query_exec_log (

        exec_start_time, exec_end_time, exec_duration_ms,

        exec_result, error_msg, query_rows

    )

    VALUES (

        v_start_datetime, NOW(),

        TIMESTAMPDIFF(MILLISECOND, v_start_datetime, NOW()),

        '任务完成',

        CONCAT('12小时持续查询任务结束，起始时间：', v_start_datetime, '，结束时间：', NOW()),

        0

    );

END //

DELIMITER ;

3.插入测试数据： 

-- 插入测试数据

INSERT INTO dep_dpsit_acct_bal_info_test

(PRDT_NO, PRDT_NM, CUST_TPCD, OPACT_ORGNO, YACM_BAL, ACCT_BAL, DDW_ETL_DT)

VALUES

-- 2024-11-25（目标日期）数据

('P001', '个人活期存款', '01', 'ORG001', 100000.00, 95000.00, '2024-11-25'),

('P001', '个人活期存款', '02', 'ORG001', 80000.00, 78000.00, '2024-11-25'),

('P002', '企业定期存款', '03', 'ORG002', 500000.00, 495000.00, '2024-11-25'),

('P003', '大额存单', '01', 'ORG003', 2000000.00, 1980000.00, '2024-11-25'),

-- 2024-11-24（前一日）数据

('P001', '个人活期存款', '01', 'ORG001', 98000.00, 93000.00, '2024-11-24'),

('P001', '个人活期存款', '02', 'ORG001', 79000.00, 77500.00, '2024-11-24'),

('P002', '企业定期存款', '03', 'ORG002', 500000.00, 495000.00, '2024-11-24'),

('P003', '大额存单', '01', 'ORG003', 1990000.00, 1970000.00, '2024-11-24'),

-- 2024-10-31（上月末）数据

('P001', '个人活期存款', '01', 'ORG001', 95000.00, 90000.00, '2024-10-31'),

('P001', '个人活期存款', '02', 'ORG001', 75000.00, 74000.00, '2024-10-31'),

('P002', '企业定期存款', '03', 'ORG002', 480000.00, 475000.00, '2024-10-31'),

('P003', '大额存单', '01', 'ORG003', 1950000.00, 1930000.00, '2024-10-31'),

-- 2024-10-26（30天前，同比基准）数据

('P001', '个人活期存款', '01', 'ORG001', 90000.00, 88000.00, '2024-10-26'),

('P001', '个人活期存款', '02', 'ORG001', 70000.00, 69000.00, '2024-10-26'),

('P002', '企业定期存款', '03', 'ORG002', 450000.00, 445000.00, '2024-10-26'),

('P003', '大额存单', '01', 'ORG003', 1900000.00, 1880000.00, '2024-10-26'),

-- 2023-11-25（去年同期，环比基准）数据

('P001', '个人活期存款', '01', 'ORG001', 85000.00, 83000.00, '2023-11-25'),

('P001', '个人活期存款', '02', 'ORG001', 65000.00, 64000.00, '2023-11-25'),

('P002', '企业定期存款', '03', 'ORG002', 400000.00, 395000.00, '2023-11-25'),

('P003', '大额存单', '01', 'ORG003', 1800000.00, 1780000.00, '2023-11-25'),

-- 2023-12-31（上年底）数据

('P001', '个人活期存款', '01', 'ORG001', 88000.00, 86000.00, '2023-12-31'),

('P001', '个人活期存款', '02', 'ORG001', 68000.00, 67000.00, '2023-12-31'),

('P002', '企业定期存款', '03', 'ORG002', 420000.00, 415000.00, '2023-12-31'),

('P003', '大额存单', '01', 'ORG003', 1850000.00, 1830000.00, '2023-12-31')；

4.调用存储过程： 

call proc_continuous_query_12h(10);

注： 

本存存储过程适用于：轻度批量测试(执行12小时)，可用来观察集群状态，以及CPU ,内存变化。