很多時候我們需要在存儲過程中使用動態(tài)的SQL，要怎么用好動態(tài)SQL呢，怎樣執(zhí)行效率最高呢。下面來介紹一下，如何使用動態(tài)SQL操作數(shù)據(jù)庫。

在存儲過程中執(zhí)行一個簡單的SQL語句使用下面的方法即可。

DECLARE BEGIN/* 使用該方法來創(chuàng)建一個新數(shù)據(jù)表，但這方法需要當(dāng)前用戶具有創(chuàng)建表的權(quán)限。 */EXECUTE IMMEDIATE 'create table test (data1 number, data2 number)';END; / DECLARE BEGIN/* 使用這個方法刪除一個已存在的數(shù)據(jù)表，需要刪除權(quán)限。 */EXECUTE IMMEDIATE 'drop table test';END; /

SQL文中不能對數(shù)據(jù)表名使用變量替換方法，則如需動態(tài)填入表名，實現(xiàn)方法如下。
create table :tbl1 ... 是不被允許的。

DECLAREtbl1 VARCHAR2(40);sql1 VARCHAR2(2000); BEGINtbl1 := 'test';sql1 := 'create table ' || tbl1 || ' (data1 number, data2 number)';EXECUTE IMMEDIATE sql1;END; /

使用變數(shù)替換帶入數(shù)值的方式實現(xiàn)方法如下列代碼。

DECLAREsql1 VARCHAR2(2000); BEGINsql1 := 'insert into test values (:data1, :data2)';EXECUTE IMMEDIATE sql1 USING 100, 200;END; /

對與帶入的數(shù)值，可以使用其他的變量代替?zhèn)魅搿?br /> 如下代碼使用變量帶入?yún)?shù)。

DECLAREdata1 NUMBER;data2 NUMBER;sql1 VARCHAR2(2000); BEGINdata1 := 100;data2 := 200;sql1 := 'insert into test values (:data1, :data2)';EXECUTE IMMEDIATE sql1 USING data1, data2;END; /

執(zhí)行結(jié)果可以被取得，但如果不是返回單行記錄是，會報出錯誤信息。

SET serveroutput ON;DECLAREtest_rec test%ROWTYPE;sql1 VARCHAR2(2000); BEGINsql1 := 'select * from test where field1 = :data1';EXECUTE IMMEDIATE sql1 INTO test_rec USING 100;dbms_output.put_line ('-- 取得データ --');dbms_output.put_line (test_rec.field1);dbms_output.put_line (test_rec.field2);END; /

如果返回結(jié)果為多行數(shù)據(jù)，則應(yīng)使用open for 和 fetch方法。

SET serveroutput ON; /* 抽出一列數(shù)據(jù)的時候 */ DECLARETYPE cutype IS REF CURSOR;cv cutype;item1 testm.key1%TYPE; BEGINOPEN cv FOR'select key1 from testm where key1 like :data1'USING 'a%';LOOPFETCH cv INTO item1;EXIT WHEN cv%notfound;dbms_output.put_line (item1);END LOOP;CLOSE cv;END; / SET serveroutput ON; /* 整行數(shù)據(jù)抽出時 */ DECLARETYPE cutype IS REF CURSOR;cv cutype;rec テストm%ROWTYPE; /*行模型*/ BEGINOPEN cv FOR'select * from テストm where キー like :data1'USING 'a%';LOOPFETCH cv INTO rec;EXIT WHEN cv%notfound;dbms_output.put_line (rec.キー);END LOOP;CLOSE cv;END;

更新SQL執(zhí)行結(jié)果也可以被取得。

codingset serveroutput ON;DECLAREsql1 VARCHAR2(2000);ret1 NUMBER; BEGINsql1 := 'update test set data2 = :data2 where data1 = :data1returning data2 into :ret1';EXECUTE IMMEDIATE sql1 USING 11, 100 RETURNING INTO ret1;dbms_output.put_line ('-- 取得データ --');dbms_output.put_line (ret1);END; /

總結(jié)

以上是生活随笔為你收集整理的动态 SQL、EXECUTE IMMEDIATE、using、into、returning的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。