ORACLE日期时间函数大全（二）
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
?? select sysdate S1,
?? round(sysdate) S2 ,
?? round(sysdate,'year') YEAR,
?? round(sysdate,'month') MONTH ,
?? round(sysdate,'day') DAY from dual
25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
?? select sysdate S1,????????????????????
???? trunc(sysdate) S2,???????????????? //返回当前日期,无时分秒
???? trunc(sysdate,'year') YEAR,??????? //返回当前年的1月1日,无时分秒
???? trunc(sysdate,'month') MONTH ,???? //返回当前月的1日,无时分秒
???? trunc(sysdate,'day') DAY?????????? //返回当前星期的星期天,无时分秒
?? from dual
26,返回日期列表中最晚日期
?? select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
27.计算时间差
???? 注:oracle时间差是以天数为单位,所以换算成年月,日
????
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual??????? //时间差-年
????? select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual??????? //时间差-月
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual???????????? //时间差-天
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual???????? //时间差-时
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual??? //时间差-分
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
28.更新时间
???? 注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual??????? //改变时间-年
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual???????????????????????????????? //改变时间-月
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual??????????? //改变时间-日
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual???????? //改变时间-时
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual????? //改变时间-分
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual?? //改变时间-秒
29.查找月的第一天,最后一天
???? SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
?????? Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
?????? Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
?????? LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
?? FROM dual;
三. 字符函数(可用于字面字符或数据库列)
1，字符串截取
?? select substr('abcdef',1,3) from dual
2,查找子串位置
?? select instr('abcfdgfdhd','fd') from dual
3,字符串连接
?? select 'HELLO'||'hello world' from dual;
4, 1)去掉字符串中的空格
??? select ltrim(' abc') s1,
??? rtrim('zhang ') s2,
??? trim(' zhang ') s3 from dual
?? 2)去掉前导和后缀
??? select trim(leading 9 from 9998767999) s1,
??? trim(trailing 9 from 9998767999) s2,
??? trim(9 from 9998767999) s3 from dual;
??
5,返回字符串首字母的Ascii值
?? select ascii('a') from dual
6,返回ascii值对应的字母
?? select chr(97) from dual
7,计算字符串长度
?? select length('abcdef') from dual
8,initcap（首字母变大写） ,lower(变小写),upper(变大写)
?? select lower('ABC') s1,
?????? upper('def') s2,
?????? initcap('efg') s3
?? from dual;
9,Replace
?? select replace('abc','b','xy') from dual;
10,translate
?? select translate('abc','b','xx') from dual; -- x是1位
11,lpad [左添充] rpad [右填充](用于控制输出格式)
?? select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
?? select lpad(dname,14,'=') from dept;
12, decode[实现if ..then 逻辑]?? 注:第一个是表达式,最后一个是不满足任何一个条件的值
?? select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
?? 例:
?? select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0
?? select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显
示相等
13 case[实现switch ..case 逻辑]
??? SELECT CASE X-FIELD
???????? WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
???????? WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'
???????? WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
???????? ELSE 'UNBEKNOWN'
??????? END
?? FROM DUAL
??
?? 注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时，用Decode更为简洁。
四.数字函数
1，取整函数（ceil 向上取整,floor 向下取整）
?? select ceil(66.6) N1,floor(66.6) N2 from dual;
2, 取幂(power) 和 求平方根(sqrt)
?? select power(3,2) N1,sqrt(9) N2 from dual;
3,求余
?? select mod(9,5) from dual;
4,返回固定小数位数 (round:四舍五入，trunc:直接截断)
?? select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
5,返回值的符号(正数返回为1,负数为-1)
?? select sign(-32),sign(293) from dual;
五.转换函数
1，to_char()[将日期和数字类型转换成字符类型]
?? 1) select to_char(sysdate) s1,
??????? to_char(sysdate,'yyyy-mm-dd') s2,
??????? to_char(sysdate,'yyyy') s3,
??????? to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
??????? to_char(sysdate, 'hh24:mi:ss') s5,
??????? to_char(sysdate,'DAY') s6
??? from dual;
?? 2) select sal,to_char(sal,'＄99999') n1,to_char(sal,'＄99,999') n2 from emp
2, to_date()[将字符类型转换为日期类型]
??? insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
??
3, to_number() 转换为数字类型
??? select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
??
六.其他函数
?? 1.user:
??? 返回登录的用户名称
??? select user from dual;
???
?? 2.vsize:
??? 返回表达式所需的字节数
??? select vsize('HELLO') from dual;
??
?? 3.nvl(ex1,ex2):
??? ex1值为空则返回ex2,否则返回该值本身ex1（常用）
??? 例：如果雇员没有佣金，将显示0，否则显示佣金
??? select comm,nvl(comm,0) from emp;
??
?? 4.nullif(ex1,ex2):
??? 值相等返空，否则返回第一个值
??? 例：如果工资和佣金相等，则显示空，否则显示工资
??? select nullif(sal,comm),sal,comm from emp;
??
?? 5.coalesce:
??? 返回列表中第一个非空表达式
??? select comm,sal,coalesce(comm,sal,sal*10) from emp;
??
?? 6.nvl2(ex1,ex2,ex3) :
??? 如果ex1不为空，显示ex2,否则显示ex3
??? 如：查看有佣金的雇员姓名以及他们的佣金
??? select nvl2(comm,ename,') as HaveCommName,comm from emp;
??
??
七.分组函数
max min avg count sum
1，整个结果集是一个组
?? 1) 求部门30 的最高工资，最低工资,平均工资，总人数，有工作的人数，工种数量及工资总和
???? select max(ename),max(sal),
???? min(ename),min(sal),
???? avg(sal),
???? count(*) ,count(job),count(distinct(job)) ,
???? sum(sal) from emp where deptno=30;
2, 带group by 和 having 的分组
?? 1)按部门分组求最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和
??? select deptno, max(ename),max(sal),
??? min(ename),min(sal),
??? avg(sal),
??? count(*) ,count(job),count(distinct(job)) ,
??? sum(sal) from emp group by deptno;
??
?? 2)部门30的最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和
??? select deptno, max(ename),max(sal),
??? min(ename),min(sal),
??? avg(sal),
??? count(*) ,count(job),count(distinct(job)) ,
??? sum(sal) from emp group by deptno having deptno=30;
??
3, stddev 返回一组值的标准偏差
??? select deptno,stddev(sal) from emp group by deptno;
??? variance 返回一组值的方差差
??? select deptno,variance(sal) from emp group by deptno;
4, 带有rollup和cube操作符的Group By
??? rollup 按分组的第一个列进行统计和最后的小计
??? cube 按分组的所有列的进行统计和最后的小计
??? select deptno,job ,sum(sal) from emp group by deptno,job;
??? select deptno,job ,sum(sal) from emp group by rollup(deptno,job);
??? cube 产生组内所有列的统计和最后的小计
??? select deptno,job ,sum(sal) from emp group by cube(deptno,job);
八、临时表
?? 只在会话期间或在事务处理期间存在的表.
?? 临时表在插入数据时，动态分配空间
?? create global temporary table temp_dept
?? (dno number,
?? dname varchar2(10))
?? on commit delete rows;
?? insert into temp_dept values(10,'ABC');
?? commit;
?? select * from temp_dept; --无数据显示,数据自动清除
?? on commit preserve rows:在会话期间表一直可以存在（保留数据）
?? on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据）
