@@ -1,58 +0,0 @@ Oracle预定义函数 | 凤凰涅槃进阶之路

Oracle预定义函数

Abel sun2022年12月24日
约 2836 字大约 9 分钟

Oracle预定义函数

1. 简介

oralce 的函数分为

  • 单行函数
    • 字符函数
    • 日期函数
    • 数字函数
    • 转换函数
  • 分组函数(后续介绍)

学前知识:哑表 dual

dual 是一个虚拟表,辅助查找和运算,通常用在select语句中,作为查询的目标表结构,oracle保证dual里面永远只有一条记录

例如:

显示1+1 的结果,可以看出,dual很多时候是为了构成select的标准语法

select 1+1 from dual;

2. 字符函数

  1. LOWER:转换成小写

  2. UPPER:转换成大写

  3. INITCAP:首字母变成大写,其余都小写

  4. CONCAT:字符串的连接

  5. SUBSTR(str,start,length)或者SUBSTR(str,start):字符串的截取

  6. LENGTH:求字符串的长度

  7. NVL : 转换null 的值。(前边已经用过)

nvl2:

  1. 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. 数字函数

  1. ROUND:四舍五入

  2. TRUNC:截取,不进行四舍五入

  3. 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. 日期函数

  1. sysdate 关键字

  2. MONTHS_BETWEEN:两个日期之间的月数,如果是正数前面的值大于后面的值

  3. ADD_MONTHS:在指定日期上增加月数

  4. NEXT_DAY:指定日期的下一个星期几是哪天

  5. LAST_DAY:指定日期的最后一天

  6. ROUND:对指定日期进行四舍五入

  7. 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. 类型转换函数

  1. TO_CHAR 将日期或者数值转换成字符串
  2. TO_NUMBER 将字符串转换成数字
  3. 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的标准,但对于性能非常有用。

  1. 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
    
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.9.1