OraclePL-SQL總結(jié)

1、目錄一：SQL基礎(chǔ)2二：?jiǎn)涡泻瘮?shù)31.字符函數(shù)3（1）大小寫處理3（2）字符處理32.數(shù)學(xué)函數(shù)5（1）三角函數(shù)5（2）冪、方根及對(duì)數(shù)運(yùn)算函數(shù)5（3）數(shù)值處理函數(shù)53.日期函數(shù)64.空值處理函數(shù)65.轉(zhuǎn)換函數(shù)76.環(huán)境函數(shù)8（1）USER函數(shù)/UID函數(shù)8（2）USERENV函數(shù)8（3）SYS_CONTEXT函數(shù)87.其它單行函數(shù)9（1）DECODE函數(shù)9（2）GREATEST函數(shù)9（3）LEAST函數(shù)9（4）正則表達(dá)式函數(shù)10三：聚合函數(shù)與分析函數(shù)101.示例表結(jié)構(gòu)102.GROUP BY、ROLLUP、CUBE113.聚合函數(shù)134.分析函數(shù)155.自定義聚合函數(shù)17四：集合操作17五：層級(jí)

2、查詢181.示例表結(jié)構(gòu)182.基本查詢19（1）一般格式19（2）偽列l(wèi)evel19（3）相關(guān)函數(shù)19（4）組成成分203.應(yīng)用例子21（1）產(chǎn)生1-100以內(nèi)連續(xù)數(shù)字21（2）查找不連續(xù)數(shù)字21（3）日期和月末日期22（4）利用層級(jí)查詢計(jì)算22六：WITH語(yǔ)句221.單個(gè)子查詢222.多個(gè)子查詢223.子查詢中使用已定義的子查詢23七：常用SQL語(yǔ)句231.管理232.開發(fā)243.跑數(shù)254.其它26八：數(shù)據(jù)庫(kù)對(duì)象271.表272.約束273.索引274.視圖275.序列276.數(shù)據(jù)庫(kù)鏈接277.同義詞278.函數(shù)279.過程2710.觸發(fā)器2711.包27九：數(shù)據(jù)字典27十：SQL優(yōu)化27

3、一：SQL基礎(chǔ)In：apcode in(select apcode from apcode_inf)或(apcode,lg_code) in (select apcode,lg_code from apcode_lg_rln)Any：any可以用來(lái)將一個(gè)值與一個(gè)列表中的任何值進(jìn)行比較，在查詢中Any操作符之前，必須使用一個(gè)=、<>、<、>、<=、>=操作符All：All可以用來(lái)將一個(gè)值與一個(gè)列表中的所有值進(jìn)行比較ExistsNot Exists：當(dāng)一個(gè)值列表包含一個(gè)空值時(shí)，not exists返回trueNot In：當(dāng)一個(gè)值列表包含一個(gè)空值時(shí)，not in

4、返回false二：?jiǎn)涡泻瘮?shù)1. 字符函數(shù)（1）大小寫處理 Initcap：每個(gè)單詞首字母大寫，其它小寫 Upper：轉(zhuǎn)變?yōu)榇髮?Lower：轉(zhuǎn)變?yōu)樾懀?）字符處理 Concat：字符串連接，一般用連接號(hào)“|”代替 Ascii：取字符ASCII編碼 Vsize：字節(jié)數(shù) Chr：將ASCII編碼轉(zhuǎn)換為對(duì)應(yīng)字符 Trim/ltrim/rtrim：去除指定字符，默認(rèn)去除空格 Lpad/rpad：填充指定字符，默認(rèn)填充空格 Length：長(zhǎng)度 Substr：截取用法格式：SUBSTR(string, start ,length)功能說(shuō)明：從字符串string的start位置開始向后截取length長(zhǎng)度

5、的子串。 Instr：查找用法格式：INSTR(string, search_string ,n ,m)功能說(shuō)明：從字符串string的第n個(gè)字符開始查找search_string第m次出現(xiàn)的位置，如果按條件沒有找到字符串，則返回0。如果n<0，則從右往左數(shù)第|n|個(gè)位置開始從右往左查找，返回從左往右的索引。注意：返回的位置是以原字符串首字符所在位置為起始點(diǎn)的，首字符位置為1。 Replace：替換用法格式：REPLACE(string, search_string ,replace_string)功能說(shuō)明：將字符串string中的所有search_string都替換為replace_s

6、tring，如果省略replace_string，則將字符串string中的所有search_string都去掉。 Translate：轉(zhuǎn)換用法格式：TRANSLATE(string, search_set, replace_set)功能說(shuō)明：將string中出現(xiàn)在search_set中的字符轉(zhuǎn)換為replace_set相應(yīng)位置的字符。說(shuō)明：（1） 關(guān)于Lpad(c1,n,c2) /Rpad(c1,n,c2)，返回指定長(zhǎng)度=n的字符串，需要注意的有幾點(diǎn)：l 如果n<c1.length則從左向右截取指定長(zhǎng)度返回l 如果n>c1.length and c2 is null，以空格從左/

7、右補(bǔ)充字符長(zhǎng)度至n并返回l 如果n>c1.length and c2 is not null，以指定字符c2從左/右補(bǔ)充c1長(zhǎng)度至n并返回，如果需要補(bǔ)充的長(zhǎng)度大于c2.length，則循環(huán)補(bǔ)充c2中字符一直到c1.length=nl 如果n<=0，返回null例如：SELECT LPAD('Hello World!', -1) A, -n<=0，返回null LPAD('Hello World!', 5) B, -n<c1.length，從左向右截取n位 LPAD('Hello World!', 15) C, -n>

8、c1.length,從左補(bǔ)充空格直到長(zhǎng)度為n LPAD('Hello World!', 17, '%$') D, -n>c1.length且c2不為空，則從左循環(huán)補(bǔ)充c2直到長(zhǎng)度為n RPAD('Hello World!', 5) E, -n<c1.length，從左向右截取n位 RPAD('Hello World!', 15) F, -n>c1.length,從右補(bǔ)充空格直到長(zhǎng)度為n RPAD('Hello World!', 15, '*') G -n>c1.length且

9、c2不為空，則從右循環(huán)補(bǔ)充c2直到長(zhǎng)度為nFROM DUAL返回結(jié)果為：ABHelloC Hello World!D%$%$%Hello World!EHelloFHello World! GHello World!*注：截取時(shí)不管是lpad還是rpad都是從左向右截取。（2） 關(guān)于Trim/Ltrim/Rtrim的特殊用法： TRIM(LEADING|TRAILING|BOTH c2 FROM c1)，語(yǔ)法圖如下A 如果沒有指定任何參數(shù)，去掉c1頭尾空格B 指定了c2參數(shù)，則去掉c1頭尾c2C 如果指定了leading參數(shù)，則去掉c1頭部c2D 如果指定了trailing參數(shù)，則去掉c1尾部

10、c2E 如果指定了both參數(shù)，則去掉c1頭尾c2注：c2的長(zhǎng)度一定要為1。 LTRIM(c1,c2)/ RTRIM(c1,c2)是從字符串c1左/右側(cè)截取掉與指定字符串c2相同的字符并返回。如果c2為空則默認(rèn)截取空格。這里c2的長(zhǎng)度可以不為1。例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') A, LTRIM('WWhhhhhaT is tHis w W', 'Wh') B, RTRIM('WWhhhhhaT is tHis w W', 'W w') C FR

11、OM DUAL返回結(jié)果：ABChaT is tHis w aT is tHis w WWWhhhhhaT is tHis（3） SUBSTR(c1,n1,n2) 截取指定長(zhǎng)度的字符串。n1=開始長(zhǎng)度； n2=截取的字符串長(zhǎng)度，如果為空，默認(rèn)截取到字符串結(jié)尾；l 如果n1=0 then n1=1。l 如果n1>0，則從左向右確認(rèn)起始位置截取。l 如果n1<0，則從右向左數(shù)確認(rèn)起始位置。l 如果n1>c1.length或n2<1則返回空。2. 數(shù)學(xué)函數(shù)（1）三角函數(shù) sin、asin、sinh：正弦、反正弦、雙曲正弦 cos、acos、cosh：余弦、反余弦、雙曲余弦 ta

12、n、atan、tanh、atan2：正切、反正切、雙曲正切（2）冪、方根及對(duì)數(shù)運(yùn)算函數(shù) power(m, n)：m的n次冪，即 sqrt(n)：n的平方根，即 exp(n)：自然對(duì)數(shù)e的n次方根，即 ln(n)：n的自然對(duì)數(shù) log(m, n)：以m為底n的對(duì)數(shù)，即（3）數(shù)值處理函數(shù) abs(n)：絕對(duì)值 sign(n)：符號(hào)函數(shù) ceil(n)：不小于n的最小整數(shù) floor(n)：不大于n的最大整數(shù) round(n, m)：按m精度對(duì)n進(jìn)行四舍五入 trunc(n, m)：按m精度對(duì)n進(jìn)行截取 mod(m, n)：模 bitand(m,n)：位與運(yùn)算 bin_to_num(n1,n2,.n

13、)：二進(jìn)制轉(zhuǎn)向十進(jìn)制說(shuō)明：（1） mod函數(shù)中，如果第二個(gè)參數(shù)為0，則直接返回第一個(gè)參數(shù)。（2） 關(guān)于round和trunc參考例子：SELECT ROUND(23.56) A, -m默認(rèn)為0 ROUND(23.56, 1) B, -m>0從小數(shù)點(diǎn)往右數(shù)m位 ROUND(23.56, -1) C, -m<0從小數(shù)點(diǎn)往左數(shù)m位 TRUNC(23.56) D, TRUNC(23.56, 1) E, TRUNC(23.56, -1) F FROM DUAL返回結(jié)果為：ABCDEF2423.6202323.5203. 日期函數(shù) Sysdate：當(dāng)前時(shí)間。 Add_months：加減指定月份

14、。 Months_between：兩個(gè)日期之間的月份數(shù)。 Last_day：月末日期。 Round(date,format_string)：按日期格式進(jìn)行舍入。 Trunc(date,format_string)：按日期格式進(jìn)行截取。 Extract(key from date) key=(year,month,day,hour,minute,second) 從指定時(shí)間提到指定日期列。日期格式含義YYYY四位數(shù)年份 （如：2009）YY二位數(shù)年份（如 09） Q季度（1-4） MM 月份（01-12） WW 年的星期數(shù)（1-53），其中第一星期為年的第一天至第七天 W月的星期數(shù)（1-5），其中

15、第一星期為月的第一天至第七天 DDD年的日（1-366） DD月的日（1-31） D周的日（1-7），其中周日為1，周六為7 HH24 24小時(shí)制（0-23） MI分鐘（0-59）SS秒（0-59）SSSSS自午夜之后的秒（0-86399）4. 空值處理函數(shù) Nvl(expr1,expr2)：如果expr1為null，返回expr2，否則返回expr1。 Nvl2(expr1,expr2,expr3)：如果expr1不為null，返回expr2，否則返回expr3。Nullif(expr1,expr2)：如果expr1=expr2返回null，否則返回expr1。 Coalesce(expr1

16、,expr2,exprn)：返回表達(dá)式中第一個(gè)非空表達(dá)式的值。 Nanvl(x,value)：如果x為NaN（非數(shù)字），就返回value，否則返回x，10g開始支持。5. 轉(zhuǎn)換函數(shù) TO_CHAR(x,format)將x轉(zhuǎn)換為字符串參數(shù)例子說(shuō)明9999返回指定位置出的數(shù)字，如果該值為負(fù)數(shù)，前面加負(fù)號(hào)00999返回一個(gè)數(shù)字，前面補(bǔ)09990返回一個(gè)數(shù)字，后面補(bǔ)0.999.99在指定位置處返回一個(gè)小數(shù)點(diǎn),9,999在指定位置處返回一個(gè)逗號(hào)$999在數(shù)字開頭返回一個(gè)美元符號(hào)BB9.99如果一個(gè)定點(diǎn)數(shù)的整數(shù)部分為0，則整數(shù)部分返回空格CC999在指定位置處返回ISO貨幣符號(hào)D9D99在指定位置處返回小

17、數(shù)點(diǎn)符號(hào)EEEE9.99EEEE科學(xué)計(jì)數(shù)法FMFM90.9刪除數(shù)字頭尾空格G9G999在指定位置處返回組分隔符號(hào)LL999在指定位置處返回本地貨幣符號(hào)MI999MI負(fù)數(shù)在后面加負(fù)號(hào)，正數(shù)在后面加空格PR999PR負(fù)數(shù)用<>包含，正數(shù)用空格包含RNRN返回大寫羅馬數(shù)字（1-3999）rnRn返回小寫羅馬數(shù)字（1-3999）SS999負(fù)數(shù)前加負(fù)號(hào)，正數(shù)前加正號(hào)999S負(fù)數(shù)后加負(fù)號(hào)，正數(shù)后加正號(hào)TMTM返回由最少個(gè)字符組成的數(shù)字UU999在指定位置處返回貨幣符號(hào)V99V99返回該數(shù)乘的結(jié)果，x是V之后9的個(gè)數(shù)XXXXX返回十六進(jìn)制，且取整 TO_NUMBER(x,format) 將x轉(zhuǎn)換

18、為數(shù)字 TO_DATE(string, format_string)（將字符型數(shù)據(jù)按照時(shí)間格式format_string轉(zhuǎn)換為相應(yīng)的日期型數(shù)據(jù)） CAST(x as newtype) 轉(zhuǎn)換x的類型 CONVERT(x,source_chat_set,dest_char_set)轉(zhuǎn)換x的字符6. 環(huán)境函數(shù)（1）USER函數(shù)/UID函數(shù)USER返回當(dāng)前用戶名，UID返回唯一標(biāo)識(shí)當(dāng)前數(shù)據(jù)庫(kù)用戶的整數(shù)（2）USERENV函數(shù) USERENV('ISDBA')（查看當(dāng)前用戶是否是DBA） USERENV('LANGUAGE')（查看環(huán)境所用的字符集） USERENV(&#

19、39;TERMINAL')（查看當(dāng)前用戶終端的標(biāo)識(shí)）（3）SYS_CONTEXT函數(shù)說(shuō)明：SYS_CONTEXT函數(shù)是在ORACLE 8i中新增的，在功能上比USERENV函數(shù)更加全面。用法格式：SYS_CONTEXT('USERENV', '<parameter>')根據(jù)參數(shù)parameter來(lái)返回相應(yīng)的信息 登錄驗(yàn)證類型select SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') from dual; 當(dāng)前登錄數(shù)據(jù)庫(kù)的用戶名select SYS_CONTEXT(&

20、#39;USERENV', 'CURRENT_USER') from dual; 當(dāng)前登錄客戶端的操作系統(tǒng)用戶名select SYS_CONTEXT('USERENV', 'OS_USER') from dual; 當(dāng)前所在客戶機(jī)名（包括所在工作組名）select SYS_CONTEXT('USERENV', 'HOST') from dual; 當(dāng)前所在客戶機(jī)/終端名select SYS_CONTEXT('USERENV', 'TERMINAL') from dual; 當(dāng)

21、前所在客戶端的IPselect SYS_CONTEXT('USERENV', 'IP_ADDRESS') from dual; 當(dāng)前是否為DBA帳戶select SYS_CONTEXT('USERENV', 'ISDBA') from dual; 當(dāng)前的字符集select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual; 當(dāng)前所使用的網(wǎng)絡(luò)協(xié)議select SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL

22、') from dual; 當(dāng)前所使用的日歷select SYS_CONTEXT('USERENV', 'NLS_CALENDAR') from dual;11 當(dāng)前的貨幣單位select SYS_CONTEXT('USERENV', 'NLS_CURRENCY') from dual;12 當(dāng)前的日期格式select SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') from dual;13 當(dāng)前的日期語(yǔ)言select SYS_CONTEXT('U

23、SERENV', 'NLS_DATE_LANGUAGE') from dual;14 當(dāng)前所在的地域select SYS_CONTEXT('USERENV', 'NLS_TERRITORY') from dual;15 當(dāng)前代理用戶select SYS_CONTEXT('USERENV', 'PROXY_USER') from dual;7. 其它單行函數(shù)（1）DECODE函數(shù)格式：DECODE(col|expression, search1, result1 , search2, result2 , .,

24、 . , default )說(shuō)明：第一個(gè)參數(shù)可是是表的列，也可以是一個(gè)表達(dá)式。如果第一參數(shù)的值與search1相等，則函數(shù)返回result1的值；如果第一參數(shù)的值與search2相等，則函數(shù)返回result2的值；以此類推。如果第一參數(shù)的值與所有的search都不相等，則函數(shù)返回default的值。附：case when 語(yǔ)句：Case expression when . then when . then else endcase when expression then when expression thenelseend（2）GREATEST函數(shù)返回序列中的最大值（3）LEAST函數(shù)返回

25、序列中的最小值（4）正則表達(dá)式函數(shù) Regexp_like() Regexp_instr() Regexp_replace() Regexp_substr()三：聚合函數(shù)與分析函數(shù)1. 示例表結(jié)構(gòu)（1） 核算碼科目關(guān)系表APCODE_LG_RLN名稱類型可為空注釋APCODECHAR(4)N核算碼LG_CODECHAR(4)N科目（2） 財(cái)務(wù)分析計(jì)劃表FA_PLN名稱類型可為空注釋CCY_CODECHAR(3)N幣別代碼ORG_IDCHAR(5)N機(jī)構(gòu)號(hào)DTCHAR(8)N日期RTNPFTNUMBER(20,4)Y凈利潤(rùn)（3） 測(cè)試表TEST稱類型可為空默認(rèn)存儲(chǔ)注釋ACHAR(2)Y 

26、;  BCHAR(2)Y   CCHAR(2)Y   DNUMBERY   其中數(shù)據(jù)有：ABCDA1B1C11A1B1C22A1B2C13A1B2C24A2B1C15A2B1C26A2B2C17A2B2C282. GROUP BY、ROLLUP、CUBE（1） GROUP BY可以將記錄分成列值相同的組，從而聚合函數(shù)能夠根據(jù)分組計(jì)算，每組返回一個(gè)聚合值。A、 如group by A，則返回A1、A2兩條記錄，group by A,B，則返回(A1,B1)、(A1,B2)、(A2,B1)

27、、(A2,B2)四條記錄。B、 Group by后面的字段應(yīng)與select中沒有使用聚集函數(shù)的字段保持一致。C、 Group by語(yǔ)句后面可以使用having子句對(duì)聚集的結(jié)果進(jìn)行篩選，但效率不如where語(yǔ)句。D、 這里A、B的順序?qū)τ诮Y(jié)果集沒有影響，但是對(duì)于執(zhí)行效率會(huì)有影響，需注意。（2） ROLLUP是GROUP BY的一種擴(kuò)展，每組除了返回一個(gè)聚合值之外，還返回按rollup字段返回相應(yīng)的合計(jì)值。A、 如rollup(A,B,C)，則首先對(duì)(A,B,C)進(jìn)行g(shù)roup by，然后對(duì)(A,B)進(jìn)行g(shù)roup by，然后對(duì)A group by，最后對(duì)全表進(jìn)行g(shù)roup by操作。SELECT

28、 A, B, C, SUM(D) D FROM TEST T GROUP BY ROLLUP(A, B, C)返回結(jié)果如下表左四列。ROLLUP(A, B, C)ROLLUP(A, B), CABCDABCDA1B1C11A1B1C11A1B1C22A1B2C13A1B13A1C14A1B2C13A2B1C15A1B2C24A2B2C17A1B27A2C112A110C116A2B1C15A1B1C22A2B1C26A1B2C24A2B111A1C26A2B2C17A2B1C26A2B2C28A2B2C28A2B215A2C214A226C22036B、 又如rollup(A,B),C，則首先

29、對(duì)(A,B,C)進(jìn)行g(shù)roup by，然后對(duì)(A,C)進(jìn)行g(shù)roup by，然后對(duì)C group by。SELECT A, B, C, SUM(D) D FROM TEST T GROUP BY ROLLUP(A, B), C返回結(jié)果如上表右四列。C、 又如 rollup(A,B)，將(A,B)作為一個(gè)字段group by。D、 總結(jié)rollup(A,B),C,D，第一group by A,B,C,D，第二group by A,C,D，第三group by C,D。因此rollup中字段順序和結(jié)果是相關(guān)的。（3） CUBE是對(duì)rollup的進(jìn)一步擴(kuò)展，如CUBE(A,B,C)，則首先會(huì)對(duì)(A,

30、B,C)進(jìn)行GROUP BY，然后依次是(A,B)，(A,C)，(A)，(B,C)，(B)，(C)，最后對(duì)全表進(jìn)行GROUP BY操作。（4） grouping(col)：rollup、cube子句的輔助函數(shù)，并且必須配合rollup或cube才能使用，用來(lái)確定當(dāng)前記錄行是否由rollup或cube生產(chǎn)，如果是返回1，否則返回0。（5） grouping_id(col1,col2)：返回grouping(col1),grouping(col2)等組成的二進(jìn)制數(shù)字轉(zhuǎn)化為10進(jìn)制后的值。它的一個(gè)應(yīng)用就是在having子句中篩選出合計(jì)數(shù)。SELECT A, B, GROUPING(A), GROUP

31、ING(B), GROUPING_ID(A, B), SUM(D) D FROM TEST T GROUP BY CUBE(A, B)返回結(jié)果：ABGROUPING(A)GROUPING(B)GROUPING_ID(A,B)D11336B110214B210222A101110A1B10003A1B20007A201126A2B100011A2B200015（6） group_id()：group字句的輔助函數(shù)，用來(lái)區(qū)分group by生成的記錄是否為重復(fù)記錄，是返回1，否則返回0。SELECT DT, CCY_CODE, SUM(RTNPFT) SUM_VALUE, GROUP_ID() I

32、S_REPEAT, -是否重復(fù)記錄 GROUPING(DT) IS_DT_TOTAL, -是否為dt的合計(jì)數(shù) GROUPING(CCY_CODE) IS_CCY_CODE_TOTAL -是否為ccy_code的合計(jì)數(shù) FROM FA_PLN GROUP BY DT, ROLLUP(DT, CCY_CODE)返回結(jié)果：DTCCY_CODESUM_VALUEIS_REPEATIS_DT_TOTALIS_CCY_CODE_TOTAL000647131395000647131395001647131395101（7） grouping sets語(yǔ)句：只返回小記記錄。SELECT A, B, SUM(D

33、) D FROM TEST T GROUP BY GROUPING SETS(A, B)返回結(jié)果：ABDA110A226B114B2223. 聚合函數(shù)聚合函數(shù)可被用于select,order by以及having子句中，其運(yùn)算可以基于group by的結(jié)果，也可以直接對(duì)所有記錄進(jìn)行運(yùn)算。除count和grouping之外，其它統(tǒng)計(jì)均會(huì)忽略值為null的列。（1） avg(distinct|allcol)：求取記錄集中的平均值。（2） count(distinct|allcol) ：返回查詢涉及到的記錄行數(shù)。SELECT COUNT(1), -返回記錄總行數(shù) COUNT(100), -返回記錄總

34、行數(shù)，這里的100也只是做為一列，并不影響行數(shù) COUNT(*), -返回記錄總行數(shù) COUNT(APCODE), -返回apcode的行數(shù) COUNT(DISTINCT APCODE) -返回不重復(fù)的apcode條數(shù) FROM APCODE_LG_RLN返回結(jié)果為：COUNT(1)COUNT(100)COUNT(*)COUNT(APCODE)COUNT(DISTINCTAPCODE)33863386338633862072（3） max(distinct|allcol) ：取同組序列數(shù)據(jù)集中最大值。（4） min(distinct|allcol) ：取同組序列數(shù)據(jù)集中最小值。（5） sum(

35、distinct|allcol) ：取同組序列數(shù)據(jù)集的和。（6） dense_rank(n1,n2) within group(order by col1 desc|asc nulls first|last,col2 desc|asc nulls first|last)：計(jì)算指定值在記錄集中的排序值，函數(shù)的參值必須一一對(duì)應(yīng)group中的列，并且二者數(shù)據(jù)類型要一致。（7） rank(n1,n2) within group(order by col1 desc|asc nulls first|last,col2 desc|asc nulls first|last)：參數(shù)及用法同dense_rank

36、。Rank與dense_rank的區(qū)別是：如果有重復(fù)記錄，rank的排序是不連續(xù)的，而dense_rank的排名是連續(xù)的。SELECT DENSE_RANK('8091') WITHIN GROUP(ORDER BY APCODE) A, DENSE_RANK('8091.5') WITHIN GROUP(ORDER BY APCODE) B, DENSE_RANK('8091','8090') WITHIN GROUP(ORDER BY APCODE,LG_CODE) C, RANK('8091') WITHIN

37、 GROUP(ORDER BY APCODE) D, RANK('8091.5') WITHIN GROUP(ORDER BY APCODE) E, RANK('8091','8090') WITHIN GROUP(ORDER BY APCODE,LG_CODE) F FROM APCODE_LG_RLNABCDEF155815592826282628272826返回結(jié)果為：（8） first：返回dense_rank函數(shù)返回為1的行，功能上與first_value相同，語(yǔ)法圖如下：（9） last：返回dense_rank函數(shù)最大值的行，語(yǔ)法同

38、first。SELECT MAX(RTNPFT) KEEP(DENSE_RANK FIRST ORDER BY CCY_CODE, ORG_ID) A, AVG(RTNPFT) KEEP(DENSE_RANK LAST ORDER BY CCY_CODE, ORG_ID) B FROM FA_PLN（10） cume_dist(expr,expr) within group(order by exprdesc|ascnulls first|last ,expr desc|asc nulls first|last)：查詢指定數(shù)值在指定分組序列中的相對(duì)位置，返回值介于0和1之間的小數(shù)值。（11）

39、percent_rank：語(yǔ)法同cume_dist，返回指定數(shù)據(jù)在分組序列中所占的相對(duì)位置，返回值介于0和1之間的小數(shù)值。SELECT CUME_DIST('8091') WITHIN GROUP(ORDER BY APCODE) A, PERCENT_RANK('8091') WITHIN GROUP(ORDER BY APCODE) B, CUME_DIST('8091.5') WITHIN GROUP(ORDER BY APCODE) C,PERCENT_RANK('8091.5') WITHIN GROUP(ORDER B

40、Y APCODE) D FROM APCODE_LG_RLN WHERE APCODE <= '8113'-這兩個(gè)條件的目的是使得記錄集中正好10條數(shù)據(jù) AND LG_CODE = '8090'-按apcode排序 8091 在第二個(gè)位置返回結(jié)果為：ABCD0.272727273=3/110.1=1/100.272727273=3/110.2=2/10其中 SELECT * FROM APCODE_LG_RLN WHERE APCODE <= '8113'AND LG_CODE = '8090'的結(jié)果為：APCODEL

41、G_CODE80718090809180908092809080938090809480908095809080968090809780908099809081138090從中可以看出cume_dist和percent_rank的區(qū)別。函數(shù)返回值分母分子指定值不在記錄集中指定值在記錄集中CUME_DISTRECORD_NUM+1NO_OF_VALUE_IN_RECORDNO_OF_VALUE_IN_RECORD+重復(fù)記錄條數(shù)PERCENT_RANKRECORD_NUMNO_OF_VALUE_IN_RECORD-1NO_OF_VALUE_IN_RECORD-1說(shuō)明：RECORD_NUM表示記錄集

42、中記錄的條數(shù)，NO_OF_VALUE_IN_RECORD表示指定值在記錄集中的排序序號(hào)。（12） percentile_cont(n) within group(order by coldesc|asc)：根據(jù)輸入的值返回該值對(duì)應(yīng)的分組序列中數(shù)值，輸入值介于0-1之間。如果分組序列中不存在對(duì)應(yīng)值，Oracle會(huì)根據(jù)如下規(guī)則計(jì)算返回值：if (CRN = FRN = RN) then (value of expression from row at RN) else (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) *

43、(value of expression for row at CRN)（13） percentile_disc(n) within group(order by coldesc|asc)：如果分組序列中不存在對(duì)應(yīng)值，返回該序列中最近最大的一個(gè)值。SELECT PERCENTILE_CONT(0.1) WITHIN GROUP(ORDER BY RTNPFT) PERCENTILE_CONT,PERCENTILE_DISC(0.1) WITHIN GROUP(ORDER BY ORG_ID) PERCENTILE_DISCFROM FA_PLN返回結(jié)果為：PERCENTILE_CONTPERC

44、ENTILE_DISC43736229.55001C1（14） stddev(distinct|allcol) ：標(biāo)準(zhǔn)差。（15） variance(distinct|allcol) ：協(xié)方差。4. 分析函數(shù)分析函數(shù)計(jì)算基于組的某種聚合值，分組查詢出的行被稱為“窗口（window）”，在根據(jù)over()執(zhí)行過程中，針對(duì)每一行都會(huì)重新定義窗口，窗口為“當(dāng)前行（current row）”確定執(zhí)行計(jì)算的行的范圍。分析函數(shù)與聚合函數(shù)的不同之處在于：（1）返回：對(duì)于每個(gè)組分析函數(shù)返回多行，而聚合函數(shù)只返回一行。（2）語(yǔ)法：分析函數(shù)有over()，聚合函數(shù)沒有over()。除了order by子句的運(yùn)算外

45、，分析函數(shù)在SQL語(yǔ)句中將會(huì)最后執(zhí)行，因此分析函數(shù)只能應(yīng)用于select的列或order by子句中，不能放在where、group by、having后。部分分析函數(shù)在選擇列時(shí)支持distinct，如果指定了該參數(shù)，則over條件中不能指定order by子句。分析函數(shù)的語(yǔ)法為：Analytic-Function(<Argument>,<Argument>,.)OVER (<Query-Partition-Clause> - 分區(qū)子句 <Order-By-Clause> - 分區(qū)排序子句 <Windowing-Clause> - 窗

46、口子句 ) 說(shuō)明：1、 Analytic-Function是分析函數(shù)的名字。2、 Argument是參數(shù)，分析函數(shù)可以有 0-3 個(gè)參數(shù)，參數(shù)必須是數(shù)字類型或者可以隱式轉(zhuǎn)換為數(shù)字類型的數(shù)據(jù)類型。3、 OVER是關(guān)鍵字，用于標(biāo)識(shí)分析函數(shù)。4、 Query-Partition-Clause分區(qū)子句，一般格式為partition by expr,expr。相當(dāng)于group by語(yǔ)句。5、 Order-By-Clause分區(qū)排序子句：5.1一般格式為：order siblings by expr|position|c_alias asd|desc nulls first|last5.2語(yǔ)法圖：5.3n

47、ulls first|last：指定空值排在有序序列中第一個(gè)或最后一個(gè)位置，默認(rèn)空值大于所有值。5.4部分分析函數(shù)支持distinct語(yǔ)句，使用了distinct時(shí)就不能再使用order by語(yǔ)句。5.5除percentile_cont和percentile_disc（它們只能取唯一的鍵值排序）之外的分析函數(shù)，分組中可以使用多個(gè)鍵值進(jìn)行排序。6、 Windowing-Clause窗口子句：6.1窗口子句為當(dāng)前行（current row）指定執(zhí)行聚集運(yùn)算的記錄集，可以通過邏輯方法（range）或者物理方法（rows）來(lái)指定窗口范圍。6.2窗口有兩種類型：6.2.1 滑動(dòng)窗口（sliding wi

48、ndow）：窗口邊界隨記錄行動(dòng)態(tài)變化。6.2.2 固定窗口（anchored window）：窗口邊界始終固定。默認(rèn)的窗口類型為固定窗口，它開始于當(dāng)前分區(qū)的第一行，結(jié)束于當(dāng)前行（current row）。6.3語(yǔ)法圖：6.4關(guān)鍵字說(shuō)明：6.4.1ROWS：物理開窗，當(dāng)前行。6.4.2RANGE：邏輯開窗，當(dāng)前值。6.4.3BETWEENEND：指定窗口的起始點(diǎn)和終止點(diǎn)。6.4.4UNBOUNDED PERCEDING：指定窗口的起始點(diǎn)是分區(qū)的第一行。6.4.5UNBOUNDED FOLLOWING：指定窗口的終止點(diǎn)是分區(qū)的最后一行。6.4.6CURRENT ROW：當(dāng)前行。6.4.7value

49、_expr PERCEDING/FOLLOWING：當(dāng)前行/當(dāng)前值的“前面/后面” value_expr處。分析函數(shù)列表：分析函數(shù)參數(shù)說(shuō)明聚集函數(shù)窗口子句備注avgdistinct|allexpr平均值有Ycorrexpr1,expr2相關(guān)系數(shù)有Ycovar_popexpr1,expr2總體協(xié)方差有Ycovar_sampexpr1,expr2樣本協(xié)方差有Ycount*|distinct|allexpr計(jì)數(shù)有Ycume_dist累計(jì)分布有N返回值dense_rank連續(xù)排名有Nfirst第一個(gè)值有Nfirst_valueexpr ignore nulls第一個(gè)值無(wú)Ylagexpr,offset,

50、default前移無(wú)Nlast最后一個(gè)值有Nlast_valueexpr ignore nulls最后一個(gè)值無(wú)Yleadexpr,offset,default后移無(wú)Nmaxdistinct|allexpr最大值有Ymindistinct|allexpr最小值有Yntileexpr等分無(wú)Npercent_rank百分比排名有Npercentile_contexpr反分布有Npercentile_discexpr反分布有Nrank排名有Nratio_to_reportexpr貢獻(xiàn)率無(wú)Nrow_number排名無(wú)Nstddevdistinct|allexpr樣本標(biāo)準(zhǔn)偏差有Ystddev_popexp

51、r總體方差平方根有Ystddev_sampexpr樣本方差平方根有Ysumdistinct|allexpr求和有Yvar_popexpr總體方差有Yvar_sampexpr樣本方差有Yvariancedistinct|allexpr方差有Y5. 自定義聚合函數(shù)四：集合操作操作符說(shuō)明備注UNION ALL各個(gè)查詢檢索出的所有行，包含重復(fù)的行（1）對(duì)兩個(gè)或多個(gè)查詢操作（2）所有查詢返回的列數(shù)及列類型必須匹配，列名可以不同UNION各個(gè)查詢檢索出的所有行，不包含重復(fù)的行INTERSECT兩個(gè)查詢檢索出的共有行MINUS第一個(gè)查詢檢索的行減去第二個(gè)查詢檢索出的行五：層級(jí)查詢1. 示例表結(jié)構(gòu)本節(jié)中以中銀

52、財(cái)務(wù)廣場(chǎng)系統(tǒng)中菜單表tp_menu和機(jī)構(gòu)org_inf、org_dim來(lái)講述Oracle中提供的層級(jí)查詢，這些表的結(jié)構(gòu)為。菜單表TP_MENU：名稱類型可為空注釋MENU_IDINTEGERN菜單號(hào)PARENT_IDINTEGERY父節(jié)點(diǎn)號(hào)MENU_NAMEVARCHAR2(100)Y菜單名稱IMG_URLVARCHAR2(500)Y圖片地址TARGET_URLVARCHAR2(500)Y目標(biāo)地址SORT_TAGINTEGERY排序標(biāo)志DESCRIPTIONVARCHAR2(2048)Y描述層級(jí)機(jī)構(gòu)信息表ORG_DIM：名稱類型可為空注釋ORG_IDCHAR(5)N機(jī)構(gòu)號(hào)ORG_NMVARCHAR2(50)Y機(jī)構(gòu)名稱SUP_ORG_IDCHAR(5)Y上級(jí)機(jī)構(gòu)號(hào)SUP_ORG_NMVARCHAR2(50)Y上級(jí)機(jī)構(gòu)名稱THDLV_ORG_IDCHAR(5)Y三級(jí)機(jī)構(gòu)號(hào)THDLV_ORG_NMVARCHAR2(50)Y三級(jí)機(jī)構(gòu)名稱SCDLV_ORG_IDC