萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

Oracle 110 個常用函數速查大全:字串、數學、日期、轉換與進階分析

Oracle 資料庫內建超過 200 個函數,本文精選 110 個最高頻函數,按功能分類整理語法、參數說明與實戰範例,涵蓋字串操作、數學計算、日期時間、型別轉換、聚合分析、條件邏輯與編碼處理,適合開發與 DBA 案頭必備。

字串函數

函數語法說明範例結果
ASCIIASCII(char)回傳字元十進位碼ASCII('A')65
CHRCHR(n)碼位轉字元CHR(65)A
CONCATCONCAT(s1,s2)連接兩字串CONCAT('010-','88888888')010-88888888
INITCAPINITCAP(s)首字母大寫INITCAP('smith')Smith
INSTRINSTR(s, sub, pos, occ)搜尋子字串位置INSTR('oracle training','ra',1,2)9
LENGTHLENGTH(s)字串長度(字元數)LENGTH('北京')2
LOWER/UPPERLOWER(s)/UPPER(s)大小寫轉換LOWER('AbC')abc
LPAD/RPADLPAD(s,n,pad)左/右填充至長度 nLPAD('gao',10,'*')*******gao
LTRIM/RTRIM/TRIMTRIM([LEADING|TRAILING|BOTH] c FROM s)修剪字元TRIM(' gao ')gao
SUBSTRSUBSTR(s, pos, len)截取子字串(支援負數)SUBSTR('13088888888',3,8)08888888
REPLACEREPLACE(s, old, new)替換所有匹配REPLACE('he love you','he','I')I love you
SOUNDEXSOUNDEX(s)發音代碼(模糊匹配)SOUNDEX('weather')=SOUNDEX('wether')TRUE
TRANSLATETRANSLATE(s, from, to)字元級對應替換TRANSLATE('abc','abc','123')123
REGEXP_SUBSTRREGEXP_SUBSTR(s, pat, pos, occ, match)正則擷取(11g+)REGEXP_SUBSTR('a1b2','[0-9]')1
REGEXP_REPLACEREGEXP_REPLACE(s, pat, repl, pos, occ, match)正則替換(11g+)REGEXP_REPLACE('a1b2','[0-9]','#')a#b#

數學函數

函數說明範例結果
ABS絕對值ABS(-100)100
ACOS/ASIN/ATAN/ATAN2反三角函數ACOS(-1)3.1415927
CEIL/FLOOR向上/向下取整CEIL(3.14)4
COS/SIN/TAN三角函數(弧度)COS(3.14159)-1
COSH/SINH/TANH雙曲函數COSH(1)1.543
EXP/LN/LOG指數/自然對數/任意底對數LOG(2,8)3
MOD餘數MOD(10,3)1
POWER冪POWER(2,10)1024
ROUND/TRUNC四捨五入/截斷(可指定精度)ROUND(55.5), TRUNC(55.5)56, 55
SIGN符號(-1/0/1)SIGN(-5)-1
SQRT平方根SQRT(10)3.162
WIDTH_BUCKET直方圖桶號(分析用)WIDTH_BUCKET(5,0,10,5)3

日期時間函數

函數說明範例結果
SYSDATE資料庫伺服器當前日期時間SYSDATE2024-01-15 10:30:00
CURRENT_DATE/TIMESTAMP會話時區當前日期/時間戳CURRENT_TIMESTAMP2024-01-15 10:30:00.123 +08:00
ADD_MONTHS加減月份(自動處理月末)ADD_MONTHS('2024-01-31',1)2024-02-29
MONTHS_BETWEEN兩日期月份差MONTHS_BETWEEN('2024-12-31','2024-01-01')11.97
NEXT_DAY下一個指定週幾NEXT_DAY(SYSDATE,'FRIDAY')2024-01-19
LAST_DAY該月最後一天LAST_DAY(SYSDATE)2024-01-31
ROUND(date,fmt)日期四捨五入(至日/月/年)ROUND(SYSDATE,'MONTH')2024-02-01
TRUNC(date,fmt)日期截斷(至日/月/年)TRUNC(SYSDATE,'MONTH')2024-01-01
EXTRACT擷取日期欄位EXTRACT(MONTH FROM SYSDATE)1
TO_CHAR日期轉字串(格式化)TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')2024-01-15 10:30:00
TO_DATE字串轉日期TO_DATE('20240115','YYYYMMDD')2024-01-15
INTERVAL間隔運算SYSDATE + INTERVAL '3' DAY2024-01-18

型別轉換函數

函數說明範例
TO_CHAR數字/日期 → 字串TO_CHAR(12345.67,'99,999.99') → '12,345.67'
TO_NUMBER字串 → 數字TO_NUMBER('$1,234.56','$9,999.99') → 1234.56
TO_DATE字串 → 日期TO_DATE('15-JAN-24','DD-MON-RR')
CASTANSI 標準轉型CAST('123' AS NUMBER)
HEXTORAW/RAWTOHEX十六進位 ↔ RAWHEXTORAW('4142') → 'AB'
CHARTOROWID/ROWIDTOCHARROWID ↔ 字串ROWIDTOCHAR(rowid)

條件與 NULL 處理

函數語法說明範例
NVLNVL(expr1, expr2)expr1 為 NULL 回傳 expr2NVL(comm,0)
NVL2NVL2(expr1, e2, e3)expr1 非 NULL 回 e2 否則 e3NVL2(comm,'Y','N')
COALESCECOALESCE(e1,e2,...)回傳第一個非 NULLCOALESCE(a,b,c,0)
NULLIFNULLIF(e1,e2)e1=e2 回 NULL 否則 e1NULLIF(len,0)
CASECASE WHEN...THEN...ELSE END多條件分支CASE WHEN sal>5000 THEN 'H' ELSE 'L' END
DECODEDECODE(expr, s1, r1, s2, r2, ..., def)Oracle 專用 switchDECODE(deptno,10,'A',20,'B','X')

聚合與分析函數

函數說明典型用法
SUM/AVG/MIN/MAX/COUNT基礎聚合GROUP BY 子句
STDDEV/VARIANCE標準差/變異數統計分析
ROW_NUMBER/RANK/DENSE_RANK排序編號TOP-N、去重
LAG/LEAD前後行存取環比計算
FIRST_VALUE/LAST_VALUE窗口首/尾值基準比較
NTILE分桶四分位數
RATIO_TO_REPORT佔比百分比計算
LISTAGG字串聚合(11gR2+)LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)

編碼與加密

  • DUMP(expr, fmt, start, len):內部表示碼
  • VSIZE(expr):內部儲存位元組數
  • STANDARD_HASH(expr, 'SHA256') (12c+):雜湊值
  • ORA_HASH(expr, max_bucket, seed):分桶雜湊

實戰小技巧

  1. 去除字串中所有空白:REPLACE(TRIM(col), ' ', '') 或 REGEXP_REPLACE(col, '\s+', '')
  2. 日期跨月加減:優先用 ADD_MONTHS/INTERVAL,避免手算天數
  3. 分頁查詢:12c+ 用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY;舊版用 ROW_NUMBER() 子查詢
  4. 字串聚合:LISTAGG 取代舊 WM_CONCAT/SYS_CONNECT_BY_PATH

函數是 SQL 表達力的延伸。熟練掌握核心 110 函數,能將大量原本需在應用層處理的邏輯下推至資料庫,減少網路傳輸、利用並行查詢加速。建議將本表製成 A4 雙面列印,貼在工位牆上——查閱比記憶更可靠。

copyright © 萬盛學電腦網 all rights reserved