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

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

1、 动态SQL的类型

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

1. 本地动态SQL（Native Dynamic SQL）：

   • 使用EXECUTE IMMEDIATE语句来执行单个的SQL语句或PL/SQL匿名块。
   • 主要用于执行不需要返回结果的SQL语句，如INSERT、UPDATE、DELETE、DDL（数据定义语言）语句等。
   • 也可以使用INTO子句将查询结果存储在PL/SQL变量中。

2. 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.

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.

-- 检查表数据
[email protected]: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.

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