run.sql

介绍Oracle PL SQL编程

/*
 * Run.sql
 * Chapter 17, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script tests the DBMS_JOB.RUN 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_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 Provide a record count from the EMAIL_TBL table
PROMPT

SELECT count(1)
FROM email_tbl;

PROMPT
PROMPT JOB Numbers
PROMPT

COL what FORMAT A40
SELECT job, what
FROM user_jobs;

PROMPT
PROMPT DBMS_JOB.RUN example
PROMPT

DECLARE
   v_error EXCEPTION;

   PRAGMA EXCEPTION_INIT(v_error, -23421);

BEGIN

   DBMS_JOB.RUN(job => &&job_number, FORCE => TRUE);
   COMMIT;
EXCEPTION
   WHEN v_error
   THEN
      DBMS_OUTPUT.PUT_LINE('The job number entered was not valid');

END;
/

SET PAGES 9999

SELECT *
FROM dba_jobs_running
WHERE job = &&job_number;


PROMPT
PROMPT Check the USER_JOBS view
PROMPT

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 A4000
SET PAGES 9999
PRINT v_job_details

PROMPT
PROMPT Provide a record count from the EMAIL_TBL table
PROMPT

SELECT count(1)
FROM email_tbl;