Oracle学习（单行函数）

nx404h6sj9

　　一.单行函数介绍
　　---单行函数对单行操作
　　---每行返回一个结果
　　---有可能返回值与原参数数据类型不一致(转换函数)
　　---单行函数可以写在SELECT,WHERE,ORDER BY子句中
　　---有些函数没有参数，有些函数有一个或多个参数
　　---函数可以嵌套
　　分类：字符函数，数字函数，日期函数，转换函数，通用函数
　　

　　二.字符函数
　　1.简介
　　定义：主要指参数类型是字符型，不同函数返回值可能是字符或数字类型
　　<1>.LOWER：全小写
　　   LOWER('SQL Course')——>sql course
　　<2>.UPPER：全大写
　　   UPPER('SQL Course')——>SQL COURSE
　　<3>.INITCAP：首字母大写
　　   INITCAP('SQL Course')——>Sql course
　　<4>.CONCAT：字符串连接
　　   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
　　

　　四.日期函数
　　<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 employeesWHERE SUBSTR(hire_date,-2,2)='98';
　　<7>.TRUNC(date[,'fmt'])对日期进行指定格式的截断操作。按照YEAR、MONTH、DAY等进行截断
　　   SELECT employee_id, hire_date, TRUNC(hire_date,'MONTH')FROM employeesWHERE SUBSTR(hire_date,-2,2)='98';
　　<8>.EXTRACT：返回从日期类型中取出指定年、月、日
　　   SELECT last_name, hire_date,EXTRACT (MONTH FROM HIRE_DATE) MONTHFROM  employeesWHERE department_id = 90;
　　

　　五.转换函数
　　---通常在字符类型、日期类型、数字类型之间进行显性转换。
　　

　　<1>.TO_CHAR(date|number|'fmt'):把日期类型/数字类型的表达式或列转换为字符类型
　　--‘fmt’:指的是需要显示的格式，需要写在单引号中，并且是大小写敏感，可包含任何有效的日期格式
　　常用日期格式：
　　---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;

　　

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

　　

　　<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;
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 '无'
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 employeeswhere commission_pct is notnullorderbylast_name;