Oracle函數(shù)及其查詢

1、單行函數(shù)可以出現(xiàn)在單行函數(shù)可以出現(xiàn)在SELECTSELECT語句的語句的SELECTSELECT子句子句WHEREWHERE子句子句ORDER BYORDER BY子句子句START WITHSTART WITH子句子句CONNECT BYCONNECT BY子句子句1.1.單行函數(shù)單行函數(shù)單行函數(shù)特點單行函數(shù)特點(1) (1) 基于單行返回結(jié)果基于單行返回結(jié)果(2) (2) 允許參數(shù)是其他單行函數(shù)產(chǎn)生的結(jié)果允許參數(shù)是其他單行函數(shù)產(chǎn)生的結(jié)果(3) (3) 對查詢返回的各行分別進行計算對查詢返回的各行分別進行計算(4) (4) 可以用常量、變量、列名以及表達式作為參數(shù)可以用常量、變量、列名以及表

2、達式作為參數(shù)數(shù)值函數(shù)數(shù)值函數(shù)接受數(shù)值型輸入數(shù)據(jù)，并返回數(shù)值型的結(jié)果接受數(shù)值型輸入數(shù)據(jù)，并返回數(shù)值型的結(jié)果1.1.單行函數(shù)單行函數(shù)ABS(n)ABS(n)CEIL(n)CEIL(n)COS(n)COS(n)COSH(n)COSH(n)EXP(n)EXP(n)LN(n)LN(n)LOG(m,n)LOG(m,n)MOD(m,n)MOD(m,n)POWER(m,n)POWER(m,n)FLOOR(n)FLOOR(n)ROUND (m,n)ROUND (m,n)SIGN(n)SIGN(n)SIN(n)SIN(n)SINH(n)SINH(n)SQRT(n)SQRT(n)TAN(n)TAN(n)TANH(n

3、)TANH(n)TRUNC(m,n)TRUNC(m,n)字符函數(shù)字符函數(shù)1.1.單行函數(shù)單行函數(shù)LOWER (char)LOWER (char)UPPER (char)UPPER (char)INITCAP (char)INITCAP (char)CONCAT (char1, char2)CONCAT (char1, char2)SUBSTR (char, m ,n )SUBSTR (char, m ,n )LENGTH (char)LENGTH (char)INSTR (char1,char2,m,n)INSTR (char1,char2,m,n)LPAD (char1,n,char2)LP

4、AD (char1,n,char2)RPAD (char1,n,char2)RPAD (char1,n,char2)TRIM(leading|trailing|both,trim_char FROM trim_source)TRIM(leading|trailing|both,trim_char FROM trim_source)REPLACE (char1,char2 ,char3)REPLACE (char1,char2 ,char3)返回數(shù)值型的字符函數(shù)返回數(shù)值型的字符函數(shù)ASCIIASCIIINSTRINSTRINSTRBINSTRBLENGTHLENGTHLENGTHBLENGTHB

5、NLSSORT NLSSORT 日期函數(shù)日期函數(shù)1.1.單行函數(shù)單行函數(shù)MONTHS_BETWEEN (d1,d2)MONTHS_BETWEEN (d1,d2)ADD_MONTHS (d,n)ADD_MONTHS (d,n)NEXT_DAY (d,s)NEXT_DAY (d,s)LAST_DAY (d)LAST_DAY (d)ROUND (date,fmt)ROUND (date,fmt)TRUNC (date,fmt)TRUNC (date,fmt)1.1.單行函數(shù)單行函數(shù)Oracle使用內(nèi)部的數(shù)值格式表示日期和時間使用內(nèi)部的數(shù)值格式表示日期和時間默認的日期顯示格式是默認的日期顯示格式是DD

6、-MON-RR (DD-MON-RR (日日- -月月- -年年) )可設定掩碼指定日期型數(shù)據(jù)的格式可設定掩碼指定日期型數(shù)據(jù)的格式1.1.單行函數(shù)單行函數(shù)RRRR格式表示的實際年份，與格式表示的實際年份，與當前年份當前年份( (在本世紀中的位置在本世紀中的位置) ) 給定的兩位年份數(shù)給定的兩位年份數(shù) 有關有關例：例：假設當前年份為假設當前年份為20092009。 EXA_08_P167_RR.SQLSELECTSELECTto_char(to_date(79-01-31,YY-MM-DD),to_char(to_date(79-01-31,YY-MM-DD),YYYYYYYY-MM-DD)-M

7、M-DD)FROM dual;FROM dual;SELECTSELECTto_char(to_date(79-01-31,RR-MM-DD),to_char(to_date(79-01-31,RR-MM-DD),RRRRRRRR-MM-DD)-MM-DD)FROM dual;FROM dual;2079-01-312079-01-311979-01-311979-01-311.1.單行函數(shù)單行函數(shù)OracleOracle數(shù)據(jù)庫中的類型轉(zhuǎn)換有兩種數(shù)據(jù)庫中的類型轉(zhuǎn)換有兩種自動類型轉(zhuǎn)換：字符型和日期型、字符型和數(shù)值型間自動類型轉(zhuǎn)換：字符型和日期型、字符型和數(shù)值型間強制類型轉(zhuǎn)換：使用類型轉(zhuǎn)換函數(shù)強制

8、類型轉(zhuǎn)換：使用類型轉(zhuǎn)換函數(shù)常用的轉(zhuǎn)換函數(shù)常用的轉(zhuǎn)換函數(shù)TO_CHAR (NUMBER | date,format)TO_CHAR (NUMBER | date,format)TO_NUMBER (char , format)TO_NUMBER (char , format)TO_DATE (char, format)TO_DATE (char, format)常用的數(shù)值格式掩碼常用的數(shù)值格式掩碼9-9-數(shù)字字符數(shù)字字符0-0-數(shù)據(jù)有前導數(shù)據(jù)有前導0 0.-.-小數(shù)點小數(shù)點,-,-千位分隔符千位分隔符$-$-美元符號美元符號$ $L-L-顯示本地貨幣符號（人民幣為￥）顯示本地貨幣符號（人民幣為￥

9、）1.1.單行函數(shù)單行函數(shù)其他常用單行函數(shù)其他常用單行函數(shù)空值空值( (NULL)NULL)與其與其他數(shù)據(jù)運算的結(jié)果他數(shù)據(jù)運算的結(jié)果一般均為空值一般均為空值根據(jù)需要，一般根據(jù)需要，一般將空值轉(zhuǎn)換為數(shù)值將空值轉(zhuǎn)換為數(shù)值0 0或或1 1，否則計算表，否則計算表達式將得不到正確達式將得不到正確的值的值2.2.分組函數(shù)分組函數(shù)分組函數(shù)對一組查詢行返回一個結(jié)果值分組函數(shù)對一組查詢行返回一個結(jié)果值除除count(count(* *) )外，分組函數(shù)均忽略空值外，分組函數(shù)均忽略空值如要包括空值，須使用空值轉(zhuǎn)換函數(shù)如要包括空值，須使用空值轉(zhuǎn)換函數(shù)分組函數(shù)不可以用在分組函數(shù)不可以用在WHEREWHERE子句中用

10、以限定查詢的子句中用以限定查詢的結(jié)果，對分組查詢結(jié)果的限定應使用結(jié)果，對分組查詢結(jié)果的限定應使用HAVINGHAVING子句子句在分組函數(shù)中可有下面兩個選項在分組函數(shù)中可有下面兩個選項(1) (1) DISTINCTDISTINCT選項：使分組函數(shù)只考慮列表達式中的不選項：使分組函數(shù)只考慮列表達式中的不同值同值(2) (2) ALLALL選項：使分組函數(shù)考慮全部值，其中包含重復值選項：使分組函數(shù)考慮全部值，其中包含重復值2.2.分組函數(shù)分組函數(shù)主要的分組函數(shù)主要的分組函數(shù)例例25 25 分組函數(shù)綜合應用。分組函數(shù)綜合應用。 EXA_08_25.SQL 3.3.DateTimeDateTime函

11、數(shù)函數(shù)在在DATEDATE類型的基礎上引入三種日期時間類型類型的基礎上引入三種日期時間類型TIMESTAMPTIMESTAMPTIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE (TSTZ)(TSTZ)TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ)(TSLTZ)時區(qū)參數(shù)時區(qū)參數(shù)數(shù)據(jù)庫時區(qū)數(shù)據(jù)庫時區(qū) DBTIMEZONEDBTIMEZONE會話時區(qū)會話時區(qū) SESSIONTIMEZONESESSIONTIMEZONE連接會話參數(shù)連接會話參數(shù)TIME_ZONE

12、TIME_ZONE設置會話時區(qū)與設置會話時區(qū)與UTCUTC時間的時間的時間差時間差TIME_ZONE = + | - hh:mmTIME_ZONE = + | - hh:mm+ +表示比表示比UTCUTC提前，提前，- -表示比表示比UTCUTC滯后滯后北京時間時區(qū)設置北京時間時區(qū)設置ALTER SESSION SET TIME_ZONE = +8:00 ;ALTER SESSION SET TIME_ZONE = +8:00 ;3.3.DateTimeDateTime函數(shù)函數(shù)DateTimeDateTime函數(shù)函數(shù)例例26 26 DateTimeDateTime函數(shù)應用函數(shù)應用 EXA_08

13、_26.SQL 4.4.OLAPOLAP函數(shù)函數(shù)OracleOracle提供了一組用于聯(lián)機分析處理提供了一組用于聯(lián)機分析處理( (OLAP)OLAP)的的SQLSQL函數(shù)函數(shù)例例27 27 對各部門各職位的工資總額進行從高到低排序。對各部門各職位的工資總額進行從高到低排序。EXA_08_27.SQL SELECT department_id, job_id, SUM(salary),SELECT department_id, job_id, SUM(salary), RANK() OVER (ORDER BY SUM(salary) DESC) AS RANK() OVER (ORDER BY

14、 SUM(salary) DESC) AS 次次序序 FROM employeesFROM employees GROUP BY department_id, job_id ; GROUP BY department_id, job_id ;4 4 用于數(shù)據(jù)分析的用于數(shù)據(jù)分析的SQLSQL多表插入可將一條源記錄根據(jù)條件向多個表插入，多表插入可將一條源記錄根據(jù)條件向多個表插入，以滿足不同的數(shù)據(jù)觀察要求以滿足不同的數(shù)據(jù)觀察要求INSERT ALL FIRST INSERT ALL FIRST WHEN WHEN condition1condition1 THEN THEN INTO table1

15、VALUES(column_val, .) INTO table1 VALUES(column_val, .)WHEN WHEN conditionXconditionX THEN THEN INTO tableX VALUES(column_val, .) INTO tableX VALUES(column_val, .)ELSEELSE INTO tableY VALUES(column_val, .) INTO tableY VALUES(column_val, .) SELECT .;SELECT .;ALLALL：對所有對所有WHENWHEN條件進行檢查，只要滿足條件就執(zhí)行對條件進行

16、檢查，只要滿足條件就執(zhí)行對應的插入操作應的插入操作FIRSTFIRST：找到第一個滿足條件的找到第一個滿足條件的WHENWHEN子句，執(zhí)行對應插入子句，執(zhí)行對應插入操作后即結(jié)束當前記錄的處理操作后即結(jié)束當前記錄的處理源數(shù)據(jù)由源數(shù)據(jù)由SELECTSELECT子句提供子句提供1.1.多表插入與旋轉(zhuǎn)插入多表插入與旋轉(zhuǎn)插入多表插入多表插入操作有如下限制操作有如下限制(1) (1) 只能對基表執(zhí)行多表插入，不能對視圖和實體化視圖執(zhí)只能對基表執(zhí)行多表插入，不能對視圖和實體化視圖執(zhí)行多表插入操作行多表插入操作(2) (2) 不能對遠程表執(zhí)行多表插入操作不能對遠程表執(zhí)行多表插入操作例例28 28 對雇員表中編

17、號大于對雇員表中編號大于200200的雇員的記錄，將工資超過的雇員的記錄，將工資超過1000010000的雇員記錄插入到的雇員記錄插入到sal_historysal_history表中，將其經(jīng)理的雇員表中，將其經(jīng)理的雇員編號超過編號超過200200的雇員記錄插入到的雇員記錄插入到mgr_historymgr_history表中。表中。EXA_08_28.SQL 1.1.多表插入與旋轉(zhuǎn)插入多表插入與旋轉(zhuǎn)插入旋轉(zhuǎn)數(shù)據(jù)插入旋轉(zhuǎn)數(shù)據(jù)插入可將一個規(guī)范化程度不高的表中的數(shù)可將一個規(guī)范化程度不高的表中的數(shù)據(jù)轉(zhuǎn)換到規(guī)范化的表中據(jù)轉(zhuǎn)換到規(guī)范化的表中例例29 29 現(xiàn)有雇員銷售記錄表現(xiàn)有雇員銷售記錄表SALES_

18、SOURCE_DATASALES_SOURCE_DATA，包含雇員編號包含雇員編號EMPLOYEE_IDEMPLOYEE_ID，星期編號星期編號WEEK_IDWEEK_ID，周一銷量周一銷量SALES_MONSALES_MON，周二銷量周二銷量SALES_TUESALES_TUE，周三銷量周三銷量SALES_WEDSALES_WED，周四銷量周四銷量SALES_THURSALES_THUR，周五銷量周五銷量SALES_FRISALES_FRI等列。等列。EXA_08_29.SQL 2.2.Top-nTop-n查詢查詢Top-nTop-n用于取某列數(shù)據(jù)中最大或最小的用于取某列數(shù)據(jù)中最大或最小的n

19、 n個值個值例例30 30 按從高到低的次序顯示工資最高的按從高到低的次序顯示工資最高的5 5個人。個人。EXA_08_30.SQL Top-nTop-n分析語法分析語法SELECT column_list , ROWNUMSELECT column_list , ROWNUMFROM ( FROM ( SELECT column_list SELECT column_list FROM table FROM table ORDER BY Top-N_column ORDER BY Top-N_column ASCASC | | DESCDESC ) )WHERE ROWNUM = N ;WH

20、ERE ROWNUM = N ;取最大的前取最大的前N N個值，個值，ORDER BYORDER BY子句需指明子句需指明DESCDESC取最小的前取最小的前N N個值，個值，ORDER BYORDER BY子句需指明子句需指明ASCASC用用ROWNUMROWNUM限制取得的結(jié)果記錄數(shù)限制取得的結(jié)果記錄數(shù)3.3.ROLLUPROLLUP與與CUBECUBE例例31 31 從最細的數(shù)據(jù)顆粒從最細的數(shù)據(jù)顆粒( (同時考慮部門、職位和受聘時間同時考慮部門、職位和受聘時間3 3個因個因素素) )到相對高的數(shù)據(jù)層次到相對高的數(shù)據(jù)層次( (只考慮部門和職位兩個因素只考慮部門和職位兩個因素) )，再到更高

21、的數(shù)據(jù)層次，再到更高的數(shù)據(jù)層次( (僅考慮部門僅考慮部門) )和整個單位和整個單位( (不考不考慮任何因素慮任何因素)4)4個不同數(shù)據(jù)層次來觀察和分析公司的工資個不同數(shù)據(jù)層次來觀察和分析公司的工資情況。情況。EXA_08_31.SQL GROUP BYGROUP BY之后的列之后的列( (各維各維) )是有層次的，最右邊的為最低層，是有層次的，最右邊的為最低層，最左邊的為最高層最左邊的為最高層ROLLUPROLLUP對對GROUP BYGROUP BY定義的維分層計算各個層次匯總值定義的維分層計算各個層次匯總值CUBECUBE的結(jié)果包括的結(jié)果包括ROLLUPROLLUP產(chǎn)生的行和交叉表行產(chǎn)生的

22、行和交叉表行( (維組合的聚集維組合的聚集值值) )數(shù)據(jù)聚集操作數(shù)據(jù)聚集操作( (ROLLUP)ROLLUP)ROLLUPROLLUP從右至左計算從右至左計算GROUP BYGROUP BY定義的維分組的小計并累計該定義的維分組的小計并累計該值至最終的合計。值至最終的合計。若給定若給定n n維分組，維分組，ROLLUPROLLUP將產(chǎn)生將產(chǎn)生n+1n+1層匯總數(shù)據(jù)層匯總數(shù)據(jù)3.3.ROLLUPROLLUP與與CUBECUBE例例32 32 CUBECUBE應用。應用。 EXA_08_32.SQL CUBECUBE操作提供了從數(shù)據(jù)的不同側(cè)面了解其內(nèi)涵的方操作提供了從數(shù)據(jù)的不同側(cè)面了解其內(nèi)涵的方法

23、，把法，把CUBECUBE稱作數(shù)據(jù)立方體操作稱作數(shù)據(jù)立方體操作CUBECUBE將將GROUP BYGROUP BY子句中的各維進行組合子句中的各維進行組合( (兩兩組合兩兩組合、三個組合，以此類推、三個組合，以此類推) )，形成交叉表，并計算各維組，形成交叉表，并計算各維組合的聚集值合的聚集值如果如果GROUP BYGROUP BY子句有子句有n n個列名個列名( (也稱也稱n n維維) )，則，則CUBECUBE操操作將產(chǎn)生作將產(chǎn)生2 2n n個分組組合個分組組合3.3.ROLLUPROLLUP與與CUBECUBE例例33 33 GROUPINGGROUPING函數(shù)的應用。函數(shù)的應用。EXA_08_33.SQL GROUPINGGROUPING函數(shù)函數(shù)用于標識某個列是否參與了聚集值