Oracle学习（单行函数）

一.单行函数介绍

---单行函数对单行操作

---每行返回一个结果

---有可能返回值与原参数数据类型不一致(转换函数)

---单行函数可以写在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;