oracle動態sql語句處理（轉）

47328983發表於2012-05-06
OracleSQL
動態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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/241379/viewspace-722823/，如需轉載，請註明出處，否則將追究法律責任。

相關文章