首页 > 代码库 > ORACLE SQL单行函数（二）【weber出品必属精品】
ORACLE SQL单行函数（二）【weber出品必属精品】
11.dual：虚表，任何用户都可以使用，表结构如下：
SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1)
12.dual的作用：
1. 查询数据库系统日期
2. 进行四则运算
SQL> select sysdate from dual; ---这里查询数据库系统日期 SYSDATE---------15-AUG-14SQL> select 34*12 from dual; ---这里进行四则运算 34*12---------- 408 SQL> select ‘abc‘ from dual; ---这里进行字符串的输出 ‘AB---abc
13.日期的格式：
默认的格式：DD-MON-RR
如何改变日期的格式：nls_date_format
SQL> alter session set nls_date_format=‘yyyy--mm--dd hh24:mi:ss‘; ---修改日期格式 Session altered. SQL> select sysdate from dual; SYSDATE---------------------2014--08--15 14:56:40
14.日期的计算
两个日期相减得到是天数：
SQL> select sysdate,hiredate,sysdate-hiredate from emp; SYSDATE HIREDATE SYSDATE-HIREDATE------------------- ------------------- ----------------2014-08-15 09:11:48 1980-12-17 00:00:00 12294.38322014-08-15 09:11:48 1981-02-20 00:00:00 12229.38322014-08-15 09:11:48 1981-02-22 00:00:00 12227.38322014-08-15 09:11:48 1981-04-02 00:00:00 12188.38322014-08-15 09:11:48 1981-09-28 00:00:00 12009.38322014-08-15 09:11:48 1981-05-01 00:00:00 12159.38322014-08-15 09:11:48 1981-06-09 00:00:00 12120.38322014-08-15 09:11:48 1987-04-19 00:00:00 9980.383192014-08-15 09:11:48 1981-11-17 00:00:00 11959.38322014-08-15 09:11:48 1981-09-08 00:00:00 12029.38322014-08-15 09:11:48 1987-05-23 00:00:00 9946.383192014-08-15 09:11:48 1981-12-03 00:00:00 11943.38322014-08-15 09:11:48 1981-12-03 00:00:00 11943.38322014-08-15 09:11:48 1982-01-23 00:00:00 11892.3832
计算周数只需要除以7即可
SQL> select sysdate,hiredate,sysdate-hiredate from emp; SYSDATE HIREDATE SYSDATE-HIREDATE------------------- ------------------- ----------------2014-08-15 09:11:48 1980-12-17 00:00:00 12294.38322014-08-15 09:11:48 1981-02-20 00:00:00 12229.38322014-08-15 09:11:48 1981-02-22 00:00:00 12227.38322014-08-15 09:11:48 1981-04-02 00:00:00 12188.38322014-08-15 09:11:48 1981-09-28 00:00:00 12009.38322014-08-15 09:11:48 1981-05-01 00:00:00 12159.38322014-08-15 09:11:48 1981-06-09 00:00:00 12120.38322014-08-15 09:11:48 1987-04-19 00:00:00 9980.383192014-08-15 09:11:48 1981-11-17 00:00:00 11959.38322014-08-15 09:11:48 1981-09-08 00:00:00 12029.38322014-08-15 09:11:48 1987-05-23 00:00:00 9946.383192014-08-15 09:11:48 1981-12-03 00:00:00 11943.38322014-08-15 09:11:48 1981-12-03 00:00:00 11943.38322014-08-15 09:11:48 1982-01-23 00:00:00 11892.3832
给日期减去1，效果作用在天上
SQL> select hiredate,hiredate-1 from emp; HIREDATE HIREDATE-1--------------------- ---------------------1980--12--17 00:00:00 1980--12--16 00:00:001981--02--20 00:00:00 1981--02--19 00:00:001981--02--22 00:00:00 1981--02--21 00:00:001981--04--02 00:00:00 1981--04--01 00:00:001981--09--28 00:00:00 1981--09--27 00:00:001981--05--01 00:00:00 1981--04--30 00:00:00
减少一小时，增加也是一样：
SQL> select sysdate,sysdate-1/(24) from dual; SYSDATE SYSDATE-1/(24)--------------------- ---------------------2014--08--15 15:04:43 2014--08--15 14:04:43
增加一分钟：
SQL> select sysdate ,sysdate+1/(24*60) from dual; SYSDATE SYSDATE+1/(24*60)--------------------- ---------------------2014--08--15 15:06:47 2014--08--15 15:07:47
增加一秒钟：
SQL> select sysdate,sysdate+1/(24*60*60) from dual; SYSDATE SYSDATE+1/(24*60*60------------------- -------------------2014-08-15 09:14:52 2014-08-15 09:14:53
15.日期函数
MONTHS_BETWEEN：两个日期之间的月数
SQL> select hiredate,sysdate,months_between(sysdate,hiredate) from emp; HIREDATE SYSDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)------------------- ------------------- --------------------------------1980-12-17 00:00:00 2014-08-15 09:18:21 403.9479921981-02-20 00:00:00 2014-08-15 09:18:21 401.8512181981-02-22 00:00:00 2014-08-15 09:18:21 401.7867011981-04-02 00:00:00 2014-08-15 09:18:21 400.4318631981-09-28 00:00:00 2014-08-15 09:18:21 394.5931531981-05-01 00:00:00 2014-08-15 09:18:21 399.4641211981-06-09 00:00:00 2014-08-15 09:18:21 398.2060561987-04-19 00:00:00 2014-08-15 09:18:21 327.883476
ADD_MONTHS：为一个日期增加月份：
SQL> select sysdate,add_months(sysdate,1) from dual; SYSDATE ADD_MONTHS(SYSDATE,1)--------------------- ---------------------2014--08--15 15:14:07 2014--09--15 15:14:07
NEXT_DAY：一个日期的下一个指定日子（例如：星期五）的日期
SQL> select sysdate ,next_day(sysdate,‘friday‘) next_day from dual; SYSDATE NEXT_DAY--------------------- ---------------------2014--08--15 15:17:50 2014--08--22 15:17:50
LAST_DAY：某个月份的最后一天
SQL> select sysdate ,last_day(sysdate) from dual; SYSDATE LAST_DAY(SYSDATE)--------------------- ---------------------2014--08--15 15:20:12 2014--08--31 15:20:12
对日期进行截取：
ROUND：要进行四舍五入
TRUNC：不进行四舍五入
SQL> select sysdate,round(sysdate) round,trunc(sysdate) trunc from dual; SYSDATE ROUND TRUNC--------------------- --------------------- --------------------2014--08--15 15:21:58 2014--08--16 00:00:00 2014--08--15 00:00:00
16. 转换函数
数据类型转换方式：
1. 隐士转换，也叫自动转换
2. 显示转换，通过转换函数进行转换
TO_CHAR函数：可以把数字和日期转换为字符
TO_CHAR(date, ‘format_model‘)：将日期转换为字符 format_model是：日期的格式
SQL> select sysdate,to_char(sysdate,‘mm-dd-yyyy hh24:mi:ss‘) from dual; SYSDATE TO_CHAR(SYSDATE,‘MM--------------------- ------------------- ---按照日期格式将格式转换后输出2014--08--15 15:28:27 08-15-2014 15:28:27
Format_model:
1、日期格式必须用单引号括起来，并且是大小写敏感的
2、日期格式必须是可用的
3、前面加上 fm字符以压缩掉开始和结束的空格或者前置的零
4、同日期值用逗号隔开
SQL> select sysdate,to_char(sysdate,‘YYYY‘) from dual; SYSDATE TO_C--------------------- ----2014--08--15 15:31:33 2014
验证：日期格式必须用单引号括起来，并且是大小写敏感的
SQL> select sysdate,to_char(sysdate,‘YEAR‘) from dual; SYSDATE TO_CHAR(SYSDATE,‘YEAR‘)--------------------- ------------------------------------------2014--08--15 15:32:31 TWENTY FOURTEEN SQL> select sysdate ,to_char(sysdate,‘year‘) from dual; SYSDATE TO_CHAR(SYSDATE,‘YEAR‘)--------------------- ------------------------------------------2014--08--15 15:32:53 twenty fourteen
验证：简写和祥写的不同 ---日期元素区分大小写
SQL> select sysdate,to_char(sysdate,‘mm‘),to_char(sysdate,‘month‘) from dual; SYSDATE TO TO_CHAR(SYSDATE,‘MONTH‘)--------------------- -- ---------------------------2014--08--15 15:35:53 08 august SQL> select sysdate,to_char(sysdate,‘dy‘),to_char(sysdate,‘day‘) from dual; SYSDATE TO_CHAR(S TO_CHAR(SYSDATE,‘DAY‘)--------------------- --------- ---------------------------2014--08--15 15:36:43 fri friday SQL> select sysdate,to_char(sysdate,‘DY‘),to_char(sysdate,‘DAY‘) from dual; SYSDATE TO_CHAR(S TO_CHAR(SYSDATE,‘DAY‘)--------------------- --------- ---------------------------2014--08--15 15:37:22 FRI FRIDAY
日期中时间部分的格式：
SQL> select sysdate,to_char(sysdate,‘yyyy/mm/dd hh24:mi:ss pm‘) time from dual; SYSDATE TIME--------------------- ----------------------2014--08--15 15:41:26 2014/08/15 15:41:26 pm
增加字符串需要用双引号将字符串括起来：
SQL> select sysdate,to_char(sysdate,‘day "of" month‘) string from dual; SYSDATE STRING------------------- ---------------------------------------------2014-08-15 09:40:38 friday of august
fm：去掉日期元素前置的零和空格
SQL> select sysdate,to_char(sysdate,‘fmyyyy-mm-dd hh24:mi:ss am‘) 去掉空格 from dual; SYSDATE 去掉空格--------------------- ----------------------2014--08--15 15:50:40 2014-8-15 15:50:40 pm ---08前面的0被去掉了
在一个数字前面显示$符号 ---$不管在9999.99的前面还是后面，结果显示都是在9999.99的前面。
SQL> select ename,sal,to_char(sal,‘9999.99$‘) from emp; ENAME SAL TO_CHAR(S------ ----- ---------SMITH 800 $800.00ALLEN 1600 $1600.00WARD 1250 $1250.00JONES 2975 $2975.00MARTIN 1250 $1250.00BLAKE 2850 $2850.00CLARK 2450 $2450.00SCOTT 4000 $4000.00KING 5000 $5000.00TURNER 1500 $1500.00ADAMS 1100 $1100.00JAMES 950 $950.00FORD 3000 $3000.00MILLER 1300 $1300.00
如果9999.99变成了999.99，大于1000的数字则会显示乱码。
SQL> select ename,to_char(sal,‘999.99‘) from emp; ENAME TO_CHAR---------- -------SMITH 800.00ALLEN #######WARD #######JONES #######MARTIN #######BLAKE #######CLARK #######SCOTT #######KING #######
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。