create or replace procedure p1
as
i number:=2;
m number:=-1;
begin
execute immediate 'create table t1(id1 number);';
insert into t1 values(3);
select count(*) into i from all_tables where table_name='t1';
select id1 into m from t1 where rownum=1;
if i>0 then
dbms_output.put_line('good1!');
elsif i>1 then
dbms_output.put_line('good2!');
else
--dbms_output.put_line('good3!');
dbms_output.put_line(m);
execute immediate 'drop table t1';
dbms_output.put_line('delete ok !');
end if;
end; ---报错:
Warning: Procedure created with compilation errors/ 如何解决啊?
我的本意是建表,添加数据,查询数据,然后删除表,怎么改呢?
as
i number:=2;
m number:=-1;
begin
execute immediate 'create table t1(id1 number);';
insert into t1 values(3);
select count(*) into i from all_tables where table_name='t1';
select id1 into m from t1 where rownum=1;
if i>0 then
dbms_output.put_line('good1!');
elsif i>1 then
dbms_output.put_line('good2!');
else
--dbms_output.put_line('good3!');
dbms_output.put_line(m);
execute immediate 'drop table t1';
dbms_output.put_line('delete ok !');
end if;
end; ---报错:
Warning: Procedure created with compilation errors/ 如何解决啊?
我的本意是建表,添加数据,查询数据,然后删除表,怎么改呢?
as
i number:=2;
m number:=-1;
begin
execute immediate 'create table t1(id1 number);';
insert into t1 values(3); ---->这一行有问题,t1表只有一列吗?从这个错误信息看,我认为t1表存在多列,插入一个值这样写语法有问题
select count(*) into i from all_tables where table_name='t1';
select id1 into m from t1 where rownum=1;
if i>0 then
dbms_output.put_line('good1!');
elsif i>1 then
dbms_output.put_line('good2!');
else
--dbms_output.put_line('good3!');
dbms_output.put_line(m);
execute immediate 'drop table t1';
dbms_output.put_line('delete ok !');
end if;
end;
SQL> create table t1(id1 number);表已创建。SQL> create or replace procedure p1
2 as
3 i number:=2;
4 m number:=-1;
5 begin
6 insert into t1 values(3);
7 commit;
8 select count(*) into i from all_tables where table_name='t1';
9 select id1 into m from t1 where rownum=1;
10 if i>0 then
11 dbms_output.put_line('good1!');
12 elsif i>1 then
13 dbms_output.put_line('good2!');
14 else
15 --dbms_output.put_line('good3!');
16 dbms_output.put_line(m);
17 execute immediate 'drop table t1';
18 dbms_output.put_line('delete ok !');
19 end if;
20 end;
21 /过程已创建。SQL>
as
i number:=2;
m number:=-1;
begin
insert into t1 values(3);
commit;
select count(*) into i from all_tables where table_name='t1';
select id1 into m from t1 where rownum=1;
if i>0 then
dbms_output.put_line('good1!');
elsif i>1 then
dbms_output.put_line('good2!');
else
dbms_output.put_line('good3!');
dbms_output.put_line(m);
execute immediate 'drop table t1';
dbms_output.put_line('delete ok !');
end if;
end;
/
execute immediate create table t1 ....
as
i number:=2;
m number:=-1;
begin
execute immediate 'create table t1(id1 number)';
execute immediate 'insert into t1 values(3)';
commit;
select count(*) into i from all_tables where table_name='T1';
execute immediate 'select id1 from t1 where rownum<=1' into m;
if i>0 then
dbms_output.put_line('good1!');
elsif i>1 then
dbms_output.put_line('good2!');
else
--dbms_output.put_line('good3!');
dbms_output.put_line(m);
execute immediate 'drop table t1';
dbms_output.put_line('delete ok !');
end if;
end; 这样就可以了,不过后面的if, elsif的逻辑有点问题,i<=0时才会进入else,进行drop table,那么肯定报错,因为这种情况下表不存在。