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

โดย: ฟิโอน่า บราวน์ ฟิโอน่า บราวน์
Hours วันที่อัพเดท

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

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

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

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

  1. NDS – Native Dynamic SQL
  2. DBMS_SQL

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

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

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

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • ไวยากรณ์ข้างต้นแสดงคำสั่ง EXECUTE IMMEDIATE
  • Clause INTO เป็นทางเลือก และใช้เฉพาะในกรณีที่ SQL ไดนามิกมีคำสั่ง select ที่ดึงค่าออกมาเท่านั้น ประเภทของตัวแปรควรตรงกับประเภทตัวแปรของคำสั่ง select
  • ส่วนคำสั่ง USING เป็นทางเลือก และใช้เฉพาะในกรณีที่ SQL ไดนามิกมีตัวแปรการโยงใดๆ

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

NDS - ดำเนินการทันที

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

เอาท์พุต

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

คำอธิบายรหัส:

  • รหัสบรรทัด 2-6: การประกาศตัวแปร
  • รหัสบรรทัดที่ 8: การเฟรม SQL ณ รันไทม์ SQL มีตัวแปรการเชื่อมโยงโดยที่เงื่อนไข ':empno'
  • รหัสบรรทัดที่ 9: การดำเนินการข้อความ SQL ที่มีเฟรม (ซึ่งทำในโค้ดบรรทัด 8) โดยใช้คำสั่ง NDS 'EXECUTE IMMEDIATE'
  • ตัวแปรในส่วนคำสั่ง 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) ใช้เพื่อเก็บค่าที่ดึงมาจากแบบสอบถาม SQL (emp_name, emp_no, เงินเดือน, ผู้จัดการ)
  • ส่วนคำสั่ง 'USING' ให้ค่าแก่ตัวแปรการผูกในการสืบค้น SQL (:emp_no)
  • รหัสบรรทัด 10-13: การแสดงค่าที่ดึงมา

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

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

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

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

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

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

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

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

เอาท์พุต

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

คำอธิบายรหัส:

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

สรุป

ในส่วนนี้ เราได้กล่าวถึง Dynamic SQL และวิธีการดำเนินการ DYNAMIC SQL นอกจากนี้เรายังได้เห็นขั้นตอนต่างๆ ในการดำเนินการ SQL แบบไดนามิกในทั้งสองวิธี นอกจากนี้เรายังได้เห็นตัวอย่างที่มีการจัดการสถานการณ์เดียวกันทั้ง NDS และ DBMS_SQL เพื่อดำเนินการในขณะรันไทม์

ที่คุณอาจชอบ: