求一个存储过程

[Liyingyue_FFS]

帮忙把这个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

[bw555]

大概写了下，没测试

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;

[Adebayor]

[Quote=引用 1 楼 bw555 的回复:]
大概写了下，没测试
SQL code
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 t…
[/Quote]
正解