一、数值函数
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： 当年第几天. 1月1日为001，2月1日为032
- dd： 当月第几天
- d： 周内第几天
- dy： 周内第几天缩写
- hh或hh12： 12进制小时数
- hh24： 24小时制
- mi： 分钟数(0～59) ：提示注意不要将mm格式用于分钟(分钟应该使用mi)。mm是用于月份的格式，将它用于分钟也能工作，但结果是错误的。
- ss： 秒数(0～59)
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：转换成序数
select to_char(122323.45, '$99999999.99') from dual;
3： to_date(string,'format')：将字符串转化为日期
主要用于比较和修改日期。
select to_date('2011/03/24', 'yyyy-mm-dd') from dual;
4： to_number：将给出的字符转换为数字
select to_number('￥2008.00'，'l9999d99') as year from dual;
五、辅助函数
1、decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值):根据条件返回相应值
值1……n 不能为条件表达式,这种情况只能用case when then end解决。
select decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期from xqb
2、greatest(exp1,exp2,exp3,……,expn):返回表达式列表中值最大的一个。 ; least(exp1,exp2,exp3,……,expn):返回表达式列表中值最小的一个。
如果表达式类型不同，会隐含转换为第一个表达式类型。
select greatest(10,32,'123','2006') from dual;
3、nullif (expr1, expr2)：expr1和expr2相等返回null，不相等返回expr1。
select nullif( 'a', 'b' ) ;--返回值 a select nullif( 'a', 'a');--返回 null
实际应用：
--添加函数查询结果，要求（将日期类型默认'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。
select nvl(null,'未知') sextype from dual;--expr1为空，返回expr2，结果：'未知' select nvl('1','2') sextype from dual;--expr1不为空，返回expr1， 结果：1
实际应用：
--加函数时情况，要求（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的类型
select nvl2(0,1,2) from dual;--不为null时，返回expr2 ，结果：1 select nvl2(null,1,2) from dual;--为null时，返回expr3 ，结果：2
实际应用：
--应用到实际查询中，要求（field 为null,返回0; field 不为null,返回本身） select nvl2(useflag,useflag,'0') 使用标志,a.name 单据名称 from tab
6、coalesce(c1, c2, …,cn)：返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值。
select coalesce(null,3*5,44) hz from dual; --返回15
7、sys_context('userenv',c2)：返回系统'userenv'变量中c2对应的的值。
sys_context('userenv','language') language,
8、sys_connect_by_path(column_name,'分隔符')：把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示
详见 oracle递归查询connect by
- 第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符
- 伪列connect_by_root,connect_by_leaf,connect_by_iscycle
结构化查询：start with …connnect by prior基本语法是：
select ...from where （过滤返回记录，仅过滤被限定节点，其根节点和子节点均不受影响） start with (根节点，可以指定多个节点) connect by prior= （连接条件，prior置于等号前，则从根节点到叶节点开始检索；置于等号后，则从叶节点到根节点开始检索）
该查询访问路径如下：从根节点开始，向下扫描子节点，该子节点已被访问则转向其最左侧未被访问的子节点，否则判断该节点是否为根节点，是则访问完毕，否则返回父节点重新执行判断。
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
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表示对不同的值求平均值,重复值的列的只取一次。
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语句
select airport_id, wmsys.wm_concat(distinct account) from airport_modify group by airport_id
七、分析函数
见专题：oracle分析函数
到此这篇关于oracle函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持<计算机技术网(www.ctvol.com)!!>。
需要了解更多数据库技术:Oracle中的函数详解，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/1088546.html