由于存储过程跨数据库移植性差且不易调试等缺点，在数据开发过程中都会尽量避免使用存储过程。但是数仓开发过程中，偶尔也会遇到大量数据运算与刷新的情况。

例如需求开发完成，需要补全大量历史数据，而数据库资源不支持一次性回刷大量数据，只能按照时间滚动回刷历史数据，此时可以使用存储过程来完成数据回刷任务。

下面为DB2环境下开发和使用存储过程做一下记录。

日志记录

存储过程不易进行调试，在执行过程中无法获取内部的运行情况，所以开发过程中一定要在存储过程中进行日志的记录。

首先根据需求创建一个日志记录表，下面是一个简单的日志记录表，以及向表中记录日志的存储过程。

-- 创建日志存储表
CREATE TABLE TKDW.PROC_LOGS(
    LEVEL        VARCHAR(20),  -- 日志级别
    PROC_NAME    VARCHAR(128), -- 存储过程名
    EXECUTE_TIME TIMESTAMP,    -- 执行时间
    SQL_CODE     INT,          -- SQL结果代码
    SQLSTATE     VARCHAR(5),   -- SQL报错代码
    MESSAGE      VARCHAR(255)  -- 日志消息
);
-- 创建日志记录存储过程
CREATE OR REPLACE PROCEDURE DW.CREATE_LOG (
    IN PROC_NAME VARCHAR(128),
    IN MESSAGE   VARCHAR(255),
    IN SQL_CODE  INT DEFAULT 0,
    IN SQL_STATE VARCHAR(5) DEFAULT '00000'
)
LANGUAGE SQL
SPECIFIC CREATE_LOG
BEGIN
    DECLARE LOG_TYPE VARCHAR(20);
    SET LOG_TYPE = CASE WHEN SQL_CODE = 0 THEN 'INFO' ELSE 'ERROR' END;
    INSERT INTO DW.PROC_LOGS(LOG_TYPE, PROC_NAME, EXECUTE_TIME, MESSAGE, SQL_CODE, SQLSTATE) 
    VALUES (LOG_TYPE, PROC_NAME, CURRENT_TIMESTAMP, MESSAGE, SQL_CODE, SQL_STATE);
END

在编写存储过程时，可以调用日志存储过程将运行信息插入到日志表中。在存储过程执行期间，可以查看日志表获取存储过程内部输出的运行信息，例如报错信息，变量值以及处理的数据条目等。

存储过程模板

下面是一个存储过程模板，错误的变量与游标定义顺序会导致报错，可以按照注释说明完成变量和游标的定义。

模板中还加入了异常自动捕获和记录的代码，发生异常可以直接在前面定义的日志表中查看异常消息。

CREATE OR REPLACE PROCEDURE DW.存储过程名()
LANGUAGE SQL
SPECIFIC 存储过程名
BEGIN

    --↓↓↓↓ 变量定义区 不要给变量赋值 游标在此处定义 ↓↓↓↓--
    -- 异常变量定义, 用于处理异常, 不要删除
    DECLARE SQLCODE         INTEGER DEFAULT 0; -- 声明后自动捕获报错号
    DECLARE SQLSTATE        CHAR(5) DEFAULT '00000'; -- 声明后自动捕获报错编码
    DECLARE V_MESSAGE_TEXT  VARCHAR(255) DEFAULT ''; -- 用于存放报错信息
    -- 定义自己的变量
    -- 定义自己的游标(cursor)
    --↑↑↑↑ 变量定义区 不要给变量赋值 游标也要定义在此处 ↑↑↑↑--


    --↓↓↓↓ 错误处理区 不要删除 ↓↓↓↓--
    -- 定义错误捕获，将错误写入执行日志
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS EXCEPTION 1
            V_MESSAGE_TEXT=MESSAGE_TEXT;
        CALL DW.CREATE_LOG('存储过程名', V_MESSAGE_TEXT, SQLCODE, SQLSTATE);
    END;
    --↑↑↑↑ 错误处理区 不用改 不要删除 ↑↑↑↑--

    --↓↓↓↓ 逻辑编写区 ↓↓↓↓--

    --↑↑↑↑ 逻辑编写区 ↑↑↑↑--
END

模板中的“存储过程名”需要替换成自己定义的存储过程名，日志记录函数需要通过传入正确的名称来区分日志来源。

定义好的变量和游标在逻辑编写区使用，所有的变量与游标要统一在上方定义。

中断控制

当面对运行时间过长的存储过程，如需要运行数个小时的情况，就需要考虑存储过程需要手动终止。

如果希望存储过程能够被手动中断，那么可以通过引入一个状态控制表来优雅的停止存储过程。

在控制表中记录存储过程的运行状态，以及用来控制存储过程中断的标记，当存储过程读到终止标记则退出运行。

创建一个状态控制表，记录存储过程名，运行状态，开始结束时间，以及手动停止标记。

-- 创建存储过程状态表
CREATE TABLE DW.PROC_STATUS (
  PROC_NAME  VARCHAR(100) NOT NULL,
  STATUS     VARCHAR(20)  NOT NULL,
  STOP_FLAG  CHAR(1)      DEFAULT 'N',
  START_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  END_TIME   TIMESTAMP,
);
COMMENT ON TABLE DW.PROC_STATUS IS '存储过程状态表';
COMMENT ON COLUMN DW.PROC_STATUS.PROC_NAME IS '存储过程名称';
COMMENT ON COLUMN DW.PROC_STATUS.STATUS IS '状态: RUNNING / SUCCESS / FAILED / STOPPED';
COMMENT ON COLUMN DW.PROC_STATUS.STOP_FLAG IS '停止标志: N=继续, Y=停止';
COMMENT ON COLUMN DW.PROC_STATUS.START_TIME IS '开始时间';
COMMENT ON COLUMN DW.PROC_STATUS.END_TIME IS '结束时间';

创建状态控制存储过程，方便在存储过程中调用刷新运行状态。

-- 记录运行状态
CREATE OR REPLACE PROCEDURE DW.PROC_RUNNING(
  PROC_NAME VARCHAR(100)
)
LANGUAGE SQL
BEGIN
    IF EXISTS(SELECT 1 FROM DW.PROC_STATUS WHERE PROC_NAME = PROC_NAME) THEN
        UPDATE DW.PROC_STATUS SET STATUS = 'RUNNING', START_TIME = CURRENT_TIMESTAMP, END_TIME = NULL, STOP_FLAG = 'N' WHERE PROC_NAME = PROC_NAME;
    ELSE
        INSERT INTO DW.PROC_STATUS(PROC_NAME, STATUS, START_TIME) VALUES(PROC_NAME, 'RUNNING', CURRENT_TIMESTAMP);
    END IF;
END
-- 记录成功状态
CREATE OR REPLACE PROCEDURE DW.PROC_SUCCESS(
  PROC_NAME VARCHAR(100)
)
LANGUAGE SQL
BEGIN
    UPDATE DW.PROC_STATUS SET STATUS = 'SUCCESS', END_TIME = CURRENT_TIMESTAMP WHERE PROC_NAME = PROC_NAME;
END
-- 记录失败状态
CREATE OR REPLACE PROCEDURE DW.PROC_FAILED(
  PROC_NAME VARCHAR(100)
)
LANGUAGE SQL
BEGIN
    UPDATE DW.PROC_STATUS SET STATUS = 'FAILED', END_TIME = CURRENT_TIMESTAMP WHERE PROC_NAME = PROC_NAME;
END
-- 记录停止状态
CREATE OR REPLACE PROCEDURE DW.PROC_STOP(
  PROC_NAME VARCHAR(100)
)
LANGUAGE SQL
BEGIN
    UPDATE DW.PROC_STATUS SET STATUS = 'STOPPED', STOP_FLAG = 'N' WHERE PROC_NAME = PROC_NAME;
END

加入状态控制后的存储过程模板。

CREATE OR REPLACE PROCEDURE DW.存储过程名()
LANGUAGE SQL
SPECIFIC 存储过程名
BEGIN
    --↓↓↓↓ 变量定义区 不要给变量赋值 游标在此处定义 ↓↓↓↓--
    -- 异常变量定义, 用于处理异常, 不要删除
    DECLARE SQLCODE         INTEGER DEFAULT 0; -- 声明后自动捕获报错号
    DECLARE SQLSTATE        CHAR(5) DEFAULT '00000'; -- 声明后自动捕获报错编码
    DECLARE V_MESSAGE_TEXT  VARCHAR(255) DEFAULT ''; -- 用于存放报错信息
    -- 定义自己的变量
    -- 定义自己的游标(cursor)
    --↑↑↑↑ 变量定义区 不要给变量赋值 游标也要定义在此处 ↑↑↑↑--

    --↓↓↓↓ 错误处理区 不要删除 ↓↓↓↓--
    -- 定义错误捕获，将错误写入执行日志
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS EXCEPTION 1
            V_MESSAGE_TEXT=MESSAGE_TEXT;
        CALL DW.CREATE_LOG('存储过程名', V_MESSAGE_TEXT, SQLCODE, SQLSTATE);
        CALL DW.PROC_FAILED('存储过程名');
    END;
    --↑↑↑↑ 错误处理区 不用改 不要删除 ↑↑↑↑--

    --↓↓↓↓ 逻辑编写区 ↓↓↓↓--
    CALL DW.PROC_RUNNING('存储过程名');
    WHILE 循环条件 DO
        -- 控制中断
        IF EXISTS(SELECT 1 FROM DW.PROC_STATUS WHERE PROC_NAME='存储过程名' AND STOP_FLAG='Y') THEN
            CALL DW.PROC_STOPPED('存储过程名');
            LEAVE;
        END IF;
        -- 循环体逻辑
    END WHILE;
    CALL DW.PROC_SUCCESS('存储过程名');
    --↑↑↑↑ 逻辑编写区 ↑↑↑↑--
END

上面是一套简单的中断控制示例，要根据实际情况按需调整。比如存储过程并发需要加入运行ID字段，非并发情况需要控制单个运行实例等。