怎样写这样的一个定时job

Question

declare
  v_msg varchar2(200);
  v_msg1 varchar2(200);
begin
  for cur in (select * from wbjol_id) loop
    RECOVERSODATAFROMSOHIS.PROCRECOVERFIRST(cur.ol_id,
                         v_msg,
                         v_msg1);
  end loop;
end;
这个是游标调用存储过程  RECOVERSODATAFROMSOHIS.PROCRECOVERFIRST这个是存过
CREATE OR REPLACE PACKAGE BODY RECOVERSODATAFROMSOHIS IS
  PROCEDURE PROCRECOVERFIRST(OLID      IN NUMBER,
                             RESULTID  OUT VARCHAR2,
                             RESULTMSG OUT VARCHAR2) IS
    STR_SQL          VARCHAR2(1000);
    TABLE_ID         TRANSFER_SO_TABLE.ID%TYPE;
    TABLE_NAME       TRANSFER_SO_TABLE.TABLE_NAME%TYPE;
    KEY_ID       TRANSFER_SO_TABLE.KEY_ID%TYPE;
    V_CNT            INTEGER;
    V_SUB_RESULT_MSG VARCHAR2(500);
    V_COL_STR        VARCHAR2(1000);
  BEGIN

    RESULTID  := 0;
    RESULTMSG := '';

    --取得购物车表
    SELECT A.ID, A.TABLE_NAME, A.KEY_ID
      INTO TABLE_ID, TABLE_NAME,KEY_ID
      FROM TRANSFER_SO_TABLE A
     WHERE A.ID = 1;

    STR_SQL := 'select COUNT(1)
                    from so_his.' || TABLE_NAME ||' ol
                   where ol.' || KEY_ID ||' = :1';
    EXECUTE IMMEDIATE STR_SQL INTO V_CNT USING OLID;
    IF V_CNT > 0 THEN
      --取表字段
      V_COL_STR := GETTABCOL(TABLE_NAME);
      STR_SQL := 'insert into so.' || TABLE_NAME || '
                select '||V_COL_STR||' from so_his.' || TABLE_NAME ||
                 ' ol where ol.' || KEY_ID || ' = :1';
      EXECUTE IMMEDIATE STR_SQL USING OLID;

      PROCRECOVERSEC(OLID, TABLE_ID, RESULTID, V_SUB_RESULT_MSG);

      STR_SQL := 'delete from so_his.' || TABLE_NAME ||
                 ' ol where ol.' || KEY_ID || ' = :1';
      EXECUTE IMMEDIATE STR_SQL USING OLID;
    END IF;
    IF (RESULTID = 1) THEN
      RESULTMSG := SUBSTR(RESULTMSG || V_SUB_RESULT_MSG, 1, 500);
    ELSE
      RESULTMSG := '成功';
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      RESULTID  := 1;
      RESULTMSG := RESULTMSG || SUBSTR(SQLERRM, 1, 400);
  END;
这个是存过 我该怎么写这个job ,小白  求帮助

Answer 1

https://blog.csdn.net/lindiwo/article/details/79238310