單行函數

單行函數 :

操作數據對象

接受參數返回一個結果

只對一行進行變換

每行返回一個結果

可以轉換數據類型

可以嵌套

參數可以是一列或一個值

字符函數

大小寫控制函數

這類函數改變字符的大小寫。

顯示員工 Higgins 的信息 :

SELECT employee_id, last_name, department_id

FROM?? employees

WHERE? last_name = 'higgins';

no rows selected

SELECT employee_id, last_name, department_id

FROM?? employees

WHERE? LOWER(last_name) = 'higgins';

字符控制函數

這類函數控制字符 :

SELECT employee_id, CONCAT(first_name, last_name) NAME,

?????? job_id, LENGTH (last_name),

?????? INSTR(last_name, 'a') "Contains 'a'?"

FROM?? employees

WHERE? SUBSTR(job_id, 4) = 'REP';

數字函數

ROUND: 四舍五入

ROUND(45.926, 2)???????????????????? 45.93

TRUNC:? 截斷

TRUNC(45.926, 2)?????????????? 45.92

MOD: 求余

MOD(1600, 300)???????????????? 100

ROUND 函數

SELECT ROUND(45.923,2), ROUND(45.923,0),

?????? ROUND(45.923,-1)

FROM?? DUAL;

DUAL 是一個 ‘ 偽表 ’ ，可以用來測試函數和表達式

TRUNC 函數

SELECT? TRUNC(45.923,2), TRUNC(45.923),

??????? TRUNC(45.923,-2)

FROM?? DUAL;

MOD 函數

SELECT last_name, salary, MOD(salary, 5000)

FROM?? employees

WHERE? job_id = 'SA_REP';

日期

Oracle 中的日期型數據實際含有兩個值 : 日期和時間。

默認的日期格式是 DD-MON-RR.

SELECT last_name, hire_date

FROM?? employees

WHERE? last_name like 'G%';

函數 SYSDATE 返回 :

日期

時間

日期的數學運算

在日期上加上或減去一個數字結果仍為日期。

兩個日期相減返回日期之間相差的天數。

可以用數字除 24 來向日期中加上或減去小時。

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM?? employees

WHERE? department_id = 90;

日期函數

TO_CHAR 函數對日期的轉換

TO_CHAR(date, 'format_model')

格式 :

必須包含在單引號中而且大小寫敏感。

可以包含任意的有效的日期格式。

日期之間用逗號隔開。

日期格式的元素

SELECT last_name,

?????? TO_CHAR(hire_date, 'DD Month YYYY')

?????? AS HIREDATE

FROM?? employees;

TO_CHAR 函數對數字的轉換

TO_CHAR(number, 'format_model')

下面是在 TO_CHAR 函數中經常使用的幾種格式 :

SELECT TO_CHAR(salary, '$99,999.00') SALARY

FROM?? employees

WHERE? last_name = 'Ernst';

TO_NUMBER 和 TO_DATE 函數

使用 TO_NUMBER 函數將字符轉換成數字 :

TO_NUMBER(char[, 'format_model'])

?

使用 TO_DATE 函數將字符轉換成日期 :

TO_DATE(char[, 'format_model'])

通用函數

這些函數適用于任何數據類型，同時也適用于空值：

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')

使用 NVL 函數

SELECT last_name, salary, NVL(commission_pct, 0),

?? (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL

FROM employees;

使用 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 。

使用 COALESCE 函數

SELECT?? last_name,

???????? COALESCE(commission_pct, salary, 10) comm

FROM ????employees

ORDER BY commission_pct;

條件表達式

在 SQL 語句中使用 IF-THEN-ELSE 邏輯

使用兩種方法 :

CASE 表達式

DECODE 函數

CASE 表達式

在需要使用 IF-THEN-ELSE 邏輯時 :

CASE expr WHEN comparison_expr1 THEN return_expr1

???????? [WHEN comparison_expr2 THEN return_expr2

????????? WHEN comparison_exprn THEN return_exprn

????????? ELSE else_expr]

END

下面是使用 case 表達式的一個例子：

SELECT last_name, job_id, salary,

?????? CASE job_id WHEN 'IT_PROG'? THEN? 1.10*salary

?????????????????? WHEN 'ST_CLERK' THEN? 1.15*salary

?????????????????? WHEN 'SA_REP'?? THEN? 1.20*salary

?????? ELSE????? salary END???? "REVISED_SALARY"

FROM?? employees;

DECODE 函數

在需要使用 IF-THEN-ELSE 邏輯時 :

DECODE(col|expression, search1, result1

????? ????????????????? ???[, search2, result2,...,]

????? ????????????????? ???[, default])

SELECT last_name, job_id, salary,

???? ??DECODE(job_id, 'IT_PROG',? 1.10*salary,

????????????????????? 'ST_CLERK', 1.15*salary,

????????????????????? 'SA_REP',?? 1.20*salary,

????????????? salary)

?????? REVISED_SALARY

FROM?? employees;

嵌套函數

單行函數可以嵌套。

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

SELECT last_name,

?????? NVL(TO_CHAR(manager_id), 'No Manager')

FROM?? employees

WHERE? manager_id IS NULL;

總結

通過本章學習，您應該學會 :

使用函數對數據進行計算

使用函數修改數據

使用函數控制一組數據的輸出格式

使用函數改變日期的顯示格式

使用函數改變數據類型

使用 NVL 函數

使用 IF-THEN-ELSE 邏輯

?

Java喬曉松-oracle的單行函數（日期函數和數字函數）