UC知道谁能帮我看看这个存储过程 那里出问题了！急！急！急！急！在线等答案
来源：百度知道 编辑：UC知道 时间：2024/09/26 16:24:45
SQL> create or replace procedure waretoshiyan
2 as
3 count1 int;
4 count2 int;
5 begin
6 create table shiyan as select * from ware_info where sysdate-wbegintime<=90;
7 select count(*) into count1 from ware_info where sysdate-wbegintime<=90;
8 select count(*) into count2 from shiyan;
9 if(count1=count2)then
10 delete from ware_info where sysdate-wbegintime<=90;
11 end if;
12 end waretoshiyan;
13 /
2 as
3 count1 int;
4 count2 int;
5 begin
6 create table shiyan as select * from ware_info where sysdate-wbegintime<=90;
7 select count(*) into count1 from ware_info where sysdate-wbegintime<=90;
8 select count(*) into count2 from shiyan;
9 if(count1=count2)then
10 delete from ware_info where sysdate-wbegintime<=90;
11 end if;
12 end waretoshiyan;
13 /
create table shiyan as select * from ware_info where sysdate-wbegintime<=90;
这地方不能直接用，必须用动态sql执行
delete from ware_info where sysdate-wbegintime<=90;
还有这个地方，你delete后必须要commit，否则跟没执行一样，兴许还会锁表
create or replace procedure waretoshiyan
as
count1 int;
count2 int;
v_sql varchar2(2000);
begin
v_sql:='create table shiyan as select * from ware_info where to_char(sysdate-wbegintime,''yyyymmdd'')<=90';
EXECUTE IMMEDIATE v_sql;
commit;
select count(*) into count1 from ware_info where to_char(sysdate-wbegintime,'yyyymmdd')<=90;
select count(*) into count2 from shiyan;
if count1=count2
then
delete from ware_info where to_char(sysdate-wbegintime,'yyyymmdd')<=90;
commit;
end if;
end waretoshiyan;
/