每天都在学习

每天都在工作，偶尔也会烦躁，但是遇到自己不会的知识点，又会激起自己的学习探索欲望。所以，这篇文章就把我工作中遇到的“新知识点”总结成文，以备将后查阅。

还记得那几天没日没夜的配报表的时候，总是参考着以前的人写的存储过程来写自己的存储过程，大体形式都差不多，一个很长的由SQL语句组成的字符串，然后调用EXECUTE IMMEDIATE语句来执行这个字符串，最后COMMIT就完事了。都这样的形式，但是自己很好奇，EXECUTE IMMEDIATE是个什么用法，最后Google了一下，终于明白了还有动态SQL这么概念的存在。好了，这篇文章就带领大家去学习动态SQL。

一个“血淋淋”的例子

不知道大家有没有写过如下这样的代码：

create or replace procedure create_tmp_tb
as
    strSQL varchar2(200);
begin
    create table tp(id int, name varchar2(20));
end;

运行上面的代码，存储过程创建会失败，错误信息如下：

Error(5,3): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:     ( begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    continue close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge pipe purge 

很抱歉，根据这个错误信息，我可以确定是由于使用了create而引起的错误，但是我无法知道为什么在存储过程中使用了create就会出错。后来，我使用了另一种方法来完成了这个任务，修改后的代码如下：

create or replace procedure create_tmp_tb
as
    rowCount number(10);
begin
    select count(1) into rowCount from all_tables where 
        TABLE_NAME = 'TP_201508' and OWNER='JELLY'; 
    if rowCount=1 then
        dbms_output.put_line('drop table tp_201508');
        execute immediate 'drop table TP_201508';
    end if; 
    execute immediate 'create table TP_201508(id int, name varchar2(20))';
end;

为什么在存储过程中直接使用create就不行，而使用execute immediate来直接就OK了呢？这就关系到这里总结的动态SQL的问题。说到动态SQL，就需要先说说静态SQL。

静态SQL

学过高级语言（C++、Java等）的都知道静态编译和动态这么回事，静态SQL就如静态编译一样，在编译时，静态SQL语句已经被解析和验证过。像我们平时写的DML、TCL等语句都是静态SQL；但是悲剧的是，静态SQL不支持DDL语句，现在你就应该知道上面的代码中使用create为什么不行了吧。既然静态SQL中不能使用DDL语句，那么现在有这个需求怎么办？好了，这个时候就需要说到今天的主角——动态SQL了。

动态SQL

动态SQL语句在编译时，并不知道SQL语句的内容，SQL语句的内容“不确定”，只有在运行时，才建立、解析并执行SQL语句。利用动态SQL，在存储过程中，可以动态创建表、视图、触发器等。

动态SQL主要用在以下两种场景：

• 编译时，无法确定SQL语句的内容
• 静态SQL不支持的SQL语句，就比如上面代码中的create

我们可以看到，静态SQL在编译时就已经提前检查了SQL正确性，以及涉及的数据库对象和对应的权限关系，而动态SQL则需要在运行的时候才能判断，所以，静态SQL的效率高于动态SQL。说了这么多概念的东西，我们现在就来实际看看如何编写动态SQL，以及如何运行动态SQL。

编写本地动态SQL

编写动态SQL有两种方式方法：

• 本地动态SQL，用于建立和执行SQL语句；本地动态SQL使用EXECUTE IMMEDIATE命令来执行动态SQL语句
• 使用包DBMS_SQL中的方法来执行动态SQL语句（没有精力总结，请自行Google）

下面我们就先来说说本地动态SQL的编写和执行。首先来一段最简单，也没有任何实际作用的SQL代码：

create or replace procedure insert_data(id varchar2, name varchar2, sex varchar2, age number)
as
    strSQL varchar2(32766);
begin
    strSQL := 'insert into jelly.tb_student values(:id, :name, :sex, :age)';
    execute immediate strSQL using id, name, sex, age;
    commit;
end;

这段代码和开始那段建立表的SQL代码又有点不一样，这里在execute immediate语句中多了个using关键字。这里使用的using关键字就是所谓的“占位符”，顾名思义，就是占住位置的符号。一般我们在使用动态SQL时，都需要拼接一个字符串，在拼接字符串的过程中，会将很多的变量拼接进来，而这些变量是一般都是外部传递进来的；如果将这些变量都使用字符串拼接符号||连接在一起，则显的代码比较乱，同时也利于变量的统一管理，以及代码的后期维护，所以我们可以使用占位符这种方式。

在上面的代码中，:id、:name、:sex和:age都是占位符，占位符必须以冒号开始，名字倒是无所谓。使用了占位符以后，就需要在execute immediate语句后面使用using将参数传递进去，参数将与占位符一一对应。但是有一点我们需要谨记，绑定参数不能是表名、列名、数据类型等，绑定参数只能是值、变量或者表达式。用DDL语句动态创建对象时，应该使用连接运算符||，最好不要使用绑定参数。

有的时候，我们执行的动态SQL语句有返回值，那么如何将这个返回值赋值给变量呢？看下面这两段代码就可以搞定这个问题。

代码片段一：

create or replace procedure get_data(stuid varchar2)
as
    strSQL varchar2(32767);
    strID varchar2(50);
    strName varchar2(50);
    strSex varchar2(10);
    iAge number(3);
begin
    strSQL := 'select id, name, sex, age from tb_student where id=:a';
    execute immediate strSQL into strID, strName, strSex, iAge using stuid;
    dbms_output.put_line('ID:' || strID || ' ;Name:' || strName || ' ;Sex:' || strSex || ' ;Age:' || iAge);
end;

这里我将select得到的值保存在变量中，但是这里只能返回一行记录，如果有多行数据返回，则会出现异常。

代码片段二：

create or replace procedure update_data(stuid varchar2, age number)
as
    strSQL varchar2(32767);
    strID varchar2(50);
    strName varchar2(50);
    strSex varchar2(50);
begin
    strSQL := 'update tb_student set age=:a where id=:b returning id, name, sex into :c, :d, :e';
    execute immediate strSQL using age, stuid returning into strID, strName, strSex;
    execute immediate 'commit'; -- 这样也是可以的
    dbms_output.put_line('ID:' || strID || ' ;Name:' || strName || ' ;Sex:' || strSex);
end;

这样的话，我们就可以将更新之后的值，以及更新对应记录的值返回到变量中；在上面的代码中，我使用了一个returning into的关键语句，这个用起来比较简单。returning into语句的主要作用是：

• delete操作：returning返回的是delete之前的结果
• insert操作：returning返回的是insert之后的结果
• update操作：returning语句是返回update之后的结果

但是问题又来了，我们假定的是更新一条数据，如果更新的数据有两条，甚至多条时，这个时候上面的存储过程就会运行出错。这个时候怎么办？这又是一个难点。

如果动态SQL语句是一个查询语句，并且返回多行记录，可以使用带有子句bulk collect into的execute immediate语句。采用bulk collect into可以将查询结果一次性地加载到集合中，我们可以在select into、fetch into、returning into语句中使用bulk collect into；但是需要特别注意的是，在使用bulk collect into时，所有的into变量都必须是集合类型。废话少说，直接通过代码来说明怎么使用就好了。

create or replace procedure get_multi_data(stuid varchar2)
is
    strSQL varchar2(32767);
    type tb_student_type is table of tb_student%rowtype;
    student_array tb_student_type;

begin
    strSQL := 'select id, name, sex, age from tb_student where id=:a';
    execute immediate strSQL bulk collect into student_array using stuid;

    for i in student_array.first .. student_array.last loop
        dbms_output.put_line('ID:' || student_array(i).id 
                            || ' ;Name:' || student_array(i).name 
                            || ' ;Sex:' || student_array(i).sex 
                            || ' ;Age:' || student_array(i).age);
    end loop;
end;

除了使用bulk collect into这种方法，我们还可以使用游标的方式，具体的代码示例请参考《Oracle学习笔记——批处理利器游标》文章中的最后一段代码。

总结

对于平时工作来说，动态SQL已经是属于比较高级的东西了，并且在实际工作中使用的也比较简单，但是如果掌握了本文中所说的关于动态SQL的所有内容，那么在实际工作中，解决一些比较麻烦的问题，你会多一种思路，多一种方式去思考和解决问题。对于动态SQL来说，希望大家玩的愉快。

2015年8月11日 于呼和浩特。