PL/SQL --> 动态SQL

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
IS
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)
----------
14

flasher@ORCL> exec trunc_table('tb2');