动态SQL
语句处理:根据要处理的sql语句的作用不同,可以使用三种不同类型的动态sql方法:使用execute immediate语句可以处理包括ddl(create、alter和drop)、DCL(grant、revoke)、DML(insert、update、delete)以及单行select语句;使用open cursorname for sql_statement语句可以处理多行查询操作;使用批量动态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
execute
immediate
'create table t11(f1 integer)'
;
execute
immediate
'drop table t11'
;
end
;
示例2:使用execute immediate执行dcl语句
begin
execute
immediate
'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'
;
execute
immediate
'update t11 set f2 = :af2 where f1 = :af1 returning f2 into :af3 '
using
af2,af1
returning
into
af3 ;
dbms_output.put_line(af3);
end
;
示例4:使用动态游标处理多行查询类动态sql语句。
declare
type
myrefcursor
is
ref
cursor
;
refcursor myrefcursor;
rec_t11 t11%
rowtype
;
begin
open
refcursor
for
'select * from t11'
;
loop
fetch
refcursor
into
rec_t11;
exit
when
refcursor%
notfound
; dbms_output.put_line(rec_t11.f1||
','
||rec_t11.f2||
','
||rec_t11.f3);
end
loop
;
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
table
of
t11%
rowtype
;
t11_table t11_table_type;
af2
varchar2
(
10
);
begin
af2 :=
'23'
;
execute
immediate
'select * from t11 where f2=:Af2'
bulk
collect
into
t11_table
using
af2;
for
i
in
1
..t11_table.
count
loop
dbms_output.put_line(t11_table(i).f1||
','
||t11_table(i).f2||
','
||t11_table(i).f3);
end
loop
;
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
is
table
of
t11.f1%
type
;
type
type_t11_table
is
table
of
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
in
1
..t11_f1.
count
execute
immediate
'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.
last
loop
dbms_output.put_line(t11_table(i));
end
loop
;
end
;
dwang
dsafwang
asdfwang
asdfwang
liasdfwang
liwang
liwang
liwang
asdfasdfwang
asdfasdfwang
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/241379/viewspace-722823/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/241379/viewspace-722823/