Oracle 之 单行函数

单行函数

•操作数据对象

•接受参数返回一个结果

•只对一行进行变换

•每行返回一个结果

•可以转换数据类型

•可以嵌套

•参数可以是一列或一个值

1.字符控制函数

这类函数改变字符的大小写

函数	结果
LOWER('SQL Course')	sql course
UPPER('SQL Course')	SQL COURSE
INITCAP('SQL Course')	Sql Course

 

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  LOWER(last_name) = 'higgins';

2.字符控制函数

这类函数控制字符:

函数	结果
CONCAT('Hello', 'World')	HelloWorld
SUBSTR('HelloWorld',1,5)	Hello
LENGTH('HelloWorld')	10
INSTR('HelloWorld', 'W')	6
LPAD(salary,10,'*')	*****24000
RPAD(salary, 10, '*')	24000*****
TRIM('H' FROM 'HelloWorld')	elloWorld
REPLACE(‘abcd’,’b’,’m’)	amcd

例如：

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

 

3.数字函数

•ROUND: 四舍五入

ROUND(45.926, 2)   ---->   45.93

•TRUNC:  截断

TRUNC(45.926, 2)  ---->    45.92

•MOD: 求余

MOD(1600, 300)   --->   100

SELECT ROUND(45.923,2), ROUND(45.923,0),
       ROUND(45.923,-1)
FROM   DUAL;

DUAL 是一个‘伪表’，可以用来测试函数和表达式

SELECT  TRUNC(45.923,2), TRUNC(45.923),
        TRUNC(45.923,-2)
FROM   DUAL;

SELECT last_name, salary, MOD(salary, 5000)
FROM   employees
WHERE  job_id = 'SA_REP';

 

4.日期

•Oracle 中的日期型数据实际含有两个值: 日期和时间。

函数SYSDATE 返回: 日期  时间

•在日期上加上或减去一个数字结果仍为日期。

•两个日期相减返回日期之间相差的天数。

–日期不允许做加法运算，无意义

•可以用数字除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	日期截断

 

5.转换函数

5.1 TO_CHAR函数对日期的转换

•必须包含在单引号中而且大小写敏感。

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

•日期之间用逗号隔开。

SELECT TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) FROM dual;

SELECT last_name,
       TO_CHAR(hire_date, 'DD Month YYYY')
       AS HIREDATE
FROM   employees;

select employee_id,last_name,hire_date
from employees
where to_char(hire_date,’yyyy-mm-dd’) = ‘1987-09-17’

5.2 TO_CHAR函数对数字的转换

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

 

5.3 TO_DATE 函数对字符的转换

使用 TO_DATE 函数将字符转换成数字

TO_DATE('2012年10月29日 08:10:21','yyyy"年"mm"月"dd"日"hh:mi:ss')
From dual

 

5.4 TO_NUMBER函数对字符的转换

TO_NUMBER(‘￥1,234,567,890.00’,’L999,999,999,999.99’)
from dual

 

6.通用函数

这些函数适用于任何数据类型，同时也适用于空值：

•NVL (expr1, expr2)

•NVL2 (expr1, expr2, expr3)

•NULLIF (expr1, expr2)

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

6.1 NVL

将空值转换成一个已知的值：（也就是给了一个默认值）

•可以使用的数据类型有日期、字符、数字。

•函数的一般形式:

–NVL(commission_pct,0)

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

–NVL(job_id,'No Job Yet')

 

6.2 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);

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

 

 6.4 COALESCE

•COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。

•如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

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

 

7.条件表达式

7.1 CASE 表达式

例如：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

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;

7.2 DECODE 函数

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;

 

8.嵌套函数

•单行函数可以嵌套。

•嵌套函数的执行顺序是由内到外。

SELECT last_name,
       NVL(TO_CHAR(manager_id), 'No Manager')
FROM   employees
WHERE  manager_id IS NULL;