createjob.sql

介绍Oracle PL SQL编程

/*
 * CreateJob.sql
 * Chapter 17, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script tests the DBMS_SCHEDULER.CREATE_JOB procedure
 */

SET VERIFY OFF
SET VERIFY OFF
UNDEFINE job_number

exec CLEAN_SCHEMA.jobs
exec CLEAN_SCHEMA.procs
exec CLEAN_SCHEMA.tables

PROMPT
PROMPT Create email_tbl to hold e-mail details
PROMPT

CREATE TABLE email_tbl (
	EMAILID NUMBER(10) 
	      CONSTRAINT emailid_pk PRIMARY KEY,
	SENDER VARCHAR2(100 CHAR) 
	      NOT NULL,
	RECIPIENTS VARCHAR2(4000 CHAR) 
	      NOT NULL,
	CC VARCHAR2(4000),
	BCC VARCHAR2(4000),
	SUBJECT VARCHAR2(50),
	MESSAGE VARCHAR2(4000),
	ATTACHMENT VARCHAR2(4000),
	DATE_LOGGED TIMESTAMP DEFAULT SYSTIMESTAMP,
	DATE_SENT TIMESTAMP);

PROMPT
PROMPT Insert four sample e-mails -- modify if you wish to receive e-mails
PROMPT

INSERT INTO email_tbl
   VALUES (1, '[e-mail]', '[e-mail]', 
           '[e-mail]', '[e-mail]',
           'Subject 1', 'This is the message for e-mail 1',
           'This is inline attachment 1', null, null);

INSERT INTO email_tbl
   VALUES (2, '[e-mail]', '[e-mail]', 
           '[e-mail]', '[e-mail]',
           'Subject 2', 'This is the message for e-mail 2',
           'This is inline attachment 2', null, null);

INSERT INTO email_tbl
   VALUES (3, '[e-mail]', '[e-mail]', 
           '[e-mail]', '[e-mail]',
           'Subject 3', 'This is the message for e-mail 3',
           'This is inline attachment 3', null, null);

INSERT INTO email_tbl
   VALUES (4, '[e-mail]', '[e-mail]', 
           '[e-mail]', '[e-mail]',
           'Subject 4', 'This is the message for e-mail 4',
           'This is inline attachment 4', null, null);


INSERT INTO email_tbl
   VALUES (5, '[e-mail]', '[e-mail]', 
           '[e-mail]', '[e-mail]',
           'Subject 5', 'This is the message for e-mail 5',
           'This is inline attachment 5', null, systimestamp);


INSERT INTO email_tbl
   VALUES (6, '[e-mail]', '[e-mail]', 
           '[e-mail]', '[e-mail]',
           'Subject 6', 'This is the message for e-mail 6',
           'This is inline attachment 6', null, systimestamp);

COMMIT;

PROMPT 
PROMPT Create a package called email_manager to send e-mail messages
PROMPT

CREATE OR REPLACE PACKAGE email_manager
IS

   PROCEDURE smtp (i_host_string VARCHAR2);
   PROCEDURE inline_email;

END;
/



CREATE OR REPLACE PACKAGE BODY email_manager
IS

PROCEDURE smtp (i_host_string VARCHAR2)
AS

   v_host_string VARCHAR2(500) :=
                      i_host_string;
   v_conn_string UTL_SMTP.CONNECTION;

   CURSOR email_cur
   IS
   SELECT *
   FROM email_tbl
   WHERE DATE_SENT IS NULL;

BEGIN
   
   FOR y IN email_cur
   LOOP
      UTL_SMTP.HELO(v_conn_string, v_host_string);
      UTL_SMTP.MAIL(v_conn_string, y.sender);
      UTL_SMTP.RCPT(v_conn_string, y.recipients);
      UTL_SMTP.OPEN_DATA(v_conn_string);
      UTL_SMTP.WRITE_DATA(v_conn_string, y.message);
      UTL_SMTP.CLOSE_DATA(v_conn_string);
      UTL_SMTP.QUIT(v_conn_string);

      UPDATE email_tbl
      SET date_sent = systimestamp
      WHERE emailid = y.emailid;
   END LOOP;

   COMMIT;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END smtp;

PROCEDURE inline_email
AS

   CURSOR email_cur
   IS
   SELECT * 
   FROM email_tbl
   WHERE DATE_SENT IS NULL;

BEGIN

   FOR y IN email_cur
   LOOP
      UTL_MAIL.SEND_ATTACH_VARCHAR2 (
         SENDER => y.sender,
         RECIPIENTS => y.recipients,
         CC => y.cc,
         BCC => y.bcc,
         SUBJECT => y.subject,
         MESSAGE => y.message,
         ATTACHMENT => y.attachment,
         ATT_INLINE => TRUE);

      UPDATE email_tbl
      SET date_sent = systimestamp
      WHERE emailid = y.emailid;
   END LOOP;

   COMMIT;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END inline_email;

END;
/


PROMPT
PROMPT This procedure retrieves details about jobs
PROMPT

CREATE OR REPLACE PROCEDURE get_scheduler_details(
   i_job_name IN VARCHAR2,
   cv_job_details IN OUT SYS_REFCURSOR)
IS
BEGIN
   OPEN cv_job_details FOR
      SELECT job_name, state, comments, next_run_date
      FROM dba_scheduler_jobs
      WHERE job_name = UPPER(i_job_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END get_scheduler_details;
/



BEGIN

   DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'EXAMPLE1',
                  JOB_TYPE => 'STORED_PROCEDURE',
                  JOB_ACTION => 'EMAIL_MANAGER.INLINE_EMAIL',
                  START_DATE => TRUNC(SYSTIMESTAMP + 1),
                  REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=30',
                  END_DATE => SYSTIMESTAMP+300,
                  ENABLED => TRUE,
                  AUTO_DROP => TRUE,
                  COMMENTS => 'Send E-Mail on 30 min intervals');

END;
/


PROMPT
PROMPT Execute the GET_JOB_DETAILS procedure
PROMPT

VARIABLE v_job_details REFCURSOR
EXEC GET_SCHEDULER_DETAILS('EXAMPLE1', :v_job_details)

COL schema FORMAT A12
COL next_date FORMAT A20
COL interval FORMAT A20
COL what FORMAT A20
PRINT v_job_details


PROMPT
PROMPT Create a job to purge the email_tbl table
PROMPT

DECLARE
   v_instance_number NUMBER;
   v_statement VARCHAR2(500);
   v_dbid NUMBER;
BEGIN
   -- Get the instance number
   SELECT instance_number
   INTO v_instance_number
   FROM v$instance;


   v_statement := 'DELETE FROM email_tbl WHERE date_sent IS NOT NULL;';

   -- Submit a job to begin today, and execute
   --  ever Sunday at 11:45 PM.
   DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'EXAMPLE2',
                  JOB_TYPE => 'PLSQL_BLOCK',
                  JOB_ACTION => v_statement,
                  START_DATE => SYSTIMESTAMP,
                  REPEAT_INTERVAL => 'FREQ=WEEKLY;BYDAY=SUN; BYHOUR=23; BYMINUTE=45',
                  END_DATE => SYSTIMESTAMP+300,
                  ENABLED => TRUE,
                  AUTO_DROP => TRUE,
                  COMMENTS => 'Delete e-mails once per week at 23:45 Sunday');
   COMMIT;
END;
/

VARIABLE v_job_details REFCURSOR
EXEC GET_SCHEDULER_DETAILS('EXAMPLE2', :v_job_details)

COL schema_user FORMAT A15
COL next_date FORMAT A20
COL interval FORMAT A60
COL what FORMAT A4000
SET PAGES 9999
PRINT v_job_details