果冻想
认真玩技术的地方

Oracle学习笔记——动态SQL

每天都在学习

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

还记得那几天没日没夜的配报表的时候,总是参考着以前的人写的存储过程来写自己的存储过程,大体形式都差不多,一个很长的由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 intoexecute immediate语句。采用bulk collect into可以将查询结果一次性地加载到集合中,我们可以在select intofetch intoreturning 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日 于呼和浩特。

未经允许不得转载:果冻想 » Oracle学习笔记——动态SQL
网站维护离不开您的支持,您可以赞助本站,谢谢支持
×

感谢您的支持,我们会一直保持!

扫码支持
请土豪扫码随意打赏

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

赞助本站
关注微信公众号
关注微信公众号和果冻一起分享你的疑惑与心得。
分享到: 更多 (0)

玩技术,我们是认真的

联系我们关于果冻