Oracle预定义函数
Oracle预定义函数
1. 简介
oralce 的函数分为
- 单行函数
- 字符函数
- 日期函数
- 数字函数
- 转换函数
- 分组函数（后续介绍）
学前知识：哑表 dual
dual 是一个虚拟表，辅助查找和运算，通常用在select语句中，作为查询的目标表结构，oracle保证dual里面永远只有一条记录
例如：
显示1+1 的结果，可以看出，dual很多时候是为了构成select的标准语法
select 1+1 from dual;
2. 字符函数
LOWER:转换成小写
UPPER:转换成大写
INITCAP:首字母变成大写，其余都小写
CONCAT:字符串的连接
SUBSTR(str,start,length)或者SUBSTR(str,start):字符串的截取
LENGTH:求字符串的长度
NVL : 转换null 的值。(前边已经用过)
nvl2:
- DECODE：
2.1 LOWER 把字符转为小写
例如：把'HELLO' 转换为小写
select lower('HELLO')
from dual;
例如: 把s_emp 表中的last_name 列的值转换为小写
select lower(last_name)
from s_emp;
2.2 UPPER 把字符转换为大写
例如:把'world'转换为大写
select upper('world')
from dual;
例如:把s_emp 表中的last_name 列的值转换为大写
select upper(last_name)
from s_emp;
例如:查询s_emp 表中名字为Ngao 的人信息 这样是查不到:
select last_name,salary,dept_id
from s_emp
where last_name='NGAO';
这样就可以查询到了:
select last_name,salary,dept_id
from s_emp
where upper(last_name)='NGAO';
2.3 initcap 把字符串首字母大写
例如:把'hELLO'转换为首字母大写,其余字母小写
select initcap('hELLO')
from dual;
2.4 concat 把俩个字符串连接在一起(类似之前的||的作用)
例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg
select concat('hello','world') msg
from dual;
例如:把first_name 和last_name 俩个列的值连接到一起
select concat(first_name,last_name)
from s_emp;
2.5 substr 截取字符串
例如:截取'hello'字符串,从第2 个字符开始,截取后面的3 个字符
select substr('hello',2,3)
from dual;
out: ell
2.6 length 获得字符串长度
例如:获得'world'字符串的长度
select length('world')
from dual;
例如:获得s_emp 表中last_name 列的每个值的字符长度
select length(last_name)
from s_emp;
2.7 nvl 替换列中为null 的值
nvl(要输出的列名，为空的时候要被替换的值) //要替换的值类型必须要和之前保持一致
select id,last_name,nvl(commission_pct,0) from s_emp;
2.7.1 NVL与NVL2两个函数的用法和区别？
NVL (expr1, expr2)：expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ：expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
2.8 instr 查找字符串
INSTR方法的格式为
- INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)
- 返回找到的位置，如果找不到则返回0.
例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR',在字符串中查找'OR'，从第三个字符位置开始查找"OR"，取第三个字后第2个匹配项的位置。
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "aaa" FROM DUAL
out: 14
3. 数字函数
ROUND:四舍五入
TRUNC:截取，不进行四舍五入
MOD:取余
3.1 round 四舍五入
切记-1 代表保存小数点后一位，0 保留到个位，1 保留到10 位
round(arg1,arg2)
第一个参数表示要进行四舍五入操作的数字
第二个参数表示保留到哪一位(负数代表小数点之前，0，正数代表小数点之后)
0 代表保留到个位！！-1 代表保存到十位
select round(45.67) from dual; 46
select round(45.67,1) from dual; 45.7
select round(45.67,2) from dual; 45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
3.2 trunc 截取到某一位
trunc(arg1,arg2)
和round 的用法一样,但是trunc 只舍去不进位
select trunc(45.67) from dual; 45
select trunc(45.67,1) from dual; 45.6
select trunc(45.67,2) from dual; 45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual; 0
select trunc(55.67,-2) from dual; 0
3.3 mod 取余
mod(arg1,arg2)
- 第一个参数表示要进行取余操作的数字
- 第二个参数表示参数1 和谁取余 例如: 把10 和3 进行取余(10 除以3 然后获取余数) select mod(10,3) from dual;
4. 日期函数
sysdate 关键字
MONTHS_BETWEEN:两个日期之间的月数，如果是正数前面的值大于后面的值
ADD_MONTHS:在指定日期上增加月数
NEXT_DAY:指定日期的下一个星期几是哪天
LAST_DAY:指定日期的最后一天
ROUND:对指定日期进行四舍五入
TRUNC:对指定日期进行截取
4.1 sysdate 关键字
表示系统的当前时间
显示时间:当前时间
SELECT sysdate from dual OUT: 2021-02-18 15:39:07
注意：sysdate 进行加减操作的时候，单位是天
例如明天这个时候
SELECT sysdate+1 from dualout: 2021-02-19 15:41:12
例如显示1小时之后的时候
SELECT sysdate+1/24 from dualout: 2021-02-18 16:42:14
4.2 months_between
俩个时间点之间相差多少个月(单位是月)
30 天之后和现在相差多少个月
SELECT MONTHS_BETWEEN (sysdate+30, sysdate) from dual;
out: 1.06451612
4.3 add_months
返回一个日期数据:表示一个时间点,往后推x 月的日期
例如:
当前时间往后推2 个月
SELECT ADD_MONTHS (sysdate, 2) from dual;
out: 2021-04-18 15:56:02
4.4 next_day
返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
例如: 当前时间的下一个星期5 是哪一个天
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL
OUT: 2021-02-19 15:57:26
4.5 last_day
返回一个日期数据:表示一个日期所在月份的最后一天
例如: 当前日期所在月份的最后一天
SELECT LAST_DAY(SYSDATE ) FROM DUAL
OUT: 2021-02-28 16:00:44
4.6 round
对日期进四舍五入,返回操作后的日期数据。逢16 日往月份进一,逢7 月往年份进一
round(sysdate,'year/y/yy/yyy/yyyy') 年7 月节点 round(sysdate,'mm/month') 月16 号节点 round(sysdate,'d/day') 星期星期四节点 round(sysdate,'dd') : 天-》12 点节点 例如: 把当前日期四舍五入到月（年月日.时分秒把这个看错数字就可以了） 今天2021 年2 月18 日四舍五入到月，就要看日是否大于16？大于进一，不大于不进一，同时舍弃为1
SELECT ROUND(sysdate, 'mm') FROM DUAL
out: 2021-03-01 00:00:00
把当前日期四舍五入到年 大致算一下，今天已经2 月了，所以不满足大于节点7 进一位，同时舍弃年前面的值
SELECT ROUND(sysdate, 'yyyy') FROM DUAL
out: 2021-01-01 00:00:00
4.7 trunc
对日期进行截取和round 类似,但是只舍弃不进位 trunc（sysdate,'yyyy/year'） --返回当年第一天。 trunc（sysdate,'mm/month'） --返回当月第一天。 trunc（sysdate,'d/day'） --返回当前星期的第一天。 trunc（sysdate,'dd'）--返回当前年月日 截取和round 基本是一样的只是，不进位而已。
5. 类型转换函数
- TO_CHAR 将日期或者数值转换成字符串
- TO_NUMBER 将字符串转换成数字
- TO_DATE 将日期字符串转换成日期
5.1 to_char 把日期转换为字符
例如: 把当前日期按照指定格式转换为字符串
SELECT TO_CHAR(SYSDATE,'yyyy-MM-dd') from dual
out: 2021-02-18
日期格式： yyyy/YYYY：四位数的年份 rrrr：四位数的年份 yy：两位数的年份 rr：两位数的年份 mm：两位数的月份（数字） D：一周的第几天 DD：一月的第几天 DDD ：一年的第几天 YEAR：英文的年份 MONTH：英文全称的月份 mon：英文简写的月份 ddsp：英文的第几天 ddspth：英文序列数的第几天 DAY：全英文的星期 DY：简写的英文星期 hh：小时 mi：分钟 ss：秒 AM:上午 PM:下午
5.2 to_char 把数字转换为字符
L : 本地货币符号 $ : $ . : 小数点 , : 千分符 9 : 0-9 0 : 0-9, 如果位数不足，强制补0
例如:
select to_char(salary,'$999,999.00')
from s_emp;
fm 表示去除结果显示中的开始的空格 L 表示系统本地的货币符号 select to_char(salary,'fmL999,999.00') from s_emp; to_number 把字符转换为数字
5.3 to_number 把字符转换为数字
例如:
select to_number('1000')
from dual;
out:1000
5.4 to_date 把字符转换为日期
.TO_DATE(char, ['fmt']):
例如
select TO_DATE ('10-September-1992','dd-Month-YYYY') from dual
out: 1992-09-10 00:00:00
.使用format 的元素格式 例如:
select to_date('10-12-2016','dd-mm-yyyy')
from dual;
select to_date('25-5 月-95','dd-month-yy')
from dual;
6. exists 和not exists 用法
SQL 代码
select Resc_id from dbo.Res_Coach
where EXISTS (select * from Res_Coach where Resc_id is null)
查询原理： 遍历dbo.Res_Coach 每一条，同时处理where 条件（EXISTS (select * from Res_Coach where Resc_id=0) 判断结果为true 或者false），为true 时拿出该条，false 时，放弃该条记录。
-- 1、where 条件中的子查询和主查询没关系
select Resc_id
from dbo.Res_Coach
where EXISTS (select Rese_id from dbo.Res_Excellent where Rese_id Is null )
-- 2、where 条件中得子查询和主查询有关系
select Resc_id
from dbo.Res_Coach
where EXISTS (select Resc_id from dbo.Res_Coach where Resc_id Is null )
实例备注：不管where 条件中得子查询和主查询有没有关系，遍历主查询中得每一条时，判断where 条件，exists 结果为真，where 条件返回true，拿出该条记录，where 条件返回false， 不返回该记录。
6.1 EXISTS 和IN 的选择
如果查询的两个表大小相当，那么用in 和exists 差别不大。如果两个表中一个较小，一个是大表，则子查询表大的 用exists，子查询表小的用in
7. DECODE函数
Oracle DECODE函数是Oracle公司独家提供的功能，它是一个功能很强的函数。它虽然不是SQL的标准,但对于性能非常有用。
DECODE 中的if-then-else逻辑
在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下： DECODE(value, if1, then1, if2,then2, if3,then3, . . . else ) Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。 需要注意的是，这里的if、then及else 都可以是函数或计算表达式。 含义解释：
DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 该函数的含义如下： IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ...... ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF