Monday, September 15, 2008

DB2 SIGNAL EXAMPLE

เขียน procedure ใน DB2 บางเวลาก็อยากจะให้มัน print ข้อความออกมาบ้าง
เพื่อให้ debug ได้ง่ายขึ้น วันนี้เลยขอนำเสนอ การใช้ SIGNAL ใน DB2 ครับ
ซึ่งก็เหมือนกัน throw exception ใน java อ่ะครับ

Example

CREATE PROCEDURE SP_THROW_MESSAGE()
LANGUAGE SQL
BEGIN
--Author: Mr.Fuangwith S.
DECLARE v_err_message VARCHAR(255) DEFAULT '' ;
SET v_err_message = 'HELLO WORLD';
SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT = v_err_message;
END

GO
CALL SP_THROW_MESSAGE()


Output

DB2 SQL error: SQLCODE: -438, SQLSTATE: 12345, SQLERRMC: HELLO WORLD
Message: Application raised error with diagnostic text: "HELLO WORLD".


Note
การกำหนดเลข SQLSTATE ให้อ้างตามนี้ครับ

The following are the rules for an SQLSTATE value specified on
the SIGNAL or RESIGNAL statement.

o Each character must be from the set of digits ('0' through
'9') or non-accented upper case letters ('A' through 'Z').

o The SQLSTATE class (first two characters) cannot be '00'.



The following are the rules for an SQLSTATE value specified by
the RAISE_ERROR function

o Each character must be from the set of digits ('0' through
'9') or non-accented upper case letters ('A' through 'Z')

o The SQLSTATE class (first two characters) cannot be '00',
'01', or '02' since these are not error classes.

o If the SQLSTATE class (first two characters) starts with the
character '0' through '6' or 'A' through 'H', then the
subclass (last three characters) must start with a character
in the range 'I' through 'Z'

o If the SQLSTATE class (first two characters) starts with the
character '7', '8', '9' or 'I' though 'Z', then the subclass
(last three characters) can be any of '0' through '9' or 'A'
through 'Z'.

No comments: