Oracle存储过程

set serveroutput on;
call job_rely_latest(8128,201707180000);

select * from tmp_res_8128;

truncate table tmp_res_8128;

create or replace procedure job_rely_latest(job_id in number,dt in number) as sid number;
  start_time varchar2(50);
  end_time   varchar2(50);
  cnt        number;
begin sid := job_id;

  delete from tmp_res_8128;

  while sid > 0 loop
    select count(1) into cnt from dspmt.job_log t where t.job_frequency = dt and t.job_id in (select to_number(column_value) job_id from table (select strsplit(replace(replace(prerequisites,'[',''),']',',') from DSPDATA.RS_JOB_ESIMATED_TIME t where t.job_id = sid and prerequisites is not null)) and t.end_time < (select * from (select start_time from dspmt.job_log t2 where t2.job_frequency = dt and t2.job_id = sid order by start_time asc) where rownum = 1);

    if cnt < 1 then
      dbms_output.put_line('循环结束');
      return;
    end if;

    select job_id,start_time,end_time into sid,end_time from (select * from dspmt.job_log t where t.job_frequency = dt and t.job_id in (select to_number(column_value) job_id from table (select strsplit(replace(replace(prerequisites,') from DSPDATA.RS_JOB_ESIMATED_TIME t where t.job_id = sid)) and t.end_time < (select * from (select start_time from dspmt.job_log t2 where t2.job_frequency = 201707180000 and t2.job_id = sid order by start_time asc) where rownum = 1) order by t.end_time desc) where rownum = 1;
    dbms_output.put_line(sid);

    if sid < 1 then
      -- idx := 10000;
      dbms_output.put_line('循环结束');
      return;
    end if;
    insert into tmp_res_8128 select sid,end_time from dual;
    commit;

  end loop;
end job_rely_latest;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!