Oracle PL/SQL进阶编程（第十五弹：动态SQL语句）

理解动态SQL语句

动态SQL语句基础

动态SQL语句不仅是指SQL语句是动态拼接而成的，更主要的是SQL语句所使用的对象也是运行时期才创建的。出现这种功能跟PL/SQL本身的早起绑定特性有关，早PL/SQL中，所有的对象必须已经存在于数据库中才能执行，比如要查询emp表，emp表必须已经存在，否则会报错。此时可以通过动态SQL，因为动态SQL不被PL/SQL引擎编译时分析，而是在运行时进行分析并执行。

虽然动态SQL语句可以让我们在运行时动态地切换表名或字段名，以及在PL/SQL中执行DDL语句，但是在如下方面仍然不及静态SQL语句方便：
- 静态SQL在编译或测试时，可以立即知道对错，比如对象是否存在，权限是否具备，而动态SQL要在运行时才知道。
- 使用静态SQL时，可以对要执行的SQL进行性能优化调整，动态SQL不具备这种能力。

动态SQL使用时机

举个例子，我们经常会需要临时存储中间数据，因此会先检测目标表是否存在，如果存在则插入数据，如果不存在则先创建表，再插入数据。
如果我们在PL/SQL代码中直接用CREATE TABLE，会报错，所以必须把CREATE TABLE语句使用动态SQL来执行：
EXECUTE IMMEDIATE 'CREATE TABLE ...';

下面是使用动态SQL的几个时机：
- 由于在PL/SQL中只能执行静态的查询和DML语句，因此如果 要执行DDL语句，必须借助动态SQL。
- 在开发报表或一些复杂的应用程序逻辑时，如果要基于参数化的查询方式，比如动态的表字段和动态的表名称，可以使用动态SQL。
- 基于数据表存储业务规则和软件代码，可以将很多的业务规则的代码写在一个表的记录中，在程序需要时检索不同的业务逻辑的代码动态地执行。

从Oracle 7开始，可以使用DBMS_SQL包来动态执行动态SQL语句，在Oracle 8i之后，Oracle提供了执行动态SQL语句的另外一个选择：本地动态SQL（NDS）。NDS是PL/SQL原生部分，比使用DBMS_SQL更简单更方便，它仅提供了一个名为EXECUTE IMMEDIATE的过程。

本地动态SQL

本地动态SQL缩写为NDS，全称是Native Dynamic SQL。NDS提供了比DBMS_SQL更简单的语法，但是NDS不支持事先不知道参数的个数、名称或数据类型的动态SQL语句，此时需要使用DBMS_SQL来解决。

可以使用如下3种不同类型的动态方法使用本地动态SQL：
- EXECUTE IMMEDIATE：该语句可以处理多数动态SQL操作，包括DDL语句，比如CREATE、ALTER、DROP等；DCL语句，比如GRANT、REVOKE等；DML语句，比如INSERT、UPDATE、DELETE等，以及单行的SELECT语句。不能使用EXECUTE IMMEDIATE来处理多行的查询语句，多行查询需要用OPEN FOR。
- 使用OPEN FOR、FETCH和CLOSE语句执行多行查询。
- 使用批量SQL的处理语句。

使用EXECUTE IMMEDIATE

执行SQL语句和PL/SQL语句块

如下代码动态地创建了一个表，并向表中插入一条数据：

DECLARE
    sql_statement VARCHAR2(100);
    plsql_block VARCHAR2(500);
BEGIN
    sql_statement := 'CREATE TABLE ddl_demo(in NUMBER, amt NUMBER)';
    EXECUTE IMMEDIATE sql_statement;
    sql_statement := 'INSERT INTO ddl_demo VALUES(1, 100)';
    EXECUTE IMMEDIATE sql_statement;

    plsql_block := 
        'DECLARE
            i INTEGER := 10;
            FOR j IN 1.. i LOOP
                INSERT INTO ddl_demo VALUES(j, j * 100);
            END LOOP;
        END;'
    EXECUTE IMMEDIATE plsql_block;

要注意，使用EXECUTE IMMEDIATE执行一个SQL语句时，不要在语句后面放分号，只有在执行PL/SQL语句块时才需要添加分号。

使用绑定变量

代码如下 ：

DECLARE
    v_loc VARCHAR2(20) := '南京';
    v_deptno := NUMBER(2) := 30;
    sql_stmt VARCHAR2(100);
BEGIN
    sql_stmt :=