注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

数据挖掘

学习数据挖掘

 
 
 

日志

 
 

动态SQL语句,及批量数据处理  

2011-08-29 15:21:26|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
动态SQL语句处理:根据要处理的sql语句的作用不同,可以使用三种不同类型的动态sql方法:
1。使用execute immediate语句可以处理包括ddl(create、alter和drop)、DCL(grant、revoke)、DML(insert、update、delete)以及单行select语句;
2.使用open cursorname for sql_statement语句可以处理多行查询操作;
3.使用批量动态sql(forall)可以加快sql语句处理,进而提高plsql程序的性能。
*execute immediate语句:
Execute immediate dynamic_string
[into {define_variable[,define_variable]…|record}] 
[using [in|out|in out] bind_argument[,[in|out|in out] bind_argument]…]
[{returning|return} into bind_argument[, bind_argument]…]
Define_variable用于指定存放单行查询结果的变量;using in bind_argument用于指定存放传递给动态sql值的变量,即在dynamic中存在占位符时使用;using out bind_argument用于指定存放动态sql返回值的变量。


示例1:使用execute immediate执行简单ddl语句
begin
executeimmediate'create table t11(f1 integer)';
executeimmediate'drop table t11';
end;
示例2:使用execute immediate执行dcl语句
begin
executeimmediate'grant insert on t11 to scott'
end;
示例3:使用execute immediate处理dml语句时,如果dml语句包含占位符,那么在execute immediate语句之后必须要带有using子句;如果dml语句带有returning子句,那么在execute immediate语句之后必须带有returning into子句,并且此时只能处理作用的单行上的dml语句,如果dml语句作用在多行上,则必须使用bulk子句。
declare
af1 varchar2(10);
af2 varchar2(10);
af3 varchar2(10);
begin
af1 := 12;
af2 := 'csdn';
executeimmediate'update t11 set f2 = :af2 where f1 = :af1 returning f2 into :af3 '
 
using af2,af1

returninginto af3 ; 
dbms_output.put_line(af3);
end;

示例4:使用动态游标处理多行查询类动态sql语句。
declare
type myrefcursor is  refcursor;
refcursor myrefcursor;
rec_t11   t11%rowtype;
begin
open refcursor for'select * from t11';
loop
    fetch refcursor into rec_t11;
    exitwhen refcursor%notfound;   
 dbms_output.put_line(rec_t11.f1||','||rec_t11.f2||','||rec_t11.f3);
endloop;
close refcursor;
end;

oracle通过使用bulk collect into子句处理动态sql中的多行查询可以加快处理速度,从而提高应用程序的性能。当使用bulk子句时,集合类型可以是plsql所支持的索引表、嵌套表和varray,但集合元素必须使用sql数据类型。在oracle9i以后,有三种语句支持bulk子句,execute immediate,fetch和forall。

示例5:在execute immediate中使用bulk collect into处理多行查询返回结果。
declare
type t11_table_type is tableof t11%rowtype;
t11_table  t11_table_type;
af2  varchar2(10);
begin
af2 := '23';
executeimmediate'select * from t11 where f2=:Af2'  bulk   collect   into   t11_table
using af2;
for i in1..t11_table.count    loop   
 dbms_output.put_line(t11_table(i).f1||','||t11_table(i).f2||','||t11_table(i).f3);
endloop; 
end;
示例6:在forall语句中使用bulkinto语句。
1          d          wang     12
2          dsaf      wang     23
3          asdf      wang     34
4          liasdf    wang     
5          li          wang     
6          asdf      wang     
7          li          wang     
8          li          wang     
declare
type type_t11_f1 istableof t11.f1%type;
type type_t11_table istableof t11.f2%type;
t11_F1 type_t11_f1;
t11_table type_t11_table;
begin
t11_f1 := type_t11_f1('d','dsaf','asdf','liasdf','li','asdf'); 
forall i in1..t11_f1.count
    executeimmediate'update t11 set f2 = f1||f2 where f1 = :p1 returning f2 into :p2'
    using t11_F1(i)
    returning  bulk   collect   into t11_table; 
for i in t11_table.first..t11_table.lastloop
    dbms_output.put_line(t11_table(i));
endloop;
end;
dwang
dsafwang
asdfwang
asdfwang
liasdfwang
liwang
liwang
liwang
asdfasdfwang
asdfasdfwang


在一般的情况下,使用批量fetch的几率并不是很多,但是Oracle提供了这个功能我们最好能熟悉一下,说不定什么时候会用上它。

declare
cursor c1 is select * from t_depart;
v_depart t_depart%rowtype ;
type v_code_type is table of t_depart.depart_code%type ;
v_code v_code_type ;
type v_name_type is table of t_depart.depart_name%type ;
v_name v_name_type ;
begin
open c1;
fetch c1 bulk collect into v_code , v_name ;
for i in 1..v_code.count loop
dbms_output.put_line(v_code(i)||' '||v_name(i));
end loop;
close c1;
end;


通过上面的这个例子,大家可以发现如果列很多的话,为每一列定义一个集合似乎有些繁琐,可以把集合和%rowtype结合起来一起使用来简化程序!

declare
cursor c1 is select * from t_depart;
type v_depart_type is table of t_depart%rowtype ;
v_depart v_depart_type ;
begin
open c1;
fetch c1 bulk collect into v_depart ;
for i in 1..v_depart.count loop
dbms_output.put_line(v_depart(i).depart_code||' '||
v_depart(i).depart_name);
end loop;
close c1;
end;


在输出结果时,既可以使用集合的count属性和可以使用first和last,在引用%rowtype类型的内容时,还有一个需要注意的地方是v_depart(i).depart_code,而不是v_depart.depart_code(i),当然没有这样的写法,即使有意义也并不一样。 

declare
cursor c1 is select * from t_depart;
type v_depart_type is table of t_depart%rowtype ;
v_depart v_depart_type ;
begin
open c1;
fetch c1 bulk collect into v_depart ;
for i in v_depart.first..v_depart.last loop
dbms_output.put_line(v_depart(i).depart_code||' '||
v_depart(i).depart_name);
end loop;
close c1;
end;



  评论这张
 
阅读(117)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018