單行函數(shù)

單行函數(shù) :

操作數(shù)據(jù)對(duì)象

接受參數(shù)返回一個(gè)結(jié)果

只對(duì)一行進(jìn)行變換

每行返回一個(gè)結(jié)果

可以轉(zhuǎn)換數(shù)據(jù)類型

可以嵌套

參數(shù)可以是一列或一個(gè)值

字符函數(shù)

大小寫控制函數(shù)

這類函數(shù)改變字符的大小寫。

顯示員工 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';

字符控制函數(shù)

這類函數(shù)控制字符 :

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

數(shù)字函數(shù)

ROUND: 四舍五入

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

TRUNC:? 截?cái)?

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

MOD: 求余

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

ROUND 函數(shù)

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

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

FROM?? DUAL;

DUAL 是一個(gè) ‘ 偽表 ’ ，可以用來測(cè)試函數(shù)和表達(dá)式

TRUNC 函數(shù)

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

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

FROM?? DUAL;

MOD 函數(shù)

SELECT last_name, salary, MOD(salary, 5000)

FROM?? employees

WHERE? job_id = 'SA_REP';

日期

Oracle 中的日期型數(shù)據(jù)實(shí)際含有兩個(gè)值 : 日期和時(shí)間。

默認(rèn)的日期格式是 DD-MON-RR.

SELECT last_name, hire_date

FROM?? employees

WHERE? last_name like 'G%';

函數(shù) SYSDATE 返回 :

日期

時(shí)間

日期的數(shù)學(xué)運(yùn)算

在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。

兩個(gè)日期相減返回日期之間相差的天數(shù)。

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

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

FROM?? employees

WHERE? department_id = 90;

日期函數(shù)

TO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換

TO_CHAR(date, 'format_model')

格式 :

必須包含在單引號(hào)中而且大小寫敏感。

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

日期之間用逗號(hào)隔開。

日期格式的元素

SELECT last_name,

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

?????? AS HIREDATE

FROM?? employees;

TO_CHAR 函數(shù)對(duì)數(shù)字的轉(zhuǎn)換

TO_CHAR(number, 'format_model')

下面是在 TO_CHAR 函數(shù)中經(jīng)常使用的幾種格式 :

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

FROM?? employees

WHERE? last_name = 'Ernst';

TO_NUMBER 和 TO_DATE 函數(shù)

使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字 :

TO_NUMBER(char[, 'format_model'])

?

使用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期 :

TO_DATE(char[, 'format_model'])

通用函數(shù)

這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

COALESCE (expr1, expr2, ..., exprn)

NVL 函數(shù)

將空值轉(zhuǎn)換成一個(gè)已知的值：

可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。

函數(shù)的一般形式 :

NVL(commission_pct,0)

NVL(hire_date,'01-JAN-97')

NVL(job_id,'No Job Yet')

使用 NVL 函數(shù)

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

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

FROM employees;

使用 NVL2 函數(shù)

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 函數(shù)

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 函數(shù)

COALESCE 與 NVL 相比的優(yōu)點(diǎn)在于 COALESCE 可以同時(shí)處理交替的多個(gè)值。

如果第一個(gè)表達(dá)式為空 , 則返回下一個(gè)表達(dá)式，對(duì)其他的參數(shù)進(jìn)行 COALESCE 。

使用 COALESCE 函數(shù)

SELECT?? last_name,

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

FROM ????employees

ORDER BY commission_pct;

條件表達(dá)式

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

使用兩種方法 :

CASE 表達(dá)式

DECODE 函數(shù)

CASE 表達(dá)式

在需要使用 IF-THEN-ELSE 邏輯時(shí) :

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 表達(dá)式的一個(gè)例子：

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 函數(shù)

在需要使用 IF-THEN-ELSE 邏輯時(shí) :

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;

嵌套函數(shù)

單行函數(shù)可以嵌套。

嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。

SELECT last_name,

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

FROM?? employees

WHERE? manager_id IS NULL;

總結(jié)

通過本章學(xué)習(xí)，您應(yīng)該學(xué)會(huì) :

使用函數(shù)對(duì)數(shù)據(jù)進(jìn)行計(jì)算

使用函數(shù)修改數(shù)據(jù)

使用函數(shù)控制一組數(shù)據(jù)的輸出格式

使用函數(shù)改變?nèi)掌诘娘@示格式

使用函數(shù)改變數(shù)據(jù)類型

使用 NVL 函數(shù)

使用 IF-THEN-ELSE 邏輯

?

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