--====================

使用动态 SQL 是在编写 PL / SQL 过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行

SQL 查询语句，对于这种情况需要使用动态 SQL 来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态 SQL 则只

能针对某几个特定的表来形成分页。而使用动态的 SQL ，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态 SQL 来

完成。本文讲述了动态 SQL 的日常用法。

一、动态 SQL 和静态 SQL

1. 静态 SQL

静态 SQL 通常用于完成可以确定的任务。比如传递部门号调用存储过程，返回该部门的所有雇员及薪水信息，则该语句为

SELECT ename ， sal INTO lv_ename , lv_sal FROM scott . emp WHERE deptno =& dno ;

对于上述类似的 DML 语句在第一次运行时进行编译，而后续再次调用，则不再编译该过程。即一次编译，多次调用，使用的相同的执行

计划。此种方式被称之为使用的是静态的 SQL 。

2. 动态 SQL

动态 SQL 通常是用来根据不同的需求完成不同的任务。比如分页查询，对于表 emp 分页，需要使用字段雇员姓名，薪水，雇用日期，且按

薪水降序生成报表，每页显示行数据。而对于表 sales ，需要使用字段雇员名称，客户名称，销售数量，销售日期，且按销售日期升序

排列。以上两种情况，可以创建存储过程来对其进行分页，通过定义变量，根据输入不同的表名，字段名，排序方法来生成不同的 SQL

语句。对于输入不同的参数， SQL 在每次运行时需要事先对其编译。即多次调用则需要多次编译，此称之为动态 SQL 。

动态 SQL 语句通常存放在字符串变量中，且 SQL 语句可以包含占位符 ( 使用冒号开头 ) 。

也可以直接将动态 SQL 紧跟在 EXECUTE IMMEDIATE 语句之后，如 EXECUTE IMMEDIATE 'alter table emp enable row movement'

3. 两者的异同

静态 SQL 为直接嵌入到 PL / SQL 中的代码，而动态 SQL 在运行时，根据不同的情况产生不同的 SQL 语句。

静态 SQL 为在执行前编译，一次编译，多次运行。动态 SQL 同样在执行前编译，但每次执行需要重新编译。

静态 SQL 可以使用相同的执行计划，对于确定的任务而言，静态 SQL 更具有高效性。但缺乏灵活性

动态 SQL 使用了不同的执行计划，效率不如静态 SQL ，但能够解决复杂的问题。

动态 SQL 容易产生 SQL 注入，为数据库安全带来隐患。

4. 动态 SQL 语句的几种方法

a. 使用 EXECUTE IMMEDIATE 语句

包括 DDL 语句， DCL 语句， DML 语句以及单行的 SELECT 语句。该方法不能用于处理多行查询语句。

b. 使用 OPEN-FOR ， FETCH 和 CLOSE 语句

对于处理动态多行的查询操作，可以使用 OPEN-FOR 语句打开游标，使用 FETCH 语句循环提取数据，最终使用 CLOSE 语句关闭游标。

c. 使用批量动态 SQL

即在动态 SQL 中使用 BULK 子句，或使用游标变量时在 fetch 中使用 BULK ，或在 FORALL 语句中使用 BULK 子句来实现。

d. 使用系统提供的 PL/SQL 包 DBMS_SQL 来实现动态 SQL ，关于该方式请参考后续博文。

二、动态 SQL 的语法

下面是动态 SQL 常用的语法之一

EXECUTE IMMEDIATE dynamic_SQL_string

[INTO defined_variable1, defined_variable2, ...]

[USING [IN | OUT | IN OUT] bind_argument1 , bind_argument2 ,

... ][{RETURNING | RETURN} field1, field2, ... INTO bind_argument1,

bind_argument2, ...]

1. 语法描述

dynamic_SQL_string ：存放指定的 SQL 语句或 PL / SQL 块的字符串变量

defined_variable1 ：用于存放单行查询结果，使用时必须使用 INTO 关键字，类似于使用 SELECT ename INTO v_name FROM scott . emp ；

只不过在动态 SQL 时，将 INTO defined_variable1 移出到 dynamic_SQL_string 语句之外。

bind_argument1 ：用于给动态 SQL 语句传入或传出参数，使用时必须使用 USING 关键字， IN 表示传入的参数， OUT 表示传出的参数，

IN OUT 则既可以传入，也可传出。

RETURNING | RETURN 子句也是存放 SQL 动态返回值的变量。

2. 使用要点

a.EXECUTE IMMEDIATE 执行 DML 时，不会提交该 DML 事务，需要使用显示提交 (COMMIT) 或作为 EXECUTE IMMEDIATE 自身的一部分。

b.EXECUTE IMMEDIATE 执行 DDL,DCL 时会自动提交其执行的事务。

c. 对于多行结果集的查询，需要使用游标变量或批量动态 SQL ，或者使用临时表来实现。

d. 当执行 SQL 时，其尾部不需要使用分号，当执行 PL/SQL 代码时，其尾部需要使用分号。

f . 动态 SQL 中的占位符以冒号开头，紧跟任意字母或数字表示。

三、动态 SQL 的使用 ( DDL ， DCL ， DML 以及单行结果集 )

1. 使用 EXECUTE IMMEDIATE 处理 DDL 操作

下面是一个简单的 DDL 操作，将其封装在存储过程之中，通过传入表名来进行调用。

CREATE OR REPLACE PROCEDURE trunc_table ( table_name VARCHAR2 ) -- 创建存储过程 trunc_table

sql_statement VARCHAR2 ( 100 );

BEGIN

sql_statement := 'TRUNCATE TABLE ' || table_name ; -- 为变量进行赋值，用于生成动态 SQL 语句

EXECUTE IMMEDIATE sql_statement ; -- 使用 EXECUTE IMMEDIATE 执行动态 SQL 语句

END ;

flasher@ORCL > create table tb2 -- 从 scott.emp 生产表 tb2

2 as select empno , ename , sal , deptno from scott . emp ;

flasher@ORCL > select count ( 1 ) from tb2 ;

COUNT ( 1 )

----------

flasher@ORCL > exec trunc_table ( 'tb2' ); -- 调用存储过程来对表 tb2 进行 truncate

flasher@ORCL > select count ( 1 ) from tb2 ; -- 表 tb2 被清空

COUNT ( 1 )

----------

flasher@ORCL > insert into tb2 -- 重新为表 tb2 生成记录

2 select empno , ename , sal , deptno from scott . emp ;

flasher@ORCL > commit ;

2. 使用 EXECUTE IMMEDIATE 处理 DCL 操作

下面使用 sys 帐户创建存储过程 grant_sys_priv 用于给用户授予权限

sys@ORCL > conn sys / redhat@orcl as sysdba

CREATE OR REPLACE PROCEDURE grant_sys_priv ( priv VARCHAR2 , username VARCHAR2 )

sql_stat VARCHAR2 ( 100 );

BEGIN

sql_stat := 'GRANT ' || priv || ' TO ' || username ;

EXECUTE IMMEDIATE sql_stat ;

END ;

sys@ORCL > exec grant_sys_priv ( 'connect' , 'usr1' );

3. 使用 EXECUTE IMMEDIATE 处理 DML 操作

在使用 EXECUTE IMMEDIATE 处理 DML 操作时，分为几种情况，即不带输入参数，带输入参数，既有输入也有输出参数或返回参数等不同情

况，下面分别对其描述。

a . 没有参数传入传出的 DML 语句

下面的示例中，使用动态 SQL 删除一条记录，且未使用参数传入。

flasher@ORCL > select * from tb2 where empno = 7900 ; -- 删除前

EMPNO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

7900 JAMES 950 30

flasher@ORCL > DECLARE sql_stat VARCHAR2 ( 100 );

2 BEGIN

3 sql_stat := 'DELETE FROM flasher.tb2 WHERE empno=7900' ; -- 使用动态 SQL 来删除记录

4 EXECUTE IMMEDIATE sql_stat ;

5 END ;

6 /

flasher@ORCL > SELECT * FROM tb2 where empno = 7900 ; -- 验证删除情况

no rows selected

b . 有参数传入的 DML 语句 ( 使用 USING 子句 )

对于使用了参数传入的动态 SQL ，需要使用 USING 子句来指明传入的参数。在下面的示例中，为表 tb2 插入一条记录，在 DML 语句中使

用了四个占位符 ( 占位符用以冒号开头，紧跟任意字母或数字表示 ) 。因此在使用 EXECUTE IMMEDIATE 使用 USING 子句为其指定其参数。

DECLARE -- 声明变量

sql_stat VARCHAR2 ( 100 );

lv_empno tb2 . empno % TYPE := 7900 ;

lv_ename tb2 . ename % TYPE := 'JAMES' ;

lv_sal tb2 . sal % TYPE := 950 ;

BEGIN

sql_stat := 'INSERT INTO tb2 VALUES(:1,:2,:3,:4)' ; --DML 语句中使用了占位符

EXECUTE IMMEDIATE sql_stat USING lv_empno , lv_ename , lv_sal , 30 ; -- 为占位符指定参数或值

COMMIT ;

END ;

flasher@ORCL > select * from tb2 where empno = 7900 ; -- 验证插入后的结果

EMPNO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

7900 JAMES 950 30

c . 处理包含 returning 子句的 DML 语句

下面的示例中，对表 tb2 进行更新，使用了两个占位符，一个是 :percent ，一个是 :eno ，因此在使用 EXECUTE IMMEDIATE 执行动态

DML 时，需要使用 USING 子句且带两个输入参数。其次，动态 DML 中使用了 RETURNING sal INTO :salary ，因此 EXECUTE IMMEDIATE 后

也必须使用 RETURNING INTO varialbe_name 。

DECLARE

salary NUMBER ( 6 , 2 );

sql_stat VARCHAR2 ( 100 );

BEGIN

sql_stat := 'UPDATE tb2 SET sal = sal * (1 + :percent / 100)' -- 更新 sal 列，使用占位符 :percent

|| ' WHERE empno = :eno RETURNING sal INTO :salary' ; -- 使用了占位符 :eno ， :salary ，以及 RETURNING 子句

EXECUTE IMMEDIATE sql_stat USING & 1 , & 2 RETURNING INTO salary ; -- 必须使用 USING 及 RETURNING 子句

COMMIT ;

dbms_output . put_line ( 'New salary: ' || salary );

END ;

Enter value for 1 : 10

Enter value for 2 : 7900

old 7 : EXECUTE IMMEDIATE sql_stat USING & 1 , & 2 RETURNING INTO salary ;

new 7 : EXECUTE IMMEDIATE sql_stat USING 10 , 7900 RETURNING INTO salary ;

New salary : 1045

d . 处理包含检索值的单行查询

下面的示例中，使用 SELECT 查询获得单行结果集，使用了占位符 :name ，因此也需要使用 USING 子句为其传递参数

DECLARE

sql_stat VARCHAR2 ( 100 );

emp_record tb2 % ROWTYPE ;

BEGIN

sql_stat := 'SELECT * FROM tb2 WHERE ename = UPPER(:name)' ; -- 动态 SQL 语句为单行 DQL 语句

EXECUTE IMMEDIATE sql_stat INTO emp_record USING '&name' ; -- 使用 USING 子句为其传递参数

DBMS_OUTPUT . PUT_LINE ( 'The salary is ' || emp_record . sal || ' for ' || emp_record . ename );

END ;

Enter value for 1 : james

old 6 : EXECUTE IMMEDIATE sql_stat INTO emp_record USING '&1' ;

new 6 : EXECUTE IMMEDIATE sql_stat INTO emp_record USING 'james' ;

The salary is 1045 for JAMES

四、动态 SQL 的使用 ( 处理多行结果集的查询语句 )

1. 使用游标变量来循环提取数据，其主要流程为

定义游标变量

TYPE cursortype IS REF CURSOR ;

cursor_variable cursortype ;

打开游标变量

OPEN cursor_variable FOR dynamic_string

[USING bind_argument[,bind_argument] ... ]

循环提取数据

FETCH cursor_variable INTO { var1[,var2] ...| record_variable} ;

EXIT WHEN cursor_variable % NOTFOUND

关闭游标变量

CLOSE cursor_variable ;

2. 使用游标变量处理查询多行结果集

下面的示例中，首先定义了一个游标类型，接下来定义游标变量，以及存放结果集的变量，动态查询语句将获得多个结果集。

OPEN cursorname FOR SELECT ... 时，其 SELECT 语句使用了字符串变量 ( 动态 SQL ) ，其后紧跟 USING 子句。

DECLARE -- 游标，变量的声明

TYPE emp_cur_type IS REF CURSOR ;

emp_cv emp_cur_type ;

emp_record tb2 % ROWTYPE ;

sql_stat VARCHAR2 ( 100 );

v_dno NUMBER := & dno ;

BEGIN

sql_stat := 'SELECT * FROM tb2 WHERE deptno = :dno' ; -- 动态多行结果集查询语句

OPEN emp_cv FOR sql_stat USING v_dno ; --OPEN 时使用动态查询语句以及 USING 子句来传递参数

FETCH emp_cv INTO emp_record ; -- 从结果集中提取记录

EXIT WHEN emp_cv % NOTFOUND ;

dbms_output . put_line ( 'Employee name ： ' || emp_record . ename || ', Salary ： ' || emp_record . sal );

END LOOP ;

CLOSE emp_cv ;

END ;

Employee name ： Henry , Salary ：

Employee name ： JONES , Salary ：

Employee name ： ADAMS , Salary ：

Employee name ： FORD , Salary ：

五、动态 SQL 的使用 (FORALL 及 BULK 子句的使用 )

1. 动态 SQL 中使用 BULK 子句的语法

EXECUTE IMMEDIATE dynamic_string --dynamic_string 用于存放动态 SQL 字符串

[BULK COLLECT INTO define_variable[,define_variable...]] -- 存放查询结果的集合变量

[USING bind_argument[,argument...]] -- 使用参数传递给动态 SQL

[{RETURNING | RETURN} -- 返回子句

BULK COLLECT INTO return_variable[,return_variable...]]; -- 存放返回结果的集合变量

使用 bulk collect into 子句处理动态 SQL 中的多行查询可以加快处理速度，从而提高应用程序的性能。当使用 bulk 子句时，集合类型可

以是 PL/SQL 所支持的索引表、嵌套表和 VARRY ，但集合元素必须使用 SQL 数据类型。常用的三种语句支持 BULK 子句，分别为 EXECUTE

IMMEDIATE ， FETCH 和 FORALL 。

2. 使用 EXECUTE IMMEDIATE 结合 BULK 子句处理 DML 语句返回子句

下面的例子，首先定义了两个索引表类型以及其变量，接下来使用动态 SQL 语句来更新 tb2 的薪水，使用 EXECUTE IMMEDIATE 配合 BULK

COLLECT INTO 来处理结果集。

DECLARE

TYPE ename_table_type IS TABLE OF tb2 . ename % TYPE INDEX BY BINARY_INTEGER ; -- 定义类型用于存放结果集

TYPE sal_table_type IS TABLE OF tb2 . sal % TYPE INDEX BY BINARY_INTEGER ;

ename_table ename_table_type ;

sal_table sal_table_type ;

sql_stat VARCHAR2 ( 120 );

v_percent NUMBER :=& percent ;

v_dno NUMBER :=& dno ;

BEGIN

sql_stat := 'UPDATE tb2 SET sal = sal * (1 + :percent / 100)' -- 动态 DML 语句

|| ' WHERE deptno = :dno'

|| ' RETURNING ename, sal INTO :name, :salary' ; -- 使用了 RETURNING 子句，有返回值

EXECUTE IMMEDIATE sql_stat USING v_percent , v_dno -- 执行动态 SQL 语句

RETURNING BULK COLLECT INTO ename_table , sal_table ; -- 使用 BULK COLLECT INTO 到集合变量

FOR i IN 1. . ename_table . COUNT -- 使用 FOR 循环读取集合变量的结果

DBMS_OUTPUT . PUT_LINE ( 'Employee ' || ename_table ( i ) || ' Salary is: ' || sal_table ( i ));

END LOOP ;

END ;

Employee Henry Salary is: 1694

Employee JONES Salary is: 3841.75

Employee ADAMS Salary is: 1573

Employee FORD Salary is: 3872

3. 使用 EXECUTE IMMEDIATE 结合 BULK 子句处理多行查询

下面示例中，与前一个示例相同，只不过其动态 SQL 有查询语句组成，且返回多个结果集，同样使用了 BULK COLLECT INTO 来传递结果。

DECLARE

TYPE ename_table_type IS TABLE OF tb2 . ename % TYPE INDEX BY BINARY_INTEGER ; -- 定义类型用于存放结果集

TYPE sal_table_type IS TABLE OF tb2 . sal % TYPE INDEX BY BINARY_INTEGER ;

ename_table ename_table_type ;

sal_table sal_table_type ;

sql_stat VARCHAR2 ( 100 );

BEGIN

sql_stat := 'SELECT ename,sal FROM tb2 WHERE deptno = :dno' ; -- 动态 DQL 语句，未使用 RETURNING 子句

EXECUTE IMMEDIATE sql_stat BULK COLLECT INTO ename_table , sal_table USING & dno ; -- 使用 BULK COLLECT INTO

FOR i IN 1. . ename_table . COUNT

DBMS_OUTPUT . PUT_LINE ( 'Employee ' || ename_table ( i ) || ' Salary is: ' || sal_table ( i ));

END LOOP ;

END ;

Employee Henry Salary is: 1694

Employee JONES Salary is: 3841.75

Employee ADAMS Salary is: 1573

Employee FORD Salary is: 4259.2

4. 使用 FETCH 子句结合 BULK 子句处理多行结果集

下面的示例中首先定义了游标类型，游标变量以及复合类型，复合变量，接下来从动态 SQL 中 OPEN 游标，然后使用 FETCH 将结果存放到复

合变量中。即使用 OPEN ， FETCH 代替了 EXECUTE IMMEDIATE 来完成动态 SQL 的执行。

DECLARE

TYPE empcurtype IS REF CURSOR ; -- 定义游标类型及游标变量

emp_cv empcurtype ;

TYPE ename_table_type IS TABLE OF tb2 . ename % TYPE INDEX BY BINARY_INTEGER ; -- 定义结果集类型及变量

ename_table ename_table_type ;

sql_stat VARCHAR2 ( 120 );

BEGIN

sql_stat := 'SELECT ename FROM tb2 WHERE deptno = :dno' ; -- 动态 SQL 字符串

OPEN emp_cv FOR sql_stat -- 从动态 SQL 中打开游标

USING & dno ;

FETCH emp_cv BULK COLLECT -- 使用 BULK COLLECT INTO 提取结果集

INTO ename_table ;

FOR i IN 1 .. ename_table . COUNT LOOP

DBMS_OUTPUT . PUT_LINE ( 'Employee Name is ' || ename_table ( i ));

END LOOP ;

CLOSE emp_cv ;

END ;

Employee Name is Henry

Employee Name is JONES

Employee Name is ADAMS

Employee Name is FORD

5. 使用 FORALL 语句中使用 BULK 子句

下面是 FORALL 子句的语法

FORALL index IN lower bound..upper bound --FORALL 循环计数

EXECUTE IMMEDIATE dynamic_string -- 结合 EXECUTE IMMEDIATE 来执行动态 SQL 语句

USING bind_argument | bind_argument(index) -- 绑定输入参数

[bind_argument | bind_argument(index)] ...

[{RETURNING | RETURN} BULK COLLECT INTO bind_argument[,bind_argument...]]; -- 绑定返回结果集

FORALL 子句允许为动态 SQL 输入变量，但 FORALL 子句仅支持 DML(INSERT,DELETE,UPDATE) 语句，不支持动态的 SELECT 语句。

下面的示例中，首先声明了两个复合类型以及复合变量，接下来为复合变量 ename_table 赋值，以形成动态 SQL 语句。紧接着使用 FORALL

子句结合 EXECUTE IMMEDIATE 来提取结果集。

DECLARE -- 定义复合类型及变量

TYPE ename_table_type IS TABLE OF tb2 . ename % TYPE ;

TYPE sal_table_type IS TABLE OF tb2 . sal % TYPE ;

ename_table ename_table_type ;

sal_table sal_table_type ;

sql_stat VARCHAR2 ( 100 );

BEGIN

ename_table := ename_table_type ( 'BLAKE' , 'FORD' , 'MILLER' ); -- 为复合类型赋值

sql_stat := 'UPDATE tb2 SET sal = sal * 1.1 WHERE ename = :1' -- 定义动态 SQL 语句

|| ' RETURNING sal INTO :2' ;

FORALL i IN 1. . ename_table . COUNT -- 为 FORALL 设定起始值

EXECUTE IMMEDIATE sql_stat USING ename_table ( i ) -- 使用 EXECUTE IMMEDIATE 结合 RETURNING BULK COLLECT INTO 获取结果集

RETURNING BULK COLLECT INTO sal_table ;

FOR j IN 1. . ename_table . COUNT

DBMS_OUTPUT . PUT_LINE ( 'The new salary is ' || sal_table ( j ) || ' for ' || ename_table ( j )) ;

END LOOP ;

END ;

The new salary is 3135 for BLAKE

The new salary is 4259.2 for FORD

The new salary is 1760 for MILLER

6. 动态 SQL 使用的常见错误，请参考： PL/SQL --> 动态 SQL 的常见错误

六、更多参考

有关 SQL 请参考

SQL 基础--> 子查询

SQL 基础--> 多表 查询

SQL 基础--> 分组与分组函数

SQL 基础--> 常用函数

SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总

SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)

有关 PL/SQL 请参考

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL 记 录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL 包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表……