Oracle单行函数
Oracle学习（单行函数）
一.单行函数介绍
---单行函数对单行操作
---每行返回一个结果
---有可能返回值与原参数数据类型不一致(转换函数)
---单行函数可以写在SELECT,WHERE,ORDER BY子句中
---有些函数没有参数，有些函数有一个或多个参数
---函数可以嵌套 www.2cto.com
分类：字符函数，数字函数，日期函数，转换函数，通用函数
二.字符函数
1.简介
定义：主要指参数类型是字符型，不同函数返回值可能是字符或数字类型
<1>.LOWER：全小写
LOWER('SQL Course')——>sql course
<2>.UPPER：全大写
UPPER('SQL Course')——>SQL COURSE
<3>.INITCAP：首字母大写
INITCAP('SQL Course')——>Sql course
<4>.CONCAT：字符串连接 www.2cto.com
CONCAT('Good','String')——>GoodString
<5>.SUBSTR：字符串截取
SUBSTR('String',1,3)——>Str
<6>.LENGTH：返回字符串长度
LENGTH('String')——>6
<7>.INSTR：返回一个字符串在另一个字符串中的位置
INSTR('String','r')——>3
<8>.LPAD：左填充
LPAD(sal,10,'*')——>******5000
<9>.RPAD：右填充
RPAD(sal,10,'*')——>5000******
<10>.TRIM：去掉左右两边指定字符
TRIM('S' FROM 'SSMITH')——>MITH
<11>.REPLACE:替换字符串
REPLACE('abc','b','d')——>adc
三.数字函数
<1>.ROUND:四舍五入函数
ROUND(12.3)——>12
ROUND(12.5)——>13
<2>.TRUNC:截断函数
TRUNC(65.654,0)——>65
TRUNC(65.654,-1)——>60
<3>.MOD:取余函数
MOD(10,12)——>10
MOD(7,12)——>7
MOD(10,4)——>2
www.2cto.com
四.日期函数
<1>.SYSDATE:返回系统日期
<2>.MONTHS_BETWEEN:返回两个日期间隔的月数
select months_between('02-2月-06','02-2月-06') from dual;
<3>.ADD_MONTHS:在指定日期基础上加上相应的月数
select add_months('02-2月-06',8) from dual;
<4>.NEXT_DAY:返回某一日期的下一个指定日期
select next_day('1-2月-12','星期一') next_day from dual;
<5>.LAST_DAY:返回指定日期当月最后一天的日期
select last_day('1-2月-12') last_day from dual;
<6>.ROUND(date[,'fmt'])对日期进行指定格式的四舍五入操作，按照YEAR、MONTH、DAY等进行四舍五入
SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';
<7>.TRUNC(date[,'fmt'])对日期进行指定格式的截断操作。按照 YEAR、MONTH、DAY等进行截断
SELECT employee_id, hire_date, TRUNC(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';
<8>.EXTRACT：返回从日期类型中取出指定年、月、日
SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) MONTH FROM employees WHERE department_id = 90;
五.转换函数
---通常在字符类型、日期类型、数字类型之间进行显性转换。
<1>.TO_CHAR(date|number|'fmt'):把日期类型/数字类型的表达式或列转换为字符类型
--‘fmt’:指的是需要显示的格式，需要写在单引号中，并且是大小写敏感，可包含任何有效的日期格式 www.2cto.com
常用日期格式：
---YYYY：4位数字表示年份 ---DY:星期的英文前三位字母
---YY：两位数字年份，但是无世纪转换 ---DAY:星期的英文拼写
---RR：2位数字表示年份，有世纪转换 ---D：数字表示一星期的第几天，星期天是一周的第一天
---YEAR:年份的英文拼写 ---DD：数字表示一个月中的第几天
---MM：两位数字表示月份 ---DDD：数字表示一年中的第几天
---MONTH:月份英文拼写 ---AM或PM：上下午表示
---HH 或HH12或HH24：数字表示小时 ---MI：数字表示分钟
HH12代表12小时计时,HH24代表24小时计时 ---SS：数字表示秒；
特殊格式：
TH：显示数字表示的英文序数词，如：DDTH显示天数的序数词
SP：显示数字表示的拼写
SPTH：显示数字表示的序数词的拼写
select to_char(sysdate,’ddspth’) from dual;
“字符串”:如在格式中显示字符串，需要两端加双引号
select to_char(sysdate,’dd “of” month ‘) from dual;
数字到字符型转换：
进行数字类型到字符型转换，格式中的宽度一定要超过实际列宽度，否则会显示为###
-- 9：一位数字
-- 0：一位数字或前
-- $：显示为美元符
-- L：显示按照区域
-- .：小数点
-- ,：千位分割符
select to_char(9832, '$9,999.00') from dual;
<2>.TO_NUMBER(char[,’fmt’]): 把字符类型列或表达式转换为数字类型
select to_number('9832', 9999) from dual;
www.2cto.com
<3>.TO_DATE(char[,‘fmt’]): 把字符类型列或表达式转换为日期类型
select to_date('20120304', 'yy-mm-dd') from dual;
六.通用函数
<1>NVL(表达式1，表达式2)函数：该函数功能是空值转换，把空值转换为其他值，解决空值问题。如果表达式1为空，则表达式2就是要转换成的值。
注意：数据格式可以是日期、字符、数字，但数据类型必须匹配
select nvl(null,1) from dual;
select nvl(2,1) from dual;
www.2cto.com
<2>.NVL2(表达式1, 表达式2, 表达式3)函数：该函数是对第一个参数进行检查。如果第一个参数不为空，则输出第二个参数，如果第一个参数为空，则输出低三个参数，表达式1可以为任何数据类型。
select nvl2(null,1,2) from dual;
select nvl2(3,1,2) from dual;
<3>.NULLIF(表达式1，表达式2)函数：该函数主要完成两个参数的比较。当两个参数不相等时，返回值是第一个参数值；当两个参数相等时，返回值是空值。
select nullif(1,1) from dual;
返回值是：
不是null;
不是null;
select nullif(1,2) from dual;
<4>.COALESCE(表达式1, 表达式2, ... 表达式n)函数：该函数是对NVL函数的扩展。COALESCE函数功能是返回第一个不为空的参数，参数个数不受限制。
select coalesce(null,null,0,1) from dual;
<5>.CASE表达式：
SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN '低'
WHEN 0.2 THEN '中'
WHEN 0.3 THEN '高'
ELSE '无' www.2cto.com
END) Commission
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY last_name;
<6>.DECODE函数：
DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…，][,缺省值])
select last_name, commission_pct,decode( commission_pct,0.1,'低',0.2,'中',0.3 , '高','无') commission
from employees where commission_pct is not null orderbylast_name;
