2008年2月24日 星期日

How to use Error handler in Cursor using DB2 UDB8.2


/* 程式目的: 如CURSOR與ERROR HANDLER 同時使用的處理 */


CREATE TABLE TEST_UDL
( ID INTEGER,
NAME VARCHAR(32) ) IN USERSPACE1;

INSERT INTO TEST_UDL VALUES (1,'AA_1');
INSERT INTO TEST_UDL VALUES (2,'AA_2');
INSERT INTO TEST_UDL VALUES (3,'BB');
INSERT INTO TEST_UDL VALUES (4,'CC');
INSERT INTO TEST_UDL VALUES (5,'DD_5');
INSERT INTO TEST_UDL VALUES (6,'DD_6');

CREATE TABLE MY_UDL
( NAME VARCHAR(32) NOT NULL PRIMARY KEY,
DESC VARCHAR(64) ) IN USERSPACE1;

CREATE TABLE N_PROCESS_ERRLOG
( SYSDT TIMESTAMP,
PNAME VARCHAR(20),
ERRCODE INTEGER,
ERRMSG VARCHAR(128) ) IN USERSPACE1;

CREATE TABLE CUR_LOG
( ID INTEGER,
ERRCODE INTEGER ) IN USERSPACE1;



DROP PROCEDURE SP_CURSOR
GO
CREATE PROCEDURE SP_CURSOR
SPECIFIC SP_CURSOR
LANGUAGE SQL
BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RETCODE INTEGER DEFAULT 0;
DECLARE nID INTEGER;
DECLARE strName VARCHAR(32);

DECLARE CUR CURSOR WITH HOLD
FOR
SELECT ID,NAME FROM TEST_UDL
WHERE LOCATE('_',NAME) > 0;

OPEN CUR;
FETCH CUR INTO nID, strName;
SET RETCODE=SQLCODE;
IF RETCODE <> 0 THEN
INSERT INTO N_PROCESS_ERRLOG
VALUES (CURRENT TIMESTAMP,'SP_CURSOR',RETCODE,'CURSOR資料不存在');
GOTO LABEL1;
END IF;
WHILE (RETCODE=0) DO

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

INSERT INTO MY_UDL VALUES (SUBSTR(strName,1,LOCATE('_',strName) - 1),'名稱重覆');

IF (retcode <> 0) THEN
INSERT INTO N_PROCESS_ERRLOG
VALUES (CURRENT TIMESTAMP,'SP_CURSOR',retcode,'新增MY_DUL失敗:'
RTRIM(CHAR(nID)) strName);
SET retcode = 0;
END IF;
COMMIT;
END;

FETCH CUR INTO nID, strName;
SET RETCODE=SQLCODE;

END WHILE;
LABEL1:
CLOSE CUR;
COMMIT;
END