第三部分:Oracle函数

Oracle函数学习

Oracle函数是Oracle提供的用来进一步修饰或者处理数据的方法，如下学习内容主要包括单行函数、多行函数、转化函数、其它函数。

单行函数

单行函数包括字符函数和日期函数，单行函数特点

不改变真实数据，只是对数据做了进一步修饰或者处理显示

可以和字段混合使用，

字符函数

字符函数是查询结果中字符信息显示设置具体函数如下表：

函数	功能
INITCAP(char)	首字母大写
LOWER(char)	转换为小写
UPPER(char)	转换为大写
LTRIM(char,set)	左剪裁
RTRIM(char,set)	右裁剪
TRANSLATE(char,from,to)	按字符翻译
REPLACE(char,search str,replace,str)	按字符串替换
INSTR(char,substr)	查找子串位置
SUBSTR(char,pos,len)	截取子串
CONCAT(char1,char2)	连接字符串

针对于字符函数的几个特殊函数做介绍：

• 字符翻译，按照对应的规则将原始值翻译成规则中的对应值

  -- 如下的规则是 A->1  B->2  C->3  D->4
  select ENAME，translate(ENAME,'ABCD','1234') from EMP;
  

  运行结果如下：
  

数值函数

数值函数的测试借用伪表

不是真是存在的表，而是为了数据验证而临时存在的表
表名为dual

下面详细介绍各个函数功能

函数	功能
ABS(n)	取绝对值
CEIL(n)	向上取整
SIN(n)	取正弦
COS(n)	取余弦
SIGN(n)	取符号
FLOOR(n)	向下取整
POW(m,n)	m的n次幂
ROUND(m,n)	四舍五入
TRUNC(m,n)	截断
SQRT(n)	取平方根

针对于字符函数的几个特殊函数做介绍：

• 四舍五入函数ROUND(m,n):其中m是要进行四舍五入的数据，n是保留位
• 截断函数TRUNC(m,n)同理

下面是测试数值函数用的语句：

select abs(-1),ceil(2.2),floor(3.3),power(2,3),mod(5,2),round(4.55),trunc(99.999999,2),sqrt(4) from dual;

运行结果如下：

日期函数

首先关于日期函数格式以及转化可以参考这一篇文章，Oracle中日期格式讲解
如下只介绍跟日期有关的函数。Oracle中的日期默认显示是"01-1月-18"(日-月-年)
日期函数跟数值以及字符函数都不太一样，下面详细介绍每个日期函数的用法：

1. MONTHS_BETWEEN(date1,date2) 返回结果是date1-date2的月数 按照默认月份格式输入

   select months_between(‘01-1月-17’,‘01-2月-18’) from dual;

   

2. ADD_MONTHS(date,month_number) 将month数量添加到date上并返回日期

   select add_months(‘01-1月-16’,10) from dual

   

3. NEXT_DAY(date,‘星期几’)，返回对应日期后的星期对应的新日期

   返回5月7号后的第一个星期二对应的日期
   select next_day(‘07-5月-18’,‘星期二’) from dual;

   

4. LAST_DAY(date),返回指定日期所在月的最后一天

   select last_day(‘07-5月-18’) from dual;

   

5. ROUND(date,[‘TEAR’|‘MONTH’|‘DAY’]) 按照指定格式对日期进行四舍五入

   • 以年为例，则参照对应月份，大于6进1，返回下一年的第一天，小于等于6则返回本年的第一天，

     select round(to_date(‘16-6月-13’),‘YEAR’),round(to_date(‘16-7月-13’),‘YEAR’) from dual;

     

   • 以月为例咋看对应天数是否大于15，大于15进1，返回下一个月的第一天小于等于15舍掉，则返回本月份第一天

     select round(to_date(‘15-6月-13’),‘MONTH’),round(to_date(‘16-6月-13’),‘MONTH’) from dual;

     

   • 以天为例，则看对应星期是否大于等于周三，大于周三的话，返回当前周周六的日期，小于等于周三的话，返回当前周周日的日期。

     select round(to_date(‘15-5月-19’),‘DAY’),round(to_date(‘16-5月-13’),‘DAY’) from dual;

     

6. TRUNC(date,[‘TEAR’|‘MONTH’|‘DAY’]) 截取函数按照指定规则进行截取，规则同上。

多行函数

多行函数对一组数据进行运算，针对一组数据只返回一个结果，也成为分组函数。

select max(SAL) from EMP;

注意：多行函数不能直接和字段直接混用，除非分组

select ename,max(SAL) from EMP; ----报错

注意：多行函数不能直接和单行函数直接混用，除非分组

select lower(ename),max(SAL) from EMP; ----报错

注意：多行函数可以和多行函数直接混用

select max(SAL),min(sal),avg(sal),sum(sal),count(*) from EMP;

下面详细介绍各个函数功能

函数	功能
MAX()	求最大值
MIN()	求最小值
COUNT()	计数
AVG()	求平均值
SUM()	求和

转化函数

转换函数

--to_number(数值类型的字符)：将字符转换为数值
--to_char(数值或者日期)：将数值或者日期转换为字符
其中to_char和to_number之间的转换时隐式自动的可以不用标明显示
--to_date(日期格式的字符):将字符转换为日期

1. to_number

   select to_number(‘123’)+2 from dual;

2. to_char 数字转字符可以指定格式

   select to_char(003) from dual;
   select to_char(123456789,‘1.999,999,999’) from dual;

   其中数字一定要小于或等于实际占位符号，否则会出现格式错乱

   select to_char(11111111123456789,’$999,999,0000.0000’) from dual;

   
   to_char函数的格式补充：

数字格式	含义
9	代表一位数字，如果没有则不进行显示
0	代表一位数字，如果没有则用0填充
$	显示美元符号
L	显示本地货币符号
.	显示小数点
，	显示千分位

to_char函数格式化输出

>  select to_char(12122122121,'$999,999,999,999.9999'),to_char(12122122121,'L000,000,000,000.0000') from dual;

3. to_date
   具体可以参考这篇文章 to_date用法

转化函数UML图如下:

其他函数

NVL函数

语法：NVL(EXP1, EXP2)，如果exp1的值为null，则返回exp2的值，否则返回exp1的值
示例：select ename,deptno,sal+NVL(comm,0) from EMP;  --有奖金则加上奖金的工资

运行结果：

NVL2函数

语法：NVL2(EXP1, EXP2, EXP3) 如果exp1的值为null，则返回exp2的值，否则返回exp3的值
示例：select ename,deptno,NVL2(comm,sal+comm,sal)   --查询同上

运行结果：

DECODE函数

语法：DECODE(VALUE,IF1,THEN1,IF2,THEN2,……,ELSE)，字段名，如果是IF1，那么处理THEN1...  如果字段的值和decode中的条件值相同则执行对象的处理，如果没有则执行公共处理
示例：select ename,job,decode(job,'CLERK','职员'，'SALESMAN','售货员','默认选项') from EMP;

运行结果：