常用function 二(日期、NULL判斷、ROWID、ROWNUM) (DML 三)

※五種日期

sessiontimezone：Asia/Taipei
current_date：20-3月 -16
sysdate：20-3月 -16
current_timestamp：20-3月 -16 05.43.14.965000000 下午 ASIA/TAIPEI
localtimestamp：20-3月 -16 05.43.14.965000000 下午
systimestamp：20-3月 -16 05.43.14.965000000 下午 +08:00

※可以使用「+」和「-」來加減天數

※如果想改變顯示的效果，可用(DD、MON…等格式，下面的to_char(日期)會說明)
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '-5:0';

※日期相減

SELECT
    CURRENT_DATE-hiredate, -- 12877.7442592592592592592592592592592593
    SYSDATE-hiredate, -- 12877.7442592592592592592592592592592593
    CURRENT_TIMESTAMP-hiredate, -- 12877 17:51:44.996
    LOCALTIMESTAMP-hiredate, -- 12877 17:51:44.996
    SYSTIMESTAMP-hiredate -- 12877 17:51:44.996
FROM emp where empno = 7369;

※只能相減，不能相加，左邊減右邊可得到天數

※日期相減 INTERVAL

SELECT sysdate - 30                  FROM dual;
SELECT sysdate - INTERVAL '30' day   FROM dual;
SELECT sysdate - INTERVAL '30' month FROM dual;
SELECT sysdate - INTERVAL '30' year  FROM dual;

※直接減其是就是 INTERVAL '天數' DAY，但如果是要減年月就要完整的寫出來

※時分秒也可以，分別是 HOUR、MINUTE、SECOND

※trunc

trunc(CURRENT_DATE)：20-3月 -16
trunc(SYSDATE)：20-3月 -16
trunc(CURRENT_TIMESTAMP)：20-3月 -16
trunc(LOCALTIMESTAMP)：20-3月 -16
trunc(SYSTIMESTAMP)：20-3月 -16
    
trunc(CURRENT_DATE-hiredate)：12877
trunc(SYSDATE-hiredate)：12877

※trunc可以只留年月日，所以五種日期都一樣

※注意：假設有個欄位叫 xxx_date，格式是年月日時分秒
下 xxx_date BETWEEN to_date('2016-03-15', 'yyyy-mm-dd') AND to_date('2016-03-20', 'yyyy-mm-dd')
此時會搜尋315~319的資料如上剛好是320零點零分零秒的資料，此時將 xxx_date，加上 trunc 就可以搜尋到320的資料

※日期相減必需注意格式要一樣，像我的hiredate只有年月日，所以有時分秒的timestamp相減就會出錯

※增加月份、兩個月份相差的天數

add_months(sysdate, 3):左邊日期增加3個月
SELECT months_between(sysdate, hiredate) FROM emp where empno=7369;

※左邊減右邊的月數(會有小數點)，所以可以用trunc去除(上一篇的trunc也能處理數字)

※這個方法會幫我們解決潤年的事

※轉日期和數字、求每月最後一天

to_number('101.25')
to_date('2013-05-25','yyyy-mm-dd')
to_timestamp('2013-05-25 20:20:20','yyyy-mm-dd hh24:mi:ss')
last_day(sysdate)--每月最後一天

※hh24…等格式，後面的to_char(日期)會說明

※to_char(數字)

to_char(1234.5678, '9999.9999'),--  1234.5678
to_char(1234.5678, '0000.0000'),--  1234.5678
to_char(0, '9999.9999'),--     .0000
to_char(0, '0000.0000'),--  0000.0000
to_char(0, '9'),-- 0
to_char(0, '0'),-- 0
to_char(1234.5678, '9990.9999'),--  1234.5678
to_char(10, '0')-- ##

※右邊是給格式，只要給的不夠，就會出現「#」，至於給幾個#要看第一個參數

※to_char(日期) 

官網格式說明

to_char(SYSTIMESTAMP, 'yyyy'),-- 年
to_char(SYSTIMESTAMP, 'mm'),-- 月01~12
to_char(SYSTIMESTAMP, 'dd'),-- 日01~31
to_char(SYSTIMESTAMP, 'hh'),-- 時(12小時制)等同「hh12」
to_char(SYSTIMESTAMP, 'hh24'),--時(24小時制)
to_char(SYSTIMESTAMP, 'mi'),-- 分0~59
to_char(SYSTIMESTAMP, 'ss'),-- 秒0~59
to_char(SYSTIMESTAMP, 'sssss'),-- 0-86399
to_char(SYSTIMESTAMP, 'ww'),-- 一年當中的第幾個星期
to_char(SYSTIMESTAMP, 'w'),-- 一個月份當中的第幾個星期
to_char(SYSTIMESTAMP, 'ddd'),-- 001~366 一年當中的第幾天
    
to_char(SYSTIMESTAMP, 'd'),-- 回傳1~7，星期日是1，星期六是7
to_char(SYSTIMESTAMP, 'month'),-- 顯示幾月，如「3月」
to_char(SYSTIMESTAMP, 'day')-- 顯示星期幾，如「星期日」

※大小寫沒差

※有七個符號可以直接在格式裡面使用(第二個參數)
「-」「/」「,」「.」「;」「:」「"text"」
其中text表示隨便打，因為前面六種不夠用就要使用「"」包起來，然後隨個人發揮

※取得下一個星期的日期

to_char(sysdate,'day')
next_day(sysdate, '星期日')

※範例的「星期日」也有可能是要打「sunday」，所以可用第一行的to_char看一下

※此範例表示依今天的日期，顯示出下一個星期日是哪一天，回傳的是date

※Extract

EXTRACT(year FROM systimestamp)-- 截取年
EXTRACT(month FROM systimestamp)
EXTRACT(day FROM systimestamp)
EXTRACT(hour FROM systimestamp)
EXTRACT(minute FROM systimestamp)
EXTRACT(second FROM systimestamp)--秒+3位的毫秒
    
EXTRACT(year FROM date '2001-03-05'),
EXTRACT(month FROM date '2001-03-05'),
EXTRACT(day FROM date '2001-03-05'),
    
EXTRACT(hour FROM timestamp '2001-03-05 12:34:56'),
EXTRACT(minute FROM timestamp '2001-03-05 12:34:56'),
EXTRACT(second FROM timestamp '2001-03-05 12:34:56')

※用to_char時，大部份格式不到10的，譬如8好了，會顯示「08」，而extract不會出現0

※要注意systimestamp是有時分秒的，所以可以截取，但sysdate沒有時分秒，截取就會出錯

※如果傳進來的日期是個變數，就用date或timestamp

※date截取方式和sysdate一樣；timestamp截取方式和systimestamp一樣

※NULL判斷

nvl(null,0),-- 0
nvl(23,0),-- 23
    
nvl2(23,0,1),-- 0
nvl2(null,0,1),-- 1
    
nullif(23,23),-- null
nullif(23,null)-- 23
-- nullif(null,23)編譯錯誤

※nvl第一個參數是null就回傳第二個參數，否則就回傳第一個參數

※nvl2第一個參數是null就回傳第二個參數，否則就回傳第三個參數

※nullif第一和第二個參數一樣就回傳null，否則就回傳第一個參數，第一個參數不能是null

※LNNVL

SELECT * FROM emp WHERE comm < 500;
SELECT * FROM emp WHERE lnnvl(comm < 500);

※使用在where條件

※LNNVL會將條件變成相反，以上面的例子會變成「>=」，並且將NULL顯示

※NANVL

用法和NVL一樣，但用在型態是BINARY_FLOAT 或 BINARY_DOUBLE，如果不是這個型態也不會報錯，結果是有沒有用NANVL都一樣，這個我還沒試過，因為我在工作時還沒遇到這些型態

※COALESCE

coalesce('a','1','2','3'),-- a
coalesce(null,1,2,3),-- 1
coalesce(null,null,2,3),-- 2
coalesce(null,null,null,3),-- 3
coalesce(null,null,null,null)-- null

※除了null外，全部的參數型態都要一樣，如第一行後面的123，如果不用「'」包起來會出錯

※可以有很多參數，第一個參數是null，就判斷第二個參數是不是null，如果是，再繼續判斷第三個參數是不是null，以此類推，如果到最後都是null，就回傳null

官方一直到12c，判斷null的就這6個，而還有兩個常用的方法也能判斷null，decode和case when then else end，看這篇

※ROWID

是每一條紀錄的唯一地址，如果刪除一條記錄又新增一樣的記錄也是會不一樣

※刪除多餘的記錄

-- 1.
CREATE TABLE xxx AS SELECT * FROM dept;
    
-- 2.
SELECT x.*, x.ROWID FROM xxx x;
    
-- 3.
INSERT INTO xxx VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO xxx VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO xxx VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO xxx VALUES (40, 'OPERATIONS', 'BOSTON');
    
-- 4.
SELECT MIN(ROWID) FROM xxx x GROUP BY deptno;
    
-- 5.
DELETE FROM xxx
WHERE ROWID NOT IN(
    SELECT MIN(ROWID) FROM xxx x GROUP BY deptno
);

※以下的測試是認為新增的資料，ROWID一定會比較大測試的

※1.複製dept來練習 ，之前有講過複製不會複製constraint

※2.看看目前的ROWID長什麼樣子

※3.複製一樣的資料到xxx，因為xxx沒有複製到constraint，所以可以新增多筆一樣的資料，複製個兩、三次以後，再回第2步看目前的ROWID長什麼樣子

※4.這一步是看一下最小的ROWID是和第2步是相同的，因為dept只有四筆而且每個欄位都不一樣，所以group by 哪個欄位都可以

※5.這一步就可以將大於原本的ROWID刪除了

※ROWNUM

可以顯示行號，本身和記錄沒有關係，可以把它想成資料全部都搜完後，再加上的行號
所以想要有和記錄挷在一起的錯覺，就要看下SQL的功力了

SELECT ROWNUM, e.* FROM emp e WHERE ROWNUM = 1;
    
SELECT ROWNUM, e.* FROM emp e WHERE ROWNUM <= 3;
    
SELECT * FROM(
    SELECT ROWNUM r, e.* FROM emp e WHERE ROWNUM <= 10
) t
WHERE t.r >= 6;

※第一條SQL是取第一筆記錄的，但要注意只能取到第一筆和第一筆之後的記錄

※第二條SQL是取前三條記錄，ROWNUM一定要包含第一筆的記錄，和第一條SQL的道理是一樣的，所以取不到非第一筆之後的記錄，如第6到第10筆

※如果想取得第一筆之後的記錄，如第6到第10筆可以用第三條SQL的下法
先取出前10筆，然後用SELECT包起來，這樣子就可以取得到了，算是一種運用吧！