SQL函數概述
SQL函數分類
SQL函數分為單行函數和多行函數單行函數語法
函數名[(參數1,參數2,...)]其中的參數可以是變量、列名、表達式單行函數特征
單行函數對單行操作每行返回一個結果有可能返回值與原參數數據類型不一致單行函數可以寫在SELECT、WHERE、ORDER BY子句中有些函數沒有參數，有些函數包括一個或多個參數函數可以嵌套單行函數分類
單行函數分為字符函數、數值函數、日期函數、轉換函數、通用函數字符函數
主要指參數類型是字符型，不同函數返回值可能是字符型或數值型大小寫轉換函數
LOWER(列名|表達式)：將大寫或大小寫混合的字符轉換成小寫，多用于WHERE子句中UPPER(列名|表達式)：將小寫或大小寫混合的字符轉換成大寫，多用于WHERE子句中INITCAP(列名|表達式)：將每個單詞的第一個字母轉換成大寫，其余的字母都轉換成小寫
| 函數 | 結果 |
|---|
| LOWER('SQL Course') | sql course |
| UPPER('SQL Course') | SQL COURSE |
| INITCAP('SQL Course') | Sql Course |
字符處理函數
ASCII(c)：返回字符c的ASCII碼CHR(i)：返回給定的ASCII碼i所對應的字符CONCAT(s1,s2)：連接兩個值，相當于“||”如果s1為NULL，則返回s2如果s2為NULL，則返回s1如果s1和s2都為NULL，則返回NULLSUBSTR(s,n1[,n2])：返回字符串s從第n1位開始，長度為n2的子串如果n2省略，取第n1位開始的所有字符如果n1是負值，表示從第一個參數的后面第ABS(n1)位開始，向右取長度為n2的子串LENGTH(s)：取字符串s長度，如果s為NULL，則返回值為NULLINSTR(s1,s2[,n1[,n2]])：返回字符s2在字符串s1中第n2次出現時的位置，搜索從字符串s1的第n1個字符開始當沒有發現要查找的字符時，返回值為0如果n1為負數，那么搜索將從右到左進行，但函數的返回位置還是按從左到右計算n1和n2可以省略，即均取默認值1LPAD(s1,n1,s2)：返回s1被s2從左面填充到n1長度后的字符串RPAD(s1,n1,s2)：返回s1被s2從右面填充到n1長度后的字符串LTRIM(s1,s2)|RTRIM(s1,s2)|TRIM(s1,s2)：去除字符串s1左邊|右邊|左右兩端字符s2，如果不指定s2，則表示去除相應方位的空格REPLACE(s1,s2[,s3])：把s1中的s2用s3替換,s3的默認值為空字符串| 函數 | 結果 |
|---|
| ASCII('Z') | 90 |
| CHR(72) | H |
| CONCAT('Good','Bye‘) | GoodBye |
| SUBSTR('String',1,3) | Str |
| LENGTH('String') | 6 |
| INSTR('String','r') | 3 |
| LPAD(sal,10,'*') | ******5000 |
| RPAD(sal,10,'*') | 5000****** |
| TRIM('SSMITHS','S') | MITH |
| REPLACE('abc','b','d') | adc |
數值函數
ROUND函數
將列或表達式所表示的數值四舍五入到小數點后的第n位格式：ROUND(列名|表達式,n)舉例：ROUND(45.926,2) → 45.93TRUNC函數
將列或表達式所表示的數值截取到小數點后的第n位格式：TRUNC(列名|表達式,n)舉例：TRUNC(45.926,2) → 45.92MOD函數
取m除以n后得到的余數格式：MOD(m,n)舉例：MOD(1600,300) → 100其他數值函數
| 函數 | 說明 |
|---|
| ABS(n) | 返回n的絕對值 |
| CEIL(n) | 返回大于等于n的最小整數 |
| COS(n) | 返回n的余弦值，n為弧度 |
| EXP(n) | 返回e的n次冪，e=2.71828183 |
| FLORR(n) | 返回小于等于n的最大整數 |
| LOG(n1,n2) | 返回以n1為底n2的對數 |
| POWER(n1,n2) | 返回n1的n2次方 |
| SIGN(n) | 若n為負數，則返回-1；若n為正數，則返回1；若n為0，則返回0 |
| SIN(n) | 返回n的正弦值，n為弧度 |
| SQRT(n) | 返回n的平方根 |
日期函數
日期的處理
Oracle是以一種內部的數值形式存儲日期的，即：世紀、年、月、日、小時、分、秒默認的日期形式是：DD-MON-RR(DD表示兩位字符的“日”，MON表示三位字符的“月”，RR表示兩位字符的“年”)日期的數學運算
日期類型可以通過加減數字實現在該日期上加減對應的天數，如('10-AUG-16'+15)結果是'25-AUG-16'日期類型之間進行減操作是計算兩個日期之間間隔了多少天，如('10-AUG-16'-'4-AUG-16')結果四舍五入后是6天如果需要對一個日期進行加減響應小時操作，可以使用n/24來實現RR日期格式
用來判定按照DD-MON-RR格式給定的日期實際代表的日期是多少
常用日期函數
SYSDATE：返回系統當前的日期MONTHS_BETWEEN：返回兩個日期類型數據之間間隔的自然月數-- 查詢所有員工服務的月數SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) monthsFROM empADD_MONTHS：返回指定日期加上相應的月數后的日期-- 查詢所有員工轉正日期，試用期按三個月考慮SELECT ename,ADD_MONTHS(hiredate,3) new_date FROM empNEXT_DAY：返回某一日期的下一個指定日期-- 返回在02-2月-16之后的下一個周一是什么日期SELECT NEXT_DAY('02-2月-16','星期一') next_dayFROM DUALLAST_DAY：返回指定日期當月最后一天的日期-- 返回02-2月-16年所在月份的最后一天SELECT LAST_DAY('02-2月-16') "LAST DAY"FROM DUALROUND(date[,’fmt‘])：將date按照fmt指定的格式進行四舍五入fmt：可選項，默認為DD，即將date四舍五入為最近的天-- 查詢81年入職的員工姓名、入職日期、按月四舍五入后的入職日期SELECT ename,hiredate,ROUND(hiredate,'MONTH')FROM empWHERE SUBSTR(hiredate,-2,2)='81'TRUNC(date[,’fmt‘])：將date按照fmt指定的格式進行截取fmt：可選項，默認為DD，即將date截取為最近的天-- 查詢81年入職的員工姓名、入職日期、按月截斷后的入職日期SELECT ename,hiredate,TRUNC(hiredate,'MONTH')FROM empWHERE SUBSTR(hiredate,-2,2)='81'EXTRACT([YEAR]|[MONTH]|[DAY] FROM [日期類型表達式])：返回日期類型數據中的年份、月份或者日-- 查詢部門編號是10的部門中所有員工入職月份SELECT ename,hiredate,EXTRACT(MONTH FROM HIREDATE) monthFROM empWHERE deptno=10NEW_TIME(date,t1,t2)：當時區t1中的日期和時間是date時，返回時區t2中的日期和時間轉換函數
隱式數據類型轉換規則
對于INSERT和UPDATE操作，Oracle會把插入值或者更新隱式轉換為字段的數據類型對于SELECT語句，Oracle會把字段的數據類型隱式轉換為變量的數據類型當比較一個字符型和數值型的值時，Oracle會把字符型的值隱式轉換為數值型當比較字符型和日期型的數據時，Oracle會把字符型轉換為日期型用連接操作符||時，Oracle會把非字符類型的數據轉換為字符類型如果字符類型的數據和非字符類型的數據作算術運算，Oracle會將字符類型的數據轉換為合適的數據類型，這些數據類型可能是number、date、rowid等顯式數據類型轉換
TO_CHAR函數用于日期型：TO_CHAR(date,'fmt')SELECT ename,TO_CHAR(hiredate,'MM/YY') month_hiredFROM emp日期格式模型fmt必須用單引號引起來并且是大小寫敏感的fmt可以包含任何有效的日期元素
| YYYY | 完整的年份數字表示 |
| YEAR | 年份的英文表示 |
| MM | 用兩位數字來表示月份 |
| MONTH | 月份的全名 |
| DAY | 星期幾 |
| DY | 用3個英文字符縮寫來表示星期幾 |
| HH24:MI:SS AM | 15:45:32 PM |
| DD "of" MONTH | 12 of OCTOBER |
用于數值型：TO_CHAR(number,'fmt')SELECT TO_CHAR(sal,'$99,999') salaryFROM empWHERE ename='SCOTT'進行數字類型到字符型轉換時，格式中的寬度一定要超過實際列寬度，否則會顯示為###數值格式模型fmt包含的數值元素
| 9 | 一位數字 |
| 0 | 顯示前導零 |
| $ | 顯示美元符號 |
| L | 顯示本地貨幣符號 |
| . | 顯示小數點 |
| , | 顯示千位符 |
TO_NUMBER(char[,'fmt'])將字符串轉換成數值型數據要轉換的char類型數據必須是由數字組成的字符串格式碼中相應的格式必須要和char中的格式匹配TO_DATE(char[,'fmt'])將字符串轉換成日期型數據要轉換的char類型數據必須是可以轉換成日期的字符格式碼的格式必須要和char中的格式匹配通用函數
NVL函數
語法：NVL(表達式1,表達式2)如果表達式1不是NULL，返回表達式1，否則返回表達式2SELECT ename,sal,comm,(sal*12)+NVL(comm,0)FROM empNVL2函數
語法：NVL2(表達式1,表達式2,表達式3)如果表達式1不是NULL，返回表達式2，否則返回表達式3NULLIF函數
語法：NULLIF(表達式1,表達式2)比較兩個表達式，如果相等，返回NULL，否則，返回表達式1COALESCE函數
語法：COALESCE(表達式1,表達式2,...,表達式n)返回第一個不為空的表達式，參數表達式個數不限，是對NVL函數的擴展SELECT ename,COALESCE(comm,0) comm,deptnoFROM empCASE函數
CASE函數語法-- CASE函數語法：CASE 字段|表達式 WHEN 條件1 THEN 結果1 [WHEN 條件2 THEN 結果2 ...... ELSE 缺省值]ENDCASE函數示例-- CASE函數示例：SELECT ename,deptno, (CASE deptno WHEN 10 THEN '銷售部' WHEN 20 THEN '技術部' WHEN 30 THEN '管理部' ELSE '無' END) deptnameFROM empDECODE函數
語法：DECODE(字段|表達式,條件1,結果1[,條件2,結果2,...][,缺省值])DECODE函數示例-- DECODE函數示例：SELECT ename,deptno, DECODE(deptno, 10,'銷售部', 20,'技術部', 30,'管理部', '無') deptnameFROM emp函數嵌套
單行函數可以嵌套于任何層嵌套的函數時從最里層向最外層的順序計算的SELECT ename, NVL(TO_CHAR(mgr),'No Manager')FROM empWHERE mgr IS NULL
