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, start_time, 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, start_time, end_time from dual;    commit;  end loop;end job_rely_latest;