Oracle PL/SQL Tips : How to generate SQL by SQL

Oracle PL/SQL Tips : How to generate SQL by SQL

สวัสดีครับ วันนี้ผมมาแนะนำวิธีสร้าง SQL Statement จำนวนมากด้วย SQL Statement เดียวครับ บางท่านอาจยังนึกภาพไม่ออกว่าผมหมายถึงอะไร ลองดูตัวอย่างง่ายๆก่อนครับ

Case 1 : สมมติว่าคุณเป็น DBA และพบว่า Tablespace จะเต็ม เนื่องจากมี objects tables ที่เป็น Temoporay มากมาย คุณต้องการจะลบทิ้ง แต่ปรากฎว่าจำนวนตารางที่จะลบมีถึง 1,000 objects ทั้งเล็กและใหญ่ปนกัน
แต่ทว่ามีสิ่งหนึ่งที่เหมือนกันคือ objects name ขึ้นต้นด้วยคำว่า “TMP” เหมือนกัน

จากโจทย์ดูแล้วเป็นงานง่ายๆเลยครับ DBA จะเรียกใช้คำสั่ง SQL> “DROP TABLE ;” แต่ปัญหาอยู่ที่ว่าจำนวนที่จะลบมีถึง 1,000 objects ให้เราพิมพ์ทีละ object เมื่อไรจะเสร็จ

อ่ะ…แต่ทางออกมีให้เลือกหลายทางเช่น

1. query ชื่อ objects ที่ต้องการลบแล้วใช้ editor เช่น vi , notepad, edit plus, etc สร้างเป็นสคริปต์ในการลบก็ได้หรือ
2. ถ้าใครเขียนโปรแกรมเป็นก็อาจเขียนโปรแกรมเข้าไปช่วย โดยการ query ชื่อ objects ที่จะลบแล้วสั่งลบเลย เช่น อาจใช้shell script, java, php, etc.
3. อื่นๆ แล้วแต่จะหาทางได้

แต่…วันนี้ผมมีอีก 1 ทางเลือกให้ใช้ สะดวก รวดเร็ว ครับ นั่นคือการใช้ SQL Statement สร้าง SQL
เราต้องการใช้จากโจทย์นี้ ผมจะใช้ SQL ดังนี้

SQL> select ‘DROP TABLE ‘||object_name||’ PURGE; ‘ from dba_objects where object_type=’TABLE’ and object_name like ‘TMP%’ ;
?
DROP TABLE TMP_VICE PURGE ;
DROP TABLE TMP_VEIL PURGE ;
DROP TABLE TMP_MAN PURGE ;
DROP TABLE TMP_NC PURGE ;
DROP TABLE TMP_AG PURGE ;
DROP TABLE TMP_I PURGE ;
DROP TABLE TMP_P PURGE ;
DROP TABLE TMP_H PURGE ;
DROP TABLE TMP_RI PURGE ;
DROP TABLE TMP_APP PURGE ;
DROP TABLE TMP_PROCESS PURGE ;
?



เมื่อเราได้ SQL สำหรับการ DROP TABLE มาแล้ว เราก็ copy มารัน เท่านี้เราก็สามารถลบตารางที่ไม่ได้ใช้งาน 1,000 ตารางได้อย่างรวดเร็ว (จากโจทย์ 1,000 objects จะทำให้ได้ผลลัพธ์เกิน 1 หน้าจอโปรแกรม เราใช้วิธีการ spool ผลลัพธ์ออกมาเป็น text files ก่อน แล้วค่อย copy ไปรันครับ)

จากตัวอย่างอธิบายหลักการได้ดังนี้

SQL statement สามารถเรียกคำสั่งเพื่อแสดงข้อความทั่วไปได้ โดยข้อความที่จะให้แสดงจะอยู่ในเครื่องหมาย ‘..’ เช่น

SQL> select ‘TEST DISPLAY SENTENCE’ from dual;’TESTDISPLAYSENTENCE’
———————
TEST DISPLAY SENTENCE



นอกจากนี้เรายังสามารถใช้เครื่องหมาย || เชื่อมข้อความกับ column name ในฐานข้อมูลที่เราใช้ query ขึ้นมาได้ เช่น
object_name, synonym_name, privilege, < other column name>,.. ได้

ดังนั้น จากโจทย์เราต้องการสร้าง SQL Script ที่มีชุดคำสั่งคือ “DROP TABLE <table_name> PURGE; ” จะเห็นว่ามีเฉพาะ <table_name> ที่เราต้องเรียกจากฐานข้อมูล ที่เหลือเป็นข้อความธรรมดา ดังนั้นจึงได้ SQL statement เป็น

?SQL> select ‘DROP TABLE ‘||object_name||’ PURGE; ‘ from ?
?



โดย object_name เป็น ชื่อ column จากตาราง DBA_OBJECTS ที่เราต้องการใช้ค้นหาชื่อตารางที่จะลบ สมมติว่าผมเปลี่ยน
จากตาราง DBA_OBJECTS เป็น DBA_SEGMENTS เนื่องจากเราต้องการเพียงชื่อตารางที่ขึ้นต้นด้วยคำว่า “TMP” ดังนั้น
SQL Statement ใหม่จะเป็น

SQL> select ‘DROP TABLE ‘||segment_name||’ PURGE; ‘ from dba_segments
where segment_type=’TABLE’ and segment_name like ‘TMP%’ ;



เพียงเท่านี้เราก็สามารถประยุกต์ใช้กับงาน DBA ทั่วไปเช่น
1. grant privilege to user2 ให้มีสิทธิเหมือนกับ user1 ทุกประการ
2. สร้าง synonym จากตารางทุกตัวที่อยู่ใน user1 -> user2 ทั้งหมด
3. และเรายังสามารถใช้สร้าง SQL ที่ซับซ้อนขึ้นได้ อยู่ที่เราจะเปลี่ยน FROM … , WHERE … อะไรเท่านั้นเองครับ

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *