当然可行咯,给你个例子: 3. 本地动态SQL a. create or replace procedure dsql1 is --v_cursor integer; begin execute immediate 'create table TABLE_A(name char(2))'; dbms_output.put_line('successful !'); exception when others then dbms_output.put_line('error !!!'); end;
就用drop table好了,不过你create table的时候一定要有create any table的权限,否则是不行的
是这样的吗? create or replace procedure dsql1 is --v_cursor integer; begin execute immediate 'create table TABLE_A(name char(2))'; dbms_output.put_line('successful !'); exception when others then execute immediate 'drop table TABLE_A'; dbms_output.put_line('error !!!'); end;
--v_cursor integer; 这句是什么意思呢? 还需要游标吗?
select count(*) into a from user_tables where table_name ='TABLE_A'if a<1 then execute immediate 'drop table TABLE_A';else execute immediate 'create table TABLE_A (a varchar2(10))';end if;
create or replace procedure dsql1 is --v_cursor integer; begin execute immediate 'create table TABLE_A(name char(2))'; dbms_output.put_line('successful !'); exception when others then execute immediate 'drop table TABLE_A'; dbms_output.put_line('error !!!'); end;不能这样,因为异常情况不一定是表存在还是还是使用if 来判断
注意: 别忘了在 drop table 前备份数据
发生了很奇怪的事情: create or replace procedure pro_yy is begin execute immediate 'create table a007 as select b.sn from emp b'; end yy;可是在PL/SQL中提示"编译成功",但在命令窗口EXEC PRO_YY; 后总提示:(1)ORA01031----"权限不足" (2)ORA06512----在ZUO.PRO_YY, LINE 4 (3)ORA06512----在LINE 1而后我检查了ZUO的权限,他已经有了"create any table"的权限! why??
3. 本地动态SQL
a.
create or replace procedure dsql1
is
--v_cursor integer;
begin
execute immediate 'create table TABLE_A(name char(2))';
dbms_output.put_line('successful !');
exception
when others then
dbms_output.put_line('error !!!');
end;
create or replace procedure dsql1
is
--v_cursor integer;
begin
execute immediate 'create table TABLE_A(name char(2))';
dbms_output.put_line('successful !');
exception
when others then
execute immediate 'drop table TABLE_A';
dbms_output.put_line('error !!!');
end;
这句是什么意思呢?
还需要游标吗?
execute immediate 'drop table TABLE_A';else
execute immediate 'create table TABLE_A (a varchar2(10))';end if;
is
--v_cursor integer;
begin
execute immediate 'create table TABLE_A(name char(2))';
dbms_output.put_line('successful !');
exception
when others then
execute immediate 'drop table TABLE_A';
dbms_output.put_line('error !!!');
end;不能这样,因为异常情况不一定是表存在还是还是使用if 来判断
create or replace procedure pro_yy
is
begin
execute immediate 'create table a007 as select b.sn from emp b';
end yy;可是在PL/SQL中提示"编译成功",但在命令窗口EXEC PRO_YY;
后总提示:(1)ORA01031----"权限不足"
(2)ORA06512----在ZUO.PRO_YY, LINE 4
(3)ORA06512----在LINE 1而后我检查了ZUO的权限,他已经有了"create any table"的权限!
why??
SORROY !
是我忘了授权!
现在成功了!
谢谢大家!