ฟังก์ชัน WMSYS.WM_CONCAT
นักพัฒนาบางท่านที่พัฒนาระบบบนฐานข้อมูล Oracle 10g หรือ 11g อาจจะผ่านตาหรือเคยใช้งานฟังก์ชัน WMSYS.WM_CONCAT โดยฟังก์ชันนี้เป็นฟังก์ชันที่ใช้ในการนำข้อมูลในฟีลด์เดียวกัน แต่อยู่ต่างเร็คคอร์ดมาเชื่อมต่อกันเป็นข้อมูลเร็คคอร์ดเดียว
ในที่นี้จะยกตัวอย่างจากตารางข้อมูลทดสอบ ชื่อว่าตาราง STATIONERY ซึ่งเก็บข้อมูลเครื่องเขียน โดยแยกเป็นสี และระบุจำนวนของเครื่องเขียนแต่และชนิดไว้ ดังนี้
ทดลองใช้คำสั่ง SELECT แบบปกติ ด้วยคำสั่ง
SELECT WMSYS.WM_CONCAT(COLOR) COLOR_LIST
FROM STATIONERY;
ผลลัพธ์ที่ได้
แต่ในเง่การใช้งานส่วนใหญ่ มักจะต้องการแสดงข้อมูลสรุปเป็นกลุ่ม เช่น จากตัวอย่างนี้ ถ้าต้องการแยกข้อมูลสรุปเป็นกลุ่มตามชนิดเครื่องเขียน ว่าเครื่องเขียนแต่ละชนิดมีสีอะไรบ้าง ซึ่งก็สามารถทำได้โดยการเพิ่มการ GROUP BY ตามฟีลด์ STATIONERY เข้าไป ดังนี้
SELECT STATIONERY, WMSYS.WM_CONCAT(COLOR) COLOR_LIST
FROM STATIONERY
GROUP BY STATIONERY;
ผลลัพธ์ที่ได้
หรือหากต้องการข้อมูลสรุปตามสีของเครื่องเขียน ว่าแต่ละสีมีเครื่องเขียนชนิดใดอยู่บ้าง คำสั่งที่ใช้งานก็จะเป็น
SELECT COLOR, WMSYS.WM_CONCAT(STATIONERY) STATIONERY_LIST
FROM STATIONERY
GROUP BY COLOR;
ผลลัพธ์ที่ได้
ที่กล่าวไปข้างต้นคือการใช้งานฟังก์ชัน WMSYS.WM_CONCAT บนฐานข้อมูล Oracle 10g หรือ 11g แต่ถ้านักพัฒนาท่านใดวางแผนที่จะอัพเกรตฐานข้อมูลไปเป็น Oracle 12c ท่านก็จะเจอกับปัญหาเมื่อมีการเรียกใช้งานฟังก์ชันนี้ โดยจะมีข้อความ error แจ้งกลับมาว่า
ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier
นั่นเป็นเพราะใน Oracle 12c จะไม่มีฟังก์ชันนี้ให้เรียกใช้งานอีกต่อไปแล้ว
ดังนั้นในบทความนี้จะขอแนะนำฟังก์ชันอีกฟังก์ชันหนึ่งซึ่งทำงานคล้ายคลึงกัน และสามารถให้ผลลัพธ์แบบเดียวกันกับฟังก์ชัน WMSYS.WM_CONCAT ซึ่งฟังก์ชันที่ว่านี้คือ LISTAGG
ฟังก์ชัน LISTAGG
ฟังก์ชัน LISTAGG เป็นฟังก์ชันที่เริ่มมีให้ใช้งานใน Oracle 11g R2 ใช้งานในลักษณะเดียวกันกับ ฟังก์ชัน WMSYS.WM_CONCAT แต่ในส่วนของรายละเอียดนั้นจะมีบางจุดที่แตกต่างกันออกไป
จากตัวอย่างข้อมูลที่นำเสนอไปข้างต้น จากที่ใช้งานกับฟังก์ชัน WMSYS.WM_CONCAT ลองเปลี่ยนมาใช้ฟังก์ชัน LISTAGG ได้ดังนี้
ตัวอย่างแรกเป็นการทดลอง SELECT แบบปกติ
SELECT LISTAGG(COLOR,’,’)
WITHIN GROUP (ORDER BY COLOR) COLOR_LIST
FROM STATIONERY;
ผลลัพธ์ที่ได้
อธิบายการใช้งานคำสั่ง
- LISTAGG(COLOR,’,’) ภายในวงเล็บเป็นฟีลด์ข้อมูลจากต่างเร็คคอร์ดกันแต่ต้องการให้แสดงเรียงต่อกัน ซึ่งในตัวอย่างนี้ก็คือฟีลด์ COLOR ส่วน ‘,’ ก็คือการระบุตัวคั่นระหว่างข้อมูล ซึ่งในทีนี้ใช้เป็นจุลภาคนั่นเอง
- WITHIN GROUP (ORDER BY COLOR) เป็นการระบุรูปแบบการเรียงข้อมูล ซึ่งในที่นี้จะเรียงตามฟีลด์ COLOR
จากตัวอย่างจะเห็นว่าสิ่งที่ฟังก์ชัน LISTAGG ทำได้แตกต่างจาก WMSYS.WM_CONCAT คือ การระบุตัวคั่นระหว่างข้อมูล และการระบุการเรียงลำดับของข้อมูลที่มาต่อกันนั่นเอง
ตัวอย่างต่อมา จะให้แสดงผลลัพธ์แยกสรุปเป็นกลุ่มตามชนิดเครื่องเขียน ว่าเครื่องเขียนแต่ละชนิดมีสีอะไรบ้าง คำสั่งที่ใช้ก็จะใช้การ GROUP BY ด้วยฟีลด์ STATIONERY เช่นเดิม คือ
SELECT STATIONERY, LISTAGG(COLOR,’,’)
WITHIN GROUP (ORDER BY STATIONERY) COLOR_LIST
FROM STATIONERY GROUP BY STATIONERY;
ผลลัพธ์ที่ได้
ส่วนตัวอย่างสุดท้าย ก็จะเป็นการให้แสดงผลลัพธ์สรุปตามสีของเครื่องเขียน ว่าแต่ละสีมีเครื่องเขียนชนิดใดอยู่บ้าง คำสั่งที่ใช้งานก็จะเป็น
SELECT COLOR, LISTAGG(STATIONERY,’,’)
WITHIN GROUP (ORDER BY STATIONERY) LIST_STATIONERY
FROM STATIONERY GROUP BY COLOR;
ผลลัพธ์ที่ได้
ข้อมูลอ้างอิง :
https://oracle-base.com/articles/misc/string-aggregation-techniques
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030