Oracle预定义函数

zszdevelop数据库大约 9 分钟

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 dual
```
out: 2021-02-19 15:41:12
例如显示1小时之后的时候
```
SELECT sysdate+1/24 from dual
```
out: 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
```

Oracle预定义函数

# Oracle预定义函数

# 1. 简介

# 2. 字符函数

# 2.1 LOWER 把字符转为小写

# 2.2 UPPER 把字符转换为大写

# 2.3 initcap 把字符串首字母大写

# 2.4 concat 把俩个字符串连接在一起(类似之前的||的作用)

# 2.5 substr 截取字符串

# 2.6 length 获得字符串长度

# 2.7 nvl 替换列中为null 的值

# 2.7.1 NVL与NVL2两个函数的用法和区别？

# 2.8 instr 查找字符串

# 3. 数字函数

# 3.1 round 四舍五入

# 3.2 trunc 截取到某一位

# 3.3 mod 取余

# 4. 日期函数

# 4.1 sysdate 关键字

# 4.2 months_between

# 4.3 add_months

# 4.4 next_day

# 4.5 last_day

# 4.6 round

# 4.7 trunc

# 5. 类型转换函数

# 5.1 to_char 把日期转换为字符

# 5.2 to_char 把数字转换为字符

# 5.3 to_number 把字符转换为数字

# 5.4 to_date 把字符转换为日期

# 6. exists 和not exists 用法

# 6.1 EXISTS 和IN 的选择

# 7. DECODE函数

Oracle预定义函数

1. 简介

2. 字符函数

2.1 LOWER 把字符转为小写

2.2 UPPER 把字符转换为大写

2.3 initcap 把字符串首字母大写

2.4 concat 把俩个字符串连接在一起(类似之前的||的作用)

2.5 substr 截取字符串

2.6 length 获得字符串长度

2.7 nvl 替换列中为null 的值

2.7.1 NVL与NVL2两个函数的用法和区别？

2.8 instr 查找字符串

3. 数字函数

3.1 round 四舍五入

3.2 trunc 截取到某一位

3.3 mod 取余

4. 日期函数

4.1 sysdate 关键字

4.2 months_between

4.3 add_months

4.4 next_day

4.5 last_day

4.6 round

4.7 trunc

5. 类型转换函数

5.1 to_char 把日期转换为字符

5.2 to_char 把数字转换为字符

5.3 to_number 把字符转换为数字

5.4 to_date 把字符转换为日期

6. exists 和not exists 用法

6.1 EXISTS 和IN 的选择

7. DECODE函数