oracle转存过程,(转)Oracle 动态sql创建ORACLE存储过程 - super-nini - BlogJava

--动态sql创建ORACLE存储过程.txt

create or replace procedure p3(v_tname varchar2) as

i      number;

m      number;

/*v_tname varchar2(10) := 't1';*/

e_createerror exception;

begin

execute immediate 'select count(*) from all_tables where table_name ='''

||

upper(v_tname) || '''' into i;

if i > 0 then

dbms_output.put_line('table is also exists');

execute immediate 'drop table ' || v_tname;

end if;

begin

execute immediate 'create table ' || v_tname || ' (id1 number)';

exception

when others then

raise e_createerror;

end;

execute immediate 'insert into ' || v_tname || '  values (3)';

execute immediate 'insert into ' || v_tname || '  values (3)';

execute immediate 'insert into ' || v_tname || '  values (3)';

execute immediate 'insert into ' || v_tname || '  values (3)';

commit;

execute immediate ' select count(*) from all_tables where table_name

=''' ||upper(v_tname) || ''''

into m;

if m > 0 then

--execute immediate ' select count(*) from '''||upper(v_tname) ||

'''' into m;

dbms_output.put_line('good1!');dbms_output.put_line('count of

v_tname:'||m);

end if;

exception

when e_createerror then

dbms_output.put_line('表创建语句出错请检查');

end;

/