Oracle Hướng dẫn SQL động PL/SQL: Thực thi ngay lập tức & DBMS_SQL

Bởi: Fiona Nâu Fiona Nâu
Hours cập nhật

SQL động là gì?

Năng động SQL là một phương pháp lập trình để tạo và chạy các câu lệnh trong thời gian chạy. Nó chủ yếu được sử dụng để viết các chương trình có mục đích chung và linh hoạt trong đó các câu lệnh SQL sẽ được tạo và thực thi trong thời gian chạy dựa trên yêu cầu.

Các cách viết SQL động

PL/SQL cung cấp hai cách để viết SQL động

  1. NDS – SQL động gốc
  2. DBMS_SQL

NDS (SQL động gốc) – Thực thi ngay lập tức

Native Dynamic SQL là cách dễ hơn để viết SQL động. Nó sử dụng lệnh 'EXECUTE IMMEDIATE' để tạo và thực thi SQL tại thời điểm chạy. Nhưng để sử dụng theo cách này, kiểu dữ liệu và số lượng biến được sử dụng tại thời điểm chạy cần phải được biết trước. Nó cũng mang lại hiệu suất tốt hơn và ít phức tạp hơn khi so sánh với DBMS_SQL.

cú pháp 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Cú pháp trên hiển thị lệnh THỰC HIỆN NGAY LẬP TỨC.
  • Mệnh đề INTO là tùy chọn và chỉ được sử dụng nếu SQL động chứa câu lệnh chọn tìm nạp các giá trị. Loại biến phải khớp với loại biến của câu lệnh chọn.
  • Mệnh đề USING là tùy chọn và chỉ được sử dụng nếu SQL động chứa bất kỳ biến liên kết nào.

Ví dụ 1: Trong ví dụ này, chúng ta sẽ tìm nạp dữ liệu từ bảng emp cho emp_no '1001' bằng cách sử dụng câu lệnh NDS.

NDS - Thực thi ngay lập tức

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;
/

Đầu ra

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

Giải thích mã:

  • Dòng mã 2-6: Khai báo các biến.
  • Dòng mã 8: Đóng khung SQL vào thời gian chạy. SQL chứa biến liên kết trong điều kiện ': empno'.
  • Dòng mã 9: Thực thi văn bản SQL được đóng khung (được thực hiện ở dòng mã 8) bằng lệnh NDS 'THỰC HIỆN NGAY LẬP TỨC'
  • Các biến trong mệnh đề 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) được sử dụng để giữ các giá trị được tìm nạp từ truy vấn SQL (emp_name, emp_no, money, manager)
  • Mệnh đề 'USING' cung cấp các giá trị cho biến liên kết trong truy vấn SQL (:emp_no).
  • Dòng mã 10-13: Hiển thị các giá trị được tìm nạp.

DBMS_SQL cho SQL động

PL/SQL cung cấp gói DBMS_SQL cho phép bạn làm việc với SQL động. Quá trình tạo và thực thi SQL động bao gồm các quá trình sau.

  • MỞ CURSOR: SQL động sẽ thực thi theo cách tương tự như một con trỏ. Vì vậy để thực thi câu lệnh SQL chúng ta phải mở con trỏ.
  • Phân tích cú pháp SQL: Bước tiếp theo là phân tích cú pháp SQL động. Quá trình này sẽ chỉ kiểm tra cú pháp và giữ cho truy vấn sẵn sàng thực thi.
  • Giá trị BIND BIND: Bước tiếp theo là gán giá trị cho các biến liên kết nếu có.
  • XÁC ĐỊNH CỘT: Bước tiếp theo là xác định cột bằng cách sử dụng vị trí tương đối của chúng trong câu lệnh chọn.
  • HÀNH HÌNH: Bước tiếp theo là thực hiện truy vấn được phân tích cú pháp.
  • LẤY GIÁ TRỊ: Bước tiếp theo là tìm nạp các giá trị được thực thi.
  • ĐÓNG CURSOR: Sau khi tìm nạp kết quả, con trỏ sẽ được đóng lại.

Ví dụ 1: Trong ví dụ này, chúng ta sẽ tìm nạp dữ liệu từ bảng emp cho emp_no '1001' bằng cách sử dụng câu lệnh DBMS_SQL.

DBMS_SQL cho SQL động

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 cho SQL động

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:
/

Đầu ra

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

Giải thích mã:

  • Dòng mã 1-9: Sự định nghĩa biến.
  • Dòng mã 10: Đóng khung câu lệnh SQL.
  • Dòng mã 11: Mở con trỏ bằng DBMS_SQL.OPEN_CURSOR. Nó sẽ trả về id con trỏ được mở.
  • Dòng mã 12: Sau khi con trỏ được mở, SQL được phân tích cú pháp.
  • Dòng mã 13: Biến liên kết '1001' đang được gán cho id con trỏ thay vì ': empno'.
  • Dòng mã 14-17: Xác định tên cột dựa trên vị trí tương đối của chúng trong câu lệnh SQL. Trong trường hợp của chúng tôi, vị trí tương đối là (1) emp_name, (2) emp_no (3) lương (4) người quản lý. Vì vậy, dựa trên vị trí này, chúng tôi đang xác định biến mục tiêu.
  • Dòng mã 18: Thực hiện truy vấn bằng DBMS_SQL.EXECUTE. Nó trả về số lượng hồ sơ được xử lý.
  • Dòng mã 19-33: Tìm nạp bản ghi bằng vòng lặp và hiển thị tương tự.
  • Dòng mã 20: DBMS_SQL.FETCH_ROWS sẽ tìm nạp một bản ghi từ các hàng được xử lý. Nó có thể được gọi nhiều lần để lấy tất cả các hàng. Nếu không thể tìm nạp hàng, nó sẽ trả về 0 và thoát khỏi vòng lặp.

Tổng kết

Trong phần này, chúng ta đã thảo luận về SQL động và các cách thực thi SQL NĂNG ĐỘNG. Chúng ta cũng đã thấy các bước khác nhau trong việc thực thi SQL động theo cả hai cách. Chúng ta cũng đã thấy các ví dụ trong đó cùng một kịch bản được xử lý theo cả hai cách NDS và DBMS_SQL để thực hiện việc thực thi trong thời gian chạy.

Tóm tắt bài viết này với: