Oracle函數(shù)及其查詢

1、Oracle函數(shù)與查詢單行函數(shù)數(shù)可以出出現(xiàn)在SELECT語句的SELECT子句WHERE子句ORDERBY子句STARTWITH子句CONNECT BY子句1.單行行函數(shù)單行函數(shù)數(shù)特點(diǎn)(1)基基于單單行返回回結(jié)果(2)允允許參參數(shù)是其其他單行行函數(shù)產(chǎn)產(chǎn)生的結(jié)結(jié)果(3)對對查詢詢返回的的各行分分別進(jìn)行行計(jì)算(4)可可以用用常量、變量、列名以以及表達(dá)達(dá)式作為為參數(shù)數(shù)值函數(shù)數(shù)接受數(shù)值值型輸入入數(shù)據(jù)，并返回回?cái)?shù)值型型的結(jié)果果1.單行行函數(shù)ABS(n)CEIL(n)COS(n)COSH(n)EXP(n)LN(n)LOG(m,n)MOD(m,n)POWER(m,n)FLOOR(n)ROUND(m,n)SI

2、GN(n)SIN(n)SINH(n)SQRT(n)TAN(n)TANH(n)TRUNC(m,n)字符函數(shù)數(shù)1.單行行函數(shù)LOWER(char)UPPER(char)INITCAP (char)CONCAT(char1,char2)SUBSTR(char, m,n)LENGTH(char)INSTR(char1,char2,m,n)LPAD (char1,n,char2)RPAD (char1,n,char2)TRIM(leading|trailing|both,trim_charFROMtrim_source)REPLACE (char1,char2,char3)返回?cái)?shù)值值型的字字符函數(shù)數(shù)AS

3、CIIINSTRINSTRBLENGTHLENGTHBNLSSORT日期函數(shù)數(shù)1.單行行函數(shù)MONTHS_BETWEEN(d1,d2)ADD_MONTHS (d,n)NEXT_DAY(d,s)LAST_DAY(d)ROUND(date,fmt)TRUNC(date,fmt)1.單行行函數(shù)Oracle使用內(nèi)部的數(shù)值格式表示日期和時間默認(rèn)的日日期顯示示格式是是DD-MON-RR(日-月-年)可設(shè)定掩掩碼指定定日期型型數(shù)據(jù)的的格式1.單行行函數(shù)RR格式表示示的實(shí)際際年份，與當(dāng)前年份份(在本本世紀(jì)中中的位置置)給定的兩兩位年份份數(shù)有有關(guān)例：假設(shè)當(dāng)前前年份為為2009。EXA_08_P167_RR.SQ

4、LSELECTto_char(to_date(79-01-31,YY-MM-DD),YYYY-MM-DD)FROM dual;SELECTto_char(to_date(79-01-31,RR-MM-DD),RRRR-MM-DD)FROM dual;2079-01-311979-01-311.單行行函數(shù)Oracle數(shù)據(jù)庫中中的類型型轉(zhuǎn)換有有兩種自動類型型轉(zhuǎn)換：字符型型和日期期型、字字符型和和數(shù)值型型間強(qiáng)制類型型轉(zhuǎn)換：使用類類型轉(zhuǎn)換換函數(shù)常用的轉(zhuǎn)轉(zhuǎn)換函數(shù)數(shù)TO_CHAR (NUMBER|date,format)TO_NUMBER(char,format)TO_DATE (char,format)

5、常用的數(shù)數(shù)值格式式掩碼9-數(shù)字字字符0-數(shù)據(jù)據(jù)有前導(dǎo)導(dǎo)0.-小數(shù)數(shù)點(diǎn),-千位位分隔符符$-美元元符號$L-顯示本地地貨幣符符號（人人民幣為為￥）1.單行行函數(shù)其他常用用單行函函數(shù)空值(NULL)與其他數(shù)數(shù)據(jù)運(yùn)算算的結(jié)果果一般均均為空值值根據(jù)需要要，一般般將空值值轉(zhuǎn)換為為數(shù)值0或1，否則計(jì)計(jì)算表達(dá)達(dá)式將得得不到正正確的值值2.分組組函數(shù)分組函數(shù)數(shù)對一組組查詢行行返回一一個結(jié)果果值除count(*)外，分組組函數(shù)均均忽略空空值如要包括括空值，須使用用空值轉(zhuǎn)轉(zhuǎn)換函數(shù)數(shù)分組函數(shù)數(shù)不可以以用在WHERE子句中用用以限定定查詢的的結(jié)果，對分組組查詢結(jié)結(jié)果的限限定應(yīng)使使用HAVING子句在分組函函數(shù)中可可有

6、下面面兩個選選項(xiàng)(1)DISTINCT選項(xiàng)：使使分組函函數(shù)只考考慮列表表達(dá)式中中的不同同值(2)ALL選項(xiàng)：使使分組函函數(shù)考慮慮全部值值，其中中包含重重復(fù)值2.分組組函數(shù)主要的分分組函數(shù)數(shù)例25分分組組函數(shù)綜綜合應(yīng)用用。EXA_08_25.SQL3.DateTime函數(shù)在DATE類型的基基礎(chǔ)上引引入三種種日期時時間類型型TIMESTAMPTIMESTAMPWITHTIME ZONE(TSTZ)TIMESTAMPWITHLOCALTIME ZONE(TSLTZ)時區(qū)參數(shù)數(shù)數(shù)據(jù)庫時時區(qū)DBTIMEZONE會話時區(qū)區(qū)SESSIONTIMEZONE連接會話話參數(shù)TIME_ZONE設(shè)置會話話時區(qū)與與UT

7、C時間的時時間差TIME_ZONE=+|-hh:mm+表示比UTC提前，-表示比比UTC滯后北京時間間時區(qū)設(shè)設(shè)置ALTERSESSION SETTIME_ZONE=+8:00 ;3.DateTime函數(shù)DateTime函數(shù)例26DateTime函數(shù)應(yīng)用用EXA_08_26.SQL4.OLAP函數(shù)Oracle提供了一一組用于于聯(lián)機(jī)分分析處理理(OLAP)的SQL函數(shù)例27對對各各部門各各職位的的工資總總額進(jìn)行行從高到到低排序序。EXA_08_27.SQLSELECTdepartment_id,job_id,SUM(salary),RANK()OVER(ORDERBYSUM(salary)DESC

8、)AS次序FROMemployeesGROUPBYdepartment_id,job_id;4用于數(shù)據(jù)據(jù)分析的的SQL多表插入入可將一一條源記記錄根據(jù)據(jù)條件向向多個表表插入，以滿足足不同的的數(shù)據(jù)觀觀察要求求INSERTALLFIRSTWHENcondition1THENINTO table1VALUES(column_val,.)WHENconditionXTHENINTO tableXVALUES(column_val,.)ELSEINTO tableYVALUES(column_val,.)SELECT.;ALL：對所有WHEN條件進(jìn)行行檢查，只要滿滿足條件件就執(zhí)行行對應(yīng)的的插入操操作FI

9、RST：找到第一一個滿足足條件的的WHEN子句，執(zhí)執(zhí)行對應(yīng)應(yīng)插入操操作后即即結(jié)束當(dāng)當(dāng)前記錄錄的處理理源數(shù)據(jù)由由SELECT子句提供供1.多表表插入與與旋轉(zhuǎn)插插入多表插入入操作有如如下限制制(1)只只能對對基表執(zhí)執(zhí)行多表表插入，不能對對視圖和和實(shí)體化化視圖執(zhí)執(zhí)行多表表插入操操作(2)不不能對對遠(yuǎn)程表表執(zhí)行多多表插入入操作例28對對雇員員表中編編號大于于200的雇員員的記錄錄，將工工資超過過10000的的雇員記記錄插入入到sal_history表中，將將其經(jīng)理理的雇員員編號超超過200的雇雇員記錄錄插入到到mgr_history表中。EXA_08_28.SQL1.多表表插入與與旋轉(zhuǎn)插插入旋轉(zhuǎn)數(shù)據(jù)據(jù)

10、插入可將一個個規(guī)范化化程度不不高的表表中的數(shù)數(shù)據(jù)轉(zhuǎn)換換到規(guī)范范化的表表中例29現(xiàn)現(xiàn)有雇雇員銷售售記錄表表SALES_SOURCE_DATA，包含雇員員編號EMPLOYEE_ID，星期編號號WEEK_ID，周一銷量量SALES_MON，周二銷量量SALES_TUE，周三銷量量SALES_WED，周四銷量量SALES_THUR，周五銷量量SALES_FRI等列。EXA_08_29.SQL2.Top-n查詢Top-n用于取某某列數(shù)據(jù)據(jù)中最大大或最小小的n個值例30按按從從高到低低的次序序顯示工工資最高高的5個個人。EXA_08_30.SQLTop-n分析語法法SELECTcolumn_list,RO

11、WNUMFROM (SELECTcolumn_list FROM tableORDERBYTop-N_columnASC|DESC)WHEREROWNUM=N;取最大的的前N個值，ORDERBY子句需指指明DESC取最小的的前N個值，ORDERBY子句需指指明ASC用ROWNUM限制取得得的結(jié)果果記錄數(shù)數(shù)3.ROLLUP與CUBE例31從從最細(xì)細(xì)的數(shù)據(jù)據(jù)顆粒(同時考考慮部門門、職位位和受聘聘時間3個因素素)到相相對高的的數(shù)據(jù)層層次(只只考慮部部門和職職位兩個個因素)，再到到更高的的數(shù)據(jù)層層次(僅僅考慮部部門)和和整個單單位(不不考慮任任何因素素)4個個不同數(shù)數(shù)據(jù)層次次來觀察察和分析析公司的的工

12、資情情況。EXA_08_31.SQLGROUPBY之后的列列(各維維)是有有層次的的，最右右邊的為為最低層層，最左左邊的為為最高層層ROLLUP對GROUPBY定義的維維分層計(jì)計(jì)算各個個層次匯匯總值CUBE的結(jié)果包包括ROLLUP產(chǎn)生的行行和交叉叉表行(維組合合的聚集集值)數(shù)據(jù)聚集集操作(ROLLUP)ROLLUP從右至左左計(jì)算GROUPBY定義的維維分組的的小計(jì)并并累計(jì)該該值至最最終的合合計(jì)。若給定n維分組，ROLLUP將產(chǎn)生n+1層匯總數(shù)數(shù)據(jù)3.ROLLUP與CUBE例32CUBE應(yīng)用。EXA_08_32.SQLCUBE操作提供供了從數(shù)數(shù)據(jù)的不不同側(cè)面面了解其其內(nèi)涵的的方法，把CUBE稱作

13、數(shù)據(jù)據(jù)立方體體操作CUBE將GROUPBY子句中的的各維進(jìn)進(jìn)行組合合(兩兩兩組合、三個組組合，以以此類推推)，形形成交叉叉表，并并計(jì)算各各維組合合的聚集集值如果GROUPBY子句有n個列名(也稱n維)，則則CUBE操作將產(chǎn)產(chǎn)生2n個分組組組合3.ROLLUP與CUBE例33GROUPING函數(shù)的應(yīng)應(yīng)用。EXA_08_33.SQLGROUPING函數(shù)用于標(biāo)識識某個列列是否參參與了聚聚集值的的計(jì)算返回值0，表示示對應(yīng)的的參數(shù)列列參與了了聚集值值的計(jì)算算返回值1，表示示對應(yīng)的的參數(shù)列列未參與與聚集值值的計(jì)算算例343個個維分組組，明確確地告知知系統(tǒng)只只從這3個角度度觀察數(shù)數(shù)據(jù)EXA_08_34.SQL對分組的的集合操操作(GROUPINGSETS子句)在一個SELECT語句中定定義多個個維分組組各維分組組用()括起起來，用用逗號分分隔各維維分組分別計(jì)算算每個維維分組的的值，然然后執(zhí)行行UNIONALL操作3.ROLLUP與CUBE例35使使用用復(fù)合列列，跳過過某些數(shù)數(shù)據(jù)