PL_SQL基礎(chǔ)培訓(xùn)PPT課件

.,1,PL/SQL基礎(chǔ)培訓(xùn)劉丹2012.8.25,CUC-AS1-N3,.,2,.,3,PL/SQL語言的編程規(guī)范Oracle數(shù)據(jù)庫的基本數(shù)據(jù)類型和函數(shù)應(yīng)用PL/SQL語言編寫基本的sql,通過本章的學(xué)習(xí)應(yīng)該掌握：,.,4,一、PL/SQL語言編程規(guī)范,1）命名語言-命名使用能表述對(duì)象意義的單詞或者約定俗成的符號(hào)，不允許使用中文或者特殊符號(hào)。單詞分隔-命名的各單詞使用下劃線進(jìn)行分隔。保留字-命名禁止使用sql保留字（可以在v$reserved_words視圖中查找所有的保留字）。命名長度-限制在30個(gè)字符內(nèi)（含前綴）。,.,5,2）命名規(guī)范普通變量-前綴為v_,存放字符、數(shù)字、日期型變量。游標(biāo)變量-前綴為cur_,存放游標(biāo)記錄集。存儲(chǔ)過程-前綴為p_,按照業(yè)務(wù)操作命名。函數(shù)-前綴為f_,按照業(yè)務(wù)操作命名。包-前綴為pkg_,按照業(yè)務(wù)操作集合命名。觸發(fā)器-前綴為trig_,觸發(fā)器名是前綴+表名+觸發(fā)器名索引-前綴為IDX_,索引名是前綴+表名+索引字段,.,6,3）使用綁定變量當(dāng)一個(gè)sql語句提交后，oracle會(huì)首先檢查一下共享緩沖池（sharedpool）里有沒有與之完全相同的語句，如果有的話只須執(zhí)行軟分析即可，否則就得進(jìn)行硬分析。而唯一使得oracle能夠重復(fù)利用執(zhí)行計(jì)劃的方法就是采用綁定變量。綁定變量的實(shí)質(zhì)就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次提交的sql語句都完全一樣硬解析需要經(jīng)解析,制定執(zhí)行路徑,優(yōu)化訪問計(jì)劃等許多的步驟.如果你的查詢本身耗時(shí)就很長，就沒必要綁定變量了，因?yàn)榻馕龅南奈⒑跗湮electsql_text,parse_callsfromv$sqlwheresql_textlikeUPDATEAA1%eg:ls_sql:=updateAA1setstatus=1,sale_trade_id=|v_trade_id|,sale_date=sysdatewherecard_no=:1;executeimmediatels_sqlusingvi_card_no;,.,7,PL/SQL語言,1）基本數(shù)據(jù)類型VARCHAR2變長字符串，最長為4000字符。NUMBER數(shù)值型。LONG變長字符數(shù)據(jù)，最長為2G字節(jié)。DATE日期型。RAW二進(jìn)制數(shù)據(jù)，最長為255字節(jié)。LONGRAW變長二進(jìn)制數(shù)據(jù)，最長為2G字節(jié)。CHAR定長字符數(shù)據(jù)，最長為255。LOB大對(duì)象”數(shù)據(jù)類型CLOB可用于存儲(chǔ)非結(jié)構(gòu)化的XML文檔。BLOB可以存儲(chǔ)較大的二進(jìn)制對(duì)象，如圖形、視頻剪輯和聲音剪輯等。,二、基本數(shù)據(jù)類型與函數(shù),.,8,PL/SQL語言,2）函數(shù),單行字符串函數(shù)-ASCII(),INSTR(),LENGTH(),UPPER(),LPAD(),LTRIM(),RPAD(),RTRIM(),REPLACE(),SUBSTR(),TRIM()，CONCAT()單行數(shù)字函數(shù)-ABS(),ACOS(),ASIN(),ATAN(),CEIL(),COS(),LOG(),MOD(),ROUND(),SIN(),TRUNC()單行日期函數(shù)-ADD_MONTHS(),LAST_DAY(),MONTHS_BETWEEN(),NEW_TIME(),NEXT_DAY(),ROUND(),SYSDATE,TRUNC(),TO_CHAR(),TO_DATE(),TO_NUMBER()其他單行函數(shù)-BFILENAME(),DECODE(),DUMP(),GREATEST(),LEAST(),USERENV(),NVL()NULL和單行函數(shù)-NULL值表示一個(gè)未知數(shù)據(jù)或者一個(gè)空值，算術(shù)操作符的任何一個(gè)操作數(shù)為NULL值，結(jié)果均為NULL值，這個(gè)規(guī)則也適合很多函數(shù)，只有CONCAT，DECODE，DUMP，NVL，REPLACE在調(diào)用了NVL參數(shù)時(shí)可以返回非NULL值。,a、單行函數(shù)-返回基于單行的單一結(jié)果,.,9,PL/SQL語言,又名集合函數(shù)-AVG(),COUNT(),MAX(),MIN(),STDDEV(),SUM(),VARIANCE()數(shù)據(jù)進(jìn)行分組-GROUPBYOracle的GROUPBY語句除了最基本的語法外，還支持ROLLUP和CUBE語句。如果是ROLLUP(A,B,C)的話，首先會(huì)對(duì)(A、B、C)進(jìn)行GROUPBY，然后對(duì)(A、B)進(jìn)行GROUPBY，然后是(A)進(jìn)行GROUPBY，最后對(duì)全表進(jìn)行GROUPBY操作。如果是GROUPBYCUBE(A,B,C)，則首先會(huì)對(duì)(A、B、C)進(jìn)行GROUPBY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后對(duì)全表進(jìn)行GROUPBY操作。grouping_id()可以美化效果,b、組函數(shù)-返回基于多行的單一結(jié)果,c、嵌套函數(shù)-函數(shù)可以嵌套，優(yōu)先權(quán)基于位置，遵循由內(nèi)到外，由左到右的原則。,.,10,PL/SQL語言,Oracle擴(kuò)展PL/SQL簡介,PL/SQL是Oracle對(duì)SQL規(guī)范的擴(kuò)展，是一種塊結(jié)構(gòu)語言，即構(gòu)成一個(gè)PL/SQL程序的基本單位（過程、函數(shù)和無名塊）是邏輯塊，可包含任何數(shù)目的嵌套了快。這種程序結(jié)構(gòu)支持逐步求精方法解決問題。一個(gè)塊（或子塊）將邏輯上相關(guān)的說明和語句組合在一起，其形式為：DECLARE-說明BEGIN-語句序列EXCEPTION-例外處理程序END；,三、應(yīng)用PL/SQL語言編寫基本的sql,.,11,PL/SQL語言,控制語句1）分支語句：IFconditionTHENSequence_of_statements;ENDIF;IFconditionTHENSequence_of_statement1;ELSESequence_of_statement2;ENDIF;,.,12,PL/SQL語言,2）循環(huán)語句：LOOPSequence_of_statements;IFconditionTHENEXIT;ENDIF;ENDLOOP;WHILEconditionLOOPSequence_of_statements;ENDLOOP;FORcounterINlower_bound.higher_boundLOOPSequence_of_statements;ENDLOOP;,.,13,PL/SQL語言,數(shù)據(jù)操縱語言命令（DML）,用于在關(guān)系數(shù)據(jù)庫對(duì)象中操縱數(shù)據(jù)INSERTinsertintotab_name(col1,col2)values(1,2);UPDATEupdatetab_nameaseta.col1=1wherecol2=1;DELETEdeletefromtab_namewherecol1=1;,.,14,PL/SQL語言,數(shù)據(jù)控制語言命令（DCL）用于創(chuàng)建與用戶訪問相關(guān)的對(duì)象，也控制著用戶的權(quán)限分配ALTERPASSWORD改變口令GRANT為用戶授予特權(quán)REVOKE從用戶處收回特權(quán),事物控制命令,Commit完成事務(wù)，數(shù)據(jù)修改成功并對(duì)其他用戶開放。Rollback撤銷事務(wù)，撤銷所有操作。rollbacktosavepoint撤銷在設(shè)置的回滾點(diǎn)以后的操作。settransaction響應(yīng)事務(wù)或語句的一致性，特別對(duì)于事務(wù)使用回滾段。,.,15,PL/SQL語言,數(shù)據(jù)定義語言命令（DDL）,Create-創(chuàng)建某個(gè)對(duì)象Createtabletab_name(colvarchar(2)notnull);Drop-刪除某個(gè)對(duì)象Droptable(view)tab_name;Grant-賦予權(quán)限命令Grantupdateontab_nametouser_name;Revoke-收回權(quán)限命令Revokeupdateontab_namefromuser_name;,.,16,PL/SQL語言,SQL中操作符的使用,定義：操作符是保留的字或字符，主要用于比較和算術(shù)運(yùn)算。分類：比較操作符，邏輯操作符和算術(shù)運(yùn)算符。1、比較操作符包括：=，,=。注意比較雙方的數(shù)據(jù)類型。2、算術(shù)運(yùn)算符包括：+，-，*，/等。與數(shù)學(xué)運(yùn)算規(guī)則一致。,.,17,PL/SQL語言,SQL中邏輯操作符的使用,ISNULL（是否為空）BETWEEN（在某兩個(gè)值之間）IN（一系列值中）LIKE（相似值的比較）EXITS（是否存在符合條件的數(shù)據(jù)）ALL/ANY（一組數(shù)據(jù)的所有/其中的任何一個(gè)）AND/OR（邏輯與/或）,.,18,PL/SQL語言,子程序1）存儲(chǔ)過程：CREATEPROCEDURE過程名(參數(shù)說明1，參數(shù)說明2，。)IS局部說明BEGIN執(zhí)行語句；END過程名；2）存儲(chǔ)函數(shù)：CREATEFUNCTION函數(shù)名(參數(shù)說明1，參數(shù)說明2，。)RETURN類型IS局部說明BEGIN執(zhí)行語句；END函數(shù)名；,.,19,PL/SQL語言,應(yīng)用PL/SQL語言編寫sql,sql語句不區(qū)分大小寫sql語句可以由一行或者多行組成在一行中關(guān)鍵字不能縮寫或者被拆開子句通常書寫在不同的行上采用行縮進(jìn)來增強(qiáng)程序的閱讀性,.,20,PL/SQL語言,CASE表達(dá)式,CASEWHENcomparison_expr1THENreturn_expr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_exprEND,.,21,PL/SQL語言,DECODE表達(dá)式,DECODE(col|expression,search1,result1,search2,result2,.,default)DECODE語句=IF-THEN-ELSE的實(shí)現(xiàn),.,22,PL/SQL語言,%type用法,declaremyidaa2.MDATE%type;mynameaa2.SNUM%type;beginselectMDATE,SNUMintomyid,mynamefromaa2whereMDATE=20120801;dbms_output.put_line(myid);dbms_output.put_line(myname);end;/當(dāng)表結(jié)構(gòu)字段類型或者長度變化后,程序中無需在變,.,23,PL/SQL語言,%rowtype用法,declaretypetype_deptistableofaa2%rowtypeindexbybinary_integer;tbtype_dept;begintb(1).MDATE:=20120812;tb(1).SNUM:=12;tb(2).MDATE:=20120813;tb(2).SNUM:=13;dbms_output.put_line(tb(1).MDATE|tb(1).SNUM);dbms_output.put_line(tb.COUNT);end;/,.,24,PL/SQL語言,TYPE用法，相當(dāng)于結(jié)構(gòu)體,declarelv_order_dateDAte:=sysdate;lv_last_txtvarchar2(5)default001;lv_lastvarchar2(10)notnull:=us;TYPEtype_testisrecord(MDATEaa2.MDATE%type,SNUMaa2.SNUM%type);rectype_test;beginlv_order_date:=sysdate;dbms_output.put_line(lv_last);selectMDATE,SNUMintorecfromaa2whereMDATE=20120801;dbms_output.put_line(rec.MDATE);dbms_output.put_line(rec.SNUM);end;,.,25,PL/SQL語言,游標(biāo)的使用:,declare-類型定義cursorc_jobisselectMDATE,SNUMfromaa2;-定義一個(gè)游標(biāo)變量c_rowc_job%rowtype;beginforc_rowinc_jobloopdbms_output.put_line(c_row.MDATE|-|c_row.SNUM);endloop;end;,.,26,PL/SQL語言,游標(biāo)的使用:Fetch游標(biāo),使用的時(shí)候必須要明確的打開和關(guān)閉,declare-類型定義cursorc_jobisselectMDATE,SNUMfromaa2;-定義一個(gè)游標(biāo)變量c_rowc_job%rowtype;beginopenc_job;loop-提取一行數(shù)據(jù)到c_rowfetchc_jobintoc_row;-判讀是否提取到值，沒取到值就退出-取到值c_job%notfound是false-取不到值c_job%notfound是trueexitwhenc_job%notfound;dbms_output.put_line(c_row.MDATE|-|c_row.SNUM);endloop;-關(guān)閉游標(biāo)closec_job;end;,.,27,PL/SQL語言,游標(biāo)的使用:別的寫法,declare-類型定義cursorc_jobisselectMDATE,SNUMfromaa2;-定義一個(gè)游標(biāo)變量c_rowc_job%rowtype;beginopenc_job;-給第一行數(shù)據(jù)fetchc_jobintoc_row;whilec_job%foundloopdbms_output.put_line(c_row.MDATE|-|c_row.SNUM);-給下一行喂數(shù)據(jù)fetchc_jobintoc_row;endloop;-關(guān)閉游標(biāo)closec_job;end;,.,28,PL/SQL語言,游標(biāo)的使用:UPDATE寫法,declare-類型定義cursorc_jobisselectMDATE,SNUMfromaa2forupdateOFSNUM;-定義一個(gè)游標(biāo)變量c_rowc_job%rowtype;beginopenc_job;-給第一行數(shù)據(jù)fetchc_jobintoc_row;whilec_job%foundloopUPDATEaa2SETSNUM=SNUM+1WHERECURRENTOFc_job;-給下一行喂數(shù)據(jù)fetchc_jobintoc_row;endloop;-關(guān)閉游標(biāo)closec_job;commit;end;,.,29,PL/SQL語言,exception用法,declareexException;v_MDATEaa2.mdate%type;begin/*Updateaa2setSNUM=14,MDATE=11111111111111whereMDATE=20120812;*/selectMDATEintov_MDATEfromaa2/*whereMDATE=20120814*/;ifSQL%NOTFOUNDThenRaiseex;endif;commit;ExceptionWhenexthendbms_output.put_line(updatefailed.);rollback;whenNO_DATA_FOUNDthendbms_output.put_line(norecordisfound);whenTOO_MANY_ROWSthendbms_output.put_line(toomanyrowsareselected);whenOTHERSthendbms_output.put_line(errormessage:|SQLERRM);rollback;end;/,.,30,PL/SQL語言,觸發(fā)器注意點(diǎn),觸發(fā)器不接受參數(shù)。一個(gè)表上最多可有12個(gè)觸發(fā)器，但同一時(shí)間、同一事件、同一類型的觸發(fā)器只能有一個(gè)。并各觸發(fā)器之間不能有矛盾。在一個(gè)表上的觸發(fā)器越多，對(duì)在該表上的DML操作的性能影響就越大。觸發(fā)器最大為32KB。若確實(shí)需要，可以先建立過程，然后在觸發(fā)器中用CALL語句進(jìn)行調(diào)用。在觸發(fā)器的執(zhí)行部分只能用DML語句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語句（CREATE、ALTER、DROP）。觸發(fā)器中不能包含事務(wù)控制語句(COMMIT，ROLLBACK，SAVEPOINT)。因?yàn)橛|發(fā)器是觸發(fā)語句的一部分，觸發(fā)語句被提交、回退時(shí)，觸發(fā)器也被提交、回退了。在觸發(fā)器主體中調(diào)用的任何過程、函數(shù)，都不能使用事務(wù)控制語句。在觸發(fā)器主體中不能申明任何Long和blob變量。新值new和舊值old也不能向表中的任何long和blob列。不同類型的觸發(fā)器(如DML觸發(fā)器、INSTEADOF觸發(fā)器、系統(tǒng)觸發(fā)器)的語法格式和作用有較大區(qū)別。,.,31,PL/SQL語言,觸發(fā)器使用,CREATEORREPLACETRIGGERtr_aa2AFTER(BEFORE)INSERTORDELETEORUPDATE(一個(gè)是在記錄操作之前觸發(fā)，一個(gè)是在記錄操作之后觸發(fā))ONaa2FOREACHROW(去掉FOREACHROW,行觸發(fā)器及語句觸發(fā)器)BEGINIFUPDATING(snum)THENRETURN;ENDIF;if:new.mdate=20120814thenreturn;elseifinsertingtheninsertintoaa3values(:new.mdate,:new.snum);endif;ifupdatingtheninsertintoaa3values(:old.mdate,:old.snum);endif;endif;END;,.,32,PL/SQL語言,視圖使用,視圖是存儲(chǔ)在數(shù)據(jù)庫中的預(yù)先定義好的查詢，具有表的外觀，可以象表一樣對(duì)其進(jìn)行存取，但不占據(jù)物理存儲(chǔ)空間createorreplaceviewview_aa2asselectMDATE,SNUMfromaa2groupbyMDATE,SNUM;視圖是可以被更新的,但必須:view的字段只涉及一個(gè)表。2.如果涉及多個(gè)表的話，被(涉及到的)view列所映射table列(或列的組合)必須是有主健或者unique約束的3.強(qiáng)制執(zhí)行，方法是加上hint/*+BYPASS_UJVC*/視圖中使用觸發(fā)器createorreplacetriggertrg_view_aa2insteadofupdateonview_aa2foreachrowbeginupdateaa2setsnum=15;end;,.,33,PL/SQL語言,同義詞使用,createorreplacesynonymmd_areaforitg1_admin.md_area;oracle同義詞有兩種類型，分別是公用Oracle同義詞與私有Oracle同義詞此外如果要?jiǎng)?chuàng)建一個(gè)遠(yuǎn)程的數(shù)據(jù)庫上的某張表的同義詞，需要先創(chuàng)建一個(gè)DatabaseLink(數(shù)據(jù)庫連接)來擴(kuò)展訪問，然后在使用如下語句創(chuàng)建數(shù)據(jù)庫同義詞：createsynonymtable_namefortable_nameDB_Link;,.,34,PL/SQL語言,序列使用,CREATESEQUENCEemp_sequenceINCREMENTBY1每次加幾個(gè)STARTWITH1從1開始計(jì)數(shù)NOMAXVALUE不設(shè)置最大值NOCYCLE一直累加，不循環(huán)CACHE10NOORDER;并行時(shí)取得序列的順序一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVALCURRVAL=返回sequence的當(dāng)前值NEXTVAL=增加sequence的值，然后返回sequence值createsequenceSEQ_TESTminvalue1maxvalue999999999999999999999999999startwith1incrementby1cache10;如果指定CACHE值，ORACLE就可以預(yù)先在內(nèi)存里面放置一些sequence，這樣存取的快些。cache里面的取完后，oracle自動(dòng)再取一組到cache。使用cache或許會(huì)跳號(hào)，比如數(shù)據(jù)庫突然不正常down掉(shutdownabort),cache中的sequence就會(huì)丟失.所以可以在createsequence的時(shí)候用nocache防止這種情況,.,35,PL/SQL語言,主鍵,約束,主鍵:PRIMARYKEY主鍵約束惟一地標(biāo)識(shí)一行。作為一個(gè)可以被外鍵有效引用的對(duì)象外鍵：FOREIGNKEY外鍵約束外鍵是該表是另一個(gè)表之間聯(lián)接的字段外鍵必須為另一個(gè)表中的主鍵外鍵的用途是確保數(shù)據(jù)的完整性唯一約束：UNIQUE唯一約束唯一約束保證在一個(gè)字段或者一組字段里的數(shù)據(jù)與表中其它行的數(shù)據(jù)相比是唯一的條件約束CHECK條件約束指定該列是否滿足某個(gè)條件ALTERTABLEstudentADDCONSTRAINTuk_studentUNIQUE(ment);ALTERTABLEstudentDROPCONSTRAINTuk_student;ALTERTABLEstudentADDCONSTRAINTstu_classFOREIGNKEY(classid)REFERENCESclass(cid);ALTERTABLEstudentADDCONSTRAINTstudent_age_ckCHECK(age18);,.,36,PL/SQL語言,索引,（1）*Tree索引。Createindexindexnameontablename(columnnamecolumnname.)（2）反向索引。Createindexindexnameontablename(columnnamecolumnname.)reverse（3）降序索引。Createindexindexnameontablename(columnnameDESCcolumnname.)（4）位圖索引。CreateBITMAPindexindexnameontablename(columnnamecolumnname.)（5）函數(shù)索引。Createindexindexnameontablename(functionname(columnname),.,37,PL/SQL語言,存儲(chǔ)函數(shù)和過程及包,1創(chuàng)建存儲(chǔ)過程和函數(shù)。2正確使用系統(tǒng)級(jí)的異常處理和用戶定義的異常處理。3建立和管理存儲(chǔ)過程和函數(shù)。4,通過使用包，就可以分類管理過程和函數(shù)。使用包還有一個(gè)好處，就是可以在包中定義自定義類型，從而在過程和函數(shù)可以直接用于定義變量,.,38,PL/SQL語言,分區(qū),范圍分區(qū)：createtablePartition_Test(PIDnumbernotnull)partitionbyrange(PID)(partitionpart_01valueslessthan(50000)tablespacedinya_space01,partitionpart_02valueslessthan(100000)tablespacedinya_space02,partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03)Hash分區(qū)（散列分區(qū)）：partitionbyhash(PID)(partitionpart_h01tablespacedinya_space01,partitionpart_h02tablespacedinya_space02,partitionpart_h03tablespacedinya_space03)List分區(qū)（列表分區(qū)）：PARTITIONBYLIST(sales_state)(PARTITIONsales_westVALUES(California,Hawaii)COMPRESS,范圍-散列分區(qū)（復(fù)合分區(qū)）partitionbyrange(PDATA)subpartitionbyhash(PID)subpartitions3storein(dinya_space01,dinya_space02,dinya_space03)(partitionpart_fh01valueslessthan(to_date(2004-01-01,yyyy-mm-dd)tablespacedinya_space01,partitionpart_fh02valueslessthan(to_date(2008-01-01,yyyy-mm-dd)tablespacedinya_space02,partitionpart_fh03valueslessthan(maxvalue)tablespacedinya_space03)范圍-列表分區(qū)（復(fù)合分區(qū)）,.,39,PL/SQL語言,分區(qū),增加一個(gè)分區(qū)altertablePart_Taddpartitionpart_05valueslessthan(10020)tablespacedinya查詢分區(qū)數(shù)據(jù)select*fromPartition_FHTestpartition(part_fh02)t(delete,update)Tuncate分區(qū)數(shù)據(jù)altertablexxtruncatepartitionupdateglobalindexes(rebuildindex)合并分區(qū)altertablePartition_HBmergepartitionspart_01,part_02intopartitionpart_02;拆分分區(qū)-spiltpartition分區(qū)名at(這里是一個(gè)臨界區(qū)，比如：50000就是說小于50000的放在part_01，而大于50000的放在part_02中)altertablePartition_HBsplitPartitionpart_02at(50000)into(Partitionpart_01tablespacedinya_space01,Partitionpart_02tablespacedinya_space02);更改分區(qū)名altertablePartition_HBrenamePartitionpart_01_testtopart_02;,.,40,PL/SQL語言,使用DBMS_JOB軟件包定時(shí)調(diào)度,SQLvariablennumber;begindbms_job.submit(job=:n,what=PKP_TEST1.p_stat_test1;,next_date=to_date(01-09-201204:00:00,dd-mm-yyyyhh24:mi:ss),interval=sysdate+1);Commit;end;/或者:SQLvariablejob_card_syncnumberexecutedbms_job.submit(:job_card_sync,PKP_TEST1.p_stat_test1;,sysdate+1/48,SYSDATE+1);commit;,.,41,PL/SQL語言,分析函數(shù),ORACLE提供了26個(gè)分析函數(shù),按功能分5類分析函數(shù)分類等級(jí)(ranking)函數(shù):用于尋找前N種查詢開窗(windowing)函數(shù):用于計(jì)算不同的累計(jì),如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個(gè)窗口上例:sum(t.sal)over(orderbyt.deptno,t.ename)running_total,sum(t.sal)over(partitionbyt.deptnoorderbyt.ename)department_total制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個(gè)分區(qū)或一組上的所有列例:sum(t.sal)over()running_total2,sum(t.sal)over(partitionbyt.deptno)department_total2制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處在于OVER語句上缺少一個(gè)ORDERBY子句!LAG,LEAD函數(shù):這類函數(shù)允許在結(jié)果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計(jì)算任何未排序分區(qū)的統(tǒng)計(jì)值,.,42,PL/SQL語言,分析函數(shù),2)PARTITION子句按照表達(dá)式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結(jié)果集被看作是一個(gè)單一的組3)ORDERBY子句分析函數(shù)中ORDERBY的存在將添加一個(gè)默認(rèn)的開窗子句,這意味著計(jì)算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒有ORDERBY時(shí),默認(rèn)的窗口是全部的分區(qū)在Orderby子句后可以添加nullslast,如:orderbycommdescnullslast表示排序時(shí)忽略comm列為空的行.4)WINDOWING子句用于定義分析函數(shù)將在其上操作的行的集合Windowing子句給出了一個(gè)定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對(duì)這些數(shù)據(jù)進(jìn)行操作默認(rèn)的窗口是一個(gè)固定的窗口,僅僅在一組的第一行開始,一直繼續(xù)到當(dāng)前行,要使用窗口,必須使用ORDERBY子句根據(jù)2個(gè)標(biāo)準(zhǔn)可以建立窗口:數(shù)據(jù)值的范圍(RANGES)或與當(dāng)前行的行偏移量,.,43,PL/SQL語言,分析函數(shù),5)Rang窗口Range5preceding:將產(chǎn)生一個(gè)滑動(dòng)窗口,他在組中擁有當(dāng)前行以前5行的集合ANGE窗口僅對(duì)NUMBERS和DATES起作用,因?yàn)椴豢赡軓腣ARCHAR2中增加或減去N個(gè)單元另外的限制是ORDERBY中只能有一列,因而范圍實(shí)際上是一維的,不能在N維空間中例:avg(t.sal)over(orderbyt.hiredateascrange100preceding)統(tǒng)計(jì)前100天平均工資6)Row窗口利用ROW分區(qū),就沒有RANGE分區(qū)那樣的限制了,數(shù)據(jù)可以是任何類型,且ORDERBY可以包括很多列7)Specifying窗口UNBOUNDEDPRECEDING:這個(gè)窗口從當(dāng)前分區(qū)的每一行開始,并結(jié)束于正在處理的當(dāng)前行CURRENTROW:該窗口從當(dāng)前行開始(并結(jié)束)NumericExpressionPRECEDING:對(duì)該窗口從當(dāng)前行之前的數(shù)字表達(dá)式(NumericExpression)的行開始,對(duì)RANGE來說,從從行序值小于數(shù)字表達(dá)式的當(dāng)前行的值開始.NumericExpressionFOLLOWING:該窗口在當(dāng)前行NumericExpression行之后的行終止(或開始),且從行序值大于當(dāng)前行NumericExpression行的范圍開始(或終止)rangebetween100precedingand100following:當(dāng)前行100前,當(dāng)前后100后,.,44,PL/SQL語言,正則表達(dá)式,方括號(hào)表達(dá)式描述:alnum:字母和數(shù)字混合的字符:alpha:字母字符:cntrl:控制字符:digit:數(shù)字字符:graph:圖像字符:lower:小寫字母字符:print:打印字符:punct：標(biāo)點(diǎn)符號(hào)字符:space:空格字符:upper:大寫字母字符:xdigit:十六進(jìn)制數(shù)字字符相當(dāng)于0-9a-fA-F,.,45,PL/SQL語言,正則表達(dá)式,元字符字符含義匹配字符串的開始位置(在中使用，此時(shí)它表示不接受該字符集合。a：匹配以a開始的字符串a(chǎn)：匹配不含a的字符串-當(dāng)使用在a-m表示范圍；當(dāng)使用在第一個(gè)字符時(shí)表示連字符串，如-abc$匹配字符結(jié)束位置a$：匹配以a結(jié)束的字符串.匹配除換行符n之外的任何單字符。?匹配前面的子表達(dá)式零次或一次tr(y(ing)?)：可以表示try或者trying*匹配前面的子表達(dá)式零次或多次+匹配前面的子表達(dá)式一次或多次()標(biāo)記一個(gè)子表達(dá)式的開始和結(jié)束位置a(b)*可以匹配ab,abb,abbb,youabb(c|d)匹配c或d標(biāo)記一個(gè)中括號(hào)表達(dá)式cd匹配選擇c或d等同于(c|d)。匹配的是單字符，cd是匹配c,d之外的單字符。a-z表示所有小寫字母m,nm=5thenintoz_test1values(id,name)whenid2thenintoz_test2values(id)elseintoz_test3values(name)select*fromz_test;(2)和(3)區(qū)別在(3)不會(huì)在把id5的記錄插入到id2對(duì)應(yīng)的表中,.,50,PL/SQL語言,oracle全局臨時(shí)表的使用,CREATEGLOBALTEMPORARYTABLETABLENAME(COL1VARCHAR2(10),COL2NUMBER)ONCOMMITPRESERVE(DELETE)ROWS;這種臨時(shí)表不占用表空間，而且不同的SESSION之間互相看不到對(duì)方的數(shù)據(jù)在會(huì)話結(jié)束后表中的數(shù)據(jù)自動(dòng)清空，如果選了DELETEROWS，則在提交的時(shí)候即清空數(shù)據(jù)，PRESERVE則一直到會(huì)話結(jié)束-ONCOMMITDELETEROWS說明臨時(shí)表是事務(wù)指定，每次提交后ORACLE將截?cái)啾恚▌h除全部行）-ONCOMMITPRESERVEROWS說明臨時(shí)表是會(huì)話指定，當(dāng)中斷會(huì)話時(shí)ORACLE將截?cái)啾怼_突的問題更本不用考慮.臨時(shí)表只是保存當(dāng)前會(huì)話(session)用到的數(shù)據(jù)，數(shù)據(jù)只在事務(wù)或會(huì)話期間存在。一個(gè)會(huì)話可以啟動(dòng)多個(gè)事務(wù)，會(huì)話是session,指