[Oracle] 프로시저로 정기 메일 발송하기

« 2025/02 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

Database 2020. 6. 2. 15:09

ORACLE 에서 프로시저를 통해 HTML 형식을 만든 후 SMTP 프로토콜로 메일을 발송하는 쿼리를 작성해본다.
샘플은 배치를 통해 월별 판매 현황을 보고하는 예제이다.

먼저 보내야 할 메일을 저장할 테이블을 하나 만든다.

CREATE TABLE TB_MAIL_HISTORY (
  EMAIL_ADDR    VARCHAR2(50 CHAR)      NOT NULL, 
  TITLE         VARCHAR2(100 CHAR)     NULL, 
  MESSAGE       CLOB                   NULL, 
  SEND_CHK      CHAR(1 CHAR)           NULL, 
  CREATED       DATE                   NOT NULL
  SEND_DT       DATE                   NULL
)
;
COMMENT ON TABLE TB_MAIL_HISTORY IS '이메일 발송 내역';
COMMENT ON COLUMN TB_MAIL_HISTORY.EMAIL_ADDR IS '수신자 이메일 주소';
COMMENT ON COLUMN TB_MAIL_HISTORY.TITLE IS '제목';
COMMENT ON COLUMN TB_MAIL_HISTORY.MESSAGE IS '내용';
COMMENT ON COLUMN TB_MAIL_HISTORY.SEND_CHK IS '발송여부';
COMMENT ON COLUMN TB_MAIL_HISTORY.CREATED IS '생성일자';
COMMENT ON COLUMN TB_MAIL_HISTORY.SEND_DT IS '발송일자';

메일 발송 성공, 실패 여부를 확인할 수 있도록 로그 테이블을 별도로 만든다.

CREATE TABLE TB_MAIL_LOG (
  ROW_ID        VARCHAR2(30)       NOT NULL, 
  CREATED       DATE               NOT NULL, 
  EMAIL_ADDR    VARCHAR2(80)       NOT NULL, 
  ERRNUM        VARCHAR2(40)           NULL, 
  JOB_MSG       VARCHAR2(4000)         NULL, 
  EXE_DATE      DATE                   NULL
)
;
COMMENT ON TABLE TB_MAIL_LOG IS '이메일 발송 LOG';
COMMENT ON COLUMN TB_MAIL_LOG.ROW_ID IS 'LOG ID';
COMMENT ON COLUMN TB_MAIL_LOG.CREATED IS '생성일자';
COMMENT ON COLUMN TB_MAIL_LOG.EMAIL_ADDR IS '이메일 주소';
COMMENT ON COLUMN TB_MAIL_LOG.ERRNUM IS '오류코드';
COMMENT ON COLUMN TB_MAIL_LOG.JOB_MSG IS '오류 메시지';
COMMENT ON COLUMN TB_MAIL_LOG.EXE_DATE IS '실행일자';

CREATE SEQUENCE SEQ_LOG_ID
    START WITH 100000
    INCREMENT BY 1
    MAXVALUE   999999999
    MINVALUE 1
    CYCLE
    CACHE 20
    NOORDER;

프로그램의 가독성 및 관리를 위하여 이메일 생성 과 발송 처리하는 프로세스를 별개로 작성하고자 한다.

월별 판매내역을 HTML 파일로 만드는 프로시저 P_MAIL_CRT_HTML 를 먼저 작성한다.

CREATE OR REPLACE PROCEDURE P_MAIL_CRT_HTML IS 
    /************************************************************************* 
    * DESCRIPTION : E-mail HTML 생성 
    *************************************************************************/ 
    V_TITLE TB_MAIL_HISTORY.TITLE%TYPE; -- 제목 
    C_MESSAGE TB_MAIL_HISTORY.MESSAGE%TYPE; -- 내용  
BEGIN  
  V_TITLE := '[월말보고] '||TO_CHAR(SYSDATE , 'YYYY')||'년 '||TO_CHAR(SYSDATE , 'MM')||'월 판매실적';  
  -- 1. 수신자 목록 
  FOR EMAIL_LIST IN 
    (SELECT DISTINCT EMAIL_ADDR AS EMAIL 
      FROM TB_MEMBER 
     WHERE USER_ROLE IN ('ADMIN' , 'SALES_LEADER') -- 시스템 관리자 및 영업팀 리더
         AND EMAIL_ADDR IS NOT NULL 
    ) 
 LOOP 
    C_MESSAGE := '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="X-UA-Compatible" content="IE=10" />
<style type="text/css">
@charset "utf-8";
body {
  margin:0;
  padding:0 20px 0 20px;
  background:#fff;
}

table, tr, td, div, p {font-size: 12px; line-height: 18px; font-family: gulim, "굴림"; letter-spacing:normal;}
</style>
<title>일일 판매 실적</title>
</head>
<body>
<div>
  <p>
    안녕하세요, 시스템 관리자 입니다.<br>' ||TO_CHAR(SYSDATE , 'YYYY')||'년 '||TO_CHAR(SYSDATE , 'MM')||'월 판매 실적을 아래와 같이 알려 드립니다.
  </p>
  <p>■ 판매 상세 내역</p>

  <table style="width: 900px; border-collapse: collapse; height: 50px;" border="1">
    <thead style="background:#BDBDBD">
      <tr>
        <th> NO </th>
        <th> 고객번호 </th>
        <th> 고객명 </th>
        <th> 제품명 </th>
        <th> 총 수량 </th>
        <th> 총 금액 </th>
        <th> 단위 </th>
      </tr>
      </thead>
      <tbody>'; 
    -- 2. 판매내역 
    BEGIN 
         FOR CUST_LIST IN 
            (SELECT '<tr> <td>' 
              || ROWNUM ||'</td> <td>' 
              || S.CUST_NO ||'</td> <td>' 
              || (SELECT CUST_NAME FROM TB_CUSTOMER C WHERE S.CUST_NO = C.CUST_NO ) AS CUST_NAME ||'</td> <td>' 
              || S.PROD_NAME ||'</td> <td>' 
              || SUM(S.QUANTITY) ||'</td> <td>' 
              || SUM(S.AMT) ||'</td> <td>' 
              || S.PAY_UNIT ||'</td>' AS DATA 
            FROM TB_SALES_HISTORY S -- 판매내역 
             WHERE TO_CHAR(S.DEAL_DT , 'YYYY') = TO_CHAR(SYSDATE , 'YYYY') 
               AND TO_CHAR(S.DEAL_DT , 'MM') = TO_CHAR(SYSDATE , 'MM') 
               AND S.SALE_CHK = 'Y' 
             GROUP BY S.CUST_NO  
               , S.PROD_NAME  
               , S.PAY_UNIT 
          ORDER BY S.AMT DESC 
            ) 
         LOOP  
            C_MESSAGE := C_MESSAGE || CUST_LIST.DATA;  
         END LOOP; 
         C_MESSAGE := C_MESSAGE || '</tbody>
  </table>
  <p>판매실적 상세 내역은 ERP 판매실적현황 메뉴에서 조회하실 수 있습니다. </p>
</div>
</body>
</html>';  
         INSERT INTO TB_MAIL_HISTORY ( CREATED , EMAIL_ADDR , TITLE , MESSAGE ) 
           VALUES ( SYSDATE , EMAIL_LIST.EMAIL , V_TITLE , C_MESSAGE ) 
         ;  
         COMMIT;  
       END;  
     END LOOP;  
   EXCEPTION 
   WHEN OTHERS THEN 
     ROLLBACK;  
   END P_MAIL_CRT_HTML;

위 프로시저를 실행하면 발송자의 수 만큼 ROW 가 생성된다. 이때 SEND_DT 컬럼이 NULL 로 되어있는 항목을 체크하여 다음에 작성할 메일 발송 프로시저에서 발송해야할 메일을 체크한다.

이제 실제 메일 발송하는 프로시저 를 작성한다.

CREATE OR REPLACE PROCEDURE P_MAIL_SEND_HTML IS 
/************************************************************************* 
* DESCRIPTION : e-mail 발송 
*************************************************************************/ 
SEND_USER VARCHAR2(100) := 'admin@mysystem.com'; 
MAIL_HOST CONSTANT VARCHAR2(50) := 'smtp@mysystem.com'; -- 이메일서버IP or SMPT HOST 
MAIL_CON UTL_SMTP.CONNECTION; 
MAIL_REP UTL_SMTP.REPLY; 
MAIL_MSG_ASC LONG; 
MAIL_MSG_RAW RAW(32767); 
BEGIN 
  P_MAIL_CRT_HTML;  
  FOR MAIL_LIST IN 

    (SELECT CREATED  
       , EMAIL_ADDR  
       , TITLE  
       , MESSAGE 
      FROM TB_MAIL_HISTORY 
     WHERE TO_DATE(CREATED) = TO_DATE(SYSDATE) 
         AND SEND_DT IS NULL 
    ) 
 LOOP 
    MAIL_MSG_ASC := 'From:' || '<' || SEND_USER || '>' || UTL_TCP.CRLF; 
    MAIL_MSG_ASC := MAIL_MSG_ASC || 'To:' || '<' || MAIL_LIST.EMAIL_ADDR || '>' || UTL_TCP.CRLF; 
    MAIL_MSG_ASC := MAIL_MSG_ASC || 'Subject:' || MAIL_LIST.TITLE || UTL_TCP.CRLF; 
    MAIL_MSG_ASC := MAIL_MSG_ASC || 'Content-Type: text/html;' || UTL_TCP.CRLF;  
    BEGIN 
         MAIL_MSG_ASC := MAIL_MSG_ASC || UTL_TCP.CRLF; 
         MAIL_MSG_ASC := MAIL_MSG_ASC || MAIL_LIST.MESSAGE; 
         MAIL_MSG_ASC := MAIL_MSG_ASC || UTL_TCP.CRLF;  
         MAIL_MSG_RAW := UTL_RAW.CAST_TO_RAW(MAIL_MSG_ASC); 
         /* 보내는 메세지 생성 종료 */  
         MAIL_REP := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST , 25 , MAIL_CON , NULL);  
         UTL_SMTP.HELO(MAIL_CON , MAIL_HOST); 
         UTL_SMTP.MAIL(MAIL_CON , SEND_USER); 
         UTL_SMTP.RCPT(MAIL_CON , MAIL_LIST.EMAIL_ADDR); 
         UTL_SMTP.OPEN_DATA(MAIL_CON);  
         UTL_SMTP.WRITE_RAW_DATA(MAIL_CON , MAIL_MSG_RAW);  
         UTL_SMTP.CLOSE_DATA(MAIL_CON);  
         UTL_SMTP.QUIT(MAIL_CON);  
         P_MAIL_LOG(MAIL_LIST.CREATED , MAIL_LIST.EMAIL_ADDR , SQLCODE , SQLERRM);   
    EXCEPTION  
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN 
         BEGIN 
            UTL_SMTP.QUIT(MAIL_CON);  
            DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || SQLCODE); 
            DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);   
            P_MAIL_LOG(MAIL_LIST.CREATED , MAIL_LIST.EMAIL_ADDR , SQLCODE , SQLERRM);  
            COMMIT;  
            EXIT; 
         EXCEPTION 
         WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN 
            BEGIN  
               P_MAIL_LOG(MAIL_LIST.CREATED , MAIL_LIST.EMAIL_ADDR , SQLCODE , SQLERRM);  
               COMMIT;  
               EXIT; 
            END;  
         WHEN OTHERS THEN 
            BEGIN 
               P_MAIL_LOG(MAIL_LIST.CREATED , MAIL_LIST.EMAIL_ADDR , SQLCODE , SQLERRM);  
               COMMIT;  
               EXIT; 
            END; 
         END; 
    END;  
    BEGIN 
         UPDATE TB_MAIL_HISTORY 
            SET SEND_DT = SYSDATE 
          , SEND_CHK = 'Y' 
        WHERE CREATED = MAIL_LIST.CREATED 
            AND EMAIL_ADDR = MAIL_LIST.EMAIL_ADDR;  
         COMMIT;  
       EXCEPTION 
       WHEN OTHERS THEN 
         BEGIN 
           ROLLBACK;  
           EXIT; 
         END; 
       END;  
       COMMIT;  
     END LOOP; 
   END P_MAIL_SEND_HTML;

이제 작성한 프로시저를 DB스케쥴러에 등록하여 매월 1일마다 자동으로 메일이 발송되도록 설정한다.

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB
    (
      job_name  => 'JOB_MAIL_SEND_HTML'
        ,job_type  => 'PLSQL_BLOCK'
        ,job_action  => '  P_MAIL_SEND_HTML;'
        ,number_of_arguments  => 0
        ,start_date  => '2020/06/05 09:00:00.000000 +09:00'
        ,repeat_interval  => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=8; BYMINUTE=30'
        ,end_date  => NULL
        ,job_class  => 'DEFAULT_JOB_CLASS'
        ,enabled  => TRUE
        ,auto_drop  => FALSE
        ,comments  => '매월 1일 8시 30분'
      );

END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'LOGGING_LEVEL'
        ,value      => OFF
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'RESTARTABLE'
        ,value      => FALSE
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'MAX_FAILURES'
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'MAX_RUNS'
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'STOP_ON_WINDOW_CLOSE'
        ,value      => FALSE
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'JOB_PRIORITY'
        ,value      => 3
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    (
         name       => 'JOB_MAIL_SEND_HTML'
        ,attribute  => 'SCHEDULE_LIMIT'
    );
END;

BEGIN
    SYS.DBMS_SCHEDULER.ENABLE
    (name  => 'JOB_MAIL_SEND_HTML');
END;

스케쥴러를 작성할 때 주기를 지정하는 옵션 repeat_interval 값을 다양하게 지정할 수 있다.

참고 : https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN10040

Using Schedules

You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Enterprise Manager. The res

docs.oracle.com

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHDJEEB

DBMS_SCHEDULER

118/244 114 DBMS_SCHEDULER The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program. This chapter contains the following topics: Data Structures The DBMS_SCHEDULER package defines OBJ

docs.oracle.com

기본 구조

'FREQ=주기; 옵션=값; 옵션=값' 각 옵션은 콜론(;)으로 구분한다.

FREQ 반복 주기 (필수)
YEARLY | MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY
사용자가 직접 지정해둔 주기를 사용할 수도 있다.
INTERVAL 반복 횟수
1 ~ 99 (기본값=1)
FREQ에 지정한 주기별 1회 혹은 지정한 n회 반복한다.
YEARLY 2 : 2년마다, MONTHLY 3 : 3개월 마다 등등.
BY- 날짜 요일 등
가장 많이 사용되는 옵션들이다. BY로 시작하는 옵션은 리스트 형태로 콤마(,)로 구분하여 여러 개의 값을 지정할 수 있다.
- BYMONTH 특정 월
  값은 숫자로 1~12 혹은 문자는 알파벳 3자로 다음과 같이 지정할 수 있다.
  JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
  예를 들어 1월, 6월 마다 반복하려면 ; BYMONTH=JAN,JUN 동일한 표현으로 ; BYMONTH=1,6 과 같이 사용할 수 있다.
- BYWEEKNO N번째 주
  YEARLY 일때만 유효한 옵션으로 1 ~ 53 까지의 수를 허용하며 음수 값을 사용하여 기준날짜의 N주 전 과 같이 사용할 수 있다.
  FREQ=YEARLY; BYWEEKNO=2; 매년 둘째주 월~일 7일동안 반복.
- BYYEARDAY 당해 1월1일 부터 N번째 날
  1 ~ 366 의 값 및 음수 값을 허용 (음수인 경우 12월31일 부터 N번째 날)
  ex) BYYEARDAY=69 : 2월이 28일까지일 때 3월10일. 윤년이면 3월 9일에 수행된다.
  ex) BYYEARDAY=-2 : 12월 30일
- BYDATE 특정 월일 지정
  [YYYY]MMDD 8자리로 지정하면 년월일 로 인식하나 보통 4자리 일자로만 사용.
  선택적 옵션 [ offset | span ]
  - SPAN : 기준일로 부터 반복될 날짜 수 +,-,^
    ex) BYDATE=0110+SPAN:5D = 0110,0111,0112,0113,0114
    ex) BYDATE=0110^SPAN:6D ^는 지정한 날짜를 중심으로 기간을 설정.
    짝수를 지정하면 다음 홀수에 맞춰진다. = 0107,0108,0109,0110,0111,0112
  - OFFSET : 특정날짜에서 이전(-)/이후(+) N일 혹은 N주
    BYDATE=0205-[OFFSET:]2W = BYDATE=0205-14D = BYDATE=0122 OFFSET키워드는 생략가능
- BYMONTHDAY 당월 1일 부터 N번째 날
  1~31 사이의 값 및 음수 값을 허용
  ex) BYMONTHDAY=-1 : 월 말. 음수인경우 말일부터 N번째 날
- BYDAY 요일
  MON | TUE | WED | THU | FRI | SAT | SUN
  요일 앞에 숫자를 붙이면 N번째 주를 의미. 이때도 음수 가능. 음수인경우 뒤에서 N번째 주.
  주기가 YEARLY면 1~53 사이의 수, MONTHLY면 1~5 사이의 수를 지정.
  ex) BYDAY=-1FRI : 매월 마지막 주 금요일
- BYHOUR 시간
  0~23의 값 허용
- BYMINUTE 분
  0~59의 값 허용
- BYSECOND 초
  0~59의 값 허용
- BYSETPOS 포지션
  MONTHLY, YEARLY 일때만 유효한 이 옵션은 모든 시간 옵션들을 지정한 뒤 가장 마지막에 정한다.
  1~9999까지의 값을 허용하며 음수도 가능하다.
  주로 BY 옵션들과 혼용하기 보다는 사용자가 커스텀해놓은 일정 중 특정 번째 를 지칭하고자 할 때 사용한다.
  FREQ=MONTHLY; BYDAY=MY_WORKING_DAY; BYSETPOS=-1 이때 MY_WORKING_DAY 는 사용자가 지정한 스케쥴 이름이다.
INCLUDE 사용자 정의 스케쥴 포함
CREATE_SCHEDULE 를 통해 생성한 스케쥴과 표현식을 합치는 옵션.
EXCLUDE 사용자 정의 스케쥴 제외
CREATE_SCHEDULE 를 통해 생성한 스케쥴과 표현식을 제외하는 옵션.

저작자표시

'Database' 카테고리의 다른 글

[Oracle] SYS_CONTEXT 함수로 현재 세션 환경 조회 (0)	2022.12.15
[Oracle] 프로시저 실행 후 리턴 값 출력 (0)	2022.05.26
[Oracle] 기본 CRUD 구문 (0)	2020.05.12
ORACLE MERGE 중복 데이터 확인 (0)	2020.04.28

DESC

Hailey's Record Hailey's Record

'Database' 카테고리의 다른 글

티스토리툴바