Oracle學習筆記十六：常用函式-有解無憂

一、單行函式

1.1、字串函式

字串函式接受字符引數，這些引數可以是表中的列，也可以是一個字串運算式，

1）常用的字串函式：

2）上面各函式的例子：

1.2、數值函式

數值函式接受數字引數，這些引數可以是表中的列，也可以是一個數字運算式，

1.3、日期函式

1）SYSDATE

作用：回傳的是當前系統的時間，

SELECT SYSDATE FROM dual;

2）SYSTIMESTAMP

作用：回傳的是當前系統的時間戳，

SELECT SYSTIMESTAMP FROM dual;

3）MONTHS_BETWEEN (date1, date2)

作用：用于計算date1和date2之間有幾個月，

如果date1在日歷中比date2晚，那么MONTHS_BETWEEN()就回傳一個正數，
如果date1在日歷中比date2早，那么MONTHS_BETWEEN()就回傳一個負數，
如果date1和date2日期一樣，那么MONTHS_BETWEEN()就回傳一個0，

SELECT MONTHS_BETWEEN(TO_DATE('2020-10-18','yyyy-mm-dd'),TO_DATE('2020-06-06','yyyy-mm-dd')) MONTHS FROM dual;

4）ADD_MONTHS(d,n)

作用：在某一個日期 d 上，加上指定的月數 n，回傳計算后的新日期，

SELECT SYSDATE,ADD_MONTHS(SYSDATE,3) FROM dual;

5）LAST_DAY(d)

作用：回傳指定日期當月的最后一天，

SELECT SYSDATE,LAST_DAY(SYSDATE) FROM dual;

6）NEXT_DAY(date,char)

作用：查詢出指定時間的下一個星期幾（由char指定）所在的日期，

char可以用1～7替代，1表示星期日，2代表星期一，以此類推，

char可以是星期一、星期二...星期日，即 monday,thusday...(看是什么字符集的)，

SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual;
SELECT NEXT_DAY(TO_DATE('2020-06-01','yyyy-mm-dd'),3) FROM dual;

7）ROUND(d[,fmt])

作用：回傳一個以 fmt 為格式的四舍五入日期值， d 是日期， fmt 是格式模型，默認 fmt 為 DDD，即月中的某一天，

如果 fmt 為"YEAR"則舍入到某年的 1 月 1 日，即前半年舍去，后半年作為下一年，
如果 fmt 為"MONTH"則舍入到某月的 1 日，即前月舍去，后半月作為下一月，
默認為"DDD"，即月中的某一天，最靠近的天，前半天舍去，后半天作為第二天，
如果 fmt 為"DAY"則舍入到最近的周的周日，即上半周舍去，下半周作為下一周周日，

SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

8）EXTRACT(fmt FROM d)

作用：提取日期中的特定部分，

fmt 為：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND，其中 YEAR、MONTH、DAY可以為 DATE 型別匹配，也可以與 TIMESTAMP 型別匹配，但是 HOUR、MINUTE、SECOND 必須與 TIMESTAMP 型別匹配，
HOUR 匹配的結果中沒有加上時區，因此在中國運行的結果少 8 小時，

SELECT SYSDATE,EXTRACT(YEAR FROM SYSDATE) YEAR,EXTRACT(MONTH FROM SYSDATE) MONTH,EXTRACT(DAY FROM SYSDATE) DAY,
    EXTRACT(HOUR FROM SYSTIMESTAMP) HOUR,EXTRACT(MINUTE FROM SYSTIMESTAMP ) MINUTE,EXTRACT(SECOND FROM SYSTIMESTAMP) SECOND 
FROM dual;

1.4、轉換函式

1）TO_CHAR(d|n[,fmt])

作用：把日期和數字轉換為制定格式的字串，Fmt是格式化字串，

代碼演示：TO_CHAR對日期的處理

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM dual;

代碼決議：

在格式化字串中，使用雙引號對非格式化字符進行參考，
年(yyyy)、月(mm)、日(dd)、時(hh、hh24)、分(mi)、秒(ss)

代碼演示：TO_CHAR對數字的處理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') "date" FROM dual;

針對數字的格式化，格式化字符有：

2）TO_DATE(X,[,fmt])

作用：把一個字串以fmt格式轉換成一個日期型別，

 SELECT TO_DATE('2020-10-18','yyyy-mm-dd') ONEDAY FROM dual;

3）TO_NUMBER(X,[,fmt])

作用：把一個字串以fmt格式轉換為一個數字，

SELECT TO_NUMBER('-$12,345.67','$99,999.99') "NUM" FROM dual;

1.5、其它單行函式

1）NVL(X,VALUE)

作用：如果X為空，回傳VALUE，否則回傳X，

--對工資是2000元以下的員工，如果沒發獎金，每人獎金100元，
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;

2）NVL2(X,VALUE1,VALUE2)

作用：如果X非空，回傳VALUE1，否則回傳VALUE2，

--對EMP表中工資為2000元以下的員工，如果沒有獎金，則獎金為200元，如果有獎金，則在原來的獎金基礎上加100元，
SELECT ENAME,JOB,SAL,NVL2(COMM,COMM+100,200) "COMM" FROM EMP WHERE SAL<2000;

3）DECODE(條件,值1,回傳值1,值2,回傳值2,…值n,回傳值n,預設值)

作用：

CASE 
    WHEN 條件=值1 THEN
        回傳值1
    WHEN 條件=值2 THEN
        回傳值2
        ......
    WHEN 條件=值n THEN
        回傳值n
    ELSE
        預設值
END

--年齡在30以上的顯示">"，30以下的顯示"<"，剛好是30的顯示"="，
SELECT ID,NAME,AGE,DECODE(SIGN(AGE-30),1,'>',-1,'<',0,'=','未知') AS SYMBOL FROM STUDENT;

二、聚合函式

作用：聚合函式同時對一組資料進行操作，回傳一行結果，比如計算一組資料的總和，平均值等，

--求本月所有員工的基本工資總和
SELECT SUM(SAL) FROM EMP;

--求不同部門的平均工資
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;

參考自：

https://blog.csdn.net/u011955534/article/details/14168913

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/179287.html

標籤：Oracle

上一篇：MySQL 預讀和緩沖池

下一篇：Redis 對過期資料的處理