oracle动态sql语句处理

wxin

动态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
　　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 isrefcursor;
　　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 istableof t11%rowtype;
　　t11_table t11_table_type;
　　af2 varchar2(10);
　　begin
　　af2 := '23';
　　executeimmediate'select * from t11 where f2=:Af2'bulkcollectinto t11_table
　　using af2;
　　for i in1..t11_table.countloop    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)
　　returningbulkcollectinto 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