Oracle PL/SQL Dynamic SQL 動態語法 1

在 Oracle PL/SQL 中要使用動態 SQL,

早期會利用 dbms_sql package, 但是麻煩又不好用,

在 Oracle Database 8i 之後提供用 execute immediate 這種超簡單的方式,

執行語法, 如下 :

 語法

execute immediate sqlString
[[bulk collect] into variable]
[using [in | out | in out] variable];

其中,
1) into: 接收 Select 單筆資料
2) bulk collect into: 接收 Select 多筆資料
3) in: 輸入變數
4) out: 輸出變數
5) 若沒有指定 in / out, 預設為 in
6) 變數是 array 也可以

 簡單範例

-- 步驟 1 : 動態建立 Temp Table (DDL)
declare
    v_sql varchar2(255) := 'create table tom_table (
                              a number
                            , b varchar2(10)
                            )';
begin
    execute immediate v_sql;
end;


-- 步驟 2 : 動態新增資料 (DML)
declare
    v_sql varchar2(255) := 'insert into tom_table values ( 100, ''aa'' )';
begin
    execute immediate v_sql;
    
    -- 利用 :1, :2, ... 做為輸入變數
    v_sql := 'insert into tom_table values( :1, :2 )';
    
    -- 利用 using 傳入輸入變數值
    execute immediate v_sql using 200, 'xx';
    
    execute immediate v_sql using 200, 'yy';
    
    -- 最好下 commit, 否則不同 session 會抓不到資料
    commit;
end;


-- 步驟 3 : 動態查詢
declare
    v_sql   varchar2(255) := 'select b from tom_table where a = :1';
    v_value varchar2(10);
begin
    -- 利用 into 取得 Select 值, using 傳入輸入變數值
    
    begin
      execute immediate v_sql into v_value using 100;
      dbms_output.put_line( v_value );
    exception
      when others then
        dbms_output.put_line( '錯誤訊息 1 : ' || SQLERRM );
    end;
    
    begin
      execute immediate v_sql into v_value using 200;
      dbms_output.put_line( v_value );
    exception
      when others then
        dbms_output.put_line( '錯誤訊息 2 : ' || SQLERRM );
    end;
    
    begin
      execute immediate v_sql into v_value using 300;
      dbms_output.put_line( v_value );
    exception
      when others then
        dbms_output.put_line( '錯誤訊息 3 : ' || SQLERRM );
    end;
end;


-- 步驟 4 : 動態 Drop Table (DDL)
declare
    v_sql varchar2(255) := 'drop table tom_table';
begin
    execute immediate v_sql;
end;

 注意事項

1) 若 sqlString 裡面為 create table or create view, 則執行動態 SQL 的 DB Owner 必須有 create any table or create any view 的 system privilege, 否則會有 ora-01031 的錯誤.
2) 若 sqlString 裡面為 drop table or drop view, 則執行動態 SQL 的 DB Owner 必須有 drop any table or drop any view 的 system privilege, 否則會有 ora-01031 的錯誤.
3) 11g 以前, sqlString 必須為 varchar2 or long, 且最大長度為 32767; sqlString 若超過 32k, 則建議將 sqlString 拆成多個來執行.
4) 11g 以後, sqlString 也可以為 clob, 這樣就無 32k 限制.
5) 若是 DML, 記得在 DML 的 dynamic SQL 執行之後, 最好下一個 Commit, 讓不同 Session 也能即時得到最新的資料.

其他 Dynamic SQL 介紹文章 :

進階 1：動態執行 Procedure / Function.

進階 2：動態執行 Begin ~ End.

進階 3：動態取得多筆資料.

昭佑.天翔