Oracle/PLSQL工作常用函数总结

前言：本人现在是在银行业从事数据开发工作，所以本篇文章联合自己的工作实际主要供准备从事银行数据开发小白参考以及自己日后查阅。

• NVL（）函数，空值转换
  语法格式：NVL(expr1,expr2)
  功能：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数原来的值。
  应用场景举例：
  ①.表A与表B根据账号进行左毗连得到一个表C,如果毗连之后表B某一字段值不为空，则接纳表B，否则接纳表A，如nvl（B.name，A.name）。
  ②.处置惩罚某一字段当值为空时的情况。
  注意事项：expr1，expr2数据范例必须一致，如果不一致，要进行转换，否则会报错。
  

2.TRUNC() 函数，日期、数值截取
①.处置惩罚日期时
语法格式：TRUNC(date,[fmt]）。此中：date 为须要参数，是输入的一个日期值,fmt 参数可忽略，默认按天截断。
fmt值参考如下:

功能：TRUNC函数返回以指定元素格式截去一部门的日期值
应用场景举例：构建某一天的月初（ 今天的日期SYSDATE为2020-12-28）

1. SELECT TRUNC(SYSDATE,'MM') FROM DUAL  

复制代码

sysdate为系统日期 dual为虚表

大概构建某一年的年初

1. SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL

复制代码

大概

1. SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL

复制代码

效果都一样，如下

其他：

1. select trunc(sysdate) from dual   

复制代码

或

1. select trunc(sysdate,'dd') from dual   

复制代码

都是返回当前年月日，如下

1. select trunc(sysdate,'d') from dual

复制代码

或

1. SELECT TRUNC(DATE'2020-12-31','D') FROM DUAL

复制代码

都是返回上周的星期天，如下（当天为星期一，31号为星期四，上周星期天日期为27号）

1. select trunc(sysdate, 'hh24') from dual    --返回当前小时

复制代码

注意事项：TRUNC()函数没有秒的精确
②.处置惩罚数值时
语法：TRUNC（number[,decimals]），此中：number 待做截取处置惩罚的数值，decimals 指明需生存小数点反面的位数。可选项，忽略它则截去所有的小数部门。
功能：TRUNC函数返回处置惩罚后的数值，其工作机制与ROUND函数极为雷同，只是该函数不对指定小数前或后的部门做相应舍入选择处置惩罚，而统统截去。
应用举例：

1. SELECT TRUNC（89.985，2） FROM DUAL

复制代码

1. SELECT TRUNC(89.985)  FROM DUAL      -- (即取整)

复制代码

1. SELECT TRUNC（89.985，-1） FROM DUAL

复制代码

1. SELECT TRUNC(189.985,-2) FROM DUAL

复制代码

1. SELECT TRUNC (89.985,-3) FROM DUAL

复制代码

注意：第二个参数可以为负数，表示为小数点左边指定位数反面的部门截去，即均以0记。与取整雷同，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推；如果所设置的参数为负数，且负数的位数大于整数的字节数的话，则返回为0。如：TRUNC(89.985,-3)=0。
3.decode函数
语法：decode(条件，值1，返回值1，值2，返回值2，…值n,返回值n，缺省值) ；
含义：函数含义
IF条件=值1THEN
RETURN(返回值1)
ELSIF条件=值2THEN
RETURN(返回值2)
…
ELSIF条件=值nTHEN
RETURN(返回值n)
ELSE
RETURN(缺省值)
ENDIF
应用场景：当用CSAE WHEN大概IF-ELSE-ENDIF逻辑时，可以考虑用该函数取代，精简sql语句。
举例：decode(X，A，B，C，D，E）
运行效果：当X = A，函数返回B；当X != A 且 X = C，函数返回D；当X != A 且 X != C，函数返回E。 此中，X、A、B、C、D、E都可以是表达式

• RPAD,LPAD—右填充、左填充。默认为填充空格。
  格式: RPAD(字符串 , 字符个数 , 填充字符)
  例:
  

1.   SELECT RPAD('ABC' , 6 , 'H') FROM DUAL

复制代码

1. SELECT LPAD(‘ABC’ , 6 , ’H’) FROM  DUAL

复制代码

5.LTRIM,RTRIM-----左右截断。默认为删除空格。
格式∶ LTRIM(STRING[,’SET’])
例∶ LTRIM(‘test*’ , ’’)=’ test***’
　　
6.LOWER----将字符串转换为小写
格式∶LOWER(string)
　　
7.UPPER—将字符串转换为大写
格式∶UPPER(string)
　　
8.SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始
格式∶ SUBSTR(STRING , START [ , COUNT])
例∶

1. SELECT SUBSTR('WORDSTAR' , 2 , 3) FROM DUAL

复制代码

9.REPLACE—搜索指定字符串并替换
格式∶REPLACE(string , substring , replace_string)
例∶

1. SELECT REPLACE(‘this is a test’ , ‘this’ , ‘that an’) FROM DULA

复制代码

10.LEAST函数
格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
功能：返回参数列表中的最小值。返回参数范例以第一参数为准
例∶

1. SELECT LEAST(1,2,5,-10,9)  FROM DUAL

复制代码

11.ADD_MONTHS()
格式: ADD_MONTH (data1,numvalue)
功能：加减月份。numvalue为负数时减去相应月份
例：

1. SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL

复制代码

12.MONTHS_BETWEEN
格式: MONTHS_BETWEEN(data1,data2)
功能：返回两日期之间的月数,在ORACLE中盘算月份的差值，都是月底或是日期相同，都会按整月盘算。如果盘算效果不是整数，ORACLE会把31作为分母来盘算。
例：

1. SELECT MONTHS_BETWEEN(SYSDATE,DATE'2021-01-17') FROM DUAL

复制代码

13.LAST_DAY
格式∶LAST_DAY(date)
功能：返回指定日期的最后一天
例：

1. SELECT LAST_DAY(SYSDATE) FROM DUAL

复制代码

14.next_day
语法：next_day(d,day)
功能：返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
例：

1. select sysdate S1,next_day(sysdate,1) S2, next_day(sysdate,'星期日') S3 FROM DUAL

复制代码

15.ROUND(）对数值大概日期进行处置惩罚
①.当对数值进行处置惩罚时：
语法：ROUND(number,num_digits)，即：Round（数值，生存的小数位数)
参数
number,要四舍五入的数，digits是要小数点后生存的位数
如果 digits 大于 0，则四舍五入到指定的小数位。
如果 digits 即是 0，则四舍五入到最靠近的整数。
如果 digits 小于 0，则在小数点左侧进行四舍五入。
如果round函数只有参数number,等同于digits 即是 0。
功能:Round函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的效果。
例：

②.当对日期进行处置惩罚时：
功能：日期进行舍入运算。
例：
a.对于年份按1–6月和7–12月四舍五入到最近的年份1月;

1. select round(sysdate,'yyyy') from dual;

复制代码

b.对于月份按1–15日和16–30日四舍五入到最近的月份1日;

1. select round(sysdate,'month') from dual;

复制代码

c.对于星期按周一–周三和周四–周日四舍五入到最近一周周日；

1. select round(sysdate,'day') from dual;

复制代码

d.对于天按1–12点和13–24点四舍五入到最近一天零时；

1. select round(sysdate,'dd') from dual;

复制代码

转换函数
16.TO_DATE
格式∶ TO_DATE(STRING[,’FORMAT’])
功能：转换字符串为日期型
例：

1. SELECT TO_DATE('2020-12-18','YYYY-MM-DD') FROM DUAL

复制代码

17.TO_CHAR
格式：TO_CHAR(DATE [,’FORMAT’])
功能：转换日期型或数值型为字符串。最重要的函数之一．其FORMAT格式多种多样
例：

1. SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL

复制代码

18.TO_NUMBER
格式：TO_NUMBER(string [ , format])

功能：转换字符串为数字
例：

1. select to_number(to_char(sysdate,’hh12’)) from dual--以数字显示的小时数

复制代码

19.substr 函数（截取字符串）
格式：SUBSTR(string，start， [length])
string：表示源字符串，即要截取的字符串。
start：开始位置，从1开始查找。如果start是负数，则从string字符串末了开始算起。
length：可选项，表示截取字符串长度。
功能：截取字符串
举例：在下面联合instr函数使用进行。
20.instr 函数
语法：INSTR(string，child_string，[start]，[show_time])
string：表示源字符串。
child_string：子字符串，即要查找的字符串。
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
show_time：可选项，表示子字符串第反复出现在源字符串当中，默认第1次，负数则报错。
功能：返回子字符串在源字符串中的位置
举例：在下面联合isubstr 函数使用进行。
substr 函数联合 instr 函数截取字符串
举例：数据查询处置惩罚需要对字段BJ_BAIDU_CEO_LY进行"拆分"，字段命名规则为：都会_所属公司_员工职位_员工姓名，此中，都会、公司、职位、姓民字符串长度不固定，由于字符串长度不固定，只使用substr函数无法实现需求，需共同instr函数定位到字符’_'的位置，然后使用substr函数进行截取。
①.获取都会：

1. SELECT        SUBSTR ('BJ_BAIDU_CEO_LY', 1, INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 1) - 1) AS CITYFROM        DUAL

复制代码

②.获取公司

1. SELECT        SUBSTR (                'BJ_BAIDU_CEO_LY',                 INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 1) + 1,                 INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 2) - INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 1)-1        ) AS COMPANYFROM        dual

复制代码

③.获取职位

1. SELECT SUBSTR('BJ_BAIDU_CEO_LY',INSTR('BJ_BAIDU_CEO_LY', '_',1,2)+1,INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 3) - INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 2)-1)  AS POSITIONFROM DUAL

复制代码

④.获取姓名

1. SELECT        SUBSTR ('BJ_BAIDU_CEO_LY', INSTR ('BJ_BAIDU_CEO_LY', '_', 1, 3) + 1) AS STF_NAMEFROM       DUAL

复制代码

来源：https://blog.csdn.net/weixin_49708329/article/details/111831512
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！