데이터베이스/오라클

[Oracle] Select문 결과를 주기적으로 이메일 발송하기

Unipiz 2020. 11. 20. 17:59

Oracle mail, 오라클 메일, Version : 11G

 

준비사항 & 순서

1. SMTP 메일서버

    -서버구축 후 메일서버에서 접속 허용 설정

 

2. network service 설정

   -오라클에서 접근 허용

 

3. SELECT문 작성 

 

4. 프로시저 만들기

 

5. 오라클 JOB 등록

 

 

기존 스프링 스케쥴러로 데이터를 주기적으로 발송했지만,

빈번한 추가요청에 대응하여 오라클에서 직접 처리했다.

 

1. SMTP 메일서버구축   -- link

 

 

2. Network Service 설정 -- link

 

 

3. 주기적으로 보낼 SELECT문 작성

SELECT '1' NO, '구분1' GU,'상태1' STATUS,TO_CHAR(SYSDATE,'YYYY-MM-DD') IL,'ID1' ID,'유저1' NM,'비고1' BG FROM DUAL
UNION ALL
SELECT '2' NO, '구분2' GU,'상태2' STATUS,TO_CHAR(SYSDATE,'YYYY-MM-DD') IL,'ID2' ID,'유저2' NM,'비고1' BG FROM DUAL
UNION ALL
SELECT '3' NO, '구분1' GU,'상태1' STATUS,TO_CHAR(SYSDATE,'YYYY-MM-DD') IL,'ID3' ID,'유저3' NM,'비고1' BG FROM DUAL

 

4. MailSender 프로시저 생성

CREATE OR REPLACE PROCEDURE YJ.MAIL_SENDER_SAMPLE IS --프로시저이름
    CURSOR DATA_CUR IS
    --Select 문 작성
    SELECT '1' NO, '구분1' GU,'상태1' STATUS,TO_CHAR(SYSDATE,'YYYY-MM-DD') IL,'ID1' ID,'유저1' NM,'비고1' BG FROM DUAL
    UNION ALL
    SELECT '2' NO, '구분2' GU,'상태2' STATUS,TO_CHAR(SYSDATE,'YYYY-MM-DD') IL,'ID2' ID,'유저2' NM,'비고1' BG FROM DUAL
    UNION ALL
    SELECT '3' NO, '구분1' GU,'상태1' STATUS,TO_CHAR(SYSDATE,'YYYY-MM-DD') IL,'ID3' ID,'유저3' NM,'비고1' BG FROM DUAL; -- `;` 필수
TYPE DATA_CUR_TYPE IS TABLE OF DATA_CUR%ROWTYPE INDEX BY BINARY_INTEGER; -- Select문의 타입으로 타입정의
  S_DATA  DATA_CUR_TYPE;                        -- 정의한 타입으로 변수 정의
  vv_host    VARCHAR2(30) := 'mail.test.com';   -- SMTP 서버주소
  vn_port    NUMBER := 25;                      -- 포트번호 (기본SMTP 포트)
  vv_domain  VARCHAR2(30) := 'test.com';        -- 도메인 명
  vv_from    VARCHAR2(50) := 'system@test.com'; -- 보내는 주소
  c utl_smtp.connection;                        --연결 객체
  vv_html    clob;                              -- HTML 메시지를 담을 변수
  T_HEADER varchar2(600);                       -- 테이블 헤더
  emailstr varchar2(500);                       -- 받을 이메일 변수
  eamil_rcpt_all varchar2(1000); -- 수신인 최종 명단 || 숨은참조를 위해 필요
  bc_rcpt varchar2(200);         --숨은 참조
BEGIN
IF(TO_CHAR(SYSDATE-1, 'D') > 5) THEN RETURN; -- 주말엔 종료.
END IF;
    SELECT EMAIL INTO emailstr FROM (SELECT 'dydcks5@gmail.com,dydcks4@gmail.com,dydcks44@gmail.com' EMAIL FROM DUAL);
    bc_rcpt := 'dydcks5@naver.com'; --숨은참조
    eamil_rcpt_all := CONCAT(emailstr||',',bc_rcpt); --최종 받을 명단 작성
OPEN BANPUM_CUR; --커서 오픈
    LOOP
        FETCH BANPUM_CUR BULK COLLECT INTO S_DATA LIMIT 1000; --1000이하로만 관리
        EXIT WHEN BANPUM_CUR%NOTFOUND; --마지막 행에서 루프종료
    END LOOP;
    CLOSE BANPUM_CUR; --커서 닫기
    IF(S_DATA.COUNT = 0) THEN RETURN; -- data가 없으면 종료.
    END IF;
    --HTML의 Table 헤더를 작성
    T_HEADER := '<table border=1 cellspacing=0><caption> [ 미확정 DATA 안내 ] </caption>
                   <tr><th bgcolor=#fdf5ea>번호</th>
                   <th bgcolor=#fdf5ea>구분</th>
                   <th bgcolor=#fdf5ea>상태</th>
                   <th bgcolor=#fdf5ea>일자</th>
                   <th bgcolor=#fdf5ea>고객ID</th>
                   <th bgcolor=#fdf5ea>고객명</th>
                   <th bgcolor=#fdf5ea>비고</th>';
    --vv_html 변수에 data 넣기
    FOR I IN S_DATA.FIRST..S_DATA.LAST LOOP
            vv_html := CONCAT(vv_html,'<tr><td>'||S_DATA(I).NO||'</td><td>'||S_DATA(I).GU||'</td><td>'||S_DATA(I).STATUS||'</td><td>'||S_DATA(I).IL||'</td><td>'||S_DATA(I).ID||'</td><td>'||S_DATA(I).NM||'</td><td>'||S_DATA(I).BG||'</td></tr>');
    END LOOP;
    --본문 html 완성하기
    vv_html := CONCAT(CONCAT('<html><head></head><style>th,td{padding:4px 7px;text-align:center;}</style><body><p>안내 메시지 작성. ('||S_DATA.COUNT||'건) 발생!</p>',CONCAT(T_HEADER,vv_html)),'</table></body></html>');
    --메일 보내기 시작
     c := UTL_SMTP.OPEN_CONNECTION(vv_host, vn_port);
      UTL_SMTP.HELO(c, vv_domain);
      UTL_SMTP.MAIL(c, vv_from);
    --실제로 받을 명단 ROW로 추출 후 입력,숨은참조 포함
    FOR LIST IN (SELECT TRIM(REGEXP_SUBSTR(TXT,'[^,]+', 1, LEVEL)) AS EMAIL FROM (SELECT eamil_rcpt_all TXT FROM DUAL) CONNECT BY INSTR(TXT,',', 1,LEVEL -1 ) >0) LOOP
      UTL_SMTP.RCPT(c, LIST.EMAIL); 
    END LOOP;
      UTL_SMTP.OPEN_DATA(c); -- 메일본문 작성 시작
      UTL_SMTP.WRITE_DATA(c,'MIME-Version: 1.0' || UTL_TCP.CRLF ); -- MIME 버전
      UTL_SMTP.WRITE_DATA(c,'Content-Type: text/html; charset="euc-kr"' || UTL_TCP.CRLF );
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('From:system@test.com' || UTL_TCP.CRLF) ); -- 보내는사람
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('To: ' || emailstr || UTL_TCP.CRLF) );  -- 받는사람 표시, 숨은참조X 
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('Subject: 미확정 DATA 안내' || UTL_TCP.CRLF) );  -- 제목
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF );  -- 한 줄 띄우기
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW(vv_html || UTL_TCP.CRLF)  );
      UTL_SMTP.CLOSE_DATA(c); -- 메일 본문 작성 종료
      UTL_SMTP.QUIT(c);  -- 메일 세션 종료
    EXCEPTION --예외처리
      WHEN UTL_SMTP.INVALID_OPERATION THEN
           dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
           dbms_output.put_line(' Temporary e-mail issue - try again');
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
           dbms_output.put_line(' Permanent Error Encountered.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN OTHERS THEN
         dbms_output.put_line(sqlerrm);
         UTL_SMTP.QUIT(c);
END;
/

EXEC YJ.MAIL_SENDER_SAMPLE;

 

오라클 JOB 등록

DECLARE
  X NUMBER;
BEGIN
  begin
    SYS.DBMS_JOB.SUBMIT
      (
        job        => X
       ,what       => 'YJ.MAIL_SENDER_SAMPLE;'
       ,next_date  => to_date('11/23/2020 13:00:00','mm/dd/yyyy hh24:mi:ss')
       ,interval   => 'TRUNC(SYSDATE)+37/24'
       ,no_parse   => FALSE);
  :JobNumber := to_char(X);
  exception
    when others then
    begin
      raise;
    end;
  end;
END;


--다음 발송시간 수정
EXECUTE DBMS_JOB.NEXT_DATE(147, TRUNC(SYSDATE)+1/144); 
EXECUTE DBMS_JOB.NEXT_DATE(147, to_date('12/01/2020 11:00:00','mm/dd/yyyy hh24:mi:ss'));

-- 간격 수정
EXECUTE DBMS_JOB.INTERVAL(147, 'DECODE(TO_CHAR(SYSDATE,''HH24''),11,SYSDATE+1/6,TRUNC(SYSDATE)+35/24)');

파라미터로 받아서 등록.

2020.11.23일부터 매일 오후 1시에 발송.

JOB 수정.

 

 

 

 

Select 문만 작성하면 Table만 컬럼에 맞춰 수정해주면 언제든지 메일을 보낼 수 있다.

html, table을 나눠논 이유는 같은 결과를 한번에, 부서별로 보내고 합쳐서도 보내기 위함..

부서별 분기는 수신명단, 부서별 본문, UTL_SMTP OPEN을 추가해서 작성해보자.

 

저작자표시

'데이터베이스 > 오라클' 카테고리의 다른 글

[Oracle] 한 달 전 데이터를 SELECT 하기  (0) 2022.08.08
[Oracle] TimeStamp 시점 데이터 복구  (0) 2020.11.26
[Oracle] Date to char 날짜 타입 포맷 변경 예제  (0) 2020.10.26
[Oracle] 오라클 DELETE FROM 조건삭제 예시  (0) 2020.10.21
[Oracle]오라클 INSERT INTO SELECT 예시  (0) 2020.10.20

'데이터베이스/오라클'의 다른글

  • 현재글[Oracle] Select문 결과를 주기적으로 이메일 발송하기

관련글

댓글

티스토리툴바