/*
DB2 9.5 FUNCTION SYSPROC.SQLERRM
傳入 SQLCODE
傳回 Error Message
ex:
SELECT SYSPROC.SQLERRM(-402) FROM SYSIBM.SYSDUMMY1
傳回
SQL0402N The data type of an operand of an arithmetic function or
operation "" is not numeric.
*/
/* create table for testing */
CREATE TABLE EXCEPTION_TEST
(
COL1 INTEGER NOT NULL PRIMARY KEY
) IN USERSPACE1
/* create table for storing log */
CREATE TABLE EXCEPTION_LOG
(
SYSDT TIMESTAMP,
INPUT_VALUE VARCHAR(50),
ERRCODE INTEGER,
ERRMSG VARCHAR(1024)
) IN USERSPACE1;
/* create procedure */
CREATE PROCEDURE SP_EXCEPTION(IN iNum INTEGER)
SPECIFIC SP_EXCEPTION
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RETCODE INTEGER DEFAULT 0;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,
SQLWARNING,
NOT FOUND
SET RETCODE = SQLCODE;
INSERT INTO EXCEPTION_TEST VALUES (iNum);
IF (RETCODE <> 0) AND (RETCODE <> 100) THEN
INSERT INTO EXCEPTION_LOG
VALUES (CURRENT TIMESTAMP,CHAR(iNum),RETCODE,
SYSPROC.SQLERRM(RETCODE));
END IF;
COMMIT;
END;
END
/* call procedure twice for testing duplicate */
C:\>db2 call sp_exception(1)
傳回狀態 = 0
C:\>db2 call sp_exception(1)
傳回狀態 = 0
/* check exception_log table */
SELECT * FROM EXCEPTION_LOG
