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

Mục lục:

Anonim

SQL động là gì?

SQL động là một phương pháp lập trình để tạo và chạy các câu lệnh tại thời điểm 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 tại thời điểm chạy dựa trên yêu cầu.

Trong hướng dẫn này, bạn sẽ học-

  • Các cách viết SQL động
  • NDS (SQL động bản địa) - Thực thi ngay lập tức
  • DBMS_SQL cho SQL động

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 bản địa) - 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 tại thời điểm chạy. Nhưng để sử dụng theo cách này, loại dữ liệu và số lượng biến được sử dụng tại một thời điểm chạy cần phải được biết trước. Nó cũng cho 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()[INTO][USING ]
  • Cú pháp trên hiển thị lệnh EXECUTE IMMEDIATE.
  • Mệnh đề INTO là tùy chọn và chỉ được sử dụng nếu SQL động chứa câu lệnh select tìm nạp các giá trị. Kiểu biến phải khớp với kiểu biến của câu lệnh select.
  • 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.

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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 : XXXEmployee Number: 1001Salary: 15000Manager ED: 1000

Giải thích mã:

  • Dòng mã 2-6 : Khai báo biến.
  • Dòng mã 8 : Tạo khung cho SQL tại thời điểm 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ó khung (được thực hiện trong 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, lương, người quản lý)
  • 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ị đã 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 quá trình sau.

  • OPEN CURSOR : SQL động sẽ thực thi theo cách giống như con trỏ. Vì vậy, để thực hiện câu lệnh SQL, chúng ta phải mở con trỏ.
  • PARSE 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 VARIABLE : Bước tiếp theo là gán giá trị cho các biến ràng buộc nếu có.
  • DEFINE COLUMN : Bước tiếp theo là xác định cột bằng cách sử dụng các vị trí tương đối của chúng trong câu lệnh select.
  • THỰC HIỆN : Bước tiếp theo là thực hiện truy vấn đã được phân tích cú pháp.
  • TÌM KIẾM GIÁ TRỊ : Bước tiếp theo là tìm nạp các giá trị đã thực thi.
  • ĐÓNG CURSOR : Sau khi kết quả được tìm nạp, 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.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:XXXEmployee Number:1001Salary:15000Manager ID:1000

Giải thích mã:

  • Dòng mã 1-9 : Khai báo 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 ràng buộc '1001' đượ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) 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 thi truy vấn bằng DBMS_SQL.EXECUTE. Nó trả về số lượng bản ghi được xử lý.
  • Dòng mã 19-33 : Tìm nạp các bản ghi bằng vòng lặp và hiển thị giống nhau.
  • 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 để tìm nạp tất cả các hàng. Nếu nó không thể tìm nạp các hàng, nó sẽ trả về 0, do đó thoát khỏi vòng lặp.

Tóm lược

Trong phần này, chúng ta đã thảo luận về SQL động và các cách thực thi DYNAMIC SQL. 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 tại thời điểm chạy.