Question

谁能帮我看看这个oracle存储过程那里出错啦！！急·急·急

谁能帮我看看这个存储过程那里出错啦！很急啊···
麻烦各位高手帮我修改修改一下咯~
谢谢啦···

1 create or replace procedure changeFirst
2 as
3 count1 int ;
4 count2 int;
5 v_sql varchar(2000);
6 begin
7 v_sql:='create table newFirst as select * from yu_first where
sys_date-fdate<=29000';
8 execute immediate v_sql;
9 select count(*) into count1 from yu_first where sys_date-

fdate<=29000;
10 select count(*) into count2 from newFirst;
11 if count1=count2
12 then
13 truncate table yu_first;
14 insert into yu_first select * from newFirst;
15 commit;
16 select count(*) into count1 from yu_first;
17 select count(*) into count2 from newFirst;
18 if count1=count2
19 then
20 drop table newFirst;
21 commit;
22 end if;
23 end if;
24 end changeFirst;
25 /

Warning: Procedure created with compilation errors ---这个是错误信息！

Answer 1

create or replace PROCEDURE CHANGEFIRST
as
count1 int;
count2 int;
v_table_name varchar2(48);
v_sql varchar(2000);
begin
v_sql:='create table newFirst as select * from fruit where to_char(sysdate,''yyyymmdd'')-to_char(fdate,''yyyymmdd'')<=29000';
execute immediate v_sql;
select count(*) into count1 from fruit where to_char(sysdate,'yyyymmdd')-to_char(fdate,'yyyymmdd')<=29000;
v_sql:='select count(*) into count2 from '||v_table_name||'';
select table_name into v_table_name from tabs where table_name ='NEWFIRST';
if count1=count2
then
v_sql:='truncate table fruit';
execute immediate v_sql;

v_sql:='insert into fruit select * from '||v_table_name||'';
execute IMMEDIATE v_sql;
commit;

select count(*) into count1 from fruit;
v_sql:='select count(*) into count2 from '||v_table_name||'';
if count1=count2
then
v_sql:='drop table '||v_table_name||'';
execute immediate v_sql;
commit;

end if;
end if;
end changeFirst;

这个执行前，先执行
grant create any table to 你现在用的用户;
用dba用户执行上边这句

存在一个问题，当执行的时候，你这个存储过程只能执行一次，因为前边是create table ，当你执行完一次以后，这张表已经存在的时候，你第二次create table 的时候就会报表名已存在的错误了