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博客

oracle procedure plsql 动态sql之动态传递表

最新文章