oracle常用函數匯總

以下是對oracle中的常用函數進行了匯總介紹需要的朋友可以過來參考下  

　　一運算符
算術運算符+ * / 可以在select 語句中使用
連接運算符|| select deptno|| dname from dept;
比較運算符> >= = != < <= like between is null in
邏輯運算符not and or
集合運算符 intersect union union all minus
要求對應集合的列數和數據類型相同
     查詢中不能包含long 列
     列的標簽是第一個集合的標簽
     使用order by時必須使用位置序號不能使用列名

例集合運算符的使用

復制代碼 代碼如下:
intersect union union all minus
select * from emp intersect select * from emp where deptno= ;
select * from emp minus select * from emp where deptno=;
select * from emp where deptno= union select * from emp where deptno in (); 不包括重復行
select * from emp where deptno= union all select * from emp where deptno in (); 包括重復行

　　
二ORACLE日期時間函數大全
   TO_DATE格式(以時間:   ::為例)

        Year:     
        yy two digits 兩位年                顯示值:
        yyy three digits 三位年                顯示值:
        yyyy four digits 四位年                顯示值:

        Month:     
        mm    number     兩位月              顯示值:
        mon    abbreviated 字符集表示          顯示值:月若是英文版顯示nov    
        month spelled out 字符集表示          顯示值:月若是英文版顯示november

        Day:     
        dd    number         當月第幾天        顯示值:
        ddd    number         當年第幾天        顯示值:
        dy    abbreviated 當周第幾天簡寫    顯示值:星期五若是英文版顯示fri
        day    spelled out   當周第幾天全寫    顯示值:星期五若是英文版顯示friday       
        ddspth spelled out ordinal twelfth

              Hour:
              hh    two digits 小時進制            顯示值:
              hh two digits 小時進制            顯示值:

              Minute:
              mi    two digits 進制                顯示值:

              Second:
              ss    two digits 進制                顯示值:

              其它
              Q     digit         季度                  顯示值:
              WW    digit         當年第幾周            顯示值:
              W    digit          當月第幾周            顯示值:

        小時格式下時間范圍為 :: ::     
        小時格式下時間范圍為 :: ::

日期和字符轉換函數用法（to_dateto_char）
        
select to_char(sysdateyyyymmdd hh:mi:ss) as nowTime from dual;   //日期轉化為字符串  
select to_char(sysdateyyyy) as nowYear   from dual;   //獲取時間的年  
select to_char(sysdatemm)    as nowMonth from dual;   //獲取時間的月  
select to_char(sysdatedd)    as nowDay    from dual;   //獲取時間的日  
select to_char(sysdatehh) as nowHour   from dual;   //獲取時間的時  
select to_char(sysdatemi)    as nowMinute from dual;   //獲取時間的分  
select to_char(sysdatess)    as nowSecond from dual;   //獲取時間的秒

select to_date( ::yyyymmdd hh:mi:ss)    from dual//

 select to_char( to_date(J)Jsp) from dual     

    顯示Two Hundred TwentyTwo  

求某天是星期幾     
   select to_char(to_date(yyyymmdd)day) from dual;     
   星期一     
   select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;     
   monday     
   設置日期語言     
   ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;     
   也可以這樣     
   TO_DATE ( YYYYmmdd NLS_DATE_LANGUAGE = American)     

兩個日期間的天數     
    select floor(sysdate to_date(yyyymmdd)) from dual;  

時間為null的用法     
   select id active_date from table     
   UNION     
   select TO_DATE(null) from dual;      
   注意要用TO_DATE(null)     

月份差  
   a_date between to_date(yyyymmdd) and to_date(yyyymmdd)     
   那麼月號中午點之後和月號的點之前是不包含在這個范圍之內的     
   所以當時間需要精確的時候覺得to_char還是必要的

日期格式沖突問題     
    輸入的格式要看你安裝的ORACLE字符集的類型 比如: USASCII date格式的類型就是: Jan     
    alter system set NLS_DATE_LANGUAGE = American     
    alter session set NLS_DATE_LANGUAGE = American     
    或者在to_date中寫     
    select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;     
    注意我這只是舉了NLS_DATE_LANGUAGE當然還有很多     
    可查看     
    select * from nls_session_parameters     
    select * from V$NLS_PARAMETERS     
     

復制代碼 代碼如下:
   select count(*)     
   from ( select rownum rnum     
       from all_objects     
       where rownum <= to_date(yyyymmdd) to_date(     
       yyyymmdd)+     
      )     
   where to_char( to_date(yyyymmdd)+rnum D )     
        not in ( )     

　　
   查找至間除星期一和七的天數     
   在前後分別調用DBMS_UTILITYGET_TIME 讓後將結果相減(得到的是/秒 而不是毫秒)     

查找月份  

復制代碼 代碼如下:
    select months_between(to_date(MMDDYYYY)to_date(MMDDYYYY)) "MONTHS" FROM DUAL;     
         
   select months_between(to_date(MMDDYYYY)to_date(MMDDYYYY)) "MONTHS" FROM DUAL;     
   

　　
Next_day的用法     

復制代碼 代碼如下:
    Next_day(date day)     

    MondaySunday for format code DAY     
    MonSun for format code DY     
    for format code D    

　　
     
   select to_char(sysdatehh:mi:ss) TIME from all_objects     
   注意第一條記錄的TIME 與最後一行是一樣的     
   可以建立一個函數來處理這個問題     

復制代碼 代碼如下:
   create or replace function sys_date return date is     
   begin     
   return sysdate;     
   end;     

   select to_char(sys_datehh:mi:ss) from all_objects;  

　　
獲得小時數     
     extract()找出日期或間隔值的字段值

復制代碼 代碼如下:
    SELECT EXTRACT(HOUR FROM TIMESTAMP ::) from offer     
    SQL> select sysdate to_char(sysdatehh) from dual;     

    SYSDATE TO_CHAR(SYSDATEHH)     
         
    ::      

    SQL> select sysdate to_char(sysdatehh) from dual;     

    SYSDATE TO_CHAR(SYSDATEHH)     
         
    ::      

　　
年月日的處理     

復制代碼 代碼如下:
   select older_date     
       newer_date     
       years     
       months     
       abs(     
        trunc(     
         newer_date     
         add_months( older_dateyears*+months )     
        )     
       ) days

   from ( select     
        trunc(months_between( newer_date older_date )/) YEARS     
        mod(trunc(months_between( newer_date older_date )) ) MONTHS     
        newer_date     
        older_date     
        from (
              select hiredate older_date add_months(hiredaterownum)+rownum newer_date     
              from emp
             )     
      )     

　　
處理月份天數不定的辦法     
   select to_char(add_months(last_day(sysdate) + ) yyyymmdd)last_day(sysdate) from dual     
找出今年的天數     
   select add_months(trunc(sysdateyear) ) trunc(sysdateyear) from dual     
   閏年的處理方法     
   to_char( last_day( to_date(    | | :yearmmyyyy) ) dd )     
   如果是就不是閏年     
yyyy與rrrr的區別     

復制代碼 代碼如下:
   YYYY TO_C     
        
   yyyy      
   rrrr      
   yyyy      
   rrrr      

　　
不同時區的處理     
   select to_char( NEW_TIME( sysdate GMTEST) dd/mm/yyyy hh:mi:ss) sysdate     
   from dual;   

秒鐘一個間隔  

復制代碼 代碼如下:
   Select TO_DATE(FLOOR(TO_CHAR(sysdateSSSSS)/) * SSSSS) TO_CHAR(sysdateSSSSS)     
   from dual     
   ::      
   SSSSS表示位秒數    
  

　　
一年的第幾天     
   select TO_CHAR(SYSDATEDDD)sysdate from dual

   ::     

計算小時分秒毫秒 

復制代碼 代碼如下:
     select     
     Days     
     A     
     TRUNC(A*) Hours     
     TRUNC(A** *TRUNC(A*)) Minutes     
     TRUNC(A*** *TRUNC(A**)) Seconds     
     TRUNC(A**** *TRUNC(A***)) mSeconds     
    from     
    (     
     select     
     trunc(sysdate) Days     
     sysdate trunc(sysdate) A     
     from dual     
   )     
   select * from tabname     
   order by decode(modeFIFO)*to_char(rqyyyymmddhhmiss);     

   //     
   floor((datedate) /) 作為年     
   floor((datedate ) /) 作為月     
   d(mod(datedate ) )作為日

　　
next_day函數      返回下個星期的日期day為或星期日星期六表示星期日
   next_day(sysdate)是從當前開始下一個星期五後面的數字是從星期日開始算起     
        
   日 一 二 三 四 五 六   

  

   select    (sysdateto_date( ::yyyymmdd hh:mi:ss))*** from ddual
   日期 返回的是天 然後 轉換為ss

round[捨入到最接近的日期](day:捨入到最接近的星期日)
   select sysdate S
   round(sysdate) S
   round(sysdateyear) YEAR
   round(sysdatemonth) MONTH
   round(sysdateday) DAY from dual

trunc[截斷到最接近的日期單位為天] 返回的是日期類型
   select sysdate S                    
     trunc(sysdate) S                 //返回當前日期無時分秒
     trunc(sysdateyear) YEAR        //返回當前年的月日無時分秒
     trunc(sysdatemonth) MONTH      //返回當前月的日無時分秒
     trunc(sysdateday) DAY           //返回當前星期的星期天無時分秒
   from dual

返回日期列表中最晚日期
   select greatest(月月月) from dual

計算時間差
     注:oracle時間差是以天數為單位所以換算成年月日

      select floor(to_number(sysdateto_date( ::yyyymmdd hh:mi:ss))/) as spanYears from dual        //時間差年
      select ceil(moths_between(sysdateto_date( ::yyyymmdd hh:mi:ss))) as spanMonths from dual        //時間差月
      select floor(to_number(sysdateto_date( ::yyyymmdd hh:mi:ss))) as spanDays from dual             //時間差天
      select floor(to_number(sysdateto_date( ::yyyymmdd hh:mi:ss))*) as spanHours from dual         //時間差時
      select floor(to_number(sysdateto_date( ::yyyymmdd hh:mi:ss))**) as spanMinutes from dual    //時間差分
      select floor(to_number(sysdateto_date( ::yyyymmdd hh:mi:ss))***) as spanSeconds from dual //時間差秒

更新時間
     注:oracle時間加減是以天數為單位設改變量為n所以換算成年月日
     select to_char(sysdateyyyymmdd hh:mi:ss)to_char(sysdate+n*yyyymmdd hh:mi:ss) as newTime from dual        //改變時間年
     select to_char(sysdateyyyymmdd hh:mi:ss)add_months(sysdaten) as newTime from dual                                 //改變時間月
     select to_char(sysdateyyyymmdd hh:mi:ss)to_char(sysdate+nyyyymmdd hh:mi:ss) as newTime from dual            //改變時間日
     select to_char(sysdateyyyymmdd hh:mi:ss)to_char(sysdate+n/yyyymmdd hh:mi:ss) as newTime from dual         //改變時間時
     select to_char(sysdateyyyymmdd hh:mi:ss)to_char(sysdate+n//yyyymmdd hh:mi:ss) as newTime from dual      //改變時間分
     select to_char(sysdateyyyymmdd hh:mi:ss)to_char(sysdate+n///yyyymmdd hh:mi:ss) as newTime from dual   //改變時間秒

查找月的第一天最後一天
     SELECT Trunc(Trunc(SYSDATE MONTH) MONTH) First_Day_Last_Month
       Trunc(SYSDATE MONTH) / Last_Day_Last_Month
       Trunc(SYSDATE MONTH) First_Day_Cur_Month
       LAST_DAY(Trunc(SYSDATE MONTH)) + / Last_Day_Cur_Month
   FROM dual;

三 字符函數(可用於字面字符或數據庫列)
字符串截取
   select substr(abcdef) from dual

查找子串位置
   select instr(abcfdgfdhdfd) from dual

字符串連接
   select HELLO||hello world from dual;

)去掉字符串中的空格
    select ltrim( abc) s
    rtrim(zhang ) s
    trim( zhang ) s from dual

   )去掉前導和後綴
    select trim(leading from ) s
    trim(trailing from ) s
    trim( from ) s from dual;

返回字符串首字母的Ascii值
   select ascii(a) from dual

返回ascii值對應的字母
   select chr() from dual

計算字符串長度
   select length(abcdef) from dual

initcap（首字母變大寫） lower(變小寫)upper(變大寫)
   select lower(ABC) s 
       upper(def) s
       initcap(efg) s
   from dual;

Replace
   select replace(abcbxy) from dual;

translate
   select translate(abcbxx) from dual; x是位

lpad [左添充] rpad [右填充](用於控制輸出格式)
   select lpad(func=) s rpad(func) s from dual;
   select lpad(dname=) from dept;

decode[實現if then 邏輯]   注:第一個是表達式最後一個是不滿足任何一個條件的值
   select deptnodecode(deptno其他) from dept;
   例:
   select seedaccount_namedecode(seed) from t_userInfo//如果seed為則取;為取;其它取
   select seedaccount_namedecode(sign(seed)big seedlittle seedequal seed) from t_userInfo//如果seed>則顯示大;為則顯示小;其它則顯示相等

case[實現switch case 邏輯]

復制代碼 代碼如下:
    SELECT CASE XFIELD
         WHEN XFIELD < THEN XFIELD 小於
         WHEN XFIELD < THEN XFIELD 小於
         WHEN XFIELD < THEN XFIELD 小於
         ELSE UNBEKNOWN
        END
   FROM DUAL

　　
注:CASE語句在處理類似問題就顯得非常靈活當只是需要匹配少量數值時用Decode更為簡潔

四數字函數
取整函數（ceil 向上取整floor 向下取整）
   select ceil() Nfloor() N from dual;

取冪(power) 和 求平方根(sqrt)
   select power() Nsqrt() N from dual;

求余
   select mod() from dual;

返回固定小數位數 (round:四捨五入trunc:直接截斷)
   select round() Ntrunc() N from dual;

返回值的符號(正數返回為負數為)
   select sign()sign() from dual;

五轉換函數
to_char()[將日期和數字類型轉換成字符類型]
   ) select to_char(sysdate) s
        to_char(sysdateyyyymmdd) s
        to_char(sysdateyyyy) s
        to_char(sysdateyyyymmdd hh:mi:ss) s
        to_char(sysdate hh:mi:ss) s
        to_char(sysdateDAY) s
    from dual;
   ) select salto_char(sal＄) nto_char(sal＄) n from emp

to_date()[將字符類型轉換為日期類型]
    insert into emp(empnohiredate) values(to_date(yyyymmdd));

to_number() 轉換為數字類型
    select to_number(to_char(sysdatehh)) from dual; //以數字顯示的小時數

六其他函數
   user:
    返回登錄的用戶名稱
    select user from dual;

   vsize:
    返回表達式所需的字節數
    select vsize(HELLO) from dual;

   nvl(exex): 　
    ex值為空則返回ex否則返回該值本身ex（常用）
    例如果雇員沒有傭金將顯示否則顯示傭金
    select commnvl(comm) from emp;

   nullif(exex):
    值相等返空否則返回第一個值
    例如果工資和傭金相等則顯示空否則顯示工資
    select nullif(salcomm)salcomm from emp;

   coalesce: 　
    返回列表中第一個非空表達式
    select commsalcoalesce(commsalsal*) from emp;

   nvl(exexex) :
    如果ex不為空顯示ex否則顯示ex
    如查看有傭金的雇員姓名以及他們的傭金
    select nvl(commename) as HaveCommNamecomm from emp;

  
七分組函數
max min avg count sum
整個結果集是一個組
   ) 求部門 的最高工資最低工資平均工資總人數有工作的人數工種數量及工資總和

復制代碼 代碼如下:
     select max(ename)max(sal)
     min(ename)min(sal)
     avg(sal)
     count(*) count(job)count(distinct(job))
     sum(sal) from emp where deptno=;

　　
帶group by 和 having 的分組
   )按部門分組求最高工資最低工資總人數有工作的人數工種數量及工資總和

復制代碼 代碼如下:    
    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=;

　　
stddev 返回一組值的標准偏差
    select deptnostddev(sal) from emp group by deptno;
    variance 返回一組值的方差差
    select deptnovariance(sal) from emp group by deptno;

帶有rollup和cube操作符的Group By
    rollup 按分組的第一個列進行統計和最後的小計
    cube 按分組的所有列的進行統計和最後的小計
    select deptnojob sum(sal) from emp group by deptnojob;
    select deptnojob sum(sal) from emp group by rollup(deptnojob);
    cube 產生組內所有列的統計和最後的小計
    select deptnojob sum(sal) from emp group by cube(deptnojob);

八臨時表
   只在會話期間或在事務處理期間存在的表
   臨時表在插入數據時動態分配空間

復制代碼 代碼如下:
   create global temporary table temp_dept
   (dno number
   dname varchar())
   on commit delete rows;
   insert into temp_dept values(ABC);
   commit;
   select * from temp_dept; 無數據顯示數據自動清除
   on commit preserve rows:在會話期間表一直可以存在（保留數據）
   on commit delete rows:事務結束清除數據（在事務結束時自動刪除表的數據）

　　
除法

復制代碼 代碼如下:
select MOD() from dual     取余
select  trunc( /) from dual  取整 trunc () =
select ceil(/) from dual          取整 ceil() =
From:http://tw.wingwit.com/Article/program/Oracle/201311/19033.html