วันนี้มี Function ดีๆ มาแนะนำให้รู้จักกันค่ะ
จากประสบการณ์ที่ผ่านมา คาดว่าหลายๆ คน อาจจะเคยต้องเขียน Function เพื่อคำนวณหาอายุ ต่างๆ
ไม่ว่าจะเป็น อายุของลูกค้า, อายุงาน, อายุสัญญา ต่างๆ เป็นต้น และวิธีที่หลายๆ คนเคยใช้ ก็คงไม่ต่างกันนัก
กล่าวคือ หาผลต่างระหว่างวันที่เริ่มต้น กับวันที่สิ้นสุด เพื่อให้ได้จำนวนวันที่อยู่ในช่วงอายุนั้นๆ จากนั้นก็นำไปคำนวณ
หาจำนวนปี และเดือน โดยการ mod และ div ด้วยจำนวนวันในหนึ่งปี และจำนวนวันในหนึ่งเดือน ได้ในที่สุด
Oracle ได้นำเสนอ Function EXTRACT ให้เป็นทางเลือกแก่ผู้ใช้ ซึ่งสามารถใช้งานได้ตั้งแต่เวอร์ชั่น 9i ขึ้นไป
โดย Function ที่ว่านี้ สามารถที่จะดึงเอา วันที่ เดือน หรือ ปี จากข้อมูลประเภท Date โดยให้ผลลัพธ์เป็นข้อมูล
ประเำภทตัวเลข ซึ่งต่างจากการแปลงรูปแบบโดยใช้ Function TO_CHAR ตามปรกติ ที่จะให้ผลลัพธ์เป็นตัวอักษร
SQL> select sysdate as current_date,
2 extract (month from sysdate) as mm_e,
3 to_char (sysdate, 'MM') as mm
4 from dual;
CURRENT_D MM_E MM --------- ---------- -- 22-JAN-08 1 01
เพื่อให้ง่ายต่อการทำความเข้าใจ เราจะลองเอา Function EXTRACT มาใช้ในการหาอายุของลูกค้าให้ดูนะค่ะ
SQL> select cust_last_name,
2 extract (year from (sysdate - date_of_birth) year to month)
3 ' years '
4 extract (month from (sysdate - date_of_birth) year to month)
5 ' months' as age
6 from customers;
CUST_LAST_NAME AGE
-------------------- --------------------
Kinski 52 years 9 months Garcia 36 years 8 months Olin 21 years 7 months Dench 54 years 7 months Altman 23 years 6 months
...
วิธีการก็ไม่มีอะไรมาก เพียงแค่ใช้ Function EXTRACT ในการ Extract ค่า YEAR และค่า MONTH จากผลต่างของวันปัจจุบันกับวันเกิด
ที่อยู่ในรูปของ Interval Year To Month ก็จะได้ผลลัพธ์ออกมาอย่างง่ายดาย
Tuesday, 22 January 2008
"Extract" Function ดีๆ ที่ไม่ควรมองข้าม
Subscribe to:
Post Comments (Atom)
3 comments:
รบกวนหน่อยครับ
หาอายุ ปี เดือนได้แล้ว
แล้วถ้าจะจำนวนวัน ต้องใช้อย่างไรครับ
ขอบคุณครับ
ถ้าจะหาจำนวนวัน ต้องเขียน Function เพิ่มค่ะ เนื่องจากในส่วนของ Interval Year To Month นั้น สามารถดึงได้แค่ ปี และ เดือน ค่ะ ถึงแม้ว่า Oracle จะมี Data Type อีกประเภทคือ Interval Day To Second ที่หากนำมาใช้ ก็จะสามารถดึงจำนวนวันได้ แต่ผลลัพธ์ที่ได้นั้น ไม่ต่างจากการนำวันที่ทั้งสองวัน มาลบกันเลยค่ะ
ตัวอย่าง Function ค่ะ
CREATE OR REPLACE FUNCTION time_interval (p_start_date DATE, p_end_date DATE)
RETURN VARCHAR2
AS
v_start_date DATE := p_start_date;
v_end_date DATE := p_end_date;
v_end_date_new DATE;
v_return VARCHAR2 (30);
v_year PLS_INTEGER;
v_month PLS_INTEGER;
v_date PLS_INTEGER;
BEGIN
IF (v_start_date <= v_end_date)
THEN
v_year := EXTRACT (YEAR FROM (v_end_date - v_start_date) YEAR TO MONTH);
v_month := EXTRACT (MONTH FROM (v_end_date - v_start_date) YEAR TO MONTH);
v_end_date_new := ADD_MONTHS (v_start_date, (v_year * 12) + v_month);
IF (v_end_date > v_end_date_new)
THEN
v_date := EXTRACT (DAY FROM (v_end_date - v_end_date_new) DAY TO SECOND);
ELSIF (v_end_date < v_end_date_new)
THEN
v_month := v_month - 1;
v_date := EXTRACT (DAY FROM ( v_end_date
- ADD_MONTHS (v_end_date_new, -1)
) DAY TO SECOND);
END IF;
v_return := NULL;
IF v_year > 0
THEN
v_return := v_year || CASE v_year
WHEN 1
THEN ' year '
ELSE ' years '
END;
END IF;
IF v_month > 0
THEN
v_return :=
v_return
|| v_month
|| CASE v_month
WHEN 1
THEN ' month '
ELSE ' months '
END;
END IF;
IF v_date > 0
THEN
v_return :=
v_return || v_date || CASE v_date
WHEN 1
THEN ' day'
ELSE ' days'
END;
END IF;
ELSE
v_return := '-';
END IF;
RETURN v_return;
END time_interval;
Post a Comment