submit.sql

介绍Oracle PL SQL编程

/*
 * Submit.sql
 * Chapter 17, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script tests the DBMS_JOB.SUBMIT procedure
 */

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_job_details(
   i_job_number IN NUMBER,
   cv_job_details IN OUT SYS_REFCURSOR)
IS
BEGIN
   OPEN cv_job_details FOR
   SELECT job, schema_user schema, 
          to_char(next_date, 'dd-mon-yyyy hh24:mi:ss') NEXT_DATE, 
          interval, what, broken
   FROM user_jobs
   WHERE job = i_job_number;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END get_job_details;
/



PROMPT
PROMPT DBMS_JOB.SUBMIT examples
PROMPT


SET SERVEROUTPUT ON
DECLARE
   v_job_number NUMBER(10);
   v_instance_number NUMBER(10);

BEGIN

   -- Get the instance number for use with DBMS_JOB.SUBMIT
   SELECT instance_number 
   INTO v_instance_number
   FROM v$instance;

   -- Submit a job to begin tonight at midnight, and execute
   --  every half-hour thereafter
   DBMS_JOB.SUBMIT (JOB => v_job_number, 
                    WHAT => 'email_manager.smtp(''&mail_server'');', 
                    NEXT_DATE => TRUNC(SYSDATE + 1), 
                    INTERVAL => 'SYSDATE + 1/48', 
                    NO_PARSE => TRUE, 
                    INSTANCE => v_instance_number,
                    FORCE => NULL);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('The job number is: '||v_job_number);

END;
/

PROMPT
PROMPT Execute the GET_JOB_DETAILS procedure
PROMPT

VARIABLE v_job_details REFCURSOR
EXEC GET_JOB_DETAILS(&&job_number, :v_job_details)

COL schema FORMAT A15
COL next_date FORMAT A20
COL interval FORMAT A20
COL what FORMAT A60
PRINT v_job_details


PROMPT
PROMPT Create a job to purge the email_tbl table
PROMPT

DECLARE
   v_job_number NUMBER;
   v_instance_number NUMBER;
   v_statement VARCHAR2(500);
BEGIN

   -- Get the instance number for use with DBMS_JOB.SUBMIT
   SELECT i.instance_number
   INTO v_instance_number
   FROM v$instance i;

   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_JOB.SUBMIT (JOB => v_job_number, 
                    WHAT => v_statement, 
                    NEXT_DATE => TRUNC(SYSDATE + 1), 
                    INTERVAL => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'') + 95/96', 
                    NO_PARSE => TRUE, 
                    INSTANCE => v_instance_number,
                    FORCE => NULL);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('The job number is: '||v_job_number);

END;
/

UNDEFINE JOB_NUMBER

VARIABLE v_job_details REFCURSOR
EXEC GET_JOB_DETAILS(&job_number, :v_job_details)

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