SQL函數 SQL函數分類 SQL函數主要有兩種，分為單行函數、多行函數單行函數：只對一行進行變換，每行返回一個結果。可以轉換數據類型，可以嵌套參數可以是一列或一個值多行函數：多行函數，每次對一組記錄進行處理。然後對於這一組記錄只返回一個結果。單行函數單行函數的分類：主要有字元、通用 ...

SQL函數

SQL函數分類

SQL函數主要有兩種，分為單行函數、多行函數
單行函數：只對一行進行變換，每行返回一個結果。可以轉換數據類型，可以嵌套參數可以是一列或一個值
多行函數：多行函數，每次對一組記錄進行處理。然後對於這一組記錄只返回一個結果。

兩種SQL函數

單行函數

單行函數的分類：主要有字元、通用、轉換、日期、數值

單行函數

字元函數

字元函數分為：大小寫控制函數、字元控制函數

字元函數

大小寫控制函數：這類函數改變字元的大小寫

 SELECT 'helloworld Java',
        LOWER('helloworld Java') AS "Lower Last_Name",
        UPPER('helloWorld Java') AS "UPper Last_name",
        INITCAP('helloWorld Java') AS "InitCap Last_Name"
FROM dual

大小寫控制函數

在全稱匹配中，可以通過大小寫的轉換函數來進行字元的大小寫轉換。

字元控制函數

字元控制函數的種類

CONCAT(字元連接函數)：CONCAT('Hello', ' World!')

SELECT CONCAT('Hello', ' World!') FROM  dual

SUBSTR(字元字串函數) ：SUBSTR('HelloWorld', 1, 5)
SUBSTR函數字元串是從 1開始，這點和 Java 有點不同。
函數的最後一個參數表示，從開始往後截取多少個字元串。

SELECT SUBSTR('abcdefgh',2,4) FROM dual;

LENGTH：取得字元串的長度LENGTH('HelloWorld!')
INSTR：獲取某個字元在字元串中的位置INSTR('HelloWorld!','W')

SELECT LENGTH('HelloWorld!'),INSTR('HelloWorld!','W') FROM dual;

LPAD：左補齊函數LPAD(salary,10,'*')
RPAD：右補齊函數RPAD(salary,10,'*')

SELECT salary,LPAD(salary,10,'*'),RPAD(salary,10,'*') FROM employees;

LPAD,RPAD

TRIM去除前後字元函數，函數能去掉字元的前後空格，或者去掉字元串中首尾特定字元（使用該方式需要使用到FROM關鍵字）

SELECT TRIM('H' FROM 'HelloHWorldHH') FROM dual;
SELECT TRIM('  HelloHWorldHH   ') FROM dual;

去除特定字元去除空格

REPLACE字元串替換函數。能夠替換字元串中所有的特定函數

SELECT REPLACE('abcdabcdabcd','a','m') FROM dual

字元串替換函數

數值函數

數值函數主要有以下：ROUND: 四捨五入、TRUNC: 截斷、MOD: 求餘
ROUND 函數：通過第二個參數，表示在第幾位進行四捨五入，並且捨棄後面的數值

SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM dual

四捨五入

TRUNC函數，能夠直接對於數值進行截斷！不做任何進位操作

SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM dual

截斷函數

MOD 函數，對數值進行求餘運算。

SELECT salary, MOD(salary,1000) AS Mod_Salary FROM employees;

Mod求餘函數

日期函數

Oracle 中的日期型數據實際含有兩個值: 日期和時間。
函數SYSDATE既包含日期也包含時間SELECT sysdate FROM dual
日期的數學運算
在日期上加上或減去一個數字結果仍為日期。
兩個日期相減返回日期之間相差的天數。
- 日期不允許做加法運算，無意義
可以用數字除24來嚮日期中加上或減去天數。
可以對兩個日期之間的天數進行操作

SELECT last_name,(SYSDATE-hire_date)/7 AS Weeks
FROM employees
WHERE department_id = 90

日期函數主要有以下幾種：

函數	描述
`MONTHS_BETWEEN`	兩個日期相差的月數
`ADD_MONTHS`	向指定日期中加上若幹個月數
`NEXT_DAY`	指定日期的下一個星期所對應的日期
`LAST_DAY`	本月的最後一天
`ROUND`	日期四捨五入
`TRUNC`	日期截斷

時間和日期的表示方法：

yyyy  年、 mm   月、dd   日
hh   小時、 mi  分鐘、ss 秒
day  星期

SELECT MONTHS_BETWEEN(SYSDATE+30,SYSDATE),
       ADD_MONTHS(SYSDATE,2),
       NEXT_DAY(SYSDATE,'星期四'),
       LAST_DAY(SYSDATE),
       ROUND(SYSDATE,'mm'),ROUND(SYSDATE,'yyyy'), 
       TRUNC(SYSDATE,'mm'),TRUNC(SYSDATE,'yyyy')
FROM dual;

轉換函數

隱式數據類型轉換

Oracle 自動完成下列轉換：

源數據類型	目標數據類型
`VARCHAR2` or `CHAR`	`NUMBER`
`VARCHAR2` or `CHAR`	`DATE`
`NUMBER`	`VARCHAR2`
`DATE`	`VARCHAR2`

DATE <---> VARCHAR2 <---> NUMBER

顯式的數據類型轉換

顯式的數據類型轉換主要使用到幾個轉換函數：
TO_DATE
TO_CAHR
TO_NUMBER
TO_CAHR函數對日期的轉換的轉換
TO_CHAR(date,'fomat_model')
格式:
必須包含在單引號中而且大小寫敏感。
可以包含任意的有效的日期格式。
日期之間用逗號隔開。

select TO_CAHR(SYSDATE,'yyyy-MM-dd hh:mm:dd day') from dual

日期格式的元素

Model	View
YYYY	2016
YEAR	twenty sixteen
MM	06
MONTH	6月(根據本地顯示) 或者June
MON	6月(根據本地顯示) 或者Jun
DY	星期六或者 SATURDAY
DAY	星期六或者 SAT
DD	18

時間格式

HH:MI:SS AM         02:06:18 下午
HH24:MI:SS          14:06:18

使用雙引號嚮日期中添加字元

-- DD "of" MONTH    18 of 6月 
select to_char(sysdate,'DD "of" MONTH') from dual

TO_DATE 函數對字元的轉換
TO_DATE(CHAR,'fomat_model')
使用TO_DATE函數將字元轉換為數字
註意：文字與格式的字元串要相匹配！

SELECT TO_DATE('2016年06月18日 14:31:35','yyyy"年"mm"月"dd"日"hh24:mi:ss')
From dual

TO_DATE 函數對數字的轉換
下麵是在TO_CHAR 函數中經常使用的幾種格式：
金額格式，如果是用9，不足的位數不會填充；但是使用0，就會在不足的位上補零

SELECT TO_CHAR(salary, '$999,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

SELECT TO_CHAR(salary, '$000,000.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

TO_NUMBER 函數對字元的轉換
使用 TO_NUMBER 函數將字元轉換成數值:
TO_DATE(CHAR,'fomat_model')

SELECT TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
FROM dual

通用函數

這些函數適用於任何數據類型，同時也適用於空值：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
NVL 函數：將空值轉換成一個已知的值
可以使用的數據類型有日期、字元、數字。
函數的一般形式:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
NVL2 函數：NVL2 (expr1, expr2, expr3) : expr1不為NULL，返回expr2；為NULL，返回expr3。

SELECT last_name,  salary, commission_pct,
       NVL2(commission_pct, 
            'SAL+COMM', 'SAL') income
FROM  employees 
WHERE department_id IN (50, 80);

NULLIF 函數：NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1

SELECT first_name, LENGTH(first_name) "expr1", 
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

COALESCE 函數
COALESCE 與 NVL 相比的優點在於 COALESCE 可以同時處理交替的多個值。
如果第一個表達式為空,則返回下一個表達式，對其他的參數進行COALESCE 。

-- 如果commision_pct 為空，返回salary，如果salary還為空就返回10.  

SELECT   last_name,
         COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;

條件表達式
在 SQL 語句中使用IF-THEN-ELSE 邏輯
使用兩種方法:
- CASE 表達式
- DECODE 函數
CASE 表達式示例

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

-- 練習：查詢部門號為 10, 20, 30 的員工信息, 若部門號為 10, 則列印其工資的 1.1 倍, 20 號部門, 則列印其工資的 1.2 倍, 30 號部門列印其工資的 1.3 倍數
SELECT employee_id,last_name,department_id,salary,
       CASE department_id WHEN 10 THEN salary*1.1
                          WHEN 20 THEN salary*1.2
                          WHEN 30 THEN salary*1.3
                          ELSE salary
       END AS "new_salary"
FROM employees
WHERE department_id in (10,20,30)

DECODE 函數示例

DECODE(col|expression, search1, result1 ,
               [, search2, result2,...,]
               [, default])

-- DECODE版本
SELECT employee_id,last_name,department_id,salary,
       DECODE (department_id,10, salary*1.1,
                             20, salary*1.2,
                             30, salary*1.3,
                             salary) AS "new_salary"
FROM employees
WHERE department_id in (10,20,30)

嵌套函數

單行函數可以嵌套。
嵌套函數的執行順序是由內到外。

Oracle_SQL函數-單行函數

SQL函數

SQL函數分類

單行函數

字元函數

大小寫控制函數 ：這類函數改變字元的大小寫

字元控制函數

數值函數

日期函數

轉換函數

隱式數據類型轉換

顯式的數據類型轉換

通用函數

嵌套函數

大小寫控制函數：這類函數改變字元的大小寫