现在的位置: 首页 > 综合 > 正文

通过oracle,是同pl/sql实现发送mail功能。

2013年06月27日 ⁄ 综合 ⁄ 共 6405字 ⁄ 字号 评论关闭

功能创建之初,我被要求编写oracle存储过程,并实现mail的发送。这个之前我做过,直接copy以前的代码,轻松实现了。

为了做个记录,我把代码复制上来。代码很容易懂,注释也足够,不多作解释了。

CREATE OR REPLACE PACKAGE sendMail
IS
PROCEDURE funcSendMail(sUid     IN       VARCHAR2);
END sendMail;
/

CREATE OR REPLACE PACKAGE BODY sendMail
IS
  c utl_smtp.connection;
  boundarystrend  varchar2(100):='--boundary_str--';
  smtpserver      varchar2(100):='lcmail01.linkcafe.ne.jp';
  boundarystr     varchar2(100):='boundary_str';
  boundarystrline varchar2(100):='--boundary_str';
  ExistAttachment boolean;
PROCEDURE funcSendMail(
                         sUid            VARCHAR2
                      ) IS
BEGIN
DECLARE
  TYPE typMailData IS RECORD (
       toAdd           DEWEY.CYSCC0370.D0120%TYPE  -- 宛先アドレス
     , toName          DEWEY.CYSCC0370.D0120%TYPE  -- 宛先名称
     , ccAdd           DEWEY.CYSCC0370.D0120%TYPE  -- CCアドレス
     , ccName          DEWEY.CYSCC0370.D0120%TYPE  -- CC名称
     , fromAdd         DEWEY.CYSCC0370.D0120%TYPE  -- 送信元アドレス
     , fromName        DEWEY.CYSCC0370.D0120%TYPE  -- 送信元名称
     , title           DEWEY.CYSCC0370.D0041%TYPE  -- メールタイトル
     , pContext        DEWEY.CYSCC0370.D0040%TYPE  -- メール本文
      ) ;
      
  MailData            typMailData ;
  binaryData          BLOB;
  rowCYSCT0291        DEWEY.CYSCT0291%ROWTYPE;
      
   CURSOR getMail
       IS
   SELECT B.D0041 B_D0041  --タイトル
        , B.D0120 B_D0120  --送信先メールアドレス
        , B.D0100 B_D0100  --送信先事業所ID
        , B.D0010 B_D0010  --送受信ナンバー
        , A.D0030 A_D0030  --PDFデータ
        , B.D0040 B_D0040  --送信内容
        , B.D0020 B_D0020  --送信日
     FROM DEWEY.CYSCT0291 A
        , DEWEY.CYSCC0370 B
    WHERE A.D0020 = B.D0010
      AND B.D0044 IN ('0','4'); --0:未送信 1:送信中 2:送信完了 3:キャンセル 4:送信エラー
  flag varchar2(1);
BEGIN
  FOR nLoop IN getMail LOOP
    flag := '1';
     
    UPDATE DEWEY.CYSCC0370
       SET D0020 = SYSDATE,
           D0042 = sysdate,
           D0044 = flag,
           D0930 = SYSDATE,
           D0940 = sUid
     WHERE D0010 = nLoop.B_D0010
         ;
           
    MailData.toAdd    := nLoop.B_D0120;
    MailData.toName   := '';
    MailData.ccAdd    := null;
    MailData.ccName   := null;
    MailData.fromAdd  := 'masamichi.kaneda@u-s-systems.co.jp';
    MailData.fromName := 'masamichi.kaneda@u-s-systems.co.jp';
    MailData.title    := nLoop.B_D0041;
    MailData.pContext := nLoop.B_D0040;

    -- メール基本部分作成
    if openMail(MailData.toAdd,    -- 宛先アドレス
                MailData.toName,   -- 宛先名
                MailData.ccAdd,    -- CCアドレス
                MailData.ccName,   -- CC名
                MailData.fromAdd,  -- 送信元アドレス
                MailData.fromName, -- 送信元名
                MailData.title,    -- メールタイトル
                MailData.pContext  -- メール本文
                ) = false then
      flag := '4';
    else
      flag := '2';
      -- 添付ファイル
      if addAttachmentFile('application/pdf',
                           nLoop.B_D0100||to_char(nLoop.B_D0020,'yyyymmdd')||'.pdf',
                           nLoop.A_D0030)  = false then
        flag := '4';
      ELSE
        flag := '2';
        utl_smtp.write_data(c,boundarystrend|| utl_tcp.CRLF);--区切り文字
        utl_smtp.close_data(c);
        utl_smtp.quit(c);
      end if;
    end if;
    IF flag = '2' THEN
      UPDATE DEWEY.CYSCC0370
         SET D0043 = sysdate
       WHERE D0010 = nLoop.B_D0010
         ;
    END IF;     
    UPDATE DEWEY.CYSCC0370
       SET D0020 = SYSDATE
         , D0044 = flag
         , D0930 = SYSDATE
         , D0940 = sUid
     WHERE D0010 = nLoop.B_D0010
         ;
          
  END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
    rollback;
    
  END ;
END funcSendMail;

FUNCTION openMail(   mailto      IN VARCHAR2,
                     totext      IN VARCHAR2,
                     mailcc      IN VARCHAR2,
                     cctext      IN VARCHAR2,
                     mailfrom    IN VARCHAR2,
                     fromtext    IN VARCHAR2,
                     title       IN VARCHAR2,
                     mailbody    IN VARCHAR2  ) RETURN BOOLEAN IS
  BEGIN
      DECLARE
          cc  varchar2(1000);
          pos integer;
      BEGIN
          c := utl_smtp.open_connection(smtpserver);
          utl_smtp.helo(c, smtpserver);
          utl_smtp.mail(c, mailfrom);
          utl_smtp.rcpt(c, mailto);
          IF mailcc IS NOT NULL AND cctext IS NOT NULL THEN
            utl_smtp.rcpt(c, mailcc);
          END IF;

          utl_smtp.open_data(c);
          utl_smtp.write_data(c,'From: "=?iso-2022-jp?B?');
          utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(fromtext) ));
          utl_smtp.write_data(c,'?=" <'||mailfrom||'>'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,'To: "=?iso-2022-jp?B?');
          IF totext IS NULL THEN
             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(' ') ));
          ELSE
             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(totext) ));
          END IF;
          utl_smtp.write_data(c,'?=" <'||mailto||'>'|| utl_tcp.CRLF);
          IF mailcc IS NOT NULL AND cctext IS NOT NULL THEN
            utl_smtp.write_data(c,'Cc: "=?iso-2022-jp?B?');
            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(cctext) ));
            utl_smtp.write_data(c,'?=" <'||mailcc||'>'|| utl_tcp.CRLF);
          END IF;
          
          utl_smtp.write_data(c,'Subject: =?iso-2022-jp?B?');
          --バイナリサイズが3の倍数でないとbase64_encodeがおかしくなる
          cc:='';
          for pos in 1..length(title) LOOP
            cc:=cc||substr(title,pos,1);
            if lengthB(cc)>20 and round(lengthB(cc)*8/3)*3=lengthB(cc)*8 then
              utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(cc)));
              cc:='';
            end if;
          end LOOP;
          if length(cc)>0 then
            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(cc)));
          end if;
           --utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(title)));
          utl_smtp.write_data(c,'?='|| utl_tcp.CRLF);
          
          utl_smtp.write_data(c,'MIME-Version: 1.0'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,'Content-Type: multipart/mixed; boundary="'||boundarystr||'"'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,''|| utl_tcp.CRLF);
          
          --本文
          utl_smtp.write_data(c,boundarystrline|| utl_tcp.CRLF);--区切り文字
          utl_smtp.write_data(c,'MIME-Version: 1.0'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,'Content-Type: text/plain; charset="SJIS"'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF);
          utl_smtp.write_data(c,''|| utl_tcp.CRLF); --空行
          IF mailbody IS NULL THEN
             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(' ') ));
          ELSE
             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(mailbody) ));
          END IF;
          utl_smtp.write_data(c, utl_tcp.CRLF);
          utl_smtp.write_data(c,boundarystrline|| utl_tcp.CRLF);--区切り文字
          RETURN  TRUE;
      EXCEPTION
          WHEN OTHERS THEN
              RETURN  FALSE;
      END;
  END;


  FUNCTION addAttachmentFile(mimetype       in VARCHAR2,
                            attachfilename in VARCHAR2,
                            data           in blob) RETURN BOOLEAN IS
  BEGIN
      DECLARE
          buffer RAW(32767);
          amt BINARY_INTEGER := 300;
          --pos INTEGER := 2147483647;
          pos INTEGER := 1;
          ix  NUMBER;
      BEGIN
           if ExistAttachment then
              utl_smtp.write_data(c,boundarystrline|| utl_tcp.CRLF);--区切り文字
            end if;
            
            --添付1
            utl_smtp.write_data(c,'MIME-Version: 1.0'|| utl_tcp.CRLF);
            utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF);
            utl_smtp.write_data(c,'Content-Type: '||mimetype||';');
            utl_smtp.write_data(c,'name="=?iso-2022-jp?B?');
            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(attachfilename)));
            utl_smtp.write_data(c,'?="'|| utl_tcp.CRLF);
              
            --ファイル名
            utl_smtp.write_data(c,'Content-Disposition: attachment;');
            utl_smtp.write_data(c,'filename="=?iso-2022-jp?B?');
            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(attachfilename)));
            utl_smtp.write_data(c,'?="'|| utl_tcp.CRLF);
           
            --添付データ
            utl_smtp.write_data(c,''|| utl_tcp.CRLF); --空行
            if dbms_lob.GETLENGTH(data)>0 then
                
                while pos < dbms_lob.getlength(data) loop
                    dbms_lob.read(data,amt,pos,buffer);
                    utl_smtp.write_raw_data(c, utl_encode.base64_encode(buffer));
                    pos  := pos + amt ;
                    --amt := least(1000,dbms_lob.getlength(src_lob) - l_ammount);
                end loop;
            else
              utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw('データがありません') ));
            end if;
            utl_smtp.write_data(c, utl_tcp.CRLF);
          ExistAttachment:=true; --添付あり
          RETURN  TRUE;
          
      EXCEPTION
          WHEN OTHERS THEN
              RETURN  FALSE;
      END;
  END;
END sendMail;
/
SHOW ERR

抱歉!评论已关闭.