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

SQL động là gì?

Năng động SQL SQL là một phương pháp lập trình để tạo và thực thi 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 đa năng và linh hoạt, trong đó các câu lệnh SQL được tạo và thực thi trong thời gian chạy dựa trên yêu cầu, ví dụ như khi tên bảng, danh sách cột hoặc điều kiện WHERE chưa được biết cho đến khi chương trình chạy.

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

PL/SQL cung cấp hai cách để viết câu lệnh SQL động:

1. NDS – SQL động gốc (các câu lệnh EXECUTE IMMEDIATE và OPEN-FOR)
2. DBMS_SQL (một gói hàng được cung cấp)

Nguyên tắc chung rất đơn giản: nếu số lượng và kiểu dữ liệu của các biến đầu vào và đầu ra được biết tại thời điểm biên dịch, hãy sử dụng Native Dynamic SQL vì nó nhanh hơn và cần ít mã hơn. Khi thông tin đó chỉ được biết tại thời điểm chạy, hãy sử dụng gói DBMS_SQL.

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

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

cú pháp

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

• dynamic_sql_string: Một biểu thức chuỗi (VARCHAR2 hoặc CHAR, không phải NVARCHAR2/NCHAR) chứa một câu lệnh SQL hoặc khối PL/SQL duy nhất.
• Mệnh đề INTO: Tùy chọn. Chỉ được sử dụng khi câu lệnh SQL động là câu lệnh SELECT một hàng; nó thu thập các giá trị trả về vào các biến hoặc một bản ghi. Mỗi cột được chọn cần một biến có kiểu dữ liệu tương thích.
• Mệnh đề USING: Tùy chọn. Các biến ràng buộc được cung cấp. Chế độ mặc định là IN; OUT và IN OUT được sử dụng để nhận giá trị trả về.
• Mệnh đề TRỞ LẠI: Được sử dụng với các câu lệnh DML có mệnh đề RETURNING, để thu thập các giá trị của hàng bị ảnh hưởng vào các đối số ràng buộc.

Ví dụ 1: Trong ví dụ này, chúng ta truy xuất dữ liệu từ bảng emp cho emp_no '1001' bằng cách sử dụng câu lệnh NDS với một biến liên kết.

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

Đầu ra

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

Code Giải thích:

• Dòng 2-6: Khai báo các biến.
• Dòng 8: Xây dựng câu lệnh SQL tại thời điểm thực thi. Câu lệnh SQL chứa biến ràng buộc ':empno' trong điều kiện WHERE.
• Dòng 9-11: Thực thi câu lệnh SQL được đóng khung bằng EXECUTE IMMEDIATE. Các biến trong mệnh đề INTO chứa các giá trị được truy xuất, và mệnh đề USING cung cấp giá trị cho biến liên kết :empno.
• Dòng 12-15: Hiển thị các giá trị đã được truy xuất.

Sử dụng SQL động cho DDL

PL/SQL tĩnh không thể chạy trực tiếp các câu lệnh DDL như CREATE, ALTER hoặc DROP. EXECUTE IMMEDIATE giải quyết vấn đề này bằng cách xây dựng câu lệnh dưới dạng chuỗi, điều này cũng rất tiện lợi khi tên đối tượng được cung cấp trong quá trình chạy:

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

Tên đối tượng (bảng, cột, lược đồ) không thể được truyền dưới dạng biến ràng buộc, vì vậy chúng phải được nối vào chuỗi. Luôn luôn xác thực đầu vào như vậy, ví dụ bằng DBMS_ASSERT.SIMPLE_SQL_NAME, để tránh tấn công SQL injection.

DBMS_SQL cho SQL động

PL/SQL cung cấp gói DBMS_SQL để làm việc với SQL động khi cấu trúc của câu lệnh không được biết cho đến khi chạy. Quá trình tạo và thực thi SQL động bao gồm các bước sau:

• MỞ CON TRỎ: Câu lệnh SQL động được thực thi như sau: con trỏĐể thực thi câu lệnh SQL, trước tiên chúng ta phải mở con trỏ.
• PHÂN TÍCH SQL: Phân tích cú pháp SQL động. Thao tác này kiểm tra cú pháp và đảm bảo truy vấn luôn sẵn sàng để thực thi.
• Giá trị biến ràng buộc: Gán giá trị cho các biến liên kết, nếu có.
• XÁC ĐỊNH CỘT: Xác định từng cột bằng cách sử dụng vị trí tương đối của nó trong câu lệnh SELECT.
• HÀNH HÌNH: Thực thi truy vấn đã được phân tích cú pháp.
• LẤY GIÁ TRỊ: Lấy các giá trị đã thực thi.
• ĐÓNG CON TRỎ: Sau khi lấy được kết quả, hãy đóng con trỏ.

Ví dụ 1: Trong ví dụ này, chúng ta truy xuất dữ liệu từ bảng emp cho emp_no '1001' bằng câu lệnh DBMS_SQL. Khối EXCEPTION sẽ đóng con trỏ ngay cả khi xảy ra lỗi.

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

Đầu ra

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

Code Giải thích:

• Dòng 1-8: Khai báo biến.
• Dòng 10: Xây dựng câu lệnh SQL.
• Dòng 11: Mở con trỏ bằng cách sử dụng DBMS_SQL.OPEN_CURSOR, phương thức này trả về ID của con trỏ đã mở.
• Dòng 12: Sau khi con trỏ được mở, câu lệnh SQL sẽ được phân tích cú pháp.
• Dòng 13: Giá trị liên kết '1001' được gán thay cho ':empno'.
• Dòng 14-17: Xác định các cột theo vị trí tương đối của chúng: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Dòng 18: Thực thi truy vấn bằng DBMS_SQL.EXECUTE, hàm này trả về số lượng bản ghi đã được xử lý.
• Dòng 19-32: Việc truy xuất các bản ghi được thực hiện trong một vòng lặp. Hàm FETCH_ROWS trả về 0 khi không còn bản ghi nào, lúc này vòng lặp sẽ kết thúc.
• Khối ngoại lệ: Đảm bảo con trỏ được đóng lại để các con trỏ đang mở không bị rò rỉ nếu xảy ra lỗi.

NDS so với DBMS_SQL: Khi nào nên sử dụng cái nào?

Cả hai phương pháp đều chạy SQL trong quá trình thực thi, nhưng chúng phù hợp với các tình huống khác nhau:

• Sử dụng SQL động gốc (EXECUTE IMMEDIATE / OPEN-FOR) Khi số lượng và kiểu dữ liệu của đầu vào và đầu ra được biết tại thời điểm biên dịch, phương pháp này nhanh hơn, dễ đọc hơn và cần ít mã hơn.
• Sử dụng DBMS_SQL Khi cấu trúc chưa được biết cho đến khi chạy, ví dụ như một truy vấn có số lượng cột được chọn hoặc biến ràng buộc thay đổi, được gọi là SQL động phương pháp 4, hoặc một câu lệnh quá lớn để vừa với một biến VARCHAR2 32K duy nhất.

Câu Hỏi Thường Gặp