Oracle การสอน PL/SQL Dynamic SQL: ดำเนินการทันที & DBMS_SQL

ไดนามิก SQL คืออะไร?

พลวัต SQL SQL เป็นวิธีการเขียนโปรแกรมสำหรับการสร้างและเรียกใช้คำสั่งขณะโปรแกรมทำงาน โดยส่วนใหญ่ใช้ในการเขียนโปรแกรมอเนกประสงค์และยืดหยุ่น ซึ่งคำสั่ง SQL จะถูกสร้างและเรียกใช้ขณะโปรแกรมทำงานตามความต้องการ เช่น ในกรณีที่ไม่ทราบชื่อตาราง รายชื่อคอลัมน์ หรือเงื่อนไข WHERE จนกว่าโปรแกรมจะเริ่มทำงาน

วิธีการเขียน SQL แบบไดนามิก

PL/SQL มีวิธีการเขียน SQL แบบไดนามิกอยู่สองวิธี:

1. NDS – Native Dynamic SQL (คำสั่ง EXECUTE IMMEDIATE และ OPEN-FOR)
2. DBMS_SQL (ชุดอุปกรณ์ที่จัดส่ง)

หลักการทั่วไปนั้นง่ายมาก: หากทราบจำนวนและชนิดข้อมูลของตัวแปรอินพุตและเอาต์พุตในระหว่างการคอมไพล์ ให้ใช้ Native Dynamic SQL เพราะเร็วกว่าและใช้โค้ดน้อยกว่า แต่หากทราบข้อมูลนั้นเฉพาะในระหว่างการทำงาน ให้ใช้แพ็กเกจ DBMS_SQL

NDS (Native Dynamic SQL) – ดำเนินการทันที

Native Dynamic SQL เป็นวิธีที่ง่ายในการเขียน SQL แบบไดนามิก โดยใช้คำสั่ง EXECUTE IMMEDIATE เพื่อสร้างและเรียกใช้ SQL ในขณะรันไทม์ ในการใช้วิธีนี้ จำเป็นต้องทราบชนิดข้อมูลและจำนวนตัวแปรที่ใช้ในขณะรันไทม์ล่วงหน้า นอกจากนี้ยังให้ประสิทธิภาพที่ดีกว่าและมีความซับซ้อนน้อยกว่าเมื่อเทียบกับ DBMS_SQL

วากยสัมพันธ์

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• สตริง SQL แบบไดนามิก: นิพจน์สตริง (VARCHAR2 หรือ CHAR ไม่ใช่ NVARCHAR2/NCHAR) ที่บรรจุคำสั่ง SQL หรือบล็อก PL/SQL เพียงคำสั่งเดียว
• เงื่อนไข INTO: ตัวเลือกเสริม ใช้เฉพาะเมื่อ SQL แบบไดนามิกเป็นการเลือกข้อมูลเพียงแถวเดียว โดยจะเก็บค่าที่ส่งคืนมาไว้ในตัวแปรหรือเรคอร์ด แต่ละคอลัมน์ที่เลือกจะต้องมีตัวแปรที่เข้ากันได้กับประเภทข้อมูล
• เงื่อนไขการใช้งาน: ตัวเลือกเสริม ใช้สำหรับกำหนดตัวแปรผูกค่า โหมดเริ่มต้นคือ IN; ส่วน OUT และ IN OUT ใช้สำหรับรับค่ากลับมา
• เงื่อนไขการกลับเข้าสู่ประโยค: ใช้ร่วมกับคำสั่ง DML ที่มีเงื่อนไข RETURNING เพื่อดึงค่าของแถวที่ได้รับผลกระทบมาใส่ในอาร์กิวเมนต์การผูกค่า

1 ตัวอย่าง: ในตัวอย่างนี้ เราดึงข้อมูลจากตาราง emp สำหรับ emp_no '1001' โดยใช้คำสั่ง NDS พร้อมตัวแปรผูกค่า

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

เอาท์พุต

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code คำอธิบาย:

• บรรทัดที่ 2-6: การประกาศตัวแปร
• 8 แนว: การกำหนดโครงสร้าง SQL ในขณะรันไทม์ SQL นี้มีตัวแปรผูกค่า ':empno' อยู่ในเงื่อนไข WHERE
• บรรทัดที่ 9-11: ดำเนินการคำสั่ง SQL ที่กำหนดไว้ด้วย EXECUTE IMMEDIATE ตัวแปรในส่วน INTO จะเก็บค่าที่ดึงมาได้ และส่วน USING จะส่งค่าให้กับตัวแปรผูกมัด :empno
• บรรทัดที่ 12-15: แสดงค่าที่ดึงมาได้

การใช้ Dynamic SQL สำหรับ DDL

PL/SQL แบบคงที่ไม่สามารถรันคำสั่ง DDL เช่น CREATE, ALTER หรือ DROP ได้โดยตรง EXECUTE IMMEDIATE แก้ปัญหานี้โดยการสร้างคำสั่งเป็นสตริง ซึ่งสะดวกเมื่อมีการระบุชื่ออ็อบเจ็กต์ในระหว่างการรันไทม์ด้วย

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

ชื่ออ็อบเจ็กต์ (ตาราง คอลัมน์ สคีมา) ไม่สามารถส่งผ่านเป็นตัวแปรผูกมัดได้ ดังนั้นจึงต้องนำมาต่อกันเป็นสตริง ตรวจสอบความถูกต้องของข้อมูลที่ป้อนเข้ามาเสมอ เช่น ด้วย DBMS_ASSERT.SIMPLE_SQL_NAME เพื่อป้องกันการโจมตีแบบ SQL injection

DBMS_SQL สำหรับไดนามิก SQL

PL/SQL มีแพ็กเกจ DBMS_SQL สำหรับทำงานกับ SQL แบบไดนามิก เมื่อโครงสร้างของคำสั่งไม่เป็นที่รู้จักจนกว่าจะถึงเวลาทำงาน กระบวนการสร้างและเรียกใช้ SQL แบบไดนามิกประกอบด้วยขั้นตอนต่อไปนี้:

• เปิดเคอร์เซอร์: คำสั่ง SQL แบบไดนามิกทำงานเหมือนกับ... เคอร์เซอร์ในการเรียกใช้คำสั่ง SQL เราต้องเปิดเคอร์เซอร์ก่อน
• วิเคราะห์คำสั่ง SQL: วิเคราะห์คำสั่ง SQL แบบไดนามิก ขั้นตอนนี้จะตรวจสอบไวยากรณ์และเตรียมคำสั่งให้พร้อมสำหรับการประมวลผล
• ค่าที่ผูกกับตัวแปร: กำหนดค่าให้กับตัวแปรผูกมัด (ถ้ามี)
• กำหนดคอลัมน์: กำหนดนิยามของแต่ละคอลัมน์โดยใช้ตำแหน่งสัมพัทธ์ในคำสั่ง SELECT
• ดำเนินการ: ดำเนินการคำสั่งค้นหาที่แยกวิเคราะห์แล้ว
• ดึงค่า: ดึงค่าที่ดำเนินการแล้ว
• ปิดเคอร์เซอร์: เมื่อดึงผลลัพธ์เสร็จแล้ว ให้ปิดเคอร์เซอร์

1 ตัวอย่าง: ในตัวอย่างนี้ เราดึงข้อมูลจากตาราง emp สำหรับ emp_no '1001' โดยใช้คำสั่ง DBMS_SQL บล็อก EXCEPTION จะปิดเคอร์เซอร์แม้ว่าจะเกิดข้อผิดพลาดก็ตาม

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

เอาท์พุต

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code คำอธิบาย:

• บรรทัดที่ 1-8: การประกาศตัวแปร
• 10 แนว: การจัดโครงสร้างคำสั่ง SQL
• 11 แนว: การเปิดเคอร์เซอร์โดยใช้ DBMS_SQL.OPEN_CURSOR จะส่งคืน ID ของเคอร์เซอร์ที่เปิดอยู่
• 12 แนว: หลังจากเปิดเคอร์เซอร์แล้ว ระบบจะทำการวิเคราะห์คำสั่ง SQL
• 13 แนว: ค่าผูกมัด '1001' ถูกกำหนดแทนที่ ':empno'
• บรรทัดที่ 14-17: กำหนดคอลัมน์ตามตำแหน่งสัมพัทธ์: (1) ชื่อพนักงาน, (2) หมายเลขพนักงาน, (3) เงินเดือน, (4) ผู้จัดการ
• 18 แนว: เรียกใช้คำสั่ง SQL ด้วย DBMS_SQL.EXECUTE ซึ่งจะส่งคืนจำนวนระเบียนที่ประมวลผลแล้ว
• บรรทัดที่ 19-32: ดึงข้อมูลในลูป ฟังก์ชัน FETCH_ROWS จะส่งค่า 0 กลับมาเมื่อไม่มีแถวเหลืออยู่ ซึ่งจะทำให้ลูปหยุดทำงาน
• บล็อกข้อยกเว้น: ช่วยให้มั่นใจได้ว่าเคอร์เซอร์ถูกปิด เพื่อป้องกันไม่ให้เคอร์เซอร์ที่เปิดอยู่รั่วไหลหากเกิดข้อผิดพลาดขึ้น

NDS กับ DBMS_SQL: ควรใช้ตัวไหนในสถานการณ์ใด

ทั้งสองวิธีใช้คำสั่ง SQL ในระหว่างการทำงาน แต่เหมาะกับสถานการณ์ที่แตกต่างกัน:

• ใช้ Native Dynamic SQL (EXECUTE IMMEDIATE / OPEN-FOR) เมื่อทราบจำนวนและชนิดข้อมูลของอินพุตและเอาต์พุตในระหว่างการคอมไพล์ จะทำให้เร็วขึ้น อ่านง่ายขึ้น และใช้โค้ดน้อยลง
• ใช้ DBMS_SQL เมื่อโครงสร้างไม่เป็นที่รู้จักจนกว่าจะถึงเวลาทำงาน ตัวอย่างเช่น คำสั่ง SQL ที่จำนวนคอลัมน์ที่เลือกหรือตัวแปรผูกค่าเปลี่ยนแปลงไป ซึ่งเรียกว่า SQL แบบไดนามิกวิธีที่ 4 หรือคำสั่งที่มีขนาดใหญ่เกินกว่าจะใส่ลงในตัวแปร VARCHAR2 ขนาด 32K ตัวเดียวได้

คำถามที่พบบ่อย