[DB2] User Define Function – SQL Scalar Example

พอดีมีรุ่นน้องให้สอนการเขียน 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 FIXEDINCOME ดังนี้ครับ

ซึ่งมันไม่สื่อถึงเวลาครับ โดยเจ้าข้อมูลที่สื่อถึงเวลามันอยู่ใน Table TENOR ดังนี้ครับ โดยมี Field ที่จำเป็น ดังนี้

ตัวอย่างแรก ถ้ามี TENOR ช่วง 1ปี-3 ปี มีการจัดเก็บข้อมูล ดังนี้

ตัวอย่างสอง ถ้ามี TENOR ช่วง 1 ปี มีการจัดเก็บข้อมูล (โดยข้อมูล TENOR ที่ตรงกัน COUPON FREQUENCY จะถูก Config ไว้ในรูปแบบนี้ครับ) ดังนี้

โดยแต่ละ TENOR ถ้ามีข้อมูลเรียงลำดับกันเป็นชุด เช่น 1 Month, 2 Month ผมได้จัดลงใน Table TENORGROUP และ TENOR GROUPDETAIL (อันนี้ไม่ขอลงในรายละเอียดครับ) หลังจากที่ปูความเข้าใจด้าน 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)
	Routine body --------------------------------------(6)

การ 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 ครับ

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;

ตัวอย่างการเรียกใช้งานครับ โดย SYSIBM.SYSDUMMY1 มัน คือ Temp ของ DB2 มองว่าเป็นกระดาษทดครับ เพราะ เวลาเรียกใช้ Function จริง เราต้องเรียกใช้คำสั่ง SQL ที่มีพวก SELECT...FROM แต่ที่นี้ ผมเอาคำสั่ง SQL จริงมาแสดงให้ดูไม่ได้ เพราะมันยาวมากกก 555 อ่านไปเดี๋ยวงงครับ เอาแบบนี้สั้นกระชับดีครับ (สรุปคือให้มองง่ายๆ ว่ามันเรียกใช้เหมือนพวกคำสั่ง SUM(), AVG(), MIN(), MAX() แหละครับ)

SELECT "INVEST"."FINDTENOROFCOUPONFREQUENCY"(1, 'BIBOR_BOND') FROM SYSIBM.SYSDUMMY1;

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