一、数值函数
1、mod(n1,n2):n1除以n2的余数。
如果n2为0,则返回n1。
1
|
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为底的自然对数。
1
|
select power(2.5,2),power(1.5,0),power(20,-1) from dual; |
3、sqrt(n):平方根。
1
|
select sqrt(64),sqrt(10) from dual; --返回:8 , 3.16227766 |
4、ceil(n):返回大于等于n的最小整数。;
floor(n):返回小于等于n的最大整数。
1
|
select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual; --返回4,5,0 |
5、sign(x):返回x的正负值
若为正值返回1,负值返回-1,0返回0。
1
|
select sign(100),sign(-100),sign(0) from dual; |
6、trunc(n[,len]):n截取到小数点len位。
len默认为0。len>0,截取到小数点右len位。len<0,截取到小数点左len位。
1
|
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。
1
|
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之间的随机数。
1
|
trunc(dbms_random.value(10,100)) //80:生成10-100之间的随机数。 |
二、字符函数
1:lower(c1):返回字符串,并将所有的字符小写
1
|
SELECT LOWER ( 'AbcDedf Gbad' ) FROM DUAL; |
2:upper(c1):返回字符串,并将所有的字符大写
1
|
SELECT UPPER ( 'abcdEf' ) FROM DUAL; |
3: initcap(c1):返回字符串并将字符串的第一个字母变为大写
全部单词的首字母大写
1
|
SELECT INITCAP( 'your didn' 't try your best' ) FROM DUAL; |
4: initcap(c1,n[,c2]):在列的左边填充字符
- C1 字符串
- n 追加后字符总长度
- c2 追加字符串,默认为空格
1
|
SELECT LPAD( 'WELCOME' , 20, 'HELLO' ) FROM DUAL; |
5: rpad(c1,n[,c2]):在列的右边填充字符
注意长度值并不是粘贴字符的长度,而是整个字符串的长度,如果长度小于原始字符串
1
2
3
|
--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]):删除左边出现的字符串。
默认为空字符串
1
2
|
SELECT LTRIM( ' hello world!' ) FROM DUAL; SELECT LTRIM( 'hello, world' , 'hello' ) FROM DUAL; |
7: rtrim(X, [TRIM_STRING]):删除右边出现的字符串
TRIM_STRING,默认为空字符串。
1
|
SELECT RTRIM( 'hello world! ' ) FROM DUAL; |
8: trim('s' from 'string'):删除两边出现的字符串
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
1
|
SELECT TRIM( 'Y' FROM 'YOU' ) FROM DUAL; |
9: instr(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
- C1 被搜索的字符串
- C2 希望搜索的字符串
- I 搜索的开始位置,默认为1
- J 出现的位置,默认为1
1
|
SELECT INSTR( 'HELLO WORLD! WELCOME' , 'WORLD' , 1) FROM DUAL; |
10:substr(string,start,count):取子字符串,从start开始,取count个
1
|
SELECT SUBSTR( 'you are right!, come on' , 3, 30) FROM DUAL; |
11:replace('string','s1','s2'):替换
- string 希望被替换的字符或变量
- s1 被替换的字符串
- s2 要替换的字符串
1
|
SELECT REPLACE ( 'HE LOVE YOU' , 'HE' , 'I' ) FROM DUAL; |
12:translate(c1,c2,c3):将指定字符替换为新字符
- c1 希望被替换的字符或变量
- c2 查询原始的字符集
- c3: 替换新的字符集,将c2对应顺序字符,替换为c3对应顺序字符
1
2
3
4
5
6
|
select TRANSLATE( 'he love you' , 'he' , 'i' ), TRANSLATE( '重庆的人' , '重庆的' , '上海男' ), TRANSLATE( '重庆的人' , '重庆的重庆' , '北京男士们' ), TRANSLATE( '重庆的人' , '重庆的重庆' , '1北京男士们' ), TRANSLATE( '重庆的人' , '1重庆的重庆' , '北京男士们' ) from dual; --i love you,上海男人,北京男人,1北京人,京男士人 |
13: length(c1):返回字符串的长度;
返回表某条数据某个列实际长度,如果该表没有数据,返回0
1
|
SELECT LENGTH(TYPE_NAME) FROM USER_TYPES |
14:ascii(x1):返回字符串的ASCII值
1
2
|
SELECT ASCII( 'A' ) FROM DUAL; SELECT ASCII( 'a' ) FROM DUAL; |
15: chr(n1):返回整数所对应的ASCII字符
1
2
|
SELECT CHR( '65' ) FROM DUAL; SELECT CHR(400) FROM DUAL; --如果超出ACII值,则返回空 |
16: concat(c1,c2):连接字符串A和字符串B
1
2
3
|
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:两日期相差的秒数
1
|
SELECT SYSDATE FROM DUAL; |
2:add_months(date,n1):增加或减去月份
1
2
|
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的月份
1
2
3
|
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):返回日期的最后一天
1
2
|
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
1
|
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL; --下周星期一, |
6、round(date[,fmt]):日期时间四舍五入结果。
fmt默认是day.
7: trunc(date[,fmt]):TRUNC函数为指定元素而截去的日期值。
1
2
|
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) :找出日期或间隔值的字段值
1
2
3
|
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时区的日期和时间
1
|
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() :返回时区
1
|
SELECT DBTIMEZONE FROM DUAL; |
11: sessiontimezone:返回会话时区
其中DBTIMEZONE是数据库的,session是针对当前会话的,因为时区在会话级可以改变
1
2
3
|
SELECT SESSIONTIMEZONE FROM DUAL; ALTER SESSION SET TIME_ZONE = '8:00' ; SELECT SESSIONTIMEZONE FROM DUAL; |
12、常用时间查询:
1
2
3
4
5
6
7
8
9
10
|
---- 上月最后一天 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:格式字符串,不分大小写。
1
|
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: 当年第几天. 1月1日为001,2月1日为032
- DD: 当月第几天
- D: 周内第几天
- DY: 周内第几天缩写
- HH或HH12: 12进制小时数
- HH24: 24小时制
- MI: 分钟数(0~59) :提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式,将它用于分钟也能工作,但结果是错误的。
- SS: 秒数(0~59)
1
|
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL; |
2: to_char(n,'format'):把对应的数字为字符串类型
FORMAT格式符:
- 9:带有指定位数的值
- 0:前导零的值
- . (句点):小数点
- , (逗号):分组(千)分隔符
- PR:尖括号内负值
- S:带负号的负值(使用本地化)
- L:货币符号(使用本地化)
- D:小数点(使用本地化)
- G:分组分隔符(使用本地化)
- MI:在指明的位置的负号(如果数字 < 0)
- PL:在指明的位置的正号(如果数字 > 0)
- SG:在指明的位置的正/负号
- RN:罗马数字(输入在 1 和 3999 之间)
- TH or th:转换成序数
1
|
SELECT TO_CHAR(122323.45, '$99999999.99' ) FROM DUAL; |
3: to_date(string,'format'):将字符串转化为日期
主要用于比较和修改日期。
1
|
SELECT TO_DATE( '2011/03/24' , 'YYYY-MM-DD' ) FROM DUAL; |
4: to_number:将给出的字符转换为数字
1
|
SELECT TO_NUMBER( '¥2008.00' , 'L9999D99' ) AS Year FROM DUAL; |
五、辅助函数
1、decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值):根据条件返回相应值
值1……n 不能为条件表达式,这种情况只能用case when then end解决。
1
|
select decode(xqn,1, '星期一' ,2, '星期二' ,3, '星期三' , '星期三以后' ) 星期 FROM xqb |
2、greatest(exp1,exp2,exp3,……,expn):返回表达式列表中值最大的一个。 ; least(exp1,exp2,exp3,……,expn):返回表达式列表中值最小的一个。
如果表达式类型不同,会隐含转换为第一个表达式类型。
1
|
SELECT greatest(10,32, '123' , '2006' ) FROM dual; |
3、nullif (expr1, expr2):expr1和expr2相等返回NULL,不相等返回expr1。
1
2
|
SELECT NULLIF ( 'a' , 'b' ) ; --返回值 a SELECT NULLIF ( 'a' , 'a' ); --返回 NULL |
实际应用:
1
2
|
--添加函数查询结果,要求(将日期类型默认'0001/1/1',改成null,不相等,返回本身日期)-- select NULLIF (RECEIVEDATE,TO_DATE( '0001/1/1' , 'yyyy-mm-dd hh24:mi:ss' )) 收货日期, NAME 单据名称 from tab |
4、nvl (expr1, expr2):若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。
1
2
|
select NVL( null , '未知' ) SexType from dual; --expr1为空,返回expr2,结果:'未知' select NVL( '1' , '2' ) SexType from dual; --expr1不为空,返回expr1, 结果:1 |
实际应用:
1
2
|
--加函数时情况,要求(field 为null,返回0 ;field 不为null,返回本身 select NVL(UseFlag, '0' ) 使用标志, NAME 单据名称 from tab |
5、nvl2(expr1, expr2, expr3) :expr1不为NULL,返回expr2;expr2为NULL,返回expr3。
expr2和expr3类型不同的话,expr3会转换为expr2的类型
1
2
|
select NVL2(0,1,2) from dual; --不为null时,返回expr2 ,结果:1 select NVL2( null ,1,2) from dual; --为null时,返回expr3 ,结果:2 |
实际应用:
1
2
|
--应用到实际查询中,要求(field 为null,返回0; field 不为null,返回本身) select NVL2(UseFlag,UseFlag, '0' ) 使用标志,A. NAME 单据名称 from tab |
6、coalesce(c1, c2, ...,cn):返回列表中第一个非空的表达式,如果所有表达式都为空值则返回1个空值。
1
|
select COALESCE ( null ,3*5,44) hz from dual; --返回15 |
7、sys_context('USERENV',c2):返回系统'USERENV'变量中c2对应的的值。
1
|
SYS_CONTEXT( 'USERENV' , 'LANGUAGE' ) language, |
8、sys_connect_by_path(column_name,'分隔符'):把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示
- 第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符
- 伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE
结构化查询:START WITH ...CONNNECT BY PRIOR基本语法是:
1
2
3
4
|
SELECT ... FROM WHERE (过滤返回记录,仅过滤被限定节点,其根节点和子节点均不受影响) START WITH (根节点,可以指定多个节点) CONNECT BY PRIOR = (连接条件, PRIOR 置于等号前,则从根节点到叶节点开始检索;置于等号后,则从叶节点到根节点开始检索) |
该查询访问路径如下:从根节点开始,向下扫描子节点,该子节点已被访问则转向其最左侧未被访问的子节点,否则判断该节点是否为根节点,是则访问完毕,否则返回父节点重新执行判断。
1
|
SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; |
--得到结果为:
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
1
|
SELECT SYS_CONNECT_BY_PATH(ename, </ '>' ) "Path" FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; |
--得到结果为:
KING
KING>JONES
KING>JONES>SCOTT
KING>JONES>SCOTT>ADAMS
KING>JONES>FORD
KING>JONES>FORD>SMITH
KING>BLAKE
KING>BLAKE>ALLEN
KING>BLAKE>WARD
KING>BLAKE>MARTIN
KING>BLAKE>TURNER
KING>BLAKE>JAMES
KING>CLARK
KING>CLARK>MILLER
六、聚合函数
- AVG(DISTINCT|ALL):平均值,DISTINCT表示对不同的值求平均值,重复值的列的只取一次。
1
|
SELECT AVG ( DISTINCT SAL) FROM SCOTT.EMP; |
- MAX(DISTINCT|ALL):最大值
- MIN(DISTINCT|ALL):最小值
- SUM(DISTINCT|ALL):求和
- COUNT(DISTINCT|ALL):求记录数
-
wmsys.wm_concat(DISTINCT|ALL):合并列。
将一列的多行记录合并到一列,用逗号隔开。例如表的有两个字段,要按airport_id合并成两行可用sql语句
1
|
select airport_id, wmsys.wm_concat( distinct account) from AIRPORT_MODIFY group by airport_id |
七、分析函数
到此这篇关于Oracle常用函数的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持服务器之家。
原文链接:https://www.cnblogs.com/springsnow/p/9399285.html