oracle动态sql语句处理——47522341的专栏在去dba的路上

动态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