您现在的位置是：网站首页> 编程资料编程资料

Oracle中的常用函数详解_oracle_

2023-05-27 444人已围观

简介 Oracle中的常用函数详解_oracle_

一、数值函数

1、mod(n1,n2):n1除以n2的余数。

如果n2为0，则返回n1。

select mod(23,8),mod(24,8) from dual;--返回：7,0

2、power(n1,n2):返回数字n1的n2次幂; 
exp(y)：返回e的y次幂。（e为数学常量）; 
log(x,y)：返回以x为底的y的对数； 
ln(y)：返回e为底的自然对数。

select power(2.5,2),power(1.5,0),power(20,-1) from dual;

3、sqrt(n):平方根。

select sqrt(64),sqrt(10) from dual;--返回：8 , 3.16227766

4、ceil(n):返回大于等于n的最小整数。;

floor(n):返回小于等于n的最大整数。

select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;--返回4，5，0

5、sign(x)：返回x的正负值

若为正值返回1，负值返回-1，0返回0。

select sign(100),sign(-100),sign(0) from dual;

6、trunc(n[,len]):n截取到小数点len位。

len默认为0。len>0，截取到小数点右len位。len<0，截取到小数点左len位。

select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;--返回：5555.66 5500 5555

7、round(n[,len]):n四舍五入到小数点len位，规则同trunc。

select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;--返回： 5555.67 ,5600 ,5556

8、sys.dbms.random.value()：产生0-1之间的随机数。

DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。 
DBMS_RANDOM.VALUE(n1,n2)：产生n1-n2之间的随机数。

trunc(dbms_random.value(10,100)) //80：生成10-100之间的随机数。

二、字符函数

1：lower(c1):返回字符串,并将所有的字符小写

SELECT LOWER('AbcDedf Gbad') FROM DUAL;

2：upper(c1):返回字符串,并将所有的字符大写

SELECT UPPER('abcdEf') FROM DUAL;

3: initcap(c1):返回字符串并将字符串的第一个字母变为大写

全部单词的首字母大写

SELECT INITCAP('your didn''t try your best') FROM DUAL;

4: initcap(c1,n[,c2]):在列的左边填充字符

  • C1 字符串
  • n 追加后字符总长度
  • c2 追加字符串,默认为空格
SELECT LPAD('WELCOME', 20, 'HELLO') FROM DUAL;

5: rpad(c1,n[,c2]):在列的右边填充字符

注意长度值并不是粘贴字符的长度，而是整个字符串的长度，如果长度小于原始字符串

--SELECT RPAD('HELLO', 4, '*') FROM DUAL; 的值为HELL SELECT RPAD('HELLO', 10, '*') FROM DUAL; SELECT RPAD('HELLO', 10, 'E') FROM DUAL;

6: ltrim(X,[TRIM_STRING]):删除左边出现的字符串。

默认为空字符串

SELECT LTRIM(' hello world!') FROM DUAL; SELECT LTRIM('hello, world', 'hello') FROM DUAL;

7: rtrim(X, [TRIM_STRING]):删除右边出现的字符串

TRIM_STRING,默认为空字符串。

SELECT RTRIM('hello world! ') FROM DUAL;

8: trim('s' from 'string'):删除两边出现的字符串

LEADING 剪掉前面的字符 
TRAILING 剪掉后面的字符 
如果不指定,默认为空格符

SELECT TRIM('Y' FROM 'YOU') FROM DUAL;

9: instr(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

  • C1 被搜索的字符串
  • C2 希望搜索的字符串
  • I 搜索的开始位置,默认为1
  • J 出现的位置,默认为1
SELECT INSTR('HELLO WORLD! WELCOME', 'WORLD', 1) FROM DUAL;

10：substr(string,start,count):取子字符串,从start开始,取count个

SELECT SUBSTR('you are right!, come on', 3, 30) FROM DUAL;

11：replace('string','s1','s2'):替换

  • string 希望被替换的字符或变量
  • s1 被替换的字符串
  • s2 要替换的字符串
SELECT REPLACE('HE LOVE YOU', 'HE' ,'I') FROM DUAL;

12：translate(c1,c2,c3)：将指定字符替换为新字符

  • c1 希望被替换的字符或变量
  • c2 查询原始的字符集
  • c3： 替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符
select TRANSLATE('he love you','he','i'), TRANSLATE('重庆的人','重庆的','上海男'), TRANSLATE('重庆的人','重庆的重庆','北京男士们'), TRANSLATE('重庆的人','重庆的重庆','1北京男士们'), TRANSLATE('重庆的人','1重庆的重庆','北京男士们') from dual; --i love you,上海男人,北京男人,1北京人,京男士人

13: length(c1)：返回字符串的长度;

返回表某条数据某个列实际长度，如果该表没有数据，返回0

SELECT LENGTH(TYPE_NAME) FROM USER_TYPES

14：ascii（x1)：返回字符串的ASCII值

SELECT ASCII('A') FROM DUAL; SELECT ASCII('a') FROM DUAL;

15: chr(n1)：返回整数所对应的ASCII字符

SELECT CHR('65') FROM DUAL; SELECT CHR(400) FROM DUAL; --如果超出ACII值，则返回空

16: concat(c1,c2)：连接字符串A和字符串B

SELECT CONCAT('您好', '欢迎来到ORACLE世界') AS TEXT FROM DUAL; --如果要连接表里面的两个字段可以用|| SELECT TYPECODE || '____' || TYPE_NAME AS "TYPE" FROM USER_TYPES;

三、日期时间函数

1：sysdate、current_date：系统的当前日期

（1）日期加上范围日期，得到新日期

  • data+n,加减n天。（n为负数，表示减去）
  • date+n/24:加减n小时
  • date+n/24/60:加减n分钟
  • date+n/24/60/3600:加减n秒

（2）date1-date2:两日期相差的天数：

(date1-date2)*24*3600:两日期相差的秒数

SELECT SYSDATE FROM DUAL;

2：add_months(date,n1)：增加或减去月份

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL; SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL

3: months_between(date2,date1)：给出date2-date1的月份

SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03', 'YYYY-MM-DD'), TO_DATE('2011-01-23', 'YYYY-MM-DD')) FROM DUAL; SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL; --SELECT MONTHS_BETWEEN('2011-1月-23', '2011-9月-1') FROM DUAL; 文字与格式字符串不匹配

4: last_day(date)：返回日期的最后一天

SELECT LAST_DAY(SYSDATE) FROM DUAL; SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;

5：next_day(date[,fmt]）:返回日期d1在下周，星期几(参数c1)的日期

星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;--下周星期一,

6、round(date[,fmt]）:日期时间四舍五入结果。

fmt默认是day.

7: trunc(date[,fmt]）:TRUNC函数为指定元素而截去的日期值。

TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')　='24-Nov-1999 12:00:00 am' TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'

8：extract(c1 from date) :找出日期或间隔值的字段值

SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL; SELECT EXTRACT(DAY FROM SYSDATE) AS "DAY" FROM DUAL; SELECT EXTRACT(YEAR FROM SYSDATE) AS "YEAR" FROM DUAL;

9：new_time(date,'this','that'):给出在this时区=other时区的日期和时间

SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,TO_CHAR(NEW_TIME(SYSDATE, 'PDT', 'GMT'), 'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;

简写 时区

  • AST OR ADT 大西洋标准时间
  • HST OR HDT 阿拉斯加—夏威夷时间
  • BST OR BDT 英国夏令时
  • MST OR MDT 美国山区时间
  • CST OR CDT 美国中央时区
  • NST 新大陆标准时间
  • EST OR EDT 美国东部时间
  • PST OR PDT 太平洋标准时间
  • GMT 格伦威治标准时间
  • YST OR YDT Yukon标准时间

10: dbtimezone() :返回时区

SELECT DBTIMEZONE FROM DUAL;

11: sessiontimezone:返回会话时区

其中DBTIMEZONE是数据库的，session是针对当前会话的，因为时区在会话级可以改变

SELECT SESSIONTIMEZONE FROM DUAL; ALTER SESSION SET TIME_ZONE = '8:00'; SELECT SESSIONTIMEZONE FROM DUAL;

12、常用时间查询：

---- 上月最后一天 SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL; ----: 上各月的今天 SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL; ---- 上个月第一天 SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL; --- 要找到某月中所有周五的具体日期 SELECT TO_CHAR(T.D, 'YY-MM-DD') FROM (SELECT TRUNC(SYSDATE, 'MM') + ROWNUM -1 AS D FROM DBA_OBJECTS WHERE ROWNUM < 32) T WHERE TO_CHAR(T.D, 'MM') = TO_CHAR(SYSDATE, 'MM') AND TRIM(TO_CHAR(T.D, 'DAY')) = '星期五'

四、转换函数

1： to_char(date,'format') ：把对应的数据转换为字符串类型

TO_CHAR的fmt:格式字符串，不分大小写。

select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from dual
  • Y或YY或YYY 年的最后一位，两位或三位
  • SYEAR或YEAR: SYEAR使公元前的年份前加一负号 --TWENTY ELEVEN
  • Q： 季度，1～3月为第一季度 -- 2表示第二季度
  • MM： 月份数 --04表示4月
  • RM： 月份的罗马表示 --IV表示4月
  • MON： 月份 --4月
  • Month： 用9个字符长度表示的月份名 -- 4月
  • WW： 当年第几周 -- 24表示2002年6月13日为第24周
  • W： 本月第几周 -- 2011年04月26日为第4周
  • DDD： 当

    上一篇：Oracle递归查询connect by用法_oracle_

    下一篇：Oracle执行Update语句的几种方式_oracle_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网