帮忙把这个sybase的存储过程改写成oracle的,谢谢create procedure proc_deloverdata
as
begin
declare @deldate datetime
declare @nowtime datetime
declare @tablemame varchar(64)
declare @sqlStr varchar(1024)
declare @maxsaveday int select @nowtime = getdate()
select @maxsaveday=timeapart from delRecordPolicy
if @maxsaveday is null
set @maxsaveday=60 select @deldate=convert(char,dateadd(dd,-@maxsaveday,@nowtime),112) dump tran zxinmeasure with no_log
set rowcount 1000 declare mopdatatab_cusor cursor for select distinct tablename from mop_typeset
open mopdatatab_cusor
fetch mopdatatab_cusor into @tablemame while @@sqlstatus = 0
begin
while 1=1
begin
begin tran mop_del
set @sqlStr = 'lock table ' + @tablemame + ' in exclusive mode '
+ ' delete from ' + @tablemame + ' where sdate < @deldate '
execute(@sqlStr)
if @@rowcount = 0
begin
commit
break
end
commit
waitfor delay '00:00:01'
end
dump tran zxinmeasure with no_log
fetch mopdatatab_cusor into @tablemame
end close mopdatatab_cusor
deallocate cursor mopdatatab_cusor
set rowcount 0
end
go
as
begin
declare @deldate datetime
declare @nowtime datetime
declare @tablemame varchar(64)
declare @sqlStr varchar(1024)
declare @maxsaveday int select @nowtime = getdate()
select @maxsaveday=timeapart from delRecordPolicy
if @maxsaveday is null
set @maxsaveday=60 select @deldate=convert(char,dateadd(dd,-@maxsaveday,@nowtime),112) dump tran zxinmeasure with no_log
set rowcount 1000 declare mopdatatab_cusor cursor for select distinct tablename from mop_typeset
open mopdatatab_cusor
fetch mopdatatab_cusor into @tablemame while @@sqlstatus = 0
begin
while 1=1
begin
begin tran mop_del
set @sqlStr = 'lock table ' + @tablemame + ' in exclusive mode '
+ ' delete from ' + @tablemame + ' where sdate < @deldate '
execute(@sqlStr)
if @@rowcount = 0
begin
commit
break
end
commit
waitfor delay '00:00:01'
end
dump tran zxinmeasure with no_log
fetch mopdatatab_cusor into @tablemame
end close mopdatatab_cusor
deallocate cursor mopdatatab_cusor
set rowcount 0
end
go
procedure proc_deloverdata
is
deldate date;
nowtime date;
tablemame varchar2(64);
sqlStr varchar2(1024);
maxsaveday number;
cursor mopdatatab_cusor is select distinct tablename from mop_typeset;
begin select sysdate into nowtime from dual;
select timeapart into maxsaveday from delRecordPolicy;
if maxsaveday is null then
maxsaveday:=60;
end if;
select sydate-maxsaveday into deldate from dual; open mopdatatab_cusor;
fetch mopdatatab_cusor into tablemame; while mopdatatab_cusor %found loop
sqlStr := 'lock table ' + tablemame + ' in exclusive mode '
+ ' delete from ' + tablemame + ' where sdate < @deldate ';
execute(sqlStr);
fetch mopdatatab_cusor into tablemame;
end loop
commit;
close mopdatatab_cusor;
end;