พอดีมีรุ่นน้องให้สอนการเขียน Function บน DB2 ครับ โดย Function ที่ผมเขียนเป็นแบบ SQL Scalar Function โดยเจ้า SQL Scalar Function เป็น Function ที่คืนค่าผลลัพธ์ มีการคืนผลลัพธ์ออกมาค่าเดียวเสมอ โดยมีรูปแบบข้อมูล เช่น datetime, int, varchar เป็นต้นครับ ตัวอย่าง Scalar Function ที่มีในระบบพวก ABS() ที่ส่งตัวเลข เข้าไป คืนผลลัพธ์ที่ตัวเครื่องหมายแล้ว ออกมาค่าเดียวครับ เช่น ABS(-5) = 5
สำหรับ Function นี้ ผมขอเอาตัวอย่างจริงเลยและกัน(ขี้เกียจคิดครับ ฮ่าๆ) โดย Function นี้มีหน้าที่แปลงรอบการจ่าย COUPON FREQUENCY(รอบการจ่ายดอกเบี้ย) ของ BOND ออกมาเป็น TENOR CODE(ช่วงเวลาครับ เช่น 1 เดือน / 2 เดือน เป็นต้นครับ) เพราะในระบบเดิมมันเก็บ COUPON FREQUENCY ใน Table FIXINCOME ดังนี้ครับ
| ID ในระบบ | ความหมาย |
| 1 | Monthly |
| 2 | Two Month |
| 3 | Quarterly |
| 4 | TRI-ANNUALLY |
| 5 | SEMI-ANNUALLY |
| 6 | ANNUALLY |
| 7 | AT CALL |
ซึ่งมันไม่สื่อถึงเวลาครับ โดยเจ้าข้อมูลที่สื่อถึงเวลามันอยู่ใน Table TENOR ดังนี้ครับ โดยมี Field ที่จำเป็น ดังนี้
| Field Name | ความหมาย |
| TENOR ID | ID ของ TENOR |
| TENOR CODE | ชื่อ TENOR เช่น 1 Week |
| START_NUM | จำนวนของ TENOR ของเวลาเริ่มต้น โดยต้องตีความร่วมกับ START_UNIT |
| START_UNIT | 1 = Days, 2 = Weeks. 3 = Months, 4 = Years |
| END_NUM | จำนวนของ TENOR ของเวลาสิ้นสุด โดยต้องตีความร่วมกับ END_UNIT |
| END_UNIT | 1 = Days, 2 = Weeks. 3 = Months, 4 = Years |
ตัวอย่างแรก ถ้ามี TENOR ช่วง 1ปี-3 ปี มีการจัดเก็บข้อมูล ดังนี้
| COLUMN NAME | TENOR ID | TENOR CODE | START_NUM | START_UNIT | END_NUM | END_UNIT |
| VALUE | 1 | 1-3 Years | 1 | 4 | 3 | 4 |
ตัวอย่างสอง ถ้ามี TENOR ช่วง 1 ปี มีการจัดเก็บข้อมูล (โดยข้อมูล TENOR ที่ตรงกัน COUPON FREQUENCY จะถูก Config ไว้ในรูปแบบนี้ครับ) ดังนี้
| COLUMN NAME | TENOR ID | TENOR CODE | START_NUM | START_UNIT | END_NUM | END_UNIT |
| VALUE | 1 | 1 Years | 1 | 4 | 1 | 4 |
โดยแต่ละ TENOR ถ้ามีข้อมูลเรียงลำดับกันเป็นชุด เช่น 1 Month, 2 Month ผมได้จัดลงใน Table TENOR GROUP และ TENOR GROUP DETAIL (อันนี้ไม่ขอลงในรายละเอียดครับ) หลังจากที่ปูความเข้าใจด้าน Business แล้ว เรามาเข้าเรื่องการเขียน Function กันครับ
เจ้า Function ของผมที่เขียนมีลำดับการทำงานประมาณนี้ครับ
- แปลง COUPON FREQUENCY มาเป็นหน่วยของ TENOR ก่อนครับ คือ TENOR_NUM และ TENOR_UNIT
- เอา TENOR_NUM และ TENOR_UNIT เข้าไปหา TENOR CODE ตาม TENOR GROUP ที่เราระบุไว้ครับ
รูปแบบโครงสร้างของ Function ใน DB2 ครับ (ขอสรุปสั้น ตามนี้ครับ เพราะ มันยาวมาก ถ้าอยากศึกษาเพิ่มเติมกดดูได้เลยครับ
CREATE OR REPLACE FUNCTION name-of-udf --------------------(1) (List of the input parameters of the UDF) ---------(2) Returns -------------------------------------------(3) Function properties -------------------------------(4) Generation options --------------------------------(5)
การ Config เพิ่มเติมสำหรับ Function ครับ
- NOT SECURED: บอกให้ตัว DBMS เข้าใจว่า Parameter ของ Function นี้ ไม่ได้อ้างอิงถึง Column ที่ถูกกำหนดสิทธิในส่วน Column Access Control (ถ้า Column ที่ถูกกำหนดสิทธิถูกส่งเข้ามาเป็น Parameter ของ Function ตัว DBMS จะ Raise Exception เกี่ยวกับสิทธิออกมาครับ)
- LANGUAGE SQL: บอกภาษาที่เราใช้เขียนไส้ในของ Function ครับ โดยถ้าเป็นภาษา C หรือ Java เราสามารถกำหนดให้ไปชี้ Path ที่เก็บ Library เพื่อ Run คำสั่งได้ครับ
- INHERIT SPECIAL REGISTERS: ให้ Function ของเราเนี่ย ใช้งานตัว SPECIAL REGISTERS ของ DB2 ได้เลยครับ เช่น SESSION TIME ZONE หรือ SQLRULES เป็นต้นครับ
สำหรับพวก BEGIN ATOMIC อ่านได้จากบทความนี้ครับ
อ๋อ และก็ถ้าดูใน QUERY ที่มันต้องมี FETCH FIRST 1 ROWS ONLY เพราะ เนื่องจาก Scalar Function มันมีข้อกำหนดว่าเราต้อ Return ได้ค่าเดียวเท่านั้นครับ
ตัวอย่างของ Function ครับ
[sql]CREATE OR REPLACE FUNCTION INVEST.FINDTENOROFCOUPONFREQUENCY (
COUPONFREQUENCY INTEGER,
TENORGROUPCODE VARCHAR(30) )
RETURNS VARCHAR(30)
NOT SECURED
LANGUAGE SQL
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE TENOR_NUM INTEGER;
DECLARE TENOR_UNIT INTEGER;
IF COUPONFREQUENCY = 1 THEN --1 = Monthly
SET TENOR_NUM = 1;
SET TENOR_UNIT = 3;
ELSEIF COUPONFREQUENCY = 2 THEN --2 = Two Month
SET TENOR_NUM = 2;
SET TENOR_UNIT = 3;
ELSEIF COUPONFREQUENCY = 3 THEN --3 = Quarterly (3 เดือน)
SET TENOR_NUM = 3;
SET TENOR_UNIT = 3;
ELSEIF COUPONFREQUENCY = 4 THEN --4 = TRI-ANNUALLY (4 เดือน)
SET TENOR_NUM = 4;
SET TENOR_UNIT = 3;
ELSEIF COUPONFREQUENCY = 5 THEN --5 = SEMI-ANNUALLY (6 เดือน)
SET TENOR_NUM = 6;
SET TENOR_UNIT = 3;
ELSEIF COUPONFREQUENCY = 6 THEN --6 = ANNUALLY (1 ปี)
SET TENOR_NUM = 12;
SET TENOR_UNIT = 3;
ELSEIF COUPONFREQUENCY = 7 THEN --7 = AT CALL (ครบกำหนด)
SET TENOR_NUM = 999;
SET TENOR_UNIT = 4;
END IF;
RETURN (SELECT TENORCODE
FROM TENORGROUP TG
INNER JOIN TENORGROUPDETAIL TGD
ON TG.TENORGROUPID = TGD.TENORGROUPID
AND TG.CREATETIME = TGD.GROUPCREATETIME
INNER JOIN TENOR T
ON TGD.TENORTYPEID = T.TENORID
AND TGD.TYPECREATETIME = T.CREATETIME
AND T.ACTIVEFLAG = 'A'
WHERE TG.TENORGROUPCODE = TENORGROUPCODE
AND TG.ACTIVEFLAG = 'A'
AND T.STARTNUM = TENOR_NUM
AND T.STARTUNIT = TENOR_UNIT
AND T.ENDNUM = TENOR_NUM
AND T.ENDUNIT = TENOR_UNIT
FETCH FIRST 1 ROWS ONLY);
END;
[/sql]
ตัวอย่างการเรียกใช้งานครับ โดย SYSIBM.SYSDUMMY1 มัน คือ Temp ของ DB2 มองว่าเป็นกระดาษทดครับ เพราะ เวลาเรียกใช้ Function จริง เราต้องเรียกใช้คำสั่ง SQL ที่มีพวก SELECT...FROM แต่ที่นี้ ผมเอาคำสั่ง SQL จริงมาแสดงให้ดูไม่ได้ เพราะมันยาวมากกก 555 อ่านไปเดี๋ยวงงครับ เอาแบบนี้สั้นกระชับดีครับ (สรุปคือให้มองง่ายๆ ว่ามันเรียกใช้เหมือนพวกคำสั่ง SUM(), AVG(), MIN(), MAX() แหละครับ)
[sql]SELECT "INVEST"."FINDTENOROFCOUPONFREQUENCY"(1, 'BIBOR_BOND') FROM SYSIBM.SYSDUMMY1;[/sql]
Reference
- CREATE FUNCTION (inlined SQL scalar)
- Special registers in a user-defined function or a stored procedure
- E-Book: Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries
Discover more from naiwaen@DebuggingSoft
Subscribe to get the latest posts to your email.
