Error handler in DB2 UDB8.2


DROP PROCEDURE SP_HANDLER
GO
CREATE PROCEDURE SP_HANDLER
SPECIFIC SP_HANDLER
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 MY_UDL VALUES ('測試','公司名稱重覆');

IF (retcode <> 0) THEN
INSERT INTO N_PROCESS_ERRLOG
VALUES (CURRENT TIMESTAMP,'SP_HANDLER',retcode,'新增失敗');
SET retcode = 0;
END IF;
COMMIT;
END;

BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET RETCODE = SQLCODE;

INSERT INTO MY_UDL VALUES ('測試','公司名稱重覆2');

IF (retcode <> 0) THEN
INSERT INTO N_PROCESS_ERRLOG
VALUES (CURRENT TIMESTAMP,'SP_HANDLER',retcode,'新增失敗2');
SET retcode = 0;
END IF;
COMMIT;
END;
END