Database – Page 16 – CoP PSU IT Blog

ฟังก์ชัน WMSYS.WM_CONCAT และการเปลี่ยนแปลงเมื่ออัพเกรดไปใช้ Oracle 12c

January 14, 2016 23:24

ฟังก์ชัน 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

ข้อจำกัดและข้อควรระวังในการใช้เงื่อนไข IN ในคำสั่ง SELECT บนฐานข้อมูล Oracle

January 13, 2016 19:42

สำหรับบทความนี้ จะนำเสนอข้อจำกัดและข้อควรระวังในการใช้งานคำสั่ง SELECT บนฐานข้อมูล Oracle ซึ่งประสบมาจากการใช้งานจริงสองเรื่องด้วยกัน เรื่องแรกจะเป็นข้อจำกัดในการใช้เงื่อนไข IN (value1,value2,value3,…) ในคำสั่ง SELECT ส่วนอีกเรื่องจะเป็นเรื่องของข้อควรระวังในการใช้ IN ร่วมกับเงื่อนไขที่เป็น subquery ในคำสั่ง SELECT เช่นกัน การใช้คำสั่ง SELECT และเงื่อนไข IN นั้น เป็นรูปแบบคำสั่งพื้นฐานแบบหนึ่งที่นักพัฒนาที่ทำงานคลุกคลีกับฐานข้อมูลส่วนใหญ่จะคุ้นเคยกันเป็นอย่างดี โดยรูปแบบที่เรามักจะใช้งานกันบ่อย คือ รูปแบบที่ 1 รูปแบบ SELECT * FROM TABLE1 WHERE FIELD1 IN (value1,value2,value3,…) โดยผลลัพธ์จะเป็นรายการข้อมูลในตาราง TABLE1 ที่ค่าของข้อมูลใน FIELD1 มีอยู่ใน value1,value2,value3 ,… รูปแบบที่ 2 คล้ายกับรูปแบบที่ 1 นั่นเอง แต่จะเป็นการใช้ subquery แทนที่ (value1,value2,value3,…) โดยมีรูปแบบ SELECT * FROM TABLE1 WHERE FIELD1 IN (SELECT FIELD2 FROM TABLE2) สำหรับผลลัพธ์จะเป็นรายการข้อมูลในตาราง TABLE1 ที่ค่าของข้อมูลใน FIELD1 มีใน FIELD2 ซึ่งเป็นผลลัพธ์จากการ SELECT ข้อมูลจากตาราง TABLE2 ข้อจำกัดในการใช้เงื่อนไข IN (value1,value2,value3,…) สำหรับใน Oracle นั้น list รายการที่อยู่ภายในเครื่องหมายวงเล็บ สามารถมีได้มากสุดไม่เกิน 1000 ค่า ซึ่งบางท่านอาจจะสงสัยว่าในการ SELECT ข้อมูลตามปกตินั้น มีโอกาสน้อยมากที่เราจะพิมพ์ค่าของข้อมูลใน list จนถึง 1000 ค่า แต่ก็มีโอกาสที่จะพบได้คือ เมื่อมีการใช้งานคำสั่งนี้ผ่านโปรแกรมที่เขียนขึ้นนั่นเอง ตัวอย่างเช่น หน้าจอการทำงานของโปรแกรมที่มีลักษณะเป็นชุดของรายการข้อมูลที่ให้ผู้ใช้สามารถเลือกเองได้ จากนั้นรายการที่ถูกเลือกจะถูกส่งไปแปลงเป็นเงื่อนไขในคำสั่ง SELECT อีกครั้ง ทำให้มีโอกาสที่จะเกิดกรณีที่มี list เกิน 1000 ค่า ได้นั่นเอง ในภาพด้านล่างจะเป็นตัวอย่างของเว็บสำหรับสืบค้นหนังสือของห้องสมุด ซึ่งผู้ใช้สามารถเลือกรายการผลลัพธ์จากการสืบค้นและเก็บรวบรวมไว้เพื่อทำการ export ไปใช้งานต่อได้ ซึ่งก็มีโอกาสที่จะเลือกผลลัพธ์ได้เกิน 1000 รายการเกิดขึ้นได้ ถือว่าเป็นจุดหนึ่งที่ผู้พัฒนาควรระมัดระวังในการเขียนโปรแกรมที่มีการใช้เงื่อนไข IN ลักษณะนี้ในคำสั่ง SELECT ข้อควรระวังในการใช้ IN ร่วมกับเงื่อนไขที่เป็น subquery ในที่นี้ขอยกตัวอย่างข้อมูลเพื่อให้เห็นภาพชัดเจน โดยมีข้อมูลจากตารางสองตาราง คือ TABLE01 และ TABLE02 สำหรับ TABLE01 เป็นข้อมูลที่ต้องการ SELECT เพื่อให้ได้ผลลัพธ์ออกมา ส่วนตาราง TABLE02 จะเป็นเงื่อนไขที่จะใช้ใน subquery ข้อมูลในตารางทั้งสองจะเป็นดังนี้ ข้อมูลใน TABLE01 ข้อมูลใน TABLE02 จะยกตัวอย่างกรณีการ SELECT ออกเป็น 2 กรณีดังนี้ ต้องการข้อมูลใน TABLE01 ที่ข้อมูลในฟีลด์ F02 ของตารางนี้มีในฟีลด์ F02 ของ TABLE02 ด้วย คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 IN (SELECT F02 FROM TABLE02); ผลลัพธ์ที่ได้คือ ซึ่งถูกต้อง ต้องการข้อมูลใน TABLE01 ที่ข้อมูลในฟีลด์ F02 ของตารางนี้ไม่มีในฟีลด์ F02 ของ TABLE02 โดยปรับคำสั่งจาก IN เป็น NOT IN คำสั่งที่ใช้คือ SELECT * FROM TABLE01 WHERE F02 NOT IN (SELECT F02

เกร็ดความรู้ประกอบการกู้หรือย้ายฐานข้อมูล SQL Server

July 14, 2015 22:28

Count record แต่ละ tables ใน database SELECT T.name AS [TABLE NAME], I.row_count AS [ROWCOUNT] FROM sys.tables AS T INNER JOIN sys.dm_db_partition_stats AS I ON T.object_id = I.object_id AND I.index_id < 2 ORDER BY I.row_count DESC หา Trigger ทั้งหมดในทุกๆ tables ใน Database SELECT [so].[name] AS [trigger_name], USER_NAME([so].[uid]) AS [trigger_owner], USER_NAME([so2].[uid]) AS [table_schema], OBJECT_NAME([so].[parent_obj]) AS [table_name], OBJECTPROPERTY( [so].[id], ‘ExecIsUpdateTrigger’) AS [isupdate], OBJECTPROPERTY( [so].[id], ‘ExecIsDeleteTrigger’) AS [isdelete], OBJECTPROPERTY( [so].[id], ‘ExecIsInsertTrigger’) AS [isinsert], OBJECTPROPERTY( [so].[id], ‘ExecIsAfterTrigger’) AS [isafter], OBJECTPROPERTY( [so].[id], ‘ExecIsInsteadOfTrigger’) AS [isinsteadof], OBJECTPROPERTY([so].[id], ‘ExecIsTriggerDisabled’) AS [disabled] FROM sysobjects AS [so] INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id WHERE [so].[type] = ‘TR’ ค้นหาข้อความในทุกๆ Stored Procedure ใน Database SELECT DISTINCT obj.name AS Object_Name,obj.type_desc FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id WHERE sm.definition Like ‘%xxx%’ วิธีการ Rebuild Full-text Catalogs of Database Use Management Studio 1. In Object Explorer, expand the server, expand Databases, and then expand the database that contains the full-text catalogs that you want to rebuild. 2. Expand Storage, and then right-click Full Text Catalogs. 3. Select Rebuild All. 4. To the question, Do you want to delete all full-text catalogs and rebuild them?, click OK. 5. In the Rebuild All Full-Text Catalogs dialog box, click Close. rebuild all index in Database use DatabaseName; DECLARE

การกู้ Suspect Database ของ SQL Server

July 14, 2015 21:48

Recovery SQL Server Suspect Database สาเหตุของ Suspect Mode การที่ฐานข้อมูล SQL Server เข้าสู่ Mode Suspect นั้นมีได้จากหลายสาเหตุ ดังนี้ Hardware เกิดความเสียหาย มีการปิด (Shutdown) ฐานข้อมูลที่ไม่เหมาะสม คือปิดโดยที่ยังมีกระบวนการทำงานยังไม่เสร็จสมบูรณ์ หรือมีบาง Transaction ค้างอยู่ เกิดความเสียหายกับ Database Files (*.mdf,*.log) SQL Server ไม่พบ Device ที่เก็บ Files SQL Server ไม่พบ Database Files Database Resource ถูกใช้งานอยู่โดย Operation System ไม่มีพื้นที่มากพอใน Page space ที่มีการเพิ่ม (Insert) ข้อมูลเข้าไป วิธีแก้ปัญหา แต่ต้องอยู่บนพื้นฐานที่ไม่มีอะไรผิดปกติที่เกิดจาก Devices หรือ ตัว Database Files จึงจะ สามารถ Recovery โดยวิธีดังนี้ EXEC sp_resetstatus [YourDatabase]; ALTER DATABASE [YourDatabase] SET EMERGENCY DBCC checkdb([YourDatabase]) ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE [YourDatabase] SET MULTI_USER อธิบาย EXEC sp_resetstatus [YourDatabase]; เป็นคำสั่งทำการปิด suspect mode เมื่อทำแล้วก็ต้อง Stop และ Restart SQL Server ด้วย ALTER DATABASE [YourDatabase] SET EMERGENCY เป็นคำสั่งให้ Database เข้าสู่ READ_ONLY MODE และจำกัดการเข้าถึงให้เข้าได้เฉพาะ SysAdmin Account เท่านั้น DBCC checkdb([YourDatabase]) CheckDB จะตรวจสอบการจัดสรรทรัพยากรสำหรับทุกๆ Object และตรวจสอบความสมบูรณ์ของโครงสร้างฐานข้อมูลทั้งหมด ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE กำหนดให้สามารถเข้าใช้งานฐานข้อมูลได้เพียง User เดียวเท่านั้น DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS) จะมีการทำงานหลายขั้นตอนซึ่งแรกสุดจะเป็นการตรวจสอบทรัพยากรต่างๆและมีการตรวจสอบโครงสร้างของฐานข้อมูลจะมีการ Run คำสั่ง DBCC CheckAlloc, DBCC CheckTable, DBCC CheckCatalog ทั้งฐานข้อมูล มีการตรวจสอบข้อมูลของแต่ละ Indexed View ตรวจสอบ Link-Level Consistency ระหว่าง table metadata และ file system directories ตรวจสอบ Service Broker Data ในฐานข้อมูลทั้งหมด ส่วน Option REPAIR_ALLOW_DATA_LOSS นั้นจะมีการพยายามซ้อมแซ่มฐานข้อมูลในส่วนที่เสียไปตามรายการที่ได้จากการ CheckDB และเป็นการอนุญาตให้ข้อมูลสามารถศูนย์หายได้บาง ALTERDATABASE [YourDatabase] SET MULTI_USER เป็นการ set database ให้กลับมาใช้งานตามปกติ หลังจากขั้นตอนเหล่านี้แล้ว ผมจะทำการ Shutdown Server แล้วเปิดขึ้นมาใหม่แล้วลองใช้คำสั่ง DBCC CheckDB() ตรวจสอบอีกครั้งเพื่อความแน่ใจ

T-SQL นับจำนวน record ที่เกิดจากการสั่ง execute Dynamic Query

November 19, 2014 09:51

ตัวอย่างไม่ต้องอธิบายมาก DECLARE @str_SQL NVARCHAR(100), @rowcount INT –กำหนดตัวแปร SET @str_SQL = ‘select * from sysobjects’ — set ค่าให้ตัวแปร EXEC sp_executesql @str_SQL — สั่ง execute dynamic query SELECT @rowcount = @@ROWCOUNT — get ค่าที่จำนวน record ที่เกิดผลกระทบจากการ execute dynamic query PRINT ‘@rowcount = ‘ + CAST(@rowcount AS VARCHAR(4)) — แสดงผลออกหน้าจอ