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:返回会话时区

阅读剩余部分

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » Oracle中的常用函数详解

Oracle中的常用函数详解

目录

一、数值函数

二、字符函数

三、日期时间函数

数据库系统概念第6版

相关推荐

评论