oracle通过job执行procedure

 

1. 先创建一个FUNCTION

CREATE OR REPLACE FUNCTION GET_TIMEOUT_PROGRAM(i_customerNo IN TK_CUST_PROG_D.CUSTOM_NO%TYPE,
                                               i_programId  IN TK_CUST_PROG_D.Prog_Id%TYPE,
                                               i_timeout    IN TK_CUST_PROG_D.PROG_TIMEOUT%TYPE)
  RETURN number AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_doresult       NUMBER := 1;
  v_station        SMALL_PROGRAM_STATUS.Station%TYPE;
  v_programId      SMALL_PROGRAM_STATUS.Program_Id%TYPE;
  v_lastActiveTime date;
  v_spanMinutes    NUMBER;
BEGIN
  BEGIN
    SELECT Station,
           PROGRAM_ID,
           Last_Active_Time,
           floor(to_number(sysdate - LAST_ACTIVE_TIME) * 24 * 60)
      INTO v_station, v_programId, v_lastActiveTime, v_spanMinutes
      from SMALL_PROGRAM_STATUS
     WHERE STATION = i_customerNo
       AND PROGRAM_ID = i_programId
       AND floor(to_number(sysdate - LAST_ACTIVE_TIME) * 24 * 60) >
           i_timeout;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return 0;
    when TOO_MANY_ROWS then
      DBMS_OUTPUT.PUT_LINE('More than one record.');
  END;

  merge into SMALL_PROGRAM_TASK t1
  using (select i_customerNo as station,
                i_programId as program_id,
                'N' send_flag
           from dual) t2
  on (t1.station = t2.station and t1.program_id = t2.program_id and t1.send_flag = t2.send_flag)
  when matched then
    update
       set t1.last_active_time = v_lastActiveTime,
           t1.program_timeout  = i_timeout,
           t1.expired_time     = v_spanMinutes,
           t1.modify_date      = sysdate()
  when not matched then
    insert
      (job_no,
       station,
       program_id,
       last_active_time,
       send_flag,
       program_timeout,
       create_date,
       expired_time)
    values
      (sys_guid(),
       v_station,
       v_programId,
       v_lastActiveTime,
       'N',
       i_timeout,
       sysdate(),
       v_spanMinutes);
  commit;
  DBMS_OUTPUT.PUT_LINE('v_doresult=' || v_doresult);
  DBMS_OUTPUT.PUT_LINE('v_station=' || v_station);
  DBMS_OUTPUT.PUT_LINE('v_spanMinutes=' || v_spanMinutes);
  RETURN v_doresult;
END;

2. 创建procedure

CREATE OR REPLACE PROCEDURE get_expired_program AS
   v_timeout TK_CUST_PROG_D.PROG_TIMEOUT%TYPE;
   v_customerNo TK_CUST_PROG_D.CUSTOM_NO%TYPE;
   v_programId TK_CUST_PROG_D.Prog_Id%TYPE;
   v_doresult number;
   v_records_sum number := 0;
   CURSOR c_cursor IS SELECT CUSTOM_NO,Prog_Id,PROG_TIMEOUT FROM TK_CUST_PROG_D WHERE MONITOR_PROGRAM='Y'; 
 BEGIN 
   OPEN c_cursor;
   LOOP
     FETCH c_cursor INTO v_customerNo,v_programId,v_timeout;
     EXIT WHEN c_cursor%NOTFOUND; 
     begin
       select GET_TIMEOUT_PROGRAM(v_customerNo,v_programId,v_timeout) into v_doresult from dual;
       v_records_sum := v_doresult;
     end;
   END LOOP;
   dbms_output.put_line('records_num: "' || v_records_sum || '"');
   CLOSE c_cursor;
 END;

 

3.创建调用定时排程调用这个Procedure，每五分钟调用一次procedure

begin
  sys.dbms_scheduler.create_job(job_name            => 'PLOEC.GET_EXPIRED_PROGRAM_TASK',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'get_expired_program',
                                start_date          => to_date('06-03-2017 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Minutely;Interval=5',
                                end_date            => to_date('07-03-2049 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                job_class           => 'DBMS_JOB$',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '每五分鐘掃呼叫get_expired_program一次');
end;
/

 

Function或者Procedure在compile时候出错，可以通过以下SQL查看errors detail

show errors function GET_TIMEOUT_PROGRAM;
show errors procedure get_expired_program;

 

Call Procedure

exec get_expired_program;

 

Test Function 

declare
  outNumber number;
begin
  select GET_TIMEOUT_PROGRAM('TPE','P0001',90) into outNumber from dual;
  dbms_output.put_line('outNumber: "' || outNumber || '"');
end;

 

View schedule log

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'GET_EXPIRED_PROGRAM_TASK';

 

FROM