數(shù)據(jù)庫相關-中級篇參考-oracle函數(shù)

軟件測試學習群 （備注“雨神”即可通過返回絕對值selectabs(100),abs(-100)from返回正負值selectsign(100),sign(-100),sign(0)from返回較大的最小整數(shù)selectceil(3.1),ceil(2.8+1.3),ceil(0)from返回較小的最大整數(shù)selectfloor(3.1),floor(2.8+1.3),floor(0)fromxy次冪.power(x,y【參數(shù)】x，yselectpower(2.5,2),power(1.5,0),power(20,-1)from【相近】 ey次冪.exp(yselectexp(3),exp(0),exp(-3)from返回 ,1ey次冪。【相反】xy的對數(shù).log(x,yselectpower(4,2),log(16,2),1/log(16,4)fromselectpower(6.5,3),log(274.625,3),1/log(power(6.5,3),6.5)from返回：274.625 【相近】 ey的對數(shù).ln(y【參數(shù)】y(selectexp(3),exp(- )from返回 , 3 【相近】eyxy的余數(shù).mod(x,yselectmod(23,8),mod(24,8)from返回四舍五入后的值yy0則四舍五入到小數(shù)點向左第y返回 【相近】返回截取后的值【參數(shù)】x,y，數(shù)字型表達式,yyy>0y位y0y0表示。selecttrunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)fromdual;返回 【相近】x的平方根selectsqrt(64),sqrt(10)from返回：81.30.三角函數(shù)【示例】selectsin(1.57079from返回：1【示例】selectsin(20),sinh(20from返回 【示例】selectcos(- )from-1【示例】selectcosh(20from【示例】selecttan(20),tan(10from 【示例】selecttanh(20),tan(20from返回：1,2.【示例】selectasin(0.5from【示例】selectacos(-1fromselectatan(1fromASCII碼SQLselectascii('A'A,ascii('a'a,asciispace,ascii('示hzfromAA--空selectchr(32),ASCII('')from>'),ASCII('/'),ASCII('?'),ASCII('|'),ASCII('')fromASCII('ASCIIx的字符【功能】：將ASCII【參數(shù)】：n1,為0~255SQL>selectchr(54740)zhao,chr(65)chr65fromdual;ZHC--趙selectascii('張chr(54725)ascii('森chr(51629fromASCII('張54725CHR(54725)張ASCII('森51629CHR(51629)森,chr(254),chr(255)from連接兩個字符串【參數(shù)】c1，c2selectconcat('010- ')||'轉(zhuǎn)23'競from競轉(zhuǎn)把每個單詞首個字母變?yōu)榇髮慡QL>selectinitcap('smithabcaBC')uppfromSmithAbc將整個字符串轉(zhuǎn)換為小寫SQL>selectlower('AaBbCcDd')AaBbCcDdfrom將整個字符串轉(zhuǎn)換為大寫SQL>selectupper('AaBbCcDd')upperfromdual;把每個單詞首個字母變?yōu)榇髮懖樵償?shù)據(jù)級的NLS設置:select*from指定排序的方式(nls_sort=)。selectnls_initcap('abcde')nls_initcap('acbde','nls_sort=SCHINESE_PINYIN_M')"test1"fromdual;返回：AbCdeACBDselectnls_initcap('abcde')nls_initcap('acbde','NLS_LANGUAGE=AMERICAN')"test1"fromdual;把整個字符串轉(zhuǎn)換為小寫【參數(shù)】Nls_param可選，指定排序的方式(nls_sort=)。selectnls_lower('abcde')nls_lower('acbde','nls_sort=SCHINESE_PINYIN_M')"test1"fromdual;返回：abcde,acbd將整個字符串轉(zhuǎn)換為大寫【參數(shù)】Nls_param可選，指定排序的方式(nls_sort=)。selectnls_upper('abcde')nls_upper('acbde','nls_sort=SCHINESE_PINYIN_M')"test1"fromdual;返回：ABCDE,ACBD字符串中搜索字符位置(1字符 【示例】selectinstr('oracletraning','ra',1,2instringfromselectinstr('重慶某軟件公司某1instrb('重慶某軟件公司某11instringfromdual;字符串中搜索字符位置(2字符 selectinstr('重慶某軟件公司某1instrb('重慶某軟件公司某11instringfromdual;返回字符串的長度(1字符【參數(shù)】C1SQL>selectlength('競'),length('市海錠區(qū)'),length('TO_CHAR')from 返回字符串的長度(2字符【參數(shù)】C1SQL>selectlength('競'),lengthB('競')from 【參數(shù)】C1SQL>selectlength('競'),length('市海錠區(qū)'),length('TO_CHAR')fromdual;Selectlength('你好fromSelectlengthB('你好'),lengthC('你好'),length2('你好length4('你好')from在左邊添加字符【參數(shù)】C1nc2追加字符串,SQL>selectlpad('gao',10,'*')from【相反】LTRIM(在右邊添加字符【參數(shù)】C1nc2追加字符串,SQL>selectrpad('gao',10,'*a')fromdual;【相反】RTRIM(刪除左邊字符串【參數(shù)】C1c2追加字符串,SQL>select gaoqianjing','')textfrom或:select gaoqianjing')textfromgaoqian【相反】LPAD(刪除右邊字符串【參數(shù)】C1c2追加字符串,SQL>selectRTRIM('gaoqianjingXXXX','X')textfromdual;gaoqian【相反】RPAD(替換子字符串 SQL>selectreplace('heloveyou','he','i')testfromilove字符串語音表示形式selectsoundex('twosoundex('toosoundex('to'fromdual--結(jié)果都selectsoundex('capsoundex('cupfromdual--結(jié)果都是selectsoundex('house'),soundex('horse')fromdual;--結(jié)果分別是SQL>select ',3,8)testfromselectsubstr('我',4,11),substrb('我',4,11),substrb('我',3,11)testfromdual;返回 , 替換子字符 selecttranslate('heloveyou','he','i'),translate('重慶的人','重慶的','男'),translate('重慶的人','重慶的重慶','男士們translate('重慶的人','重慶的重慶','1男士們translate('重慶的人','1重慶的重慶','男士們')fromdual;返回：iloveyou,,,1人,京男士刪除左邊和右邊字符串TRIM(c1from【參數(shù)】C2刪除前字符串c1刪除字符串,selecttrim('X'from'XXXgaoqianjingXXXX'),trim('X'from'XXXgaoXXjingXXXX')textfrom返回：gaoqian 【相似】LTRIM()刪除左邊出現(xiàn)的字符串RTRIM()返回系統(tǒng)當前日期【示例】selectsysdatehzfromselectto_date('2017-01-2215:14:01','YYYY-MM-DDto_char(sysdate,'YYYY-MM-DDHH:MI:SS'),to_char(sysdate,'YYYY-MM-DDHH24:MI:SS')fromselectto_char(systimestamp,'yyyy-mm-ddHH24:MI:SS:ff4'),to_char(systimestamp,'yyyy-mm-ddHH24:MI:SS')fromselectto_date(to_char(systimestamp,'yyyy-mm-ddHH24:MI:SS'),'yyyy-mm-ddfromSELECTsysdate,Trunc(sysdate,'MI'),to_date(sysdate,'YYYY-MM-DDHH24:MI:SS'),to_char(sysdate,'YYYY-MM-DDHH24:MI:SS')FROMdual;selectsysdate,to_char(to_date('2011-3-200:00:00','YYYY-MM-DDHH24:MI:SS')-interval'1'minute,'YYYY-MM-DDHH24:MI:SS'),to_char(to_date(to_char(sysdate-interval'6'hour,'YYYY-MM-DDHH24:MI:SS'),'YYYY-MM-DDHH24:MI:SSinterval'1'SECOND,'YYYY-MM-DDHH24:MI:SS')fromdual;--減1分鐘1秒selectsysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-ddHH24:MI:SS加1秒fromdual;--加1秒SELECTTO_CHAR(TO_DATE('21-7月-11''dd-mm"月"-yy')'yyyy-mm-dd'FROMSELECTTO_CHAR(SYSDATE'dd-mm"月"-yyyy'FROMselectto_char(to_date(' ','yyyymmdd')-interval'1'month,'yyyymmdd')fromdual;selectto_char(to_date(' ','yyyymmdd')-interval'1'month,'yyyy-mm-dd')fromdual;Selectlast_day(TO_CHAR(TO_DATE('26-7月-11''dd-mm"月"-'yyyy-mm-dd'))fromselectto_char(sysdate,'PMyyyy-mm-ddhh24:mi:ssADyearmondaydddiw')FROMDUAL;SELECTTO_CHAR(SYSDATE,'DSDL')FROMto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American')fromdual;UAGE=American')V_datefromdual;selectsysdate,to_char(sysdateinterval'1minute,'YYYY-MM-DDHH24:MI:SS')fromdual;--減1分鐘SELECTsysdate,to_char(sysdate,'PMyyyy-mm-ddhh24:mi:ssADyearmondaydddiw')FROMdual;返回指定月數(shù)后的日期【示例】selectsysdate,add_months(sysdate,3)hzfrom返回本月最后一天的日期【示例】selectsysdate,last_day(sysdate)hzfrom2個日期間隔月數(shù)【參數(shù)】：d1，d2selectmonths_between(sysdate,to_date('2006-01-01','YYYY-MM-DD')),months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD'))from返回時區(qū)的對應時間【參數(shù)】：dt1，d2【參數(shù)】：c1,c2selectto_char(sysdate,'yyyy.mm.ddhh24:mi:ss')bj_time,to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.ddhh24:mi:ss')fromdual; 2008.11.0520:11:582008.11.06selectsysdatenew_time(sysdate,'PDT','GMT')los_anglesfromdual; 2008-11-0520:11:582008-11-06四舍五入后的期間第一天fromfrom最近0點日期取消參數(shù)c1或jselectsysdate當時日期,round(sysdate最近0點日期,round(sysdate,'day')最近星期日,round(sysdate,'month')最近月初,round(sysdate,'q')最近季初日期,round(sysdate'year最近年初日期from返回日期所在期間的第一天最近0點日期取消參數(shù)c1或最近的星期日：day或dy或d每周順序：日，一，二，三，四，五，六）selectsysdate當時日期,trunc(sysdate今天日期,trunc(sysdate'day本周星期日,trunc(sysdate,'month')本月初,trunc(sysdate,'q')本季初日期,trunc(sysdate'year本年初日期返回下周某一天的日期selectsysdate當時日期next_day(sysdate星期一下周星期一,next_day(sysdate星期二下周星期二,next_day(sysdate星期三下周星期三,next_day(sysdate星期四下周星期四,next_day(sysdate星期五下周星期五,next_day(sysdate星期六下周星期六,next_day(sysdate,'星期日')下周星期日from提取時間日期中數(shù)據(jù)extract(c1fromselectextract(hourfromtimestamp'2001-2-162:38:40')小時,extract(minutefromtimestamp2001-2-162:38:40分鐘,extract(secondfromtimestamp'2001-2-162:38:40')秒,extract(dayfromtimestamp'2001-2-162:38:40')日,extract(monthfromtimestamp'2001-2-162:38:40')月,extract(yearfromtimestamp'2001-2-162:38:40')年fromselectextract(yearfromdate'2001-2-16'fromdual;selectsysdate當前日期,extract(hourfromsystimestamp小時extract(dayfromsysdate)日,extract(monthfromsysdate月,extract(yearfromsysdate)年返回會話中的日期和時間【示例】selectlocaltimestampfrom返回：14-11月-0853000返回當前會話時區(qū)中的當前日期和時間【功能】：以timestampwithtimezone【示例】selectcurrent_timestampfrom返回：14-11月-0809000返回當前會話時區(qū)中的當前日期【示例】selectcurrent_datefrom返回數(shù)據(jù)庫時區(qū)設置【示例】selectdbtimezonefrom返回當前會話時區(qū)【示例】selectdbtimezone,sessiontimezonefrom返回 3.29.變動日期時間數(shù)值INTERVALc11selecttrunc(sysdateinterval1second--加1秒(1/24/60/60)trunc(sysdate)+(interval'1'minute),--加1分鐘(1/24/60)trunc(sysdate)+(interval'1'hour),--加1小時(1/24)trunc(sysdate)+(interval'1'day),--加1天(1)trunc(sysdate)+(interval'1'month),--加1月trunc(sysdate)+(interval'1'year),--加1年trunc(sysdateinterval01:02:03'hourtosecond--加指定小時到秒trunc(sysdateinterval'01:02minutetosecond)--加指定分鐘到秒trunc(sysdateinterval'01:02hourtominute)--加指定小時到分鐘trunc(sysdateinterval'201:02'daytominute--加指定天數(shù)到分鐘from3.30OracleSQL語句生成日歷.txtOracleSQL語句生成日歷200611select ','yyyymmdd')asstartDayOfMonfromselect ','yyyymmdd'))asendDayOfMonfromselect ', ','yyyymmdd')+1asfromdual;selectlast_day(to_date(' ','yyyymmdd')), ','yyyymmdd')+1asdayofmonfrom2006-11-302006-11-select*FROMselectto_date(' ','yyyymmdd')+level-1aseverydayfromdualconnectbylevel<= ',- ','yyyymmdd')+selectto_char(everyday,'yyyy')as年,to_char(everyday,'mm')as月,to_char(everyday,'dd')as日,to_char(everyday,'dy')as星期幾,lpad(to_char(everyday,w6as該月的第幾周,lpad(to_char(everyday,ww')6asFROMselectto_date(' ','yyyymmdd')+level-1aseverydayfromdualconnectbylevel<= ',- ','yyyymmdd')+selecteveryDay,to_char(everyday,'yyyy')as年,to_char(everyday,'mm')as月,to_char(everyday,'dd')as日,to_char(everyday,'dy')as星期幾,lpad(to_char(everyday,'w'),6)as該月的第幾周,lpad(to_char(everyday,'ww'),6as該年的第幾周lpad(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd')),3)as星期日,lpad(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd')),3)as星期一,lpad(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd')),3)as星期二,lpad(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd')),3)as星期三,lpad(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd')),3)as星期四,lpad(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd')),3)as星期五,lpad(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd')),3)as星期六from(selectto_date(' ','yyyymmdd')+level-1aseveryDayfromconnectbylevel ','yyyymmdd')) ','yyyymmdd')selectto_char(everyday,'w')asweek,',to_char(everyday,'dd'))as星期日',to_char(everyday,'dd')))as星期一,',to_char(everyday,'dd')))as星期二,',to_char(everyday,'dd')))as星期三,',to_char(everyday,'dd')))as星期四,',to_char(everyday,'dd')))as星期五,',to_char(everyday,'dd')))as星期六from(selectto_date(' ','yyyymmdd')+level-1aseveryDayfromdualconnectbylevel<=(last_day(to_date(' ','yyyymmdd'))- ','yyyymmdd')+1))groupbyselectto_char(everyday,'ww')asweek,',to_char(everyday,'dd'))as星期日',to_char(everyday,'dd')))as星期一,',to_char(everyday,'dd')))as星期二,',to_char(everyday,'dd')))as星期三,',to_char(everyday,'dd')))as星期四,',to_char(everyday,'dd')))as星期五,',to_char(everyday,'dd')))as星期六from(select ','yyyymmdd')+level-1asfromconnectbylevel<=(last_day(to_date(' ','yyyymmdd'))- ','yyyymmdd')+1))groupby ')-1))/7)asweek,',to_char(everyday,'dd')))as星期日,',to_char(everyday,'dd')))as星期一,',to_char(everyday,'dd')))as星期二,',to_char(everyday,'dd')))as星期三,',to_char(everyday,'dd')))as星期四,',to_char(everyday,'dd')))as星期五,',to_char(everyday,'dd')))as星期六from(selectto_date(' ','yyyymmdd')+level-1aseveryDayfromdualconnectbylevel<=(last_day(to_date(' ','yyyymmdd'))- ','yyyymmdd')+1))group ))/7)asas星期日,as星期一,as星期二,as星期三,as星期四as星期五,as星期六from(selectto_date(' ','yyyymmdd')+level-1aseverydayfromdualconnectbylevel )group orderbyweekSQLServer , , , , , , , , '01/01/2002' '12/31/2004' '+ selectmax(su)su,max(mo)mo,max(tu)tu,max(we)we,max(th)th,max(fr)fr,max(sa)safrom(selectcasewhend=1thenddend casewhend=2thenddend casewhend=3thenddend casewhend=4thenddend casewhend=5thenddend casewhend=6thenddend casewhend=7thenddend selectdates,to_char(dates,'dd')dd,to_number(to_char(dates,'d'))d,to_char(dates,'w')w,to_char(dates+1,'iw')iwfrom(selecttrunc(sysdate,'month')+level-1datesfromdualconnectbyrownum<last_day(sysdate)-trunc(sysdate,'month')+1)))groupbyiworderby ')-1))/7)asweek,',to_char(everyday,'dd')))as星期日,',to_char(everyday,'dd')))as星期一,',to_char(everyday,'dd')))as星期二,',to_char(everyday,'dd')))as星期三,',to_char(everyday,'dd')))as星期四,',to_char(everyday,'dd')))as星期五,',to_char(everyday,'dd')))as星期六from(selectto_date(' ','yyyymmdd')+level-1aseveryDayfromdualconnectbylevel<=(last_day(to_date(' ','yyyymmdd'))- ','yyyymmdd')+1))group rowid值chartorowid(c1SELECTchartorowid('AAAADeAABAAAAZSAAA')FROMrowid值轉(zhuǎn)換字符串ROWIDTOCHAR(rowidselectrowidtochar(rowid)from字符串語言字符集轉(zhuǎn)換selectconvert('strutz','we8hp','f7dec')"conversion"fromselectconvert('name','us7ascii','zhs16cgb231280')"conversion"from16進制轉(zhuǎn)換為二進制【示例】selecthextoraw('A123'from16進制【示例】selectrawtohex('A123'from數(shù)字或日期轉(zhuǎn)換為字符串xnlsparm=NLS_NUMERIC_CHARACTERS用來指定小數(shù)位和千分位的分隔符，NLS_NUMERIC_CHARACTERS="dg",序號1十分位,百分位出現(xiàn),N次,視乎數(shù)字的大小而定.234599.在小數(shù)位,則表示轉(zhuǎn)換為對應字符,0表示;在整數(shù)位,6格符'S'||TO_CHAR(1234,'99B99')='S7ISO貨幣符號(NLS_ISO_CURRENCY參數(shù)所',altersessionsetNLS_ISO_CURRENCY='JAPAN';來修改8不同的是,數(shù)據(jù)庫會根據(jù)NLS_NUMERIC_CHARACTER的參數(shù)值來設置內(nèi)altersesssionset來修改.altersessionset 9 10注意事項:-D此外如果要轉(zhuǎn)換出小數(shù)點,D配合使11C的本地版本.可以放在整個格式的最前面和最后面.注意事項:12號=123413號14)把整數(shù)(1-3999)轉(zhuǎn)換為羅馬字符.RN表示轉(zhuǎn)為大寫,rn表示小寫的.iint;foriin1..20loopendloop;15S1617U雙幣符號,例如歐元.11L18VTO_CHAR(N,'999V9'),pV的位置,則該表達式＝to_char(N×(10的P-1次方)).但是9個數(shù)又必須保證大于等于乘積之TO_CHAR(50,'9V99')='######9的個數(shù)不夠19XTO_CHAR(100,'XX')=00FM組合20數(shù)值類：(.),(,),D,G其中點好和逗號因為表示不明顯，所以用小貨幣類：其它類selectto_char(1210.739999.9--返回to_char(1210.739,999.99')--返回to_char(1210.73$9,999.00--返回to_char(21'000099--返回to_char(852'xxxx--返回'from序號1-/,.;略例如日2AnnoDomini的簡寫，表示公元.nls的不同轉(zhuǎn)換為公ad等3,pmp.m（下午中文環(huán)境輸出為上午（午4B.cc小寫）BC,Oracle56D7NLS_DATE_LANGUAGE=SIMPLIFIEDCHINESE8910'200842811NLS_TERRITORY,NLS_LANGUAGE參數(shù)控制。例如2008-04-281213E1415161718192021IYY,IY,IISO年(4位）4,3，2，1位數(shù)字(倒數(shù)22 iyy,iy,i')=2112,112,12,2I23J 2425264月表示272829Q30 ,II,III,IV ,VI,VII,VIII,IX ,XI312s=[0,49],c=[50,99],3s=[50,99],c=[0,49],r=c2-4s=[50,99],c=[50,99],SQL>-dd')FROM 32333435dltsdlts中間以空格間隔開。TO_CHAR(SYSDATE,'TS')=午363738394041W42X43四位年，用分隔例如442008=twothousand4546Ynls是密切相關的；最后某些輸出（返回）和格式大小englishto_char(sysdate,'day')為例子，如果是sun(sysdate位于周末），如果selectto_char(sysdate,'d')每周第幾天,to_char(sysdate,'dd')每月第幾天,to_char(sysdate'ddd'每年第幾天,to_char(sysdate,'ww')每年第幾周,to_char(sysdate,'mm')每年第幾月,to_char(sysdate,'q')每年第幾季,to_char(sysdate,'yyyy')年fromSQL>selectto_char(sysdate,'PMyyyy-mm-ddhh24:mi:sssssADyearmondaydddiw')FROMDUAL;2008-03-2709:58:35917twothousandeight308713SQL>selectto_char(systimestamp,'HH24:MI:SS.FF5')fromdual;SQL>selectto_char(sysdate,'DSDL')from 年月selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American')fromdual;字符串轉(zhuǎn)換為日期型【相反】selectto_date('199912','yyyymm'),to_date('2000.05.20','yyyy.mm.dd'),(date'2008-12-31')xxdate,to_date('2008-12-3112:31:30','yyyy-mm-ddhh24:mi:ss'),(timestamp'2008-12-3112:31:30')xxtimestampfrom字符串轉(zhuǎn)換為數(shù)字型【相反】selectTO_NUMBER('199912'),TO_NUMBER('450.05')fromTO_CHAR(100,'XX')=selectto_char(100,'XX')from半角轉(zhuǎn)化為全角SQLselectto_multi_byte('高A'textfrom全角轉(zhuǎn)化為半角SQLselectto_single_byte('高Ａtextfrom高selectto_single_byte('高Ａtextto_multi_byte('高A'textfrom返回高Asql>selectnls_charset_id('zhs16gbk')fromID轉(zhuǎn)為名稱sql>selectnls_charset_name(852)fromselectnls_charset_name(852)fromdual;selectnls_charset_id('zhs16gbk')fromdual;統(tǒng)計平均值createtabletable3(xmvarchar(8),salnumber(7,2));insertintotable3values('gao',1111.11);insertintotable3values('gao',1111.11);insertintotable3values('zhu',5555.55);selectavg(distinctsal),avg(allsal),avg(sal)from結(jié)果：3333.332592.59統(tǒng)計合計值selectSUM(distinctsal),SUM(allsal),SUM(sal)from結(jié)果： 統(tǒng)計標準誤差selectSTDDEV(distinctsal),STDDEV(allsal),STDDEV(sal)from結(jié)果： 2565.99863039714統(tǒng)計方差結(jié)果 統(tǒng)計查詢所得的行數(shù)selectcount(allxm),count(distinctsal),count(allsal),fromtable3;結(jié)果： 44 5統(tǒng)計最大值selectMAX(distinctsal),MAX(xm)from結(jié)果 統(tǒng)計最小值createtabletable3(xmvarchar(8),salnumber(7,2));insertintotable3values('gao',1111.11);insertintotable3values('gao',1111.11);insertintotable3values('zhu',5555.55);insertintotable3values('',1111.11);insertintotable3values('zhu',0);結(jié)果 gaogao12.1語法FUNCTION_NAME(<參數(shù)>,…)OVER(<PARTITIONBY表達式,…><ORDERBY表達<ASCDESC<NULLSFIRSTNULLSLAST><WINDOWING子句PARTITIONORDERBY子句WINDOWING子句缺省時相當于RANGEUNBOUNDEDPRECEDING值域窗(RANGERANGENPRECEDING僅對數(shù)值或日期類型有效,選定窗為排序后當前行之前,某列(即排序列)值大于/小于(當前行該列值–/+N)的所有行,因此與ORDERBY子句有關系。行窗(ROWROWSNPRECEDING選定窗為當前行及之前N還可以加上BETWEENAND形式,例如RANGEBETWEENmPRECEDINGANDnAVG(<distinctall一組或選定窗中表達式的平均值CORR(expr,expr)即COVAR_POP(exp1,exp2)/(STDDEV_POP(expr1*STDDEV_POP(expr2)),兩個表達式的互相關,-1(反相關~1(正COUNT(<distinct<*<expr>計數(shù)COVAR_POP(expr,expr)總體協(xié)方差COVAR_SAMP(expr,expr)樣本協(xié)方差CUME_DIST累積分布,即行在組中的相對位置,返回0~DENSE_RANK行的相對排序(與ORDERBY搭配),相同的值具有一樣的序數(shù)(NULL計為相同F(xiàn)IRST_VALUELAG(expr,<offset>,<default>)之前的行,OFFSET是缺省為1的正數(shù),表示相對LAST_VALUELEAD(expr,<offset>,<default>)之后的行,OFFSET是缺省為1的正數(shù),表示相對MAX(exprMIN(expr)PERCENT_RANK類似CUME_DIST,1/(RANK相對序數(shù),答應并列,并空出隨后序號RATIO_TO_REPORT(exprSUM(表達式值)ROW_NUMBER排序的組中行的偏移STDDEV(expr)標準差STDDEV_POP(expr)總體標準差STDDEV_SAMP(expr樣本標準差SUM(expr)合計VAR_POP(expr)總體方差VAR_SAMP(expr)樣本方差VARIANCE(expr)方差REGR_xxxx(exprexprREGR_SLOPE：返回斜率，等于COVAR_POP(expr1expr2VAR_POP(expr2)AVG(expr1REGR_SLOPE(expr1expr2*AVG(expr2)IfVAR_POP(expr2)=0thenreturnIfVAR_POP(expr1)=0andVAR_POP(expr2)!=0thenreturn1IfVAR_POP(expr1)>0andVAR_POP(expr2!=0thenreturnREGR_AVGX：計算回歸線的自變量(expr2)的平均值，去掉了空對(expr1expr2)后，等REGR_AVGY：計算回歸線的應變量(expr1)的平均值，去掉了空對(expr1expr2)后，等REGR_SXX：返回值等于REGR_COUNT(expr1,expr2)*VAR_POP(expr2)REGR_SYY：返回值等于REGR_COUNT(expr1,expr2)*VAR_POP(expr1)REGR_SXY返回值等于REGR_COUNT(expr1expr2*COVAR_POP(expr1expr2)createtablestudents(idnumber(15,0),areavarchar2(10),stu_typevarchar2(2),scorenumber(20,2));1）GROUPBY--A、GROUselectid,area,stu_type,sum(score)scorefromstudentsgroupbygrousets((id,area,stu_type),(id,area),fromfromorderbyid,area,/*--------理解grousetsselectabcsumdfromtgroupbygrousets(a,b,cselect*fromselecta,null,null,sum(d)fromtgroupbyaunionallselectnull,b,null,sum(d)fromtgroupbybunionallselectnull,null,c,sum(d)fromtgroupby)selectid,area,stu_type,sum(score)fromstudentsgroupbygrousets(id,area,selectid,area,stu_type,sum(score)scorefromstudentsgroupbyrollup(id,area,stu_type)orderbyid,area,stu_type; 理解selecta,b,c,sum(d)fromtgroupbyrollup(a,b,select*fromselecta,b,c,sum(d)fromtgroupbya,b,cunionallselecta,b,null,sum(d)fromtgroupbya,bunionallselecta,null,null,sum(d)fromtgroupbyaunionallselectnull,null,null,sum(d)from)selectid,area,stu_type,sum(score)fromstudentsgroupbyrollup(id,area,selectid,area,stu_type,sum(score)groupbycube(id,area,stu_type)orderbyid,area,stu_type; 理解selecta,b,c,sum(d)fromtgroupbycube(a,b,c)selecta,b,c,sum(d)fromtgroupbygrousets((a,b,c(a,b),(a),(b,c(b),(a,c),(c),())selectdecode(grou(id),1,'allid',id)id,decode(grou(area),1,'allarea',to_char(area))area,sum(score)scorefromgroupbycube(id,area,stu_type)orderbyid,area,stu_type; 將score不分組：dense_rank()over(orderbyscoreselectdense_rank(over(partitionbyidorderbyscoredesc分組id排序dense_rank(over(orderbyscoredescfromstudentsorderbyid,area;將score不分組：row_number()over(orderbyscoreselectrow_number(over(partitionbyidorderbyscoredesc分組id排序row_number(over(orderbyscoredescorderbyid, 將score按ID分組：rank()over(partitionbyidorderbyscore將score不分組：rank()over(orderbyscoreselectrank(over(partitionbyidorderbyscoredesc分組id排序rank(over(orderbyscoredescfromstudentsorderbyid,area;函數(shù)：cume_dist(over(orderbyselectcume_dist()over(orderbyid)a,--按ID最大/總個cume_dist(over(partitionbyidorderbyscoredescb--ID分row_number(over(orderbyidfromstudentsorderbyid,area;將score按ID分組：cume_dist()over(partitionbyidorderbyscoredesc)*sum(1)over(partitionbyid)將score不分組：cume_dist()over(orderbyscoredesc)*sum(1)selectsum(1over(as總數(shù)sum(1over(partitionbyidas分組個數(shù)(cume_dist(over(partitionbyidorderbyscoredesc)*(sum(1)over(partitionbyid))分組id排序,(cume_dist(over(orderbyscoredesc)*(sum(1over()fromstudentsorderbyid,areaselectsum(1)over()as錄數(shù)sum(1over(partitionbyidas分組記錄數(shù)求和

sum(scoreover(as總計sum(scoreover(partitionbyidas分組求和,sum(score)over(orderbyid)as分組連續(xù)求和,sum(score)over(partitionbyid,area)as分組ID和area求和,sum(scoreover(partitionbyidorderbyareaas分組ID并連續(xù)按areamax(scoreover(as最大值max(scoreover(partitionbyidas分組最大值,max(score)over(orderbyid)as分組連續(xù)最大值,max(score)over(partitionbyid,area)as分組ID和area求最大值,max(scoreover(partitionbyidorderbyareaas分組ID并連續(xù)按area求最大值avg(scoreover(as所有平均avg(scoreover(partitionbyidas分組平均,avg(score)over(orderbyid)as分組連續(xù)平均,avg(score)over(partitionbyid,area)as分組ID和area平均,avg(scoreover(partitionbyidorderbyareaas分組ID并連續(xù)按area平均

RATIO_TO_REPORT(score)over()as"占所有%",RATIO_TO_REPORT(scoreover(partitionbyidas占分組%",from3、LAG(COL,n,default)、LEAD(OL,n,default取前后邊N條數(shù)據(jù)取前面記錄的值：lag(score,n,x)over(orderbyid)取后面記錄的值：lead(score,n,xover(orderbyselectidlag(score10over(orderbyidlgscorefromstudents;selectidlead(score10over(orderbyidlgscorefromstudents;4、取第起始1行值：first_value(score,nover(orderby取第最后1行值：LAST_value(score,nover(orderbyselectid,first_value(score)over(orderbyid)fv,scorefromstudents;selectid,last_value(score)over(orderbyid)fv,scorefrom連續(xù)求和分析函數(shù).sum(...sum(...)overselectbdcode,sum(1)over(orderbybdcode)aafromSQLbreakondeptnoskip1selectdeptno,ename,salfromemporderbyDEPTNO sum(salover(orderbyename)..查詢員工的薪水“連續(xù)”求和注意over(order byename)如果沒有orderby子句，求和就不是“連續(xù)”的，selectsum(salover(orderbyename連續(xù)求和sum(salover(總和此處sum(salover等同于100*round(salsum(salover()4份額(%)"fromempDEPTNO SAL連續(xù)求 總和份額sum(sal)over(partitionbydeptnoorderbyename)按部門“連續(xù)”求總和sum(sal)over(partitionbydeptno)按部門求總和sum(sal)over(orderbydeptno，ename)不按部門“連續(xù)”求總和sum(salover不按部門，求所有員工總和，效果等同于sum(sal)。selectdeptno,sum(salover(partitionbydeptnoorderbyename部門連續(xù)求和的薪水"連續(xù)"求sum(salover(partitionbydeptno部門總和部門統(tǒng)計的總和，同一100*round(salsum(salover(partitionbydeptno)4部門份sum(salover(orderbydeptnoename連續(xù)求和--所有部門的薪水"連sum(salover(總和此處sum(salover等同于sum(sal)，所有員100*round(salsum(salover()4總份額(%)"fromempDEPTNOENAMESAL部門連續(xù)求和部門總和部門份額(%)連續(xù)求和總 總份額10CLARKKING ADAMSFORD BLAKEJAMES WARDselectsum(sal)over(partitionbydeptnoorderbysal)dept_sum,sum(sal)over(orderbydeptno,sal)sumfromDEPTNO SAL selectsum(sal)over(partitionbydeptnoorderbydeptnodesc,saldesc)sum(sal)over(orderbydeptnodesc,saldesc)sumfromemp;DEPTNO SAL 302010體會在"...fromemp;"后面不要加order by子句使用的分析函數(shù)的(partitionbydeptnoorderbysal)selectsum(sal)over(partitionbydeptnoorderbysal)dept_sum,sum(sal)over(orderbydeptno,sal)sumfromorderbydeptnoDEPTNO SAL 302010【語法】RANK()OVER([query_partition_clause]order_by_clause)dense_RANKOVER[query_partition_clauseorder_by_clause【功能】聚合函數(shù)RANKdense_rank聚合函數(shù)RANKdense_rankRANK()OVER([query_partition_clause]order_by_clauseRANK(expr[,expr]...)WITHINGROUP(ORDERBYexpr[DESC|ASC][NULLS{FIRST|LAST[,expr[DESC|ASC][NULLS{FIRST|LAST)例子COL112561112123523523625例子TABLE：A（科目，分數(shù)）現(xiàn)在要的結(jié)果是：（即想要每門科目的前3名的分數(shù)）select*from(selectrank(over(partitionbyorderbydesc)rk,a.*froma)twhere例子合計功能：計算出數(shù)值(4,1)在OradeByCol1,Col2排序下的排序值，也就是SELECTRANK(4,3)WITHINGROUP(ORDERBYcol1,col2)"Rank"FROMABCaaabbbbmABCa1a2a3b1b2b2b4selectm.a,m.b,m.c,dense_rank()over(partitionbyaorderbyb)liufromtest3mABCa1a2a3b1b2b2b3排序后順序號分析函數(shù)【語法】ROW_NUMBER(OVER(PARTITIONBYCOL1ORDERBY)name|seqno|A|1|A|2|A|3|A|4|B|1|B|2|B|3|B|4|C|1|C|2|C|3|C|4|A|1|A|2|B|1|B|2|C|1|testC|2|testselectfrom(selectname,seqno,description,row_number()over(partitionbynameorderbyseqno)idfromtable_name)whereCOL1COL2排序，而這個值就表示每組內(nèi)部排序lead（）lag（）--CreatecreatetableLEAD_TABLECASEIDVARCHAR2(10),STEPIDVARCHAR2(10),ACTIONDATEDATE)tablespaceCOLM_DATApctfree10initransmaxtrans255(initial64Kminextents1maxextentsunlimitedinsertinto insertinto insertintoLEAD_TABLE insertintoLEAD_TABLE insertintoLEAD_TABLE insertintoLEAD_TABLE insertintoLEAD_TABLE insertintoLEAD_TABLE insertintoLEAD_TABLE insertintoLEAD_TABLE selectnextactiondate-actiondatedatebetweenfrom(selectcaseid,lead(actiondate)over(partitionbycaseidorderactiondate)lag(stepid)over(partitionbycaseidorderbyfrom11111111lead（）lag（）selectcaseid,lead(stepid)over(partitionbycaseidorderbyactiondate)lead(actiondate)over(partitionbycaseidorderbyactiondate)lag(stepid)over(partitionbycaseidorderbyactiondate)lag(actiondate)over(partitionbycaseidorderbyactiondate)from返回數(shù)據(jù)類型、字節(jié)長度和在內(nèi)部的位置【返回】類型<[長度]>，符號/指數(shù)位[數(shù)字1，數(shù)字2，數(shù)字 如：Typ=2Len=7selectdump(123456.789)fromdual;--Typ=2Len=6:195,13,35,57,79,91selectdump(-123456.789)fromdual;--Typ=2Len=7:60,89,67,45,23,11,102selectdump('ABC',1016)fromdual;--Typ=96Len=3CharacterSet=ZHS16GBK:selectdump('ABC',1016)from返回結(jié)果為：Typ=96Len=3CharacterSet=ZHS16GBK0對應1對應對應12對應23對應24LONG69對應96對應106對應類型Number型,Type=2類型代碼可以從Oracle的文檔上查到正數(shù)：指數(shù)=符號/指數(shù)位-193(最為1是代表正數(shù))負數(shù)：指數(shù)=62-第一字節(jié)每個<數(shù)字位>乘以100^(指數(shù)-N(N是有效位數(shù)的順序位，第一個有效位的N=0)SQL>selectdump(123456.789)fromdual;--Typ=2Len=6:返回：Typ=2Len=6-=-1=-1=-1=-1=-1=SQL>selectdump(-123456.789)fromdual;--Typ=2Len=7:返回：Typ=2Len=7<指數(shù)>62-60=2(最是0，代表為負數(shù)-=-=-=-=-=返回表達式列表中最大值selectgreatest(10,32,'123','2006')fromdual;selectgreatest('kdnf','dfd','a','206')fromdual;返回表達式列表中最小值selectleast(10,32,'123','2006')fromdual;selectleast('kdnf','dfd','a','206')fromdual;為空值賦值【語法】NVL(expr1【語法】NVL2(expr1expr2返回當前會話對應的數(shù)據(jù)庫用戶名selectuserfromid號selectuidfrom返回當前會話上下文屬性fo')fromdual;條件取值【參數(shù)】c1, 注：值1……n不能為條件表達式,這種情況只能用casewhenthenendIF條件=值1ELSIF條件=值2THENELSIF條件=值nENDIFwhencase條件=值1THENElseCase條件=值2THENElseCase條件=值nTHENselectdecode(sign(變量1-變量2),-1,變量1,變量2fromdual取較小值 createorreplaceviewfromsalegroupby相等返回空【語法】NULLIF(expr1返回列表第一個不為空的表達式COALESCE(c1, 【參數(shù)】c1, 【說明】從Oracle9i版開始，COALESCE函數(shù)在很多情況下就成為替代CASE語句的一條捷selectCOALESCE(null,3*5,44)hzfromdual;返回15selectCOALESCE(0,3*5,44)hzfromdual;返回0selectCOALESCE(null,'','AAAhzfromdual返回AAAselectCOALESCE('','AAA')hzfromdual;返回AAA返回當前行號指定一個外部二進制文件X的大小(字節(jié))數(shù)【參數(shù)】selectvsize(user),userfrom返回：6selectlength('adfad合理')"bytesLengthIs"fromdual--7selectlengthb('adfad')"bytesLengthIs"fromdual--5selectlengthb('adfad合理"bytesLengthIsfromdual9selectvsize('adfad合理')"bytesLengthIs"fromdual--9selectlengthc('adfad合理')"bytesLengthIs"fromdual--7條件取值.casewhenthencase表達式when<表達式條件值1>then<滿足條件時返回值1>[when表達式條件值2then滿足條件時返回值2>[else<不滿足上述條件時返回值>]]【功能】當：<表達式>＝<表達式條件值1……n<滿足條件時返回值件時返回值2>……,<不滿足上述條件時返回值>)<表達式默認為true邏輯型>【3、ELSE為除此之外的默認情況，類似于高級語言程序中switchcase的default，可以4、ENDcreatetablexqb(xqninsertintoxqbxqnvalues(1);insertintoxqbxqnvalues(2);insertintoxqbxqnvalues(3);insertintoxqbxqnvalues(4);insertintoxqbxqnvalues(5);insertintoxqbxqnvalues(6);insertintoxqbxqnvalues(7);SELECTWHENxqn1THEN星期一'WHENxqn2THEN星期二'WHENxqn3THEN星期三else星期三以后ENDFROMSELECTCASEWHEN1THEN星期一'WHEN2THEN星期二'WHEN3THEN星期三else星期三以后'END星期FROMxqbSELECTxqn,decode(xqn1星期一2星期二3星期三星期三以后FROMxqbSELECTxqn,decode(TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后星FROMSELECTWHENxqn1THEN星期一WHENxqn2THEN星期二'--條件同：not(xqn<=1andWHENxqn<=3THEN'星期三 --條件同：not(xqn<=1andxqn<=2)else星期三以后'END星期FROM32位的隨機數(shù)【語法】selectsys_guid()from結(jié)果 返回系統(tǒng)數(shù)據(jù)【語法】selectSYS_CONTEXT('USERENV','TERMINAL')terminal,SYS_CONTEXT('USERENV','LANGUAGE')language,SYS_CONTEXT('USERENV','SESSIONID')sessionid,SYS_CONTEXT('USERENV','INSTANCE')instance,SYS_CONTEXT('USERENV','ENTRYID')entryid,SYS_CONTEXT('USERENV','ISDBA')isdba,SYS_CONTEXT('USERENV','NLS_TERRITORY')nls_territory,SYS_CONTEXT('USERENV','NLS_CURRENCY')nls_currency,SYS_CONTEXT('USERENV','NLS_CALENDAR')nls_calendar,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')nls_date_format,SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE')nls_date_language,SYS_CONTEXT('USERENV','NLS_SORT')nls_sort,SYS_CONTEXT('USERENV','CURRENT_USER')current_user,SYS_CONTEXT('USERENV','CURRENT_USERID')current_userid,SYS_CONTEXT('USERENV','SESSION_USER')session_user,SYS_CONTEXT('USERENV','SESSION_USERID')session_userid,SYS_CONTEXT('USERENV','PROXY_USER')proxy_user