oracle常用函数

 

一、集合运算符

例：集合运算符的使用：

intersect ，union， union all， minus

--表A与B的交集

select * from ucr_param.td_A_score intersect select * from ucr_param.td_B_score;

--在表A中且不在表B

select * from ucr_param.td_A_score minus select * from ucr_param.td_B_score;

--不包括重复的行

select * from ucr_param.td_A_score union  select * from ucr_param.td_B_score;

--包括重复的行

select * from ucr_param.td_A_score union all select * from ucr_param.td_B_score;

 

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

 

可以在最后一个结果集中指定Order by子句改变排序方式。

二、ORACLE日期时间函数
1.日期和字符转换函数用法（to_date,to_char）        
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;
2.某天是星期几     
 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;     
     
3.两个日期间的天数     
 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

4.获取两个日期之间的具体时间间隔，extract函数是最好的选择

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 2:38:40') from dual;

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 2:38:40') from dual;

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 2:38:40') from dual;

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from dual;

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 2:38:40') from dual;

--获取两个日期之间的具体时间间隔，extract函数是最好的选择

select extract(day from dt2-dt1) day   

      ,extract(hour from dt2-dt1) hour   

      ,extract(minute from dt2-dt1) minute   

      ,extract(second from dt2-dt1) second   

from (    

     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1    

           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2    

     from dual)    
5.查找2002-02-28至2002-02-01间除星期一和七的天数    

select count(*)

  from (select rownum - 1 rnum

          from all_objects

         where rownum <= to_date('2014-03-15', 'yyyy-mm-dd') -

               to_date('2014-03-02', 'yyyy-mm-dd') + 1

)

  where to_char(to_date('2002-02-01', 'yyyy-mm-dd') + rnum - 1, 'D')     not in ('1', '7');
        

6.查找月份

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) month_num FROM DUAL;     

select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) month_num FROM DUAL;  
   
7.Next_day的用法     

SELECT next_day(sysdate,'星期一') from dual;--或者

SELECT next_day(sysdate,2) from dual;

三、字符函数(可用于字面字符或数据库列)

1.字符串截取        
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;
2.查找子串位置     
 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;     
     
3.字符串连接     
 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

4.去掉字符串中的空格

select ltrim(' abc') s1,rtrim('zhang ') s2,trim(' zhang ') s3 from dual;

5.去掉前导和后缀 

select trim(leading 9 from 9998767999) s1,

    trim(trailing 9 from 9998767999) s2,

trim(9 from 9998767999) s3 from dual;

876799999987678767

6.计算字符串长度

select length('abcdef') from dual;

7.nitcap（首字母变大写） ,lower(变小写),upper(变大写) 

select lower('ABC') s1, upper('def') s2,initcap('efg') s3 from dual;

8.Replace

SELECT REPLACE('AAAAbbbbbbbbb','A','a') from dual;

9.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
selectseed,account_name,decode(sign(seed-111),1,'bigseed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显示相等

 

10.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;

四、数字函数

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;

五、分组函数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;

六、Oracle trunc()函数的用法

/**************日期********************/

select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06

select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.

select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天

select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日

select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天

select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35

select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确

/***************数字********************/

/*

TRUNC（number,num_digits）

Number 需要截尾取整的数字。

Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。

TRUNC()函数截取时不进行四舍五入

*/

select trunc(123.458) from dual --123

select trunc(123.458,0) from dual --123

select trunc(123.458,1) from dual --123.4

select trunc(123.458,-1) from dual --120

select trunc(123.458,-4) from dual --0

select trunc(123.458,4) from dual --123.458

select trunc(123) from dual --123

select trunc(123,1) from dual --123

select trunc(123,-1) from dual --120