1、第89頁 共89頁Oracle PL/SQL 編程手冊(SQL大全) 一、SQLPLUS1引言SQL命令以下17個(gè)是作作為語句開頭頭的關(guān)鍵字：alterddroprevokkeauditggrantrollbback*commit*inserrtselecctcommenttlockupdattecreatenoaudditvaliddatedeleterenamme這些命令必須以以“;”結(jié)尾帶*命令句尾不不必加分號，并并且不存入SSQL緩存區(qū)區(qū)。SQL中沒有的的SQL*PPLUS命令令這些命令不存入入SQL緩存存區(qū)definnepausee#delqquit$descrriberemarrk

2、/discoonnecttrunacceptdocummentsaveappendeditsetbreakeexitshowbtitlegetspoollchangehelpsqlpllusclearhhoststarttcolumninputttiminngcomputeelistttitlleconnecttnewpaageundeffinecopy2數(shù)據(jù)庫查詢詢數(shù)據(jù)字典TAB用戶創(chuàng)創(chuàng)建的所有基基表、視圖和和同義詞清單單DTAB構(gòu)成成數(shù)據(jù)字典的的所有表COL用戶創(chuàng)創(chuàng)建的基表的的所有列定義義的清單CATALOGG用戶可存取取的所有基表表清單select*fromtab;describbe命令

3、描述基表的的結(jié)構(gòu)信息describbedeptselect*fromemmp;selectempnoo,enamme,jobbfromemmp;select*fromdeptorderbbydeptnnodesc;邏輯運(yùn)算符=!=或=.05*saalorderbbycomm/saldesc;日期型數(shù)據(jù)的運(yùn)運(yùn)算addtwoodaysto6-Marr-876-Mar-887+2=8-Marr-87addtwoohourssto6-Marr-876-Mar-887+2/24=6-Marr-87and2hrsadd15seconndsto6-Marr-876-Mar-887+15/(224*60*60

4、)=6-Marr-87and15secs列名的別名selectenameeemplooyeefromempwhereddeptnoo=10;（別名：empployeee）selectenamee,sal,comm,comm/salC/SRATIOOfromempwhereccomm.05*saalorderbbycomm/saldesc;SQL命令的編編輯listorrl顯示緩沖區(qū)區(qū)的內(nèi)容list4顯示當(dāng)前SQQL命令的第第4行，并把把第4行作為為當(dāng)前行，在在該行號后面面有個(gè)*。changeorc用新的內(nèi)容容替換原來在在一行中第一一次出現(xiàn)內(nèi)容容SQLc/(.)/(anaalyst)/input

5、oori增加一行或或多行appendora在一行后追追加內(nèi)容del刪除當(dāng)當(dāng)前行刪除SQL緩緩沖區(qū)中的當(dāng)當(dāng)前行run顯示并并運(yùn)行SQLL緩沖區(qū)中的的命令/運(yùn)行SQLL緩沖區(qū)中的的命令edit把SSQL緩沖區(qū)區(qū)中的命令寫寫到操作系統(tǒng)統(tǒng)下的文本文文件，并調(diào)用操作系統(tǒng)統(tǒng)提供的編輯輯器執(zhí)行修改改。3數(shù)據(jù)操縱數(shù)據(jù)的插入insertintodeptvalues(10,accouuntingg,neewyork);insertintodept(dnamme,depptno)values(acccountiing,110);從其它表中選擇擇插入數(shù)據(jù)insertintoemp(empnno,enaame,deep

6、tno)selectid,naame,deepartmmentfromolld_emppwhereddeparttmentin(100,20,330,40);使用參數(shù)insertintodeptvalues(&depttno,&ddname,&loc);執(zhí)行時(shí)，SQLL/PLUSS對每個(gè)參數(shù)數(shù)將有提示用用戶輸入?yún)?shù)對應(yīng)日期型型或字符型數(shù)數(shù)據(jù)時(shí)，可在在參數(shù)上加引引號，輸入時(shí)時(shí)就可不用引引號insertintodeptvalues(&depttno,&dnamee,&lloc);插入空值（NUULL）insertintodeptvalues(50,eeducattion,null);插入日期型數(shù)據(jù)

7、據(jù)日期型數(shù)據(jù)缺省省格式：DDD-MON-YYinsertintoemp(empno,enamee,hireedate)values(7963,stonne,007-APRR-87);系統(tǒng)時(shí)間：SYYSDATEEinsertintoemp(empno,enamee,hireedate)values(7600,kohnn,SYSSDATE);數(shù)據(jù)更新updateempsetjobb=mannagerwhereeename=marttin;updateempsetjobb=marrketrepwhereeename=saleesman;updateempsetdepptno=440,jobb=mar

8、rketrepwherejjob=ssalesmman;數(shù)據(jù)刪除deleteempwhereeempno=765;更新的提交commit自動(dòng)提交方式setauttocommmiton如果狀態(tài)設(shè)為開開，則使用iinesrtt,updaate,deelete會(huì)會(huì)立即提交。更新取消rollbacck兩次連續(xù)成功的的commiit之間的操操作，稱為一一個(gè)事務(wù)4創(chuàng)建基表、視視圖創(chuàng)建基表createtableedept(deptnoonumbeer(2),dnamecchar(114),locchaar(13);數(shù)據(jù)字典會(huì)自動(dòng)動(dòng)更新。一個(gè)基表最多2254列。表名列名命名規(guī)規(guī)則：限制第一個(gè)字符必須須是字母，

9、后后面可任意（包包括$#_但不能是逗逗號）。名字不得超過330個(gè)字符。唯一某一用戶的基表表名必須唯一一，不能是OORACLEE的保留字，同同一基表的列列名互不相同同。使用雙引號如果表名用雙引引號括起來，則則可不滿足上上述規(guī)則；只有使用雙引號號，才能區(qū)別別大、小寫；命名時(shí)使用了雙雙引號，在以以后的操作也也必須使用雙雙引號。數(shù)據(jù)類型：char(n)（不得超過過240字符符）number(n,d)datelong（最最多655336字符）raw（二進(jìn)進(jìn)制原始數(shù)據(jù)據(jù)）空值處理有時(shí)要求列值不不能為空createtableedept(deptnoonumberr(2)notnull,dnamecchar(

10、114),locchaar(13);在基表中增加一一列alterttabledeptadd(heeadcnttnumbeer(3);修改已有列屬性性alterttabledeptmodifydnameechar(20);注：只有當(dāng)某列列所有值都為為空時(shí)，才能能減小其列值值寬度。只有當(dāng)某列所有有值都為空時(shí)時(shí)，才能改變變其列值類型型。只有當(dāng)某列所有有值都為不空空時(shí)，才能定定義該列為nnotnull。例：alterttabledeptmodiffy(locchar(12);alterttabledeptmodiffylocchar(12);alterttabledeptmodiffy(dnammec

11、har(13),llocchar(12);創(chuàng)建視圖createviewmanaggersasselectenamee,job,salfromemmpwherejjob=mmanageer;為視圖列名取別別名createviewmydeppt(personn,titlle,sallary)asseleectenamee,job,salfromemmpwhereddeptnoo=10;withchheckoptioon選項(xiàng)使用withcheckkoptioon，保證當(dāng)當(dāng)對視圖插入入或更新數(shù)據(jù)據(jù)時(shí)，該數(shù)據(jù)必須滿足足視圖定義中中selecct命令所指指定的條件。createviewdept220ass

12、electenamee,job,sal,ddeptnoofromemmpwhereddeptnoo=20withchheckoptioon;在做下述操作時(shí)時(shí)，會(huì)發(fā)生錯(cuò)錯(cuò)誤updatedept220setdepptno=330whereeename=wardd;基表、視圖的拷拷貝createtableeemp2asseleect*fromemp;基表、視圖的刪刪除droptaable表名dropviiew視圖名5SQL*PPLUS報(bào)表表功能SQL*PLUUS的一些基基本格式命令令columndeptnnoheadiingdeparrtmenttcolumnenameeheadiingnameco

13、lumnsalheadiingsalarrycolumnsalformaat$99,9999.000ttitlesampllereporrtforhhitechhcorpbtitlestricctlyconfiidentiialbreakoondeptnnocomputeesumofsalondeptnnorun表頭和表尾ttitlesampllereporrtforhhitechhcorpbtitlerighttstricttlyconfiidentiial“”表示換行行，結(jié)尾不必必加分號選項(xiàng)有三種：lleftrighttcenteer使用TTITLLE，系統(tǒng)將將自動(dòng)地在每每頁的頂部顯顯示日

14、期和頁頁號。TTITLETT和BTITTLE命令有有效，直至重重新設(shè)置表頭頭或表尾，或或退出SQLL*PLUSS。下面命令使標(biāo)題題語句失效TTITLEOFFBTITLEOFF列名column命命令定義用于于顯示列名若名字為一個(gè)單單詞，不必加加引號columnenameeheadiingemplooyeecolumnenameheadiingemplloyeename（為換行）取消欄定義columnenameeclearr列的格式columnenameeformaatA15columnsalformaat$9,9999.99columncommlikesallike子句，使使得某一列的的格式參照

15、另另一列格式，它它拷貝列名及及其格式控制記錄顯示分分組順序breakoondeptnno（不顯示重復(fù)值值）selectdeptnno,enaamefromemmporderbbydeptnno;（ORDERBY子句用用于控制BRREAK）顯示為10clarrkniller20smitthscott30alleenblake每次只有一個(gè)BBREAK命命令起作用，但但一次可以在在多個(gè)列上使使用BREAAK命令breakoon列名1on列名2記錄分組breakoondeptnnoskip2selectdeptnno,enaamefromemmporderbbydeptnno;每個(gè)deptnno之間空

16、兩兩行clearbbreak（取取消BREAAK命令）breakoonpage（每每次從一新頁頁開始）breakoonreporrt（每次從從一新報(bào)表開開始）breakoonpageonreporrt（聯(lián)合使使用）分組計(jì)算breakoondeptnnoskip2computeesumofsalondeptnno計(jì)算每個(gè)部門的的工資總和skip子句使使部門之間的的信息分隔開開其他計(jì)算命令computeeavgofsalondeptnno（平均值值）count非非空值的總數(shù)數(shù)MAX最大值值MIN最小值值STD標(biāo)準(zhǔn)偏偏差VAR協(xié)方差差NUMBER行數(shù)使computte命令失效效一旦定義了COOMPU

17、TEE，則一直有有效，直到關(guān)閉COMPUUTE（cllearcompuute）SQL/PLUUS環(huán)境命令令show選項(xiàng)項(xiàng)（顯示當(dāng)前參數(shù)數(shù)設(shè)置情況）showalll（顯示全全部參數(shù)）設(shè)置參數(shù)set選項(xiàng)值或開關(guān)setauttocommmitonSET命令包括括setauttocommmitoffonimmmediaate（自動(dòng)提交，OOFF缺省）setechhooffon（命令文件執(zhí)行行，是否在終終端上顯示命命令本身，OOFF缺?。﹕etfeeedbackkoffon（ON：查詢結(jié)結(jié)束時(shí)，給出出結(jié)果，記錄錄數(shù)的信息，缺缺??；OFF：無查詢詢結(jié)果，記錄錄數(shù)的信息）setheaadingoffon（O

18、N：列的頭頭標(biāo)在報(bào)表上上顯示，缺省??；OFF：不在報(bào)表上上顯示）setlinnesizeen一行顯示的最大大字符數(shù)，缺缺省為80setpaggesizeen每頁的行數(shù)，缺缺省是14setpauuseoffonteext（ON：當(dāng)報(bào)表表超過一頁時(shí)時(shí)，每顯示一一屏就暫停顯顯示，等待用用戶打回車鍵鍵，再接著顯顯示；OFF：頁與頁頁不停頓，缺缺省；texxt：頁與頁頁停頓，并向向用戶提示信信息）SETBUFFFERbuffeer設(shè)置當(dāng)頭的命令令緩沖區(qū)，通通常情況下，SSQL命令緩緩沖區(qū)已為當(dāng)當(dāng)前緩沖區(qū)。由于SQL命令令緩沖區(qū)只能能存放一條SSQL命令，所以可用其它緩緩沖區(qū)來存放放SQL命令令和SQL*

19、PLUS命命令。經(jīng)常用到的設(shè)置置可放在loogin.ssql文件中中。SETNULLLsetnulllnodataselectenamee,commmfromemmpwhereddeptnoo=30;把部門30中無無傭金雇員的的傭金顯示為為“NODATA”。setnulll是SQLL*PLUSS命令，用它它來標(biāo)識空值值（NULLL），可以設(shè)設(shè)置為任意字字符串。存盤命令SAVVEsave文件件名input1selecctempnoo,enamme,jobb2fromemp3whereejob=analyystsavereesearcch目錄中會(huì)增加一一個(gè)reseearch.sql文件件。編輯命令

20、EDIITeditEDIT編輯當(dāng)當(dāng)前緩沖區(qū)中中的內(nèi)容。編輯一個(gè)文件editreesearcch調(diào)入命令GETTgetressearchh把磁盤上的文件件內(nèi)容調(diào)入緩緩沖區(qū)，并顯顯示在屏幕上上，文件名尾尾不必加.ssql。START命令令運(yùn)行指定的文件件startrresearrch輸出命令SPOOOLspoolttryfille不僅可以使查詢詢結(jié)果在屏幕幕上顯示，還還可以使結(jié)果果存入文件停止向文件輸出出spoolooff把查詢結(jié)果在打打印機(jī)上輸出出，先把它們們存入一個(gè)文文件中，然后不必使用SSPOOLOFF，而而用：spoolooutSPOOLOOUT關(guān)閉該該文件并在系系統(tǒng)缺省的打打印機(jī)上輸出出

21、制作報(bào)表舉例edittrryfileesetechhooffsetauttocommmitonsetpaggesizee25insertintoemp(empnno,enaame,hiiredatte)values(9999,geigger,ssysdatte);insertintoemp(empnno,enaame,deeptno)values(3333,samsson,220);spoolnnew_emmpselect*fromempwhereddeptnoo=20ordepttnoisnull/spoolooffsetauttocommmitoff用start命命令執(zhí)行這個(gè)個(gè)文件6函數(shù)字

22、符型函數(shù)initcapp(enamme);將eename中中每個(gè)詞的第第一個(gè)字母改改為大寫。如：jacksmithh-JacckSmithhlength(enamee);計(jì)算字字符串的長度度。substr(job,11,4);其它lowerupperleast取取出字符串列列表中按字母母排序排在最最前面的一個(gè)個(gè)串greatesst取出字符串串列表中按字字母排序排在在最后的一個(gè)個(gè)串日期函數(shù)add_monnth(hiiredatte,5)在雇傭時(shí)間間上加5個(gè)月月month_bbetweeen(syssdate,hireddate)計(jì)算雇傭時(shí)時(shí)間與系統(tǒng)時(shí)時(shí)間之間相差差的月數(shù)next_daay(hir

23、redatee,FRIIDAY)計(jì)算受雇日日期之后的第第一個(gè)星期五五的日期例selectenamee,sal,next_day(ssysdatte,FRRIDAY)as_offfromemmpwhereddeptnoo=20;（as_of是是別名）如果不用to_char函函數(shù)，日期在在ORACLLE中的缺省省格式是DDD_MONN_YYto_charr(datee,dateepictuure)selectenamee,to_cchar(hhiredaate,DDyMondd,yyyyy)hireddfromemmpwhereddeptnoo=10;to_datee(字符串,格式)inserti

24、ntoemp(eempno,enamee,hireedate)values(7999,asmss,to_date(07033870833000,MMDDDYYHHMMISS);日期型數(shù)據(jù)的格格式dd12dyfridayfriidayddspthtwelffthmm03monmarrmonthmmarchyy87yyyy19987例Mar12,1987Mondd,yyyyyMAR12,1987MONdd,yyyyyThursdaayMARCHH12DayMONTHHddMar1211:000amMonddhh:miiamThu,theetwelffthDy,thedddspthh算術(shù)函數(shù)leas

25、t(vv1,v2)selectenamee,empnno,mgrr,leasst(emppno,mggr)lownuumfromemmpwhereeempno00trunc(ssal,0)取sal的近似似值（截?cái)啵┛罩岛瘮?shù)nvl(v1,v2)v1為列名，如如果v1不是是空值，nvvl返回其列列值。v1為空值，返返回v2的值值。聚組函數(shù)selectsum(ccomm)fromemmp;（返回一個(gè)匯總總信息）不能把sum用用在seleect語句里里除非用grroupby字符型、日期型型、數(shù)字型的的聚組函數(shù)minmaxxcountt可用于任何何數(shù)據(jù)類型selectmin(eename)fromemm

26、p;selectmin(hhiredaate)fromemmp;selectmin(ssal)fromemmp;有多少人有工作作？selectcountt(job)fromemmp;有多少種不同的的工種？selectcountt(disttinctjob)fromemmp;countddistinnct計(jì)算某一字字段中不同的的值的個(gè)數(shù)其它聚組函數(shù)（只只用于數(shù)字型型數(shù)據(jù)）avg計(jì)算平平均工資selectavg(ssal)fromemmp;stddev計(jì)算工資的的平均差selectstddeev(sall)fromemmp;sum計(jì)算總總工資selectsum(ssal)fromemmp;grou

27、pbby子句selectdeptnno,summ(sal),avg(sal)fromemmpgroupbbydeptnno;按多個(gè)條件分組組每個(gè)部門的雇員員數(shù)selectdeptnno,couunt(*)fromemmpgroupbbydeptnno;每個(gè)部門的每個(gè)個(gè)工種的雇員員數(shù)selectdeptnno,jobb,counnt(*)fromemmpgroupbbydeptnno,jobb;滿足條件的分組組（where是是針對sellect的，hhavingg是針對grroupby的）哪些部門的工資資總和超過了了9000selectdeptnno,summ(sal)fromemmpgroup

28、bbydeptnnohavingsum(ssal)99000;select小小結(jié)除去職員，哪些些部門的工資資總和超過了了8000selectdeptnno,summ(sal)fromemmpwherejjob!=clerkkgroupbbydeptnnohavingsum(ssal)88000orderbbysum(ssal);7高級查詢等值聯(lián)接selectempnoo,enamme,jobb,emp.deptnno,dnaamefromemmp,depptwhereeemp.deeptno=dept.deptnno;外聯(lián)接selectenamee,deptt.depttno,loocfrom

29、emmp,depptwhereeemp.deeptno(+)=deept.deeptno;如果在deptt.depttno中有的的數(shù)值，在eemp.deeptno中中沒有（如ddeptnoo=40），則作外聯(lián)接時(shí)，結(jié)結(jié)果中會(huì)產(chǎn)生生一個(gè)空值自聯(lián)接：同一基基表的不同行行要做聯(lián)接，可可使用自聯(lián)接接指出每個(gè)雇員的的經(jīng)理名字selectworkeer.enaame,maanagerr.enammemanaggerfromemmpworkeer,emppmanaggerwherewworkerr.mgr=managger.emmpno;非等值聯(lián)接哪些雇員的工資資屬于第三級級別selectenamee,sa

30、lfromemmp,sallgradeewhereggrade=3andsallbetweeenlosallandhisall;（基表salggrade：gradeelosallhisall）集合運(yùn)算行的連接集合運(yùn)算把2個(gè)個(gè)或多個(gè)查詢詢結(jié)果合并為為一個(gè)union-ssetunionnRowsofffirsttqueryyplusofseconndqueryy,lessdupliicaterowsinterseect-seetinterrsectiionRowsboothqueriieshaveincommoonminus-ssetdiffeerenceerowsunniquetothefirs

31、ttqueryy介紹幾個(gè)視圖accounttviewenamessaljobsalesvviewenamessaljobresearcchviewenamessaljobunion運(yùn)算算返回一個(gè)查詢結(jié)結(jié)果中有但又又不重要的行行，它將基表表或視圖中的的記錄合并在在一起所有部門中有哪哪些雇員工資資超過20000對應(yīng)列的數(shù)據(jù)類類型必須相同同selectenamee,salfromacccounttwheressal20000unionselectenamee,salfromreesearcchwheressal20000unionselectenamee,salfromsaaleswheressal

32、20000;interseect運(yùn)算返回查詢結(jié)果中中相同的部分分各個(gè)部門中有哪哪些相同的工工種selectjobfromacccounttinterseectselectjobfromreesearcchinterseectselectjobfromsaales;minus運(yùn)算算返回在第一個(gè)查查詢結(jié)果中與與第二個(gè)查詢詢結(jié)果不相同同的那部分行行記錄。有哪些工種在財(cái)財(cái)會(huì)部中有，而而在銷售部中中沒有？selectjobfromaccouuntminusselectjobfromsaless;子查詢slecteename,deptnnofromemmpwhereddeptnoo=(selecttdep

33、tnnofromemmpwhereeename=smitth);多級子查詢selectenamee,job,salfromemmpwherejjob=(selecttjobfromemmpwhereeename=clarrk)orsal(selecttsalfromemmpwhereeename=clarrk);多個(gè)基表與子查查詢selectenamee,job,salfromemmp,depptwherelloc=nnewyorkandempp.depttno=deept.deeptnoandsall(selecttsalfromemmpwhereeename=scottt);子查詢中使用聚

34、聚組函數(shù)selectenamee,hireedatefromemmpwherehhiredaate=(selecttmin(hhiredaate)fromemmp);8授權(quán)系統(tǒng)權(quán)限D(zhuǎn)BA所有權(quán)權(quán)限RESOURCCE注冊，創(chuàng)建建新的基表CONNECTT，注冊，查查詢只有DBA才有有權(quán)創(chuàng)建新的的用戶grantcconneccttoscotttidentiffiedbytigerr;DBA或用戶自自己可以改變變用戶口令grantcconneccttoscotttidentiffiedbyleopaard;基表權(quán)限1有兩種方法獲得得對基表操作作的權(quán)限創(chuàng)建自己的基表表獲得基表創(chuàng)建用用戶的許可grants

35、selectt,inseertonemptoscottt;這些權(quán)限有selectinserrtupdattedelettealterrindexx把所有權(quán)限授于于他人grantaallonemptoscottt;同義詞select*fromsccott.eemp創(chuàng)建同義詞為用戶alleen的EMPP基表創(chuàng)建同同義詞empployeeecreatesynonnymemplooyeeforalllen.emmp基表權(quán)限2你可以使其他用用戶有這樣的的權(quán)力，即其其他用戶可以以把你的基表表權(quán)限授予他他人grantaallonemptoscotttwithgrrantoptioon;收回權(quán)限系統(tǒng)權(quán)限只有有

36、被DBA收收回基表權(quán)限隨時(shí)時(shí)都可以收回回revokeinserrtonempfromsccott;9索引建立索引createindexxemp_eenameonemp(enamee);刪除索引dropinndexemp_eename;關(guān)于索引只對較大的基表表建立索引（至至少50條記記錄）建立索引之前插插入數(shù)據(jù)對一個(gè)基表可建建立任意多個(gè)個(gè)索引一般是在作為主主鍵的列上建建立索引建立索引之后，不不影響SQLL命令的執(zhí)行行建立索引之后，OORACLEE自動(dòng)維護(hù)和和使用索引保證數(shù)據(jù)唯一性性提高執(zhí)行速度的的同時(shí)，索引引還可以保證證每個(gè)記錄中中的每個(gè)列值值是不重復(fù)的的。createuniquueindexx

37、emp_eempnoonemp(empnoo);練習(xí)和答案有沒有工資比獎(jiǎng)獎(jiǎng)金多的雇員員？如果有，按按工資的降序序排列。如果有兩個(gè)以上上的雇員工資資相同，按他他們的名字排排序。selectenameeemplooyee,ssalsalarry,commmcommiisionfromemmpwheressalcoommorderbbysaldesc,enamee;列出有關(guān)雇員姓姓名、獎(jiǎng)金占占收百分比的的信息。要求顯示時(shí)列名名意義明確，按按雇員姓名排排序，不包括括獎(jiǎng)金未知的的雇員。selectenameeemplooyee,(comm/(commm+sal)*1000incenntivefromem

38、mpwhereccommisnotnullorderbbyenamee;在chicaggo（部門330）工作的的所有雇員的的工資上漲110%。updateempsetsall=1.1*salwhereddeptnoo=30;updateempsetsall=1.1*salwhereddeptnoo=(sellectdeptnnofromdeeptwherelloc=cchicaggo);為hitechh公司新建一一個(gè)部門，編編號為50，其其它信息均不不可知。insertintodept(dnamee,depttno)values(facllitiess,50);創(chuàng)建視圖，三個(gè)個(gè)列名，其中中不包

39、括職員員信息createviewemplooyee(emplooyeename,employyeenumbeer,employyeejob)asseleectenamee,empnno,jobbfromemmpwherejjob!=clerkk;制作工資報(bào)表，包包括雇員姓名名、受雇時(shí)間間（按星期計(jì)計(jì)算），工資資和部門編號號，一頁顯示一個(gè)部部門的信息，每每頁尾，顯示示該頁的工資資之和以及受受雇時(shí)間之和和，報(bào)表結(jié)尾處，顯顯示所有雇員員的工資總和和以及受雇時(shí)時(shí)間總和，工資按美元計(jì)算算，受雇時(shí)間間按星期計(jì)算算，每頁的上上方應(yīng)有標(biāo)題題。ttitleservvicebreakoondeptnnoonpag

40、eonreporrtcomputeesumofsalondeptnnocomputeesumofsalonreporrtcomputeesumofserviice_leengthondeptnnocomputeesumofserviice_leengthonreporrtcolumnsalformaat$99,9999.000columnserviice_leengthformaat9999selectdeptnno,enaameemplooyee,(sysdaate-hiiredatte)/7serviice_leength,salfromemmporderbbydeptnno;制作報(bào)表，包

41、括括雇員姓名、總總收入和受傭傭日期，且：姓名的第一一個(gè)字母必須須大寫，雇傭傭日期格式為為MM/DDD/YYYYY，總收入包括沒有有獎(jiǎng)金的雇員員的總收入，姓姓名按字母順順序排列。colhiiredateformaatA12colemmployeeeformaatA10colcoompenssationnformaat$99,9999.000selectinitccap(enname)emplloyee,(sal+nvvl(commm,0)comppensattion,to_charr(hireedate,MM/DDD/YYYYY)hireedatefromemmporderbbyenamee;列

42、出有超過7個(gè)個(gè)周邊國家的的國家名字和和面積。selectnatioon,areeafromnaationwhereccodein(selecttnatioon_coddefromboordergroupbbynatioon_coddehavingcountt(*)77);列出所有面積大大于等于日本本的島國的國國名和人口。selectnatioon,poppulatiionfromnaation,bordeerwhereccode=nnationn_codee(+)andnattion_ccodeisnullandareea=(selecttareafromnaationwhereuupper(

43、natioon)=JJAPAN);列出所有邊界在在其它國家中中的國名，并并且顯示其邊邊界國家名字字。breakoonnatioonselectnatioon1.naation,nation22.natiionborderrin_coountryyfromnaationnatioon1,boorder,natioonnatioon2wherennationn1.codde=borrder.nnationn_codeeandborrder.bborderr_codee=natiion2.ccodeorderbbynatioon1.naation;PL/SQL2PL/SQQL的塊結(jié)構(gòu)構(gòu)和數(shù)據(jù)類型型塊

44、結(jié)構(gòu)的特點(diǎn)嵌套beginbeginexceptiionend;exceptiionend;標(biāo)識符：不能超過30個(gè)個(gè)字符第一個(gè)字符必須須為字母其余字符可以是是字母，數(shù)字字，$，_，或或#不區(qū)分大小寫形形式如果用雙引號括括起來，則字字符順序可以以任意排列，并并區(qū)分大小寫寫形式無SQL保留字字?jǐn)?shù)據(jù)類型數(shù)字型：整數(shù)，實(shí)數(shù)，以以及指數(shù)字符串：用單引號括起來來若在字符串表示示單引號，則則使用兩個(gè)單單引號字符串長度為零零（兩個(gè)單引引號之間沒有有字符），則則表示NULLL字符：長度為1的字符符串?dāng)?shù)據(jù)定義語法標(biāo)識符常數(shù)數(shù)據(jù)類型NNOTNULL:=PLL/SQL表表達(dá)式;:=表示給給變量賦值數(shù)據(jù)類型包括數(shù)字型nu

45、mmber(77,2)字符型chaar(1200)日期型datte布爾型booolean（取取值為truue,fallse或nuull，不存存貯在數(shù)據(jù)庫庫中）日期型anniverrsarydate:=05-JUL-995;projectt_comppletioondate;布爾型over_buudgetbooleeannotnull:=falsee;availabblebooleean;（初始值為NUULL）%type類型型匹配books_pprinteednumbeer(6);books_ssoldbook_printted%tyype;managerr_nameeemp.eename%ty

46、pe;變量賦值變量名:=PLL/SQL表表達(dá)式numvar:=5;boolvarr:=truue;datevarr:=111-JUN-87;字符型、數(shù)字型型表達(dá)式中的的空值null+=nuull（空值值加數(shù)字仍是是空值）null=nuull（空值值與數(shù)字進(jìn)行行比較，結(jié)果果仍是空值）null字符串=字符串（nulll即）（空值與字符串串進(jìn)行連接運(yùn)運(yùn)算，結(jié)果為為原字符串）變量作用范圍標(biāo)識符在宣言它它的塊中有效效標(biāo)識符如果不在在子塊中重新新定義，則在在PL/SQQL塊的所有有子塊中同樣樣有效重新定義后的標(biāo)標(biāo)識符，作用用范圍僅在本本子塊中有效效例declareee_messchar(80);begin

47、/*子塊1*/declareev1numbber(4);beginselectempnoointov1fromempwherejjob=ppresiddent;exceptiionwhentooo_manny_rowwstheninsertintojob_eerrorssvalues(moreethanonepresiident);end;/*子塊2*/declareev1numbber(4);beginselectempnoointov1fromempwherejjob=mmanageer;exceptiionwhentooo_manny_rowwstheninsertintojob_eer

48、rorssvalues(moreethanonemanagger);end;exceptiionwhenotthersthene_mess:=subsstr(sqqlerrmm,1,800);insertintogenerralerrorrsvaluees(e_mmess);end;3SQL和PPL/SQLL插入declareemy_salnumbeer(7,22):=30040.555;my_enammechar(25):=wandda;my_hireedatedate:=08-SEP-888;begininsertintoemp(empno,enmaee,job,hireddate,ssa

49、l,deeptno)values(2741,my_enname,cabdriveer,myy_hireedate,my_saal,20);end;刪除declareebad_chiild_tyypechar(20):=naugghty;begindeletefromsantaas_gifft_lisstwhereekid_ratting=bbad_chhild_ttype;end;事務(wù)處理commitWORK;rollbacckWORRK;（關(guān)鍵字WORRK可選，但但對命令執(zhí)行行無任何影響響）savepoiint標(biāo)記名;（保保存當(dāng)前點(diǎn)）在事務(wù)中標(biāo)記當(dāng)當(dāng)前點(diǎn)rollbacckWORKKtoSAV

50、EEPOINTT標(biāo)記名;（回回退到當(dāng)前保保存點(diǎn)）取消saveppoint命命令之后的所所有對數(shù)據(jù)庫庫的修改關(guān)鍵字WORKK和SAVEEPOINTT為可選項(xiàng)，對對命令執(zhí)行無無任何影響函數(shù)PL/SQL塊塊中可以使用用SQL命令令的所有函數(shù)數(shù)insertintophoneebook(lastnname)valuee(uppeer(my_lastnname);selectavg(ssal)intoavg_ssalfromemp;對于非SQL命命令，可使用用大多數(shù)個(gè)體體函數(shù)不能使用聚組函函數(shù)和參數(shù)個(gè)個(gè)數(shù)不定的函函數(shù)，如x:=sqrtt(y);lastnamme:=uppper(llastnaame);a

51、ge_difff:=moonths_betweeen(biirthdaay1,biirthdaay2)/112;賦值時(shí)的數(shù)據(jù)類類型轉(zhuǎn)換4種賦值形式:變量名:=表達(dá)達(dá)式insertinto基表名valuees(表達(dá)式式1,表達(dá)式式2,);update基表名set列名=表達(dá)式式;select列名into變量名from.;數(shù)據(jù)類型間能進(jìn)進(jìn)行轉(zhuǎn)換的有有:char轉(zhuǎn)成nnumberrnumber轉(zhuǎn)轉(zhuǎn)成charrchar轉(zhuǎn)成ddatedate轉(zhuǎn)成cchar例char_vaar:=nmm_var;數(shù)字型轉(zhuǎn)換成字字符型date_vaar:=225-DECC-88;字符型轉(zhuǎn)換成日日期型insertinto表名(

52、numm_col)valuees(6004badnnumberr);錯(cuò)誤，無法成功功地轉(zhuǎn)換數(shù)據(jù)據(jù)類型4條件控制例declareenum_jobbsnumbeer(4);beginselectcountt(*)intonum_jjobsfromaudittionswhereaactoriid=&aactor_idandcalleed_bacck=yees;ifnum_jobs100thenupdateactorrsetactorr_ratiing=wwordclassswhereaactoriid=&aactor_id;elsifnnum_joob=75thenupdateactorrsetac

53、torr_ratiing=ddaytimmesoapsswhereaactoriid=&aactor_id;elseupdateactorrsetactorr_ratiing=wwaiterrwhereaactoriid=&aactor_id;endif;endif;commit;end;5循環(huán)語法loopendlooop;exit;（退退出循環(huán)）exitwwhen;（退出循環(huán)環(huán)，當(dāng)滿足WWHEN時(shí)）例1declareectrnummber(33):=0;beginloopinsertintotablee1valuees(taastesgreatt);insertintotablee2valu

54、ees(leessfilliing);ctr:=cttr+1;exitwhhenctr=1100;endlooop;end;（注：如果cttr取為NUULL，循環(huán)環(huán)無法結(jié)束）例2FOR語法for變量loopendlooop;declareemy_indeexchar(20):=fetttuciniialfreedo;bowlchhar(200);beginformy_indexxinreverrse21.330loopinsertintotemp(coll)valuees(my_indexx);/*循環(huán)次數(shù)從從30到211*/endlooop;bowl:=mmy_inddex;end;跟在in

55、reeversee后面的數(shù)字字必須是從小小到大的順序序，必須是整整數(shù)，不能是是變量或表達(dá)達(dá)式6游標(biāo)顯式游標(biāo)打開游標(biāo)open例opencoolor_ccur;游標(biāo)屬性%notfouund%found%rowcouunt%isopenn例fetchmmy_currintomy_vaar;whilemmy_curr%founndloop(處理數(shù)據(jù))fetchmmy_currintomy_vaar;exitwhhenmy_cuur%rowccount=10;endlooop;%notfouund屬性取值情況如下：fetch操作作沒有返回記記錄，則取值值為trueefetch操作作返回一條記記錄，則取值

56、值為falsse對游標(biāo)無fettch操作時(shí)時(shí)，取值為nnull%notfoound例ifcoloor_curr%notffoundthen注：如果沒有ffetch操操作，則%notffound將將導(dǎo)致出錯(cuò)，因?yàn)?notffound的的初始值為NNULL。關(guān)閉游標(biāo)close例closeccolor_cur;游標(biāo)的FOR循循環(huán)語法forinloopendlooop;其中：索引是建立在每每條記錄的值值之上的記錄名不必聲明明每個(gè)值對應(yīng)的是是記錄名，列列名初始化游標(biāo)指打打開游標(biāo)活動(dòng)集合中的記記錄自動(dòng)完成成FETCHH操作退出循環(huán)，關(guān)閉閉游標(biāo)隱式游標(biāo)隱式游標(biāo)是指SSQL命令中中用到的，沒沒有明確定義義的游

57、標(biāo)insert,updatte,dellete,sselectt語句中不必必明確定義游游標(biāo)調(diào)用格式為SQQL%存貯有關(guān)最新一一條SQL命命令的處理信信息隱式游標(biāo)的屬性性隱式游標(biāo)有四個(gè)個(gè)屬性SQL%NOTTFOUNDDSQL%FOUUNDSQL%ROWWCOUNTT：隱式游標(biāo)標(biāo)包括的記錄錄數(shù)例：deletefrombasebball_tteamwhereebattiing_avvg55thninsertintotempvalues(yourrteamneedsshelp);endif;SQL%ISOOPEN：取取值總為FAALSE。SSQL命令執(zhí)執(zhí)行完畢，PPL/SQLL立即關(guān)閉隱隱式游標(biāo)。7標(biāo)

58、號GOTO語句用法：gotoyoou_aree_heree;其中you_aare_heere是要跳跳轉(zhuǎn)的語句標(biāo)標(biāo)號標(biāo)號必須在同一一組命令，或或是同一塊中中使用正確的使用（標(biāo)號）x:=x+1ifabthenb:=b+c;gotodiinner;endif;錯(cuò)誤的使用gotojaail;ifabthenb:=b+c;（標(biāo)號）x:=x+1;endif;標(biāo)號：解決意義義模糊標(biāo)號可用于定義義列值的變量量declareedeptnonumbeer:=200;beginupdateempsetsal=ssal*1.1whereddeptnoo=sampple.deeptno;commit;endsammpl

59、e;如果不用標(biāo)號和和標(biāo)號限制符符，這條命令令將修改每條條記錄。8異常處理預(yù)定義的異常情情況任何ORACLLE錯(cuò)誤都將將自動(dòng)產(chǎn)生一一個(gè)異常信息息一些異常情況已已命名，如：no_dataa_founnd當(dāng)SELECCT語句無返返回記錄時(shí)產(chǎn)產(chǎn)生too_manny_rowws沒有定義游游標(biāo)，而SEELECT語語句返回多條條記錄時(shí)產(chǎn)生生wheneveernotfoound無對應(yīng)的記記錄用戶定義的異常常情況由用戶自己獲取取在DECLARRE部分定義義：declareexnumbeer;somethiing_issnt_riightexcepption;用戶定義的異常常情況遵循一一般的作用范范圍規(guī)則條件滿足

60、時(shí)，獲獲取異常情況況：raissesometthing_isnt_rightt注意：同樣可以以獲取預(yù)定義義的異常情況況exceptiion_innit語句允許為ORACCLE錯(cuò)誤命命名調(diào)用格式：pragmaexcepption_init(,);例declareedeadlocck_dettecteddexcepption;pragmaexcepption_init(deadllock_ddetectted,-660);raise語句句單獨(dú)使用RAIISE命令，可可再一次獲取取當(dāng)前的異常常情況（就象象異常情況被被重復(fù)處理了了一樣）。在異常處理中，此此語句只能單單獨(dú)使用。異常處理標(biāo)識符符一組用于處