很多時候我們需要在儲存過程中使用動態的SQL，要怎麼用好動態SQL呢，怎樣執行效率最高呢。下面來介紹一下，如何使用動態SQL運算元據庫。

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

DECLARE BEGIN

 /* 使用該方法來建立一個新資料表，但這方法需要當前使用者具有建立表的許可權。 */

 EXECUTE IMMEDIATE 'create table test (data1 number, data2 number)';

END; /

DECLARE BEGIN

/* 使用這個方法刪除一個已存在的資料表，需要刪除許可權。 */

 EXECUTE IMMEDIATE 'drop table test'; END; /

SQL文中不能對資料表名使用變數替換方法，則如需動態填入表名，實現方法如下。
create table :tbl1 ... 是不被允許的。

DECLARE tbl1 VARCHAR2(40);
  sql1 VARCHAR2(2000);

BEGIN  
  tbl1 := 'test';
  sql1 := 'create table ' || tbl1 || ' (data1 number, data2 number)';

EXECUTE IMMEDIATE sql1; END; /

使用變數替換帶入數值的方式實現方法如下列程式碼。

DECLARE sql1 VARCHAR2(2000);

 BEGIN  
  sql1 := 'insert into test values (:data1, :data2)';

 EXECUTE IMMEDIATE sql1 USING 100, 200; END; /

對與帶入的數值，可以使用其他的變數代替傳入。
如下程式碼使用變數帶入引數。

DECLARE data1 NUMBER;
  data2 NUMBER;
  sql1 VARCHAR2(2000); BEGIN  
  data1 := 100;
  data2 := 200;
  sql1 := 'insert into test values (:data1, :data2)';

EXECUTE IMMEDIATE sql1 USING data1, data2; END; /

執行結果可以被取得，但如果不是返回單行記錄是，會報出錯誤資訊。

SET serveroutput ON;
  DECLARE test_rec test%ROWTYPE;
  sql1 VARCHAR2(2000); BEGIN  
  sql1 := '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; /

如果返回結果為多行資料，則應使用open for 和 fetch方法。

SET serveroutput ON;
/* 抽出一列資料的時候 */ DECLARE TYPE cutype IS REF CURSOR;
  cv cutype;
  item1 testm.key1%TYPE; BEGIN

OPEN cv FOR 'select key1 from testm where key1  like :data1' USING 'a%';
  LOOP FETCH cv INTO item1;

EXIT WHEN cv%notfound;
    dbms_output.put_line (item1);

END LOOP; CLOSE cv; END; /

SET serveroutput ON;
/* 整行資料抽出時 */ DECLARE TYPE cutype IS REF CURSOR;
  cv cutype;
  rec テストm%ROWTYPE;  /*行模型*/

OPEN cv FOR 'select * from テストm where キー like :data1' USING 'a%';
  LOOP FETCH cv INTO rec; EXIT WHEN cv%notfound;
    dbms_output.put_line (rec.キー);

END LOOP; CLOSE cv; END;

更新SQL執行結果也可以被取得。

  codingset serveroutput ON;
  DECLARE sql1 VARCHAR2(2000);
  ret1 NUMBER; BEGIN  
  sql1 := 'update test set data2 = :data2 where data1 = :data1
    returning data2 into :ret1';

 EXECUTE IMMEDIATE sql1 USING 11, 100 RETURNING INTO ret1;
  dbms_output.put_line ('-- 取得データ --');
  dbms_output.put_line (ret1);
 END; /

動態 SQL、EXECUTE IMMEDIATE、using、into、returning

相關文章