Oracle 传动态列名,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

即要正确运行