1 SQL函数介绍

单行函数

• 操作数据对象
• 接受函数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值

2 DUAL 伪表

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

获取当前时间：

select sysdate from dual

3. 字符函数

3.1 大小写控制函数

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

select lower('HELLO') from dual -- 小写

select upper('hello world') from dual -- 大写

select initcap('hello world') from dual -- 首字母大写

3.2 字符控制函数

select concat('hello','world') from dual;--合并

select substr('helloworld',1,3) from dual;--截取

select length('helloworld') from dual;--长度

select instr('helloworld','w') from dual;--查找

4 数字函数

select round(45.0418,3) from dual

select trunc(45.0498,2) from dual

select mod(3,2) from dual

5 日期函数

Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒。

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

–可以只指定年的后两位在20世纪存放21世纪的日期。

–同样可以在21世纪存放20世纪的日期

5.1 日期的数学运算

• 在日期上加上或减去一个数字结果仍为日期
• 两个日期相减返回日期之间相差的天数

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

FROM employees

WHERE department_id = 90

5.2 日期函数

select sysdate from dual

select e.*,sysdate-e.emp_birthday,(sysdate-e.emp_birthday )/7 from emp e

select months_between(sysdate,'01-1月-15') from dual

6 转换函数

6.1 隐式数据类型转换

select '123'+321 from dual--隐式

6.2 TO_CHAR函数对日期的转换

• 必须包含在单引号中而且大小写敏感。
• 可以包含任意的有效的日期格式。
• 可以使用fm去掉多余的空格或者前导零。
• 与日期指用逗号隔开。

TO_CHAR(date, 'format_model')

select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual

6.3 TO_NUMBER

TO_NUMBER('123.45') 结果 123.45

TO_NUMBER('999,999.99') 结果 123,456.78

6.4 TO_DATE

select to_date(’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual

select * from user_info where

user_time>to_date('2015-01-01','yyyy-MM-dd')

7 通用函数

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

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

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

7.1 NVL函数

将空值转换成一个已知的值：

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

?函数的一般形式:

–NVL(commission_pct,0)

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

–NVL(job_id,'NoJobYet')

7.2 NVL2函数

7.3 NULLIF函数

7.4 COALESCE

• COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值。
• 如果第一个表达式非空,则返回这个表达式，对其他的参数进行COALESCE。

8 条件表达式

使用两种方法:

– CASE表达式

– DECODE函数

8.1 CASE表达式

在需要使用IF-THEN-ELSE 逻辑时

select e.*,
 CASE e.emp_mark
 WHEN 1 THEN 'Java初级程序员'
 WHEN 2 THEN 'Java中级程序员'
 WHEN 3 THEN 'Java高级程序员'
 ELSE
 '未入门'
 END CASE
 from emp e

8.2 DECODE函数

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

 select e.*,
 decode(e.emp_mark,
 1,'Java初级程序员',
 2,'Java中级程序员',
 3,'Java高级程序员',
 '未入门')
 from emp e