11-job.sql

《Oracle8i PL/SQL程序设计》附源码

REM 11-JOBS.SQL
REM This file contains the DBMS_JOB examples from Chapter 11
REM of "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, updated 2/18/96.
REM Comments and questions should go to Scott Urman, at
REM [e-mail].


REM *** Chapter 11: TempInsert Procedure ***
CREATE SEQUENCE temp_seq
  START WITH 1
  INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE TempInsert AS
BEGIN
    INSERT INTO temp_table (num_col)
      VALUES (temp_seq.nextval);
    COMMIT;
END TempInsert;
/

REM *** Chapter 11: A Job Which Modifies NEXT_DATE ***
CREATE OR REPLACE PROCEDURE TempInsert
  (p_NextDate IN OUT DATE) AS
  v_CurrVal NUMBER;
BEGIN
  INSERT INTO temp_table (num_col)
    VALUES (temp_seq.nextval);
  SELECT temp_seq.currval
    INTO v_CurrVal
    FROM dual;
  IF v_CurrVal > 15 THEN
    p_NextDate := NULL;
  END IF;
  COMMIT;
END TempInsert;
/

REM *** Chapter 11: Submitting TempInsert ***
VARIABLE v_JobNum NUMBER
BEGIN
  DBMS_JOB.SUBMIT(:v_JobNum, 'TempInsert(next_date);', sysdate,
                  'sysdate + (1/(24*60*60))');
END;
/
PRINT v_JobNum