--动态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;
/
posted on 2009-07-21 14:15
super_nini 阅读(912)
评论(0) 编辑 收藏