目录
前面我们学了一些简单的用法,今天要学习oracl函数的使用,函数是oracle非常重要的功能,如果将函数学会了,那么基本上在项目的一些增删改查功能,就可以很轻松的上手了,所以这篇文章一定要认真的看
这里的函数相当于java中写好的一些方法,有名字,可以传递参数,实现某一项具体功能。 函数分为: 1.单行函数 1.字符函数 2.日期函数 3.数字函数 4.转换函数 2.分组函数 学习之前,我们先了解一下什么是哑表,后面我们会用到
dual是一个虚拟表,辅助查找和运算。通常用在select语句中,作为查询的目标表结构,oracle保证dual里面永远只有一条记录。 例如: 显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法 select 1+1 from dual;
1) LOWER:转换成小写 2) UPPER:转换成大写 3) INITCAP:首字母变成大写,其余都小写 4) CONCAT:字符串的连接 5) SUBSTR(str,start,length):字符串的截取 6) LENGTH:求字符串的长度 7) NVL : 转换null的值。(前边已经用过) nvl2: LOWER 把字符转为小写 例如:把'HELLO'转换为小写 select lower('HELLO') from dual; 例如:把s_emp表中的last_name列的值转换为小写 select lower(last_name) from s_emp; upper 把字符转换为大写 例如:把'world'转换为大写 select upper('world') from dual;
例如:把s_emp表中的last_name列的值转换为大写 select upper(last_name) from s_emp; 例如:查询s_emp表中名字为Ngao的人信息 这样是查不到: select last_name,salary,dept_id from s_emp where last_name='NGAO';
这样就可以查询到了: select last_name,salary,dept_id from s_emp where upper(last_name)='NGAO';
initcap 把字符串首字母大写 例如:把'hELLO'转换为首字母大写,其余字母小写 select initcap('hELLO') from dual;
concat 把俩个字符串连接在一起(类似之前的||的作用) 例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg select concat('hello','world') msg from dual; 例如:把first_name和last_name俩个列的值连接到一起 select concat(first_name,last_name) from s_emp;
substr 截取字符串 例如:截取'hello'字符串,从第2个字符开始,截取后面的3个字符 select substr('hello',2,3) from dual;
length 获得字符串长度 例如:获得'world'字符串的长度 select length('world') from dual;
例如:获得s_emp表中last_name列的每个值的字符长度 select length(last_name) from s_emp;
nvl 替换列中为null的值 在前面的章节已经使用过了 例子: 1.查找last_name全小写的值为velasquez的员工的lastname select last_name from s_emp where lower(last_name) = 'velasquez';
2.查找last_name的长度>10的所有员工的last_name select last_name from s_emp where length(last_name)>10;
1) ROUND:四舍五入 2) TRUNC:截取,不进行四舍五入 3) MOD:取余
round 四舍五入 round(arg1,arg2) 第一个参数表示要进行四舍五入操作的数字 第二个参数表示保留到哪一位(负数代表小数点之前,0,正数代表小数点之后)
例如: 保留到小数点后面2位 select round(45.923,2) from dual;
保留到个位 (个十百千万...) select round(45.923,0) from dual;
保留到十位 (个十百千万...) select round(45.923,-1) from dual;
trunc 截取到某一位 trunc(arg1,arg2) 和round的用法一样,但是trunc只舍去不进位
例如: 截取到小数点后面2位 select trunc(45.923,2) from dual;
截取到个位 (个十百千万...) select trunc(45.923,0) from dual;
截取到十位 (个十百千万...) select trunc(45.923,-1) from dual;
mod 取余 mod(arg1,arg2) 第一个参数表示要进行取余操作的数字 第二个参数表示参数1和谁取余 例如: 把10和3进行取余 (10除以3然后获取余数) select mod(10,3) from dual;
1) MONTHS_BETWEEN:两个日期之间的月数 2) ADD_MONTHS:在指定日期上增加月数 3) NEXT_DAY:指定日期的下一个星期几是哪天 4) LAST_DAY:指定日期的最后一天 5) ROUND:对指定日期进行四舍五入 6) TRUNC:对指定日期进行截取
sysdate关键字 表示系统的当前时间 例如: 显示时间:当前时间 select sysdate from dual;
注意:sysdate进行加减操作的时候,单位是天 例如: 显示时间:明天的这个时候 select sysdate+1 from dual;
例如: 显示时间:昨天的这个时候 select sysdate-1 from dual; 例如: 显示时间:1小时之后的时候 select sysdate+1/24 from dual;
months_between 俩个时间点之间相差多少个月(单位是月) 例如: 30天之后和现在相差多少个月 select months_between(sysdate+30,sysdate) from dual;
add_months 返回一个日期数据:表示一个时间点,往后推x月的日期 例如: '01-2月-2016'往后推2个月 select add_months('01-2月-2016',2) from dual; 例如: 当前时间往后推4个月 select add_months(sysdate,4) from dual;
next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天 例如: 当前时间的下一个星期5是哪一个天 select next_day(sysdate,'星期五') from dual; 注意: 如果要使用'FRIDAY',那么需要把当前会话的语言环境修改为英文
last_day 返回一个日期数据:表示一个日期所在月份的最后一天 例如: 当前日期所在月份的最后一天 select last_day(sysdate) from dual;
round 对日期进四舍五入,返回操作后的日期数据。逢16日往月份进一,逢7月往年份进一 round(sysdate,'year/y/yy/yyy/yyyy') 年 7月节点 round(sysdate,'mm/month') 月 16号节点 round(sysdate,'d/day') 星期 星期四节点 round(sysdate,'dd') : 天 -》 12点节点 例如: 把当前日期四舍五入到月 select round(sysdate,'MONTH') from dual; 把当前日期四舍五入到年 select round(sysdate,'YEAR') from dual;
trunc 对日期进行截取 和round类似,但是只舍弃不进位 trunc(sysdate,'yyyy/year') --返回当年第一天。 trunc(sysdate,'mm/month') --返回当月第一天。 trunc(sysdate,'d/day') --返回当前星期的第一天。 trunc(sysdate,'dd')--返回当前年月日
1).TO_CHAR 将日期或者数值转换成字符串 2).TO_NUMBER 将字符串转换成数字 3).TO_DATE 将日期字符串转换成日期
to_char 把日期转换为字符 to_char('日期字符串','fmt'); 例如: 把当前日期按照指定格式转换为字符串 select to_char(sysdate,'yyyy') from dual; 日期格式: yyyy/YYYY:四位数的年份 rrrr:四位数的年份 yy:两位数的年份 rr:两位数的年份 mm:两位数的月份(数字) D:一周的第几天 DD:一月的第几天 DDD :一年的第几天 YEAR:英文的年份 MONTH:英文全称的月份 mon:英文简写的月份 ddsp:英文的第几天 ddspth:英文序列数的第几天 DAY:全英文的星期 DY:简写的英文星期 hh:小时 mi:分钟 ss:秒 AM:上午 PM:下午
例如: 测试常见的一些日期数据转换为字符串的格式 select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY') from dual;
select to_char(sysdate,'dd-mm-yy') from dual;
select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM') from dual;
在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说,假如是1971年,在计算机里就会被表示为71,但是到了2000年的时候这个情况就出现了问题,计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫 数据库中表示日期中年份的有俩种: yy和rr 之前一直使用的时候yy格式,后来才有的rr格式 yy表示使用一个俩位数表示当前年份: 1990 ---yy数据库格式---> 90 1968 ---yy数据库格式---> 68 1979 ---yy数据库格式---> 79 rr格式表示: 另外参照图片:rr日期格式规则.png 如果日期中的年份采用的格式为rr,并且只提供了最后2位年份,那么年份中的前两位数字就由两部分共同确定:提供年份的两位数字(指定年),数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下: 规则1 如果指定年在00~49之间,并且当前年份在00~49之间,那么指定年的世纪就与当前年份的世纪相同。因此,指定年的前两位数字就等于当前年份的前两位数字。例如,如果指定年为15,而当前年份为2007,那么指定年就是2015。 规则2 如果指定年在50~99之间,并且当前年份在00~49之间,那么指定年的世纪就等于当前年份的世纪减去1。因此,指定年的前两位数字等于当前年份的前两位数字减去1。例如,如果指定年为75,而当前年份为2007,那么指定年就是1975。 规则3 如果指定年在00~49之间,并且当前年份在50~99之间,那么指定年的世纪就等于当前年份的世纪加上1。因此,指定年的前两位数字等于当前年份的前两位数字加上1。例如,如果指定年为15,而当前年份为2075,那么指定年就是2115。 规则4 如果指定年在50~99之间,并且当前年份在50~99之间,那么指定年的世纪就与当前年份的世纪相同。因此,指定年的前两位数字就等于当前年份的前两位数字。例如,如果指定年为55,而当前年份为2075,那么指定年就是2055。
注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。
to_char 把数字转换为字符 L : 本地货币符号 : . : 小数点 , : 千分符 9 : 0-9 0 : 0-9, 如果位数不足,强制补0 例如: select to_char(salary,'
fm表示去除结果显示中的开始的空格 select to_char(salary,'fmL999,999.00') from s_emp;
L表示系统本地的货币符号 select to_char(salary,'fmL999,999.00') from s_emp;
to_number 把字符转换为数字 例如: select to_number('1000') from dual; //这个写法是错的 abc不能转换为数字 select to_number('abc') from dual;
to_date 把字符转换为日期 .TO_DATE(char, ['fmt']):例如 select TO_DATE ('10-September-1992','dd-Month-YYYY') from dual .使用format的元素格式 例如: select to_date('10-12-2016','dd-mm-yyyy') from dual;
select to_date('25-5月-95','dd-month-yy') from dual;
//session语言环境设置为英文下面可以运行 select to_date('25-MAY-95','dd-MONTH-yy') from dual;
oracle数据库中表示一个日期数据的几种方式 1.sysdate 2.oracle默认的日期格式 例如:'25-MAY-95' 3.to_date函数转换