需求是: 我想要创建一个存储过程, 在这个存储过程里要完成两件事情, 一要创建一个表, 二要在这个表里添加数据;
- create or replace procedure ctab as
- i number;
- begin
- execute immediate 'create table dd as
- select * from dept';
- execute immediate 'alter table dd add constraint pk_dd primary key(deptno)';
- for i in 901 .. 10000 loop
- insert into dd values (i, 'TEST', 'BEIJING');
- end loop;
- commit;
- end;
编译报错, 提示表 DD 不存在.
编译器认为这个表 DD 是没有创建, 所以报错. 下面是修改过的存储过程.
- create or replace procedure ctab as
- i number;
- begin
- select count(*) into i from user_tables where table_name = 'DD';
- if i = 0 then
- execute immediate 'create table dd as
- select * from dept';
- execute immediate 'alter table dd add constraint pk_dd primary key(deptno)';
- for i in 901 .. 10000 loop
- insert into dd values (i, 'TEST', 'BEIJING');
- end loop;
- commit;
- end if;
- end;
问题解决.
来源: http://www.bubuko.com/infodetail-2619013.html