随便记录一个DB2的存储过程

CREATE OR REPLACE PROCEDURE SP_SALARY(INT COMP_DATE DATE)
 SPECIFIC SP_SALARY
 LANGUAGE SQL
BEGIN
    DECLARE V_CNT INT;
    DECLARE V_NAME CHAR(20);
    DECLARE PROC_NAME STRING DEFAULT 'SP_SALARY';
    DECLARE SQLCODE INT;
    DECLARE SQLSTATE CHAR(5);

    --定义异常 START
    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
        INSERT INTO PROC_LOG VALUES(PROC_NAME,'执行异常:'||SQLERRM,SQLCODE,CURRENT TIMESTAMP);
    END;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO PROC_LOG VALUES(PROC_NAME,'执行异常:'||SQLERRM,SQLCODE,CURRENT TIMESTAMP);
    END;

    DECLARE CONTINUE HANDLER FOR NOTFOUND
    BEGIN
        INSERT INTO PROC_LOG VALUES(PROC_NAME,'执行异常:'||SQLERRM,SQLCODE,CURRENT TIMESTAMP);
    END;
    --定义异常 END

    FOR V AS CUR_EMP CURSOR FOR SELECT * FROM EMP
    DO
        BEGIN
            SELECT NAME,COALESCE(MIN(AMT),0) INTO V_NAME,V_CNT FROM SALARY;
            IF V_CNT < 1000 THEN
                INSERT INTO RESULT(NAME,TOTAL_AMT) VALUES (V_NAME,V_CNT);
            ELSEIF V_CNT >= 1000 THEN
                INSERT INTO RESULT(NAME,TOTAL_AMT) VALUES (V_NAME,V_CNT);
            ELSEIF V_CNT 
                INSERT INTO RESULT(NAME,TOTAL_AMT) VALUES (V_NAME,V_CNT);
            END IF;
            SET V_INT = 0;
        END
    END FOR;
    
END;

写这个的目的就是为了记录下DB2的存储过程的语法，比如定义异常，是在DECLARE的时候定义的，有异常会自动调用定义的异常那里的实现；IF语法；循环，个人不喜欢用open cursor游标方式。

异常参考链接：DB2存储过程中的异常处理_declare continue handler for sqlexception-CSDN博客