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
         即要正确运行

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-754699/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-754699/