概述
使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成。再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只能针对某几个特定的表来形成分页。而使用动态的SQL,则可以对不同的表,不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来完成。
动态SQL和静态SQL
静态SQL
静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程,返回该部门的所有雇员及薪水信息,则该语句为
SELECT ename,sal INTO lv_ename,lv_sal FROM scott.emp WHERE deptno=&dno;
对于上述类似的DML语句在第一次运行时进行编译,而后续再次调用,则不再编译该过程。即一次编译,多次调用,使用的相同的执行计划。此种方式被称之为使用的是静态的SQL。
动态SQL
动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询,对于表emp分页,需要使用字段雇员姓名,薪水,雇用日期,且按薪水降序生成报表,每页显示行数据。而对于表sales,需要使用字段雇员名称,客户名称,销售数量,销售日期,且按销售日期升序排列。以上两种情况,可以创建存储过程来对其进行分页,通过定义变量,根据输入不同的表名,字段名,排序方法来生成不同的SQL语句。对于输入不同的参数,SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译,此称之为动态SQL。动态SQL语句通常存放在字符串变量中,且SQL语句可以包含占位符(使用冒号开头)。也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后,如
EXECUTE IMMEDIATE 'alter table emp enable row movement'
两者的异同
静态SQL为直接嵌入到PL/SQL中的代码,而动态SQL在运行时,根据不同的情况产生不同的SQL语句。
静态SQL为在执行前编译,一次编译,多次运行。动态SQL同样在执行前编译,但每次执行需要重新编译。
静态SQL可以使用相同的执行计划,对于确定的任务而言,静态SQL更具有高效性。但缺乏灵活性动态SQL使用了不同的执行计划,效率不如静态SQL,但能够解决复杂的问题。
动态SQL容易产生SQL注入,为数据库安全带来隐患。
动态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, ...]
语法描述
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动态返回值的变量。
使用要点
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以及单行结果集)
例:使用EXECUTE IMMEDIATE处理DDL操作。在存储过程之中封装一简单的DDL语句,通过传入表名来进行调用。
SQL> create table tb2 as select * from emp;
Table created.
SQL> create or replace procedure trunc_table(table_name varchar2)
Procedure created.
SQL> select count(1) from tb2;
----------
SQL> exec trunc_table('tb2');
PL/SQL procedure successfully completed.
SQL> select count(1) from tb2;
----------
例:使用EXECUTE IMMEDIATE处理DCL操作,下面使用sys帐户创建存储过程grant_sys_priv用于给用户授予权限
SQL> conn sys/redhat@orcl as sysdba
SQL>CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2, username VARCHAR2)
IS
BEGIN
END;
/
SQL> exec grant_sys_priv('connect','usr1');
例:使用EXECUTE IMMEDIATE处理没有参数传入传出的DML语句
SQL> select * From tb2 where empno = 7900;
----------
SQL> declare
PL/SQL procedure successfully completed.
SQL> select * From tb2 where empno = 7900;
no rows selected
例:有参数传入的DML语句(使用USING子句),对于使用了参数传入的动态SQL,需要使用USING子句来指明传入的参数。在下面的示例中,为表tb2插入一条记录,在DML语句中使用了四个占位符(占位符用以冒号开头,紧跟任意字母或数字表示)。因此在使用EXECUTE IMMEDIATE使用USING子句为其指定其参数。
SQL>
PL/SQL procedure successfully completed.
SQL> select * from tb2 where empno=7900;
----------
例:处理包含returning子句的DML语句。下面的示例中,对表tb2进行更新,使用了两个占位符,一个是:percent,一个是:eno,因此在使用EXECUTE IMMEDIATE执行动态。DML时,需要使用USING子句且带两个输入参数。其次,动态DML中使用了RETURNING sal INTO :salary,因此EXECUTE IMMEDIATE后也必须使用RETURNING INTO varialbe_name。
SQL>
Enter value for 1: 10
Enter value for 2: 7900
old
new
New salary: 1045
PL/SQL procedure successfully completed.
例:处理包含检索值的单行查询。下面的示例中,使用SELECT 查询获得单行结果集,使用了占位符:name,因此也需要使用USING子句为其传递参数
SQL> declare
Enter value for name: james
old
new
The salary is 1045 for JAMES
PL/SQL procedure successfully completed.
游标和动态SQL处理多行结果集的查询语句
其主要流程为
1.定义游标变量
TYPE cursortype IS REF CURSOR;
cursor_variable cursortype;
2.打开游标变量
OPEN cursor_variable FOR dynamic_string
[USING bind_argument[,bind_argument]...]
3.循环提取数据
FETCH cursor_variable INTO {var1[,var2]...| record_variable};
EXIT WHEN cursor_variable%NOTFOUND
4.关闭游标变量
CLOSE cursor_variable;
使用游标变量处理查询多行结果集
下面的示例中,首先定义了一个游标类型,接下来定义游标变量,以及存放结果集的变量,动态查询语句将获得多个结果集。
OPEN cursorname FOR SELECT ... 时,其SELECT 语句使用了字符串变量(动态SQL),其后紧跟USING子句。
SQL>
Enter value for inputno: 7900
old
new
Employee Name: JAMES ,Salary: 1045
PL/SQL procedure successfully completed.
BULK子句和动态SQL的使用
动态SQL中使用BULK子句的语法
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT INTO define_variable[,define_variable...]]
[USING bind_argument[,argument...]]
[{RETURNING | RETURN}
BULK COLLECT INTO return_variable[,return_variable...]];
使用bulk collect into子句处理动态SQL中T的多行查询可以加快处理速度,从而提高应用程序的性能。当使用bulk子句时,集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRY,但集合元 素必须使用SQL数据类型。常用的三种语句支持BULK子句,分别为EXECUTE IMMEDIATE,
下面的例子,首先定义了两个表类型以及其变量,接下来使用动态SQL语句来更新tb2的薪水,使用EXECUTE IMMEDIATE配合BULK COLLEC INTO 来处理结果集。
SQL>
Enter value for percent: 10
old
new
Enter value for dno: 20
old
new
Employee SMITH Salary is: 880
Employee JONES Salary is: 3272.5
Employee SCOTT Salary is: 3300
Employee ADAMS Salary is: 1210
Employee FORD Salary is: 3300
PL/SQL procedure successfully completed.
使用EXECUTE IMMEDIATE 结合BULK子句处理多行查询。下面示例中,与前一个示例相同,只不过其动态SQL有查询语句组成,且返回多个结果集,同样使用了BULK COLLECT INTO来传递结果。
SQL>declare
begin
end;
Enter value for dno: 20
old
new
Employee SMITH Salary is: 880
Employee JONES Salary is: 3272.5
Employee SCOTT Salary is: 3300
Employee ADAMS Salary is: 1210
Employee FORD Salary is: 3300
PL/SQL procedure successfully completed.
使用FETCH子句结合BULK子句处理多行结果集
下面的示例中首先定义了游标类型,游标变量以及复合类型,复合变量,接下来从动态SQL中OPEN游标,然后使用FETCH将结果存放到复合变量中。即使用OPEN,FETCH代替了EXECUTE IMMEDIATE来完成动态SQL的执行。
SQL>
old
new
Employee Name: SMITH
Employee Name: JONES
Employee Name: SCOTT
Employee Name: ADAMS
Employee Name: FORD
PL/SQL procedure successfully completed.
在FORALL语句中使用BULK子句
下面是FORALL子句的语法
FORALL index IN lower bound..upper bound
EXECUTE IMMEDIATE dynamic_string
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 来提取结果集。
SQL>
The BLAKE's new salalry is 3448.5
The FORD's new salalry is 3993
The MILLER's new salalry is 1573
PL/SQL procedure successfully completed.
常见错误
1、使用动态DDL时,不能使用绑定变量。下面的示例中,在创建表示,使用了绑定变量:dno,在执行的时候收到了错误信息。
SQL> declare
declare
*
ERROR at line 1:
ORA-01027: bind variables not allowed for data definition operations
ORA-06512: at line 7
解决办法,将绑定变量直接拼接,如下:
sql_stat := 'CREATE TABLE tb_tmp ' || 'AS SELECT * FROM scott.emp ' || 'WHERE deptno = ' || v_deptno;
2、不能使用schema对象作为绑定参数,下面的示例中,动态SQL语句查询需要传递表名,因此收到了错误提示。
SQL> declare
begin
end;
解决办法,将绑定变量直接拼接,如下:
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_tbname into v_count;
3、动态SQL块不能使用分号结束(;)
下面的示例中,动态SQL语句使用了分号来结束,收到错误提示。
SQL> declare
begin
end;4、动态PL/SQL块不能使用正斜杠来结束块,但是块结尾处必须要使用分号(;)
SQL>
*
ERROR at line 1:
ORA-06550: line 2, column 18:
PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.
ORA-06512: at line 11
4、空值传递的问题
下面的示例中对表tb_emp更新,并将空值更新到sal列,直接使用USING NULL收到错误提示。
SQL> declare
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
正确的处理方法
SQL> declare
PL/SQL procedure successfully completed.
5、日期和字符型必须要使用引号来处理
下面的示例中,使用了日期型变量,未使用引号标注,且使用了变量绑定,但直接输入日期型数据,而不加引号,则收到错误提示。
SQL>
Enter value for dt: 1987-05-23
old
new
ERROR at line 3:
ORA-06550: line 3, column 27:
PLS-00382: expression is of wrong type
ORA-06550: line 3, column 20:
PL/SQL: Item ignored
ORA-06550: line 11, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 9, column 10:
PL/SQL: Statement ignored
处理办法一
执行时输入带引号的字串
SQL> /
Enter value for dt: '1981-05-01'
old
new
Employee Name BLAKE, sal is 2850
PL/SQL procedure successfully completed.
处理办法二
在声明变量时赋值用引号,如下
v_date
如存在字符格式转换,可以直接使用转换函数,如
v_date
如果上面的例子中,动态SQL语句不使用绑定日期变量,而是将其连接成字符串,则可以使用下面的方式来实现
如果上面的例子中,动态SQL语句不使用绑定日期变量,而是将其连接成字符串,则可以使用下面的方式来实现
DECLARE
BEGIN
END;
6、单行SELECT 查询不能使用RETURNING INTO返回
下面的示例中,使用了动态的单行SELECT查询,并且使用了RETURNING子句来返回值。事实上,RETURNING coloumn_name INTO 子句仅仅支持对DML结果集的返回,因此,收到了错误提示。SQL>declare
Enter value for empno: 7900
old
new
ERROR at line 7:
ORA-06550: line 7, column 54:
PLS-00103: Encountered the symbol "ENAME" when expecting one of the following:
into bulk
The symbol "ENAME" was ignored.
上,RETURNING coloumn_name INTO
解决方式
去掉动态SQL语句中的RETURNING coloumn_name INTO子句,在执行EXECUTE IMMEDIATE时,直接使用INTO子句来传递值。
execute immediate sql_stat
参考至:http://blog.csdn.net/robinson_0612/article/details/6118010
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com