Oracle数据库中的动态SQL(Dynamic SQL)

Oracle数据库中的动态SQL是一种在运行时构建和执行SQL语句的技术。与传统的静态SQL（在编写程序时SQL语句就已经确定）不同，动态SQL允许开发者在程序执行过程中根据不同的条件或用户输入来构建SQL语句。这使得动态SQL在处理复杂查询、存储过程中灵活处理未知或变化的数据结构时非常有用。

1、动态SQL的类型

Oracle中动态SQL主要有两种形式：

本地动态SQL（Native Dynamic SQL）：
- 使用EXECUTE IMMEDIATE语句来执行单个的SQL语句或PL/SQL匿名块。
- 主要用于执行不需要返回结果的SQL语句，如INSERT、UPDATE、DELETE、DDL（数据定义语言）语句等。
- 也可以使用INTO子句将查询结果存储在PL/SQL变量中。
DBMS_SQL包：
- 提供了一套用于执行动态SQL语句的接口，允许执行更复杂的动态SQL，包括查询和DML操作。
- 可以处理游标和绑定变量，使得处理查询结果集和参数化查询成为可能。
- 使用步骤包括打开游标、绑定变量、执行SQL语句、处理结果集（如果有的话）、关闭游标。

2、使用EXECUTE IMMEDIATE

EXECUTE IMMEDIATE语句非常适合执行简单的动态SQL语句，如：

2.1、从动态PL/SQL块调用子程序

Invoking Subprogram from Dynamic PL/SQL Block

In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram created at schema level.

sql">create or replace procedure sp_insert_dept
( deptid in out number,
  dname in varchar2,
  mgrid in number,
  locid in number
) authid definer as
begin
  deptid := departments_seq.nextval;
  insert into departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID, LOCATION_ID) values(deptid,dname,mgrid,locid);
  commit;
end;
/

--  定义输入参数并执行
DECLARE
  plsql_block VARCHAR2(500);
  new_deptid  NUMBER(4);
  new_dname   VARCHAR2(30) := 'super';
  new_mgrid   NUMBER(6)    := 200;
  new_locid   NUMBER(4)    := 1700;
BEGIN
 -- Dynamic PL/SQL block invokes subprogram:
  plsql_block := 'BEGIN sp_insert_dept(:a, :b, :c, :d); END;';

 /* Specify bind variables in USING clause.
    Specify mode for first parameter.
    Modes of other parameters are correct by default. */

  EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/
-- 执行结果  
deptid = 280 new_dname= dbms_output.put_line new_mgrid= 202 new_locid= 3200

PL/SQL procedure successfully completed.

-- 检查表数据
HR@192.168.80.190:1521/racdb> select * from departments;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
		。。。。。。。。中间省略    。。。。。。。。。。。。。
          280 super                                 200        1700

2.2、用BOOLEAN形式参数动态调用子程序

Dynamically Invoking Subprogram with BOOLEAN Formal Parameter

In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL data type BOOLEAN.

sql">CREATE OR REPLACE PROCEDURE sp_test_boolean (x BOOLEAN) AUTHID DEFINER AS
BEGIN
  IF x THEN
    DBMS_OUTPUT.PUT_LINE('x is true');
  END IF;
END;
/

DECLARE
  dyn_stmt VARCHAR2(200);
  b        BOOLEAN := TRUE;
BEGIN
  dyn_stmt := 'BEGIN sp_test_boolean(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING b;
END;
/
-- 执行结果
x is true

PL/SQL procedure successfully completed.
-- 注意执行成功的数据库版本oracle19c
-- 测试如果是oracle11g的环境会报错
ERROR at line 6:
ORA-06550: line 6, column 36:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

2.3、用RECORD形式参数动态调用子程序

Dynamically Invoking Subprogram with RECORD Formal Parameter

In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL (but not SQL) data type RECORD. The record type is declared in a package specification, and the subprogram is declared in the package specification and defined in the package body.

sql">CREATE OR REPLACE PACKAGE pkg_record_datatype 
AUTHID DEFINER 
AS
  TYPE rec IS RECORD (n1 NUMBER, n2 NUMBER);
  PROCEDURE sp_record_datatype (x OUT rec, y NUMBER, z NUMBER);
END pkg_record_datatype ;
/

CREATE OR REPLACE PACKAGE BODY pkg_record_datatype 
AS
  PROCEDURE sp_record_datatype (x OUT rec, y NUMBER, z NUMBER) AS
  BEGIN
    x.n1 := y;
    x.n2 := z;
  END sp_record_datatype ;
END pkg_record_datatype ;
/

DECLARE
  r       pkg_record_datatype.rec;
  dyn_str VARCHAR2(3000);
BEGIN
  dyn_str := 'BEGIN pkg_record_datatype.sp_record_datatype(:x, 100, 1008); END;';
 
  EXECUTE IMMEDIATE dyn_str USING OUT r;
 
  DBMS_OUTPUT.PUT_LINE('r.n1 = ' || r.n1);
  DBMS_OUTPUT.PUT_LINE('r.n2 = ' || r.n2);
END;
/

执行结果 – 注意(oracle19c版本)，oracle11g依旧报错

sql">r.n1 = 100
r.n2 = 1008

3、使用DBMS_SQL包

DBMS_SQL包用于执行更复杂的动态SQL，包括查询和需要处理结果集的DML操作。以下是使用DBMS_SQL包的基本步骤：

3.1、DBMS_SQL.RETURN_RESULT Procedure

In this example, the procedure p invokes DBMS_SQL.RETURN_RESULT without the optional to_client parameter (which is TRUE by default). Therefore, DBMS_SQL.RETURN_RESULT returns the query result to the subprogram client (the anonymous block that invokes p). After p returns a result to the anonymous block, only the anonymous block can access that result.

sql">CREATE OR REPLACE PROCEDURE sp_dbms_sql_test AUTHID DEFINER AS
  c1 SYS_REFCURSOR;
  c2 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR
    SELECT first_name, last_name
    FROM employees
    WHERE employee_id = 176;
 
  DBMS_SQL.RETURN_RESULT (c1);
  -- Now p cannot access the result.
 
  OPEN c2 FOR
    SELECT city, state_province
    FROM locations
    WHERE country_id = 'AU';
 
  DBMS_SQL.RETURN_RESULT (c2);
  -- Now p cannot access the result.
END;
/

BEGIN
  sp_dbms_sql_test ;
END;
/

执行结果

sql">ResultSet #1

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jonathon             Taylor

ResultSet #2

CITY                           STATE_PROVINCE
------------------------------ -------------------------
Sydney                         New South Wales

注意：Oracle12c中PL/SQL(DBMS_SQL)新特性之隐式语句结果，DBMS_SQL.RETURN_RESULT隐式返回查询结果，Oracle11g执行上面的示例会报错，不支持RETURN_RESULT。

动态SQL为Oracle数据库应用提供了极大的灵活性和功能，但使用时也需要注意SQL注入等安全问题。因此，在处理用户输入时，应该使用参数化查询或适当的输入验证来防止潜在的安全风险。

3.2、DBMS_SQL.GET_NEXT_RESULT

希望通过客户端应用来处理这些结果集，这可以通过DBMS_SQL包的 GET_NEXT_RESULT过程来解决

sql">DECLARE
  l_sql_cursor    PLS_INTEGER;
  l_ref_cursor    SYS_REFCURSOR;
  l_return        PLS_INTEGER;

  l_col_cnt       PLS_INTEGER;
  l_desc_tab      DBMS_SQL.desc_tab;

  l_count         NUMBER;
  l_EMPLOYEE_ID   EMPLOYEES.EMPLOYEE_ID%TYPE;
  l_FIRST_NAME    EMPLOYEES.FIRST_NAME%TYPE;
  l_LAST_NAME    EMPLOYEES.LAST_NAME%TYPE;
BEGIN
  -- 执行过程
  l_sql_cursor := DBMS_SQL.open_cursor(treat_as_client_for_results => TRUE);


  DBMS_SQL.parse(c             => l_sql_cursor,
                 statement     => 'BEGIN get_result_emp(30); END;',
                 language_flag => DBMS_SQL.native);


  l_return := DBMS_SQL.execute(l_sql_cursor);


  -- 循环遍历每个结果集
  LOOP
    -- 获取下个结果集
    BEGIN
      DBMS_SQL.get_next_result(l_sql_cursor, l_ref_cursor);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;


    -- 检查结果集列数
    l_return := DBMS_SQL.to_cursor_number(l_ref_cursor);
    DBMS_SQL.describe_columns (l_return, l_col_cnt, l_desc_tab);
    l_ref_cursor := DBMS_SQL.to_refcursor(l_return);

    -- 根据列数处理结果集
    CASE l_col_cnt
      WHEN 1 THEN
        DBMS_OUTPUT.put_line('The column is COUNT:');
        FETCH l_ref_cursor
        INTO  l_count;


        DBMS_OUTPUT.put_line('l_count=' || l_count);
        CLOSE l_ref_cursor;
      WHEN 3 THEN
        DBMS_OUTPUT.put_line('The columns are EMPLOYEE_ID and FIRST_NAME and l_LAST_NAME:');
        LOOP
          FETCH l_ref_cursor
          INTO  l_EMPLOYEE_ID, l_FIRST_NAME,l_LAST_NAME;


          EXIT WHEN l_ref_cursor%NOTFOUND;

          DBMS_OUTPUT.put_line('l_EMPLOYEE_ID=' || to_char(l_EMPLOYEE_ID) || CHR(9) || 'l_FIRST_NAME=' || l_FIRST_NAME || CHR(9)|| 'l_LAST_NAME=' || l_LAST_NAME);
        END LOOP;
        CLOSE l_ref_cursor;
      ELSE
        DBMS_OUTPUT.put_Line('I wasn''t expecting that!');
    END CASE;
  END LOOP;
END;
/

执行结果

sql">The columns are EMPLOYEE_ID and FIRST_NAME and l_LAST_NAME:
l_EMPLOYEE_ID=114       l_FIRST_NAME=Den        l_LAST_NAME=Raphaely
l_EMPLOYEE_ID=115       l_FIRST_NAME=Alexander  l_LAST_NAME=Khoo
l_EMPLOYEE_ID=116       l_FIRST_NAME=Shelli     l_LAST_NAME=Baida
l_EMPLOYEE_ID=117       l_FIRST_NAME=Sigal      l_LAST_NAME=Tobias
l_EMPLOYEE_ID=118       l_FIRST_NAME=Guy        l_LAST_NAME=Himuro
l_EMPLOYEE_ID=119       l_FIRST_NAME=Karen      l_LAST_NAME=Colmenares
The column is COUNT:
l_count=107

PL/SQL procedure successfully completed.