Oracle SQL取日期

 

當月第一天 select trunc(sysdate, ‘mm’) from dual

當年第一天 select trunc(sysdate,‘yy’) from dual

當前年月日 select trunc(sysdate,‘dd’) from dual

當年第一天 select trunc(sysdate,‘yyyy’) from dual

當前星期的第一天 （也就是星期天） select trunc(sysdate,‘d’) from dual

當前日期 select trunc(sysdate) from dual

當前時間（準確到小時） select trunc(sysdate, ‘hh’) from dual

當前時間（準確到分鐘） select to_char(trunc(sysdate, ‘mi’),‘yyyy-MM-dd HH:mm:ss’) from dual

前一天的日期 select TRUNC(SYSDATE - 1) from dual

前一個月的日期 select add_months(trunc(sysdate),-1) from dual

後一個月的日期 select add_months(trunc(sysdate),1) from dual

本月最後一天 select to_char(last_day(sysdate), ‘yyyy-mm-dd’) from dual

utl_smtp發送Mail

 CREATE OR REPLACE PROCEDURE send_mail (

   p_to        IN VARCHAR2,

   p_cc        IN VARCHAR2,

                                       p_from      IN VARCHAR2,

                                       p_mail_subject IN VARCHAR2, 

                                       p_message   IN VARCHAR2,

                                       p_smtp_host IN VARCHAR2,

                                       p_smtp_port IN NUMBER DEFAULT 25,

                                       p_mime_type IN VARCHAR2 DEFAULT NULL,

                                       p_directory IN VARCHAR2 DEFAULT NULL,

                                       p_attach    IN VARCHAR2 DEFAULT NULL )

AS

/********************************

 * 使用範例:

 * BEGIN

 * apex_ws_erp.send_mail(p_to => 'kn@mail.com.tw',

 *           p_cc => kn@mail.com.tw;kn@mail.com.tw',

 *           p_from      => 'admin@mail.com.tw',

 *           p_mail_subject => 'Subject test', 

 *           p_message   => '<b>This is Test msessage</b><p>hello world</p><p>哈囉你好嗎</p>',

 *           p_smtp_host => 'localhost',

 *           p_mime_type => 'text/plain',

 *           p_directory => 'DUMP_CSV_DIR',

 *           p_attach => 'test.csv'); 

 * END; 

 * 

 * 

 */  

  l_mail_conn   utl_smtp.connection;

  CRLF          varchar2(2) := CHR( 13 ) || CHR( 10 );

  --utl_smtp.write_data 用來放置一般資訊

  --utl_smtp.write_raw_data 用來放置 16 進位資訊, 也是中文字必須轉換成 16 進位才可正常顯示

  file_handle     bfile;

  file_exists     boolean;

  block_size      number;

  file_len        number;

  pos             number;

  total           number;

  read_bytes      number;

  data            raw(200);

 

  loopcount       number;

  att_count       number;

BEGIN

  -- 開啟 Mail Connection 物件

  l_mail_conn := utl_smtp.open_connection(p_smtp_host, p_smtp_port);

  -- 建立連線

  utl_smtp.helo(l_mail_conn, p_smtp_host);

  -- 設定 寄件者

  utl_smtp.mail(l_mail_conn, p_from);

  -- 設定 收件者

  --utl_smtp.rcpt(l_mail_conn, p_to);

  -- 設定 多個收件者

  FOR r1 IN (SELECT NVL(REGEXP_SUBSTR(p_to||';'||p_cc, '[^;]+', 1, LEVEL, 'i'), 'NULL') AS mail_to 

FROM DUAL 

CONNECT BY LEVEL <= LENGTH(p_to||';'||p_cc) - LENGTH(REGEXP_REPLACE(p_to||';'||p_cc, ';', ''))+1)

  LOOP 

    IF r1.mail_to <> 'NULL' THEN 

  utl_smtp.rcpt(l_mail_conn, r1.mail_to);

END IF;

  END LOOP;

  -- 設定 發信內容

  utl_smtp.open_data(l_mail_conn);

  -- 寫入 Mail Header

  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Subject:'||p_mail_subject|| CRLF));

  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('To:'||p_to|| CRLF));

  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Cc:'||p_cc|| CRLF)); 

  utl_smtp.write_data(l_mail_conn, 'MIME-Version: 1.0' || CRLF );  

  utl_smtp.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="gcboundary0p"'|| CRLF|| CRLF);

  /*

  IF p_message IS NOT NULL THEN 

  utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset=utf-8/big5' || CRLF );

    utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_message) );

    utl_smtp.write_data(l_mail_conn, '' || CRLF );

  END IF;

 */

  IF p_message IS NOT NULL THEN

    utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF);

    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || CRLF || CRLF);

    --utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_message) );

    utl_smtp.write_data(l_mail_conn, CRLF || CRLF);

  END IF;

 

  dbms_output.put_line('Starting attachment segment');

  dbms_output.put_line('Directory: '||p_directory);

  dbms_output.put_line('AttachList: '||p_attach); 

  -- MAIL附件

  BEGIN 

  IF p_attach IS NOT NULL THEN 

    

    loopcount := 0; 

    loopcount := loopcount +1;

  dbms_output.put_line('Attaching: '||p_directory||'/'||p_attach);

  utl_file.fgetattr(p_directory, p_attach, file_exists, file_len, block_size);

  IF file_exists THEN 

  dbms_output.put_line('Getting mime_type for the attachment');

      utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

  utl_smtp.write_data(l_mail_conn, 'Content-Type: '||p_mime_type ||'; name="' || p_attach || '"' || CRLF );

  utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || CRLF );

  utl_smtp.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="'||p_attach||'"' || CRLF );

  utl_smtp.write_data(l_mail_conn,  CRLF );

 

  file_handle := bfilename(p_directory, p_attach);

  pos := 1;

  total := 0;

  file_len := dbms_lob.getlength(file_handle);

  dbms_lob.open(file_handle, dbms_lob.lob_readonly);

LOOP 

IF pos + 57 - 1 > file_len THEN 

              read_bytes := file_len - pos + 1;

              --dbms_output.put_line('Last read - Start: '||pos);

            ELSE 

              --dbms_output.put_line('Reading - Start: '||pos);

              read_bytes := 57;

            END IF;

            total := total + read_bytes;

            dbms_lob.read(file_handle, read_bytes, pos, data);

            utl_smtp.write_raw_data(l_mail_conn, utl_encode.base64_encode(data));

            

            pos := pos + 57;

            IF pos > file_len THEN

              EXIT;

            END IF;

END LOOP;

dbms_output.put_line('Length was '||file_len);

dbms_lob.close(file_handle);

        IF (loopcount < att_count) then

            utl_smtp.write_data(l_mail_conn,  CRLF );

            utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

        ELSE 

        utl_smtp.write_data(l_mail_conn,  CRLF );

            utl_smtp.write_data(l_mail_conn, '--gcboundary0p--'||CRLF );

            dbms_output.put_line('Writing end boundary');

        END IF;

  ELSE 

          dbms_output.put_line('Skipping: '||p_directory||'/'||p_attach||'Does not exist.');

  END IF;  

  

  END IF;

 

  EXCEPTION WHEN OTHERS THEN 

  dbms_output.put_line('Error Message：'||sqlerrm);

  END;

  -- 結束連線

  utl_smtp.close_data(l_mail_conn);

  utl_smtp.quit(l_mail_conn);

END;