[Oracle] oracle常用函数汇总(分享)

intersect ，union， union all， minus 
select * from emp intersect select * from emp where deptno=10 ;
select * from emp minus select * from emp where deptno=10;
select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 
select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行

select count(*)    
   from ( select rownum-1 rnum    
     from all_objects    
     where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-    
     02-01','yyyy-mm-dd')+1    
    )    
   where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )    
    not in ( '1', '7' )

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;    
  1    
   select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;    
  1.03225806451613

Next_day(date, day)    
  Monday-Sunday, for format code DAY    
  Mon-Sun, for format code DY    
  1-7, for format code D

create or replace function sys_date return date is    
   begin    
   return sysdate;    
   end;    
   select to_char(sys_date,'hh:mi:ss') from all_objects;

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer    
  SQL> select sysdate ,to_char(sysdate,'hh') from dual;    
  SYSDATE TO_CHAR(SYSDATE,'HH')    
  -------------------- ---------------------    
  2003-10-13 19:35:21 07    
  SQL> select sysdate ,to_char(sysdate,'hh24') from dual;    
  SYSDATE TO_CHAR(SYSDATE,'HH24')    
  -------------------- -----------------------    
  2003-10-13 19:35:21 19

select older_date,    
     newer_date,    
     years,    
     months,    
     abs(    
    trunc(    
     newer_date-    
     add_months( older_date,years*12+months )    
    )    
     ) days 
   from ( select    
    trunc(months_between( newer_date, older_date )/12) YEARS,    
    mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,    
    newer_date,    
    older_date    
    from ( 
        select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date    
        from emp 
       )    
    )

'YYYY99 TO_C    
   ------- ----    
   yyyy 99 0099    
   rrrr 99 1999    
   yyyy 01 0001    
   rrrr 01 2001

Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')    
   from dual    
   2002-11-1 9:55:00 35786    
   SSSSS表示5位秒数

select    
   Days,    
   A,    
   TRUNC(A*24) Hours,    
   TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,    
   TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,    
   TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds    
  from    
  (    
   select    
   trunc(sysdate) Days,    
   sysdate - trunc(sysdate) A    
   from dual    
   )    
   select * from tabname    
   order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');    
   //    
   floor((date2-date1) /365) 作为年    
   floor((date2-date1, 365) /30) 作为月    
   d(mod(date2-date1, 365), 30)作为日.

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

select max(ename),max(sal), 
   min(ename),min(sal),
   avg(sal),
   count(*) ,count(job),count(distinct(job)) ,
   sum(sal) from emp where deptno=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;

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;

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:事务结束清除数据（在事务结束时自动删除表的数据）

select MOD(65,50) from dual   --取余 
select  trunc( 65/33) from dual  -- 取整 trunc (1.9) = 1
select ceil(65/60) from dual      -- 取整 ceil(1.1) = 2