一、通用函数
函数 | 说明 |
---|---|
NVL | 语法:NVL(expr1,expr2) |
说明:如果expr1为NULL,则该函数显示expr2的值; | |
例子: SELECT SALARY, NVL(TO_CHAR(COMMISSION_PCT), 0) FROM EMPLOYEES; | |
NVL2 | 语法:NVL2(expr1,expr2,expr3) |
说明:如果expr1的值为NULL,则该函数显示expr3的值;不为NULL,显示expr2的值; | |
例子: 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) |
说明:如果expr1=expr2,返回NULL;若不等,则返回第一个表达式的值; | |
例子: SELECT FIRST_NAME, LENGTH(FIRST_NAME) "expr1", LAST_NAME, LENGTH(LAST_NAME) "expr2", NULLIF(LENGTH(FIRST_NAME), LENGTH(LAST_NAME)) RESULT FROM EMPLOYEES; | |
COLESCE | 语法:COALSECE(expr1,expr2,expr3) |
说明:如果全为NULL,则函数值为NULL;若有一项不为NULL,则显示那一项exprN;若三项都不为空,则显示最前面的一项expr; | |
例子:
|
二、条件表达函数
函数 | 说明 |
---|---|
CASE | 语法: CASE expr WHEN comparison_expr1THEN return_expr1 [WHENcomparison_expr2 THENreturn_expr2 WHENcomparison_exprn THENreturn_exprn ELSE else_expr] END |
说明:
| |
例句: SELECT last_name,salary, (CASE WHEN salary<5000 THEN 'Low' WHEN salary<10000 THEN 'Medium' WHEN salary<20000 THEN 'Good' ELSE 'Excellent' END) qualified_salary FROM employees; | |
DECODE | 语法: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) |
说明:decode 具有和 IF-THEN-ELSE 一样的功能。 | |
例句: SELECT PRODUCT_ID, DECODE(WAREHOUSE_ID, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non-domestic') "Location of inventory" FROM INVENTORIES WHERE PRODUCT_ID < 1775; |
三、嵌套函数
例句:SELECT LAST_NAME,
SALARY,
DECODE(TRUNC(SALARY / 2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;