Oracle的常用函数

最新推荐文章于 2022-05-08 16:51:36 发布

宇宙中的Philip

最新推荐文章于 2022-05-08 16:51:36 发布

阅读量1k

点赞数 3

分类专栏： Oracle 文章标签： Oracle常用函数

本文链接：https://blog.csdn.net/qq_33439938/article/details/99293337

版权

Oracle 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

Oracle的常用函数

本文初衷是为了学习归纳，方便遗忘时查看。若有错误，欢迎指出。

1.字符串函数

1. Lower(char)
将字符串表达式char中的所有大写字母转换为小写字母。

--应用示例：
select LOWER('DARROW') from dual;--darrow

2. Upper(char)
将字符串表达式char中的所有小写字母转换为大写字母。

--应用示例：
select UPPER('darrow') from dual;--DARROW

3. Initcap(char)
首字母转换成大写。

--应用示例：
 select INITCAP('darrow') from dual;--Darrow

4.Substr(char, start, length)
返回字符串表达式char中从第start开始的length个字符。

--应用示例：
select SUBSTR('http://www.Baidu.com', 12, 10) from dual; -- Baidu.com
--(注：下标从1开始,但写成0开始效果是和1一样的)

5.Length(char)
返回字符串表达式char的长度。

--应用示例：
select LENGTH('中移建设有限公司') from dual;--8

6.Replace(char,search_str[,replacement_str])
将字符串char中的子串search_str替换成replacement_str；
如果search_str=null，返回char；
如果replacement_str=null，则会去掉char中的search_str。

--应用示例：
UPDATE TMP_TEST SET NSRMC = REPLACE(NSRMC,'有限公司','集团') WHERE  NSRMC LIKE '%中移建设有限公司%';
SELECT REPLACE('中移建设有限公司','有限') FROM DUAL;--中移建设公司
SELECT REPLACE('中移建设有限公司','有限','测试') FROM DUAL;--中移建设公司

7.Instr(char1,char2[,n[,m]])
获取子串char2在字符串char1中的位置。
n为其实搜索位置，m为子串出现的次数；n为负，则从尾部开始搜索；n和m默认为1。

--应用示例：
select INSTR('DarrowThink', 'Darrow', 1) from dual; -- 1
select INSTR('中移建设有限公司','公',1,0) from dual;--0

8.Lpad(char1,n,char2)
在字符串char1的左端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符。

--应用示例：
select LPAD('DarrowThink', 12, '*') from dual; -- *DarrowThink

9.Rpad(char1,n,char2)
在字符串char1的右端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符。

--应用示例：
select RPAD('DarrowThink', 12, '*') from dual; -- DarrowThink*

10.Ltrim(char1[,set])
去掉字符串char1左端包含的set中的任意字符。

--应用示例：
select LTRIM('中移建设有限公司','中') from dual;--移建设有限公司
select LTRIM('中移建设有限公司','移') from dual;--中移建设有限公司，只要不是最左端的就不会被去掉。

11.Rtrim(char1[,set])
去掉字符串char1右端包含的set中的任意字符。

--应用示例：
同Ltrim()函数；

12.Trim(char | char From string)
从字符串string的头尾或者两端截断特定字符char。

--应用示例：
select TRIM('*' from '**DarrowThink**') from dual; -- DarrowThink
select TRIM('  DarrowThink  ') from dual; -- DarrowThink(注：默认去掉空格)

13.Concat(str1,str2)
连接字符串，同 || 的作用一样。

--应用示例：
select CONCAT('Darrow', ' Think') from dual; -- DarrowThink

14.ASCII(char)
取char的ASCII值。

--应用示例：
select ASCII('中移建设有限公司') from dual;--14989485

15.Chr(number)
取number的ASCII值。

--应用示例：
select CHR(87) from dual;--W

2.日期函数

1.Next_Day(day,char)
返回指定日期day后的第一个工作日char所对应的日期。

若指定工作日仍在当前星期范围内，则返回该星期的工作日时间。
这里系统如果设置的参数默认是英文，那么char要用英文的星期写法：mon；

--应用示例：
select NEXT_DAY(DATE'2019-08-08','星期一') from dual;--2019-08-12 (下一周的星期一)      
select NEXT_DAY(DATE'2019-08-08','星期五') from dual;--2019-08-09 (8号是星期四，返回这个星期的星期五)

2.Last_Day(day)
返回day日期所指定月份中最后一天所对应的日期。

--应用示例：
 select LAST_DAY(DATE'2019-08-08') from dual;--2019-08-31 
 --计算本月剩余多少天：
 select TRUNC(SYSDATE,''month),to_char(SYSDATE,'YYYY-MM-DD'),LAST_DAY(SYSDATE)-SYSDATE from dual;

3.Add_Months(day,n)
返回day日期在n个月后(n为正数)或前(n为负数)的日期。

--应用示例：
 select ADD_MONTHS(DATE'2019-08-08',1) from dual;--2019-09-08

4.Months_Between(day1,day2)
返回day1日期和day2日期之间相差得月份。

day1 减去 day2，相邻月份等于1或-1

--应用示例：
select MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, 2)) from dual; -- -4

5.Round(day[,fmt])
返回日期的四舍五入结果。

如果fmt指定年度，则7月1日为分界线；如果fmt指定月，则16日为分界线；如果指定天，则中午12:00为分界线，默认舍入到日。

--应用示例：
select ROUND(DATE'2019-08-08') from dual; -- 2019-08-08
select ROUND(DATE'2019-08-08', 'YEAR') from dual; -- 2020-01-01
select ROUND(DATE'2019-08-08', 'MONTH') from dual; -- 2019-08-01

6.Trunc(day,[,fmt])
日期截断函数。

如果fmt指定年度，则结果为本年度的1月1日；如果为月，则将结果为本月1日，默认截断到日。

--应用示例：
select TRUNC(DATE'2015-08-08', 'YEAR') from dual; -- 2015/1/1
select TRUNC(DATE'2015-08-08', 'MONTH') from dual; -- 2015/8/1       
--获取某月的第一天和最后一天
SELECT TRUNC(TRUNC(SYSDATE, 'MONTH') - 1, 'MONTH') FIRST_DAY,
       TRUNC(SYSDATE, 'MONTH') - 1 / 86400 LAST_DAY,
       TRUNC(SYSDATE, 'MONTH') FIRST_DAY_CUR_MONTH,
       LAST_DAY(TRUNC(SYSDATE, 'MONTH')) + 1 - 1 / 86400 lAST_DAY_CUR_MONTH
  FROM DUAL;                     
--结果显示：   --SYSDATE := 2019-08-12
2019/7/1	2019/7/31 23:59:59	2019/8/1	2019/8/31 23:59:59

7.Current_Date
返回当前会话时区所对应日期时间。

--应用示例：
类似SYSDATE

8.Extract
从日期中获取所需要的特定数据。

--应用示例：
select EXTRACT(YEAR from SYSDATE) from dual; -- 2015
select EXTRACT(MONTH from SYSDATE) from dual; -- 9
select EXTRACT(DAY from SYSDATE) from dual; -- 29    

--EXTRACT(fmt FROM d);--fmt可以是以下6种，

SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE) 年份,
EXTRACT(MONTH FROM SYSDATE) 月份,
EXTRACT(DAY FROM SYSDATE) 日,
EXTRACT(HOUR FROM SYSTIMESTAMP)+8 小时,--因为没有加上时区，所以在中国运行的结果小8小时
EXTRACT(MINUTE FROM SYSTIMESTAMP) 分钟,
EXTRACT(SECOND FROM SYSTIMESTAMP) 秒 FROM DUAL; 
--结果显示：
SYSDATE  			年份  	月份  日 小时 分钟  秒
2019/8/12 23:06:50	2019	 8	 12	 23	  6	  50.514

9.DateAdd (datepart,number,date)
DATEADD() 函数在日期中添加或减去指定的时间间隔。

语法：

DATEADD(datepart,number,date)

date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

datepart 参数可以是下列的值：
在这里插入图片描述

--应用示例：
SELECT OrderId,DATEADD(day,2,'2008-12-29 16:25:46.635') AS OrderPayDate FROM Orders；
--结果：2008-12-31 16:25:46.635

--假设删除3年以前的订单：
delete from 订单表 where 订购日期<DATEADD(yy,-3,getdate())；

拓展知识： 阿里云ODPS上面的语法稍有不同，但是用法都是一样的，如下 dateadd(datetime,delta,datepart)

参数说明： datetime:datetime类型，日期值 delta：bigint类型，修改幅度 datepart：string类型常量，修改单位，支持格式对天的修改，‘dd’；对月的修改，‘mm’；对年的修改，‘yyyy’；对小时修改，‘hh’；对分和秒的修改，‘mi’和‘ss’。

备注：按照指定的单位增减delta时导致的对更高单位的进位或者退位，年、月、日、时、分、秒分别按照10进制，12进制，24进制，60进制，60进制计算。当delta的单位是月份时，计算规则如下：若datetime的月部分在增加delta值后不造成day溢出，则保持day值不变，否则把day设置成为结果月份的最后一天。
如果数据溢出，最好加case when判断处理脏数据。

3.类型转换函数

1.To_char( n [, fmt [, ‘nlsparam’]] )
将一个数字或日期转换成字符串。

--应用示例：
select TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') from dual; -- 2015-09-29 15:36:24 
SELECT TO_CHAR(DATE'2019-08-12','YYYY-MM-DD') FROM DUAL;--2019-08-12
SELECT TO_CHAR(DATE'2019-08-12','YYYY-MM-DD HH:MI:SS') FROM DUAL;--2019-08-12 12:00:00
SELECT TO_CHAR(DATE'2019-08-12','BC-YYYY-MM-DD AM:HH24:MI:SS') FROM DUAL;--公元-2019-08-12 上午:00:00:00
SELECT TO_CHAR(DATE'2019-08-12','BC-YYYY-MON-DD-Day AM:HH24:MI:SS') FROM DUAL;--公元-2019-8月 -12-星期一 上午:00:00:00
SELECT TO_CHAR(DATE'2019-08-12','YYYY"年"MM"月"DD"日"') FROM DUAL;--2019年08月12日,要使用双引号！
SELECT TO_CHAR(DATE'2019-08-12','Day, HH12:MI:SS') FROM DUAL;--星期一, 12:00:00

--DDD 一年第几天 
SELECT TO_CHAR(DATE'2019-08-12','DDD') FROM DUAL;--224
--D 数字星期 
SELECT TO_CHAR(DATE'2019-08-12','D') FROM DUAL;--2,星期日等于1；
--DAY 星期几 
SELECT TO_CHAR(DATE'2019-08-12','DAY') FROM DUAL;--星期一
--英文显示星期几，DAY对应结果全大写，Day对应大小写混合
SELECT TO_CHAR(DATE'2019-08-12','DAY','NLS_DATE_LANGUAGE=AMERICAN') FROM DUAL;--MONDAY 
--WW 一年第几周 
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;--32
--W 一月第几周 
SELECT TO_CHAR(DATE'2019-08-12','W') FROM DUAL;--2   

--FM:去空格，后面限制小数点最多4位，最少保留2位，且在第5位进行四舍五入
SELECT TO_CHAR(123.02335,'FM9999999.0099') FROM DUAL;--123.0234       
SELECT TO_CHAR(132133148.5,'FM9,999,999,999.09') FROM DUAL;--132,133,148.5

2.To_Number
将字符型数据转换成数字型数据。

--应用示例：
select TO_NUMBER('10') from dual; -- 10
select TO_NUMBER('JellyThink') from dual; -- 无效数字

3.To_Date
将字符型数据转换为日期型数据。

--应用示例：
select TO_DATE('2015-9-29', 'YYYY-MM-DD') from dual; -- 2015/9/29

4.Cast
将一种built-in类型转换成另一种built-in类型，若是小数，能执行四舍五入操作。还可以转换数组、集合。

--应用示例：
select CAST('100' as NUMBER) from dual; -- 100
select CAST(2 as char) from dual; -- 2

4.数字函数

在这里插入图片描述

5.聚合函数

在这里插入图片描述

6.其它常用函数

1.Decode(expression , search , result [, search , result]… [, default])
IF语句的另一形式。

将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。

--应用示例：
--将20与10做比较，不相等则继续和200比较，不相等则继续和20比较，相等则等于20对应的result值
select DECODE(20, 10, 5, 200, 10, 20, 30) from dual; -- 30

2.Sign(number)

如果number大于0，SIGN则返回1；如果number小于0，SIGN则返回-1；如果number等于0，SIGN则返回0。

--应用示例：
select SIGN(20) from dual; -- 1
select SIGN(-30) from dual; -- -1
select SIGN(0) from dual; -- 0

3.Trunc(number, [ decimal_places ])

number是要截取的数字，decimal_places是要保留的小数位(不四舍五入)。
这个参数必须是个整数。如果此参数缺省，默认保留0位小数。

--应用示例：
select TRUNC(20.2183, 2) from dual; -- 20.21
select TRUNC(20.1, 4) from dual; -- 20.1

4.Greatest(expr1[,expr2]…)
返回表达式中值最大的一个。

--应用示例：
select GREATEST(20, 100, 30, 20, 40, 400) from dual; -- 400      
--若换成英文，则会以首字母排序靠后为最大
select GREATEST('austin', 'darrow', 'oven', 'jaychou', 'tal', 'jhon') from dual; -- tal

5.Least(expr1[,expr2]…)
返回表达式中值最小的一个。

--应用示例：
select LEAST(20, 100, 30, 20, 40, 400) from dual; -- 20
--若换成英文，则会以首字母排序靠前为最小
select LEAST('austin', 'darrow', 'oven', 'jaychou', 'tal', 'jhon') from dual; -- austin

6.Nullif(expr1,expr2)
如果expr1=expr2，则返回null，否则返回expr1。

--应用示例：
select NULLIF(20, 20) from dual; -- NULL
select NULLIF('DARROW', 'darrow') from dual; -- DARROW,不相等。这里还区分大小写

7.Nvl(expr1,expr2)
如果expr1=null；则返回expr2，否则返回expr1。

--应用示例：
select NVL('91440000190300001', '另一个值') from dual; -- 91440000190300001
select NVL(NULL, 30) from dual; -- 30

8.Nvl2(expr1,expr2,expr3)
如果expr1!=null，则返回expr2；如果expr1=null；则返回expr3。

--应用示例：
select NVL2(NULL, 20, 30) from dual; -- 30
select NVL2('它不是空所以返回第一个参数', 20, 30) from dual; -- 20

9.Translate(char, from, to)
将from中的每个字符替换为to中的相应字符以后的字符串。若from比to字符串长，那么在from中比to中多出的字符将会被删除。
三个参数中有一个是空，返回值也将是空值。

--应用示例：
select translate('abcdefga','abc','wo') 返回值 from dual;--wodefgw

函数概括图片

在这里插入图片描述

修改记录

时间	内容
2019年8月12日	第一次发布

宇宙中的Philip

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录