oracle procedure plsql 動態sql之動態傳遞表

SQL> create or replace procedure proc_sql
  2  as
  3  v_tab varchar2(100);
  4  begin
  5    v_tab:='zxy';
  6  execute immediate 'create table :tab(a int)' using v_tab;
  7  end;
  8  /
 
Procedure created
 
SQL> exec proc_sql;
 
begin proc_sql; end;
 
ORA-00903: invalid table name
ORA-06512: at "TBS_AUTO.PROC_SQL", line 6
ORA-06512: at line 2

小結;不能用繫結變數方式傳入表名

SQL> create or replace procedure proc_sql
  2  as
  3  v_tab varchar2(100);
  4  v_sql varchar2(4000);
  5  begin
  6  v_tab:='zxy';
  7  execute immediate 'create table '||v_tab||'(a int)';
  8  end;
  9  /
 
Procedure created
 
SQL> exec proc_sql;
 
begin proc_sql; end;
 
ORA-01031: insufficient privileges
ORA-06512: at "TBS_AUTO.PROC_SQL", line 7
ORA-06512: at line 2

 

小結:使用拼接字串可以建立動態sql

         但若執行動態sql所屬的儲存過程,即提上述錯誤

 

SQL> create or replace procedure proc_sql
  2  AUTHID CURRENT_USER is
  3  v_tab varchar2(100);
  4  v_sql varchar2(4000);
  5  begin
  6  v_tab:='zxy';
  7  execute immediate 'create table '||v_tab||'(a int)';
  8  end;
  9  /
 
Procedure created
 
SQL> exec proc_sql;
 
PL/SQL procedure successfully completed
 
SQL> desc zxy;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y

小結:新增選項AUTHID CURRENT_USER is
         即要正確執行