PL/SQL --> 动态SQL

谁借莪１个温暖的怀抱￠ 2022-05-15 03:27 417阅读 0赞

\--====================

\-- PL/SQL --> 动态SQL

\--====================

使用动态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子句来传递参数

LOOP

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循环读取集合变量的结果

LOOP

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

LOOP

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

LOOP

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的常见错误][PL_SQL --_ _SQL]

六、更多参考

有关SQL请参考

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

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

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

有关PL/SQL请参考

[PL/SQL --> 语言基础][PL_SQL --_]

[PL/SQL --> 流程控制][PL_SQL --_ 1]

[PL/SQL --> 存储过程][PL_SQL --_ 2]

[PL/SQL --> 函数][PL_SQL --_ 3]

[PL/SQL --> 游标][PL_SQL --_ 4]

[PL/SQL -->隐式游标(SQL%FOUND)][PL_SQL --_SQL_FOUND]

[PL/SQL --> 异常处理(Exception)][PL_SQL --_ _Exception]

[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[PL/SQL --> 包的创建与管理][PL_SQL --_ 5]

[PL/SQL --> 包重载、初始化][PL_SQL --_ 6]

[PL/SQL --> DBMS\_DDL包的使用][PL_SQL --_ DBMS_DDL]

[PL/SQL --> DML 触发器][PL_SQL --_ DML]

[PL/SQL --> INSTEAD OF 触发器][PL_SQL --_ INSTEAD OF]

[PL_SQL --_ _SQL]: http://blog.csdn.net/robinson_0612/archive/2011/01/05/6118049.aspx
[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063399.aspx
[PL_SQL --_ 1]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx
[PL_SQL --_ 2]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx
[PL_SQL --_ 3]: http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx
[PL_SQL --_ 4]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[PL_SQL --_ _Exception]: http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx
[PL_SQL --_ 5]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx
[PL_SQL --_ 6]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx
[PL_SQL --_ DBMS_DDL]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx
[PL_SQL --_ DML]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx
[PL_SQL --_ INSTEAD OF]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx