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博客