Create User-Define Function to check date format USING DB2 UDB8.2

建立測試table

CREATE TABLE TEST_DATE
(
　　　DT INTEGER
);
INSERT INTO TEST_DATE VALUES(20080101);
INSERT INTO TEST_DATE VALUES(20080131);
INSERT INTO TEST_DATE VALUES(20080208);
INSERT INTO TEST_DATE VALUES(20080229);
INSERT INTO TEST_DATE VALUES(20080231);
INSERT INTO TEST_DATE VALUES(20081131);
INSERT INTO TEST_DATE VALUES(80327);


/* 建立一個user define function
　　input:字串
　　return:0 => 表示日期格式正確
　　　　　　-1 => 表示格示為非日期格式(挾雜非數字等...)


DROP FUNCTION FN_CHKVALIDATE
GO
CREATE FUNCTION FN_CHKVALIDATE (D VARCHAR(32))
RETURNS INTEGER
SPECIFIC FN_CHKVALIDATE
LANGUAGE SQL
BEGIN ATOMIC
　　　　DECLARE N INTEGER;
　　　　DECLARE i INTEGER DEFAULT 1;

　　　　-- length must = 8
　　　　IF LENGTH(RTRIM(D)) <> 8 OR RTRIM(COALESCE(D,'')) = '' THEN
　　　　　　　RETURN -1;
　　　　END IF;

　　　　-- whole character should allow to turn into numbers
　　　　WHILE i <= LENGTH(RTRIM(COALESCE(D,''))) DO
　　　　　　　IF ASCII(SUBSTR(RTRIM(D),i,1)) NOT BETWEEN 48 AND 57 THEN
　　　　　　　　　　RETURN -1;
　　　　　　　END IF;
　　　　　　　SET i = i + 1;
　　　　END WHILE;

　　　　-- month should be valid
　　　　IF INT(SUBSTR(D,1,4)) < 101 THEN
　　　　　　　RETURN -1;
　　　　END IF;

　　　　IF INT(SUBSTR(D,5,2)) NOT BETWEEN 1 AND 12 THEN
　　　　　　　RETURN -1;
　　　　END IF;

　　　　IF INT(SUBSTR(D,7,2)) < 1 THEN
　　　　　　　RETURN -1;
　　　　END IF;

　　　　-- date is valid
　　　　IF (D > (SELECT CHAR(INT(DT))
　　　　　　　　　FROM　(SELECT DATE(SUBSTR(D,1,4) || '-' ||
　　　　　　　　　　　　　　　　　　　　SUBSTR(D,5,2) || '-01') +
　　　　　　　　　　　　　　　　　1 MONTH - 1 DAY DT
　　　　　　　　　　　　　FROM SYSIBM.SYSDUMMY1) T
　　　　　　　　　)) THEN
　　　　　　SET N = -1;
　　　　ELSE
　　　　　　SET N = 0;
　　　　END IF;
RETURN N;
END


以TEST_DATA裡的DT欄位來測試FUNCTION是否WORK

SELECT USR_TO_DATE(CHAR(DT)),
　　　　FN_CHKVALIDATE(CHAR(DT))
FROM 　TEST_DATE
WHERE FN_CHKVALIDATE(CHAR(DT)) >= 0;

得到以下結果

2008/1/1　　　0
2008/1/31　　 0
2008/2/8　　　0
2008/2/29 　　0

(4 row(s) affected)

不使用function的結果

SELECT USR_TO_DATE(CHAR(DT))
FROM TEST_DATE;

得到以下結果

2008/1/1　　0
2008/1/31 　0
2008/2/8　　0
2008/2/29 　0

(4 row(s) affected)

Server Msg: -181, State: 22007, [IBM][CLI Driver][DB2/NT]
SQL0181N 日期時間值的字串表示式超出範圍 SQLSTATE=22007


UDF:USR_TO_DATE USING DB2 UDB8.2


CREATE FUNCTION USR_TO_DATE(szDT CHAR(8))
RETURNS DATE
SPECIFIC USR_TO_DATE
LANGUAGE SQL
RETURN
SELECT DATE(SUBSTR(szDT,1,4) || '-' ||
SUBSTR(szDT,5,2) || '-' ||
SUBSTR(szDT,7,2))
FROM SYSIBM.SYSDUMMY1;



SELECT USR_TO_DATE(CHAR('20080101')) FROM SYSIBM.SYSDUMMY1;


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


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