Oracle數(shù)據(jù)庫(kù)10g：新的SQL和PLSQL功能

1、Oracle數(shù)據(jù)庫(kù)10g中新的SQL和PL/SQL功能議程新的SQL特性新的PL/SQL特性PL/SQL的速度是是原來(lái)的的兩倍總結(jié)問(wèn)答Oracle8OracleSQL引擎的發(fā)發(fā)展Oracle8iOracle9iOracle9iR2SQL/XML集成（XMLType、URIType等）OLAPDatetime類型ANSI聯(lián)接CASE表達(dá)式XML數(shù)據(jù)庫(kù)SQL/XMLW3C標(biāo)準(zhǔn)數(shù)據(jù)挖掘掘網(wǎng)絡(luò)Web服務(wù)正則表達(dá)達(dá)式本地?cái)?shù)值值結(jié)構(gòu)BLAST對(duì)象擴(kuò)展展數(shù)據(jù)插件件SQL/Java集成interMedia類型數(shù)據(jù)和處處理復(fù)雜雜性新的SQL特性正則表達(dá)達(dá)式本地?cái)?shù)值值結(jié)構(gòu)LOB增強(qiáng)集合增強(qiáng)強(qiáng)正則表達(dá)達(dá)式簡(jiǎn)介介起源

2、于數(shù)數(shù)學(xué)首先在UNIX中計(jì)算機(jī)機(jī)化從ed、grep、perl、cgi、web到任何地地方多種應(yīng)用用程序HTML FORMS中的驗(yàn)證證生物信息息學(xué)服務(wù)器配配置數(shù)據(jù)挖掘掘Oracle正則表達(dá)達(dá)式對(duì)數(shù)據(jù)庫(kù)庫(kù)的本地地支持SQL和PL/SQL中的接口口基于POSIX標(biāo)準(zhǔn)與GNU、PERL、Java、Xquery等同步描述數(shù)據(jù)據(jù)的模式式變?yōu)閿?shù)數(shù)據(jù)的一一個(gè)屬性性數(shù)據(jù)庫(kù)內(nèi)內(nèi)部強(qiáng)大大的字符符串處理理主要特性性POSIX擴(kuò)展的正正則表達(dá)達(dá)式接口REGEXP_LIKE模式是否否匹配？REGEXP_SUBSTR它與什么么匹配？REGEXP_INSTR它在哪里里匹配？REGEXP_REPLACE替換匹配配的記錄錄。匹配

3、選項(xiàng)項(xiàng)語(yǔ)言環(huán)境境支持LOB支持REGEXP_LIKE確定模式式是否存存在。查找名稱稱JonStevens的變體John或JonSteven或Stevens或Stephen或StephensSELECTc1FROM t1WHEREREGEXP_LIKE(c1,Joh?nSte(ph|v)ens?);JonStevensJohn StephensJohn Stevens和DDL一起使用用允許使用用過(guò)濾器器的數(shù)據(jù)據(jù)，并帶帶檢查約約束郵政編碼碼 (ZIPcode)列是VARCHAR2(5)，但可以包包含任意意的5 個(gè)字字符。使用視圖圖的查詢?cè)冏蛹秃透袷交瘜㈦娫捥?hào)號(hào)碼格式式化為(xxx) xxx-x

4、xxx創(chuàng)建基于于函數(shù)的的索引與PL/SQL一起使用用非常強(qiáng)大大的字符符串處理理功能src:=REGEXP_REPLACE(src,);src:=REGEXP_REPLACE(src,);src:=REGEXP_REPLACE(src,);可以增強(qiáng)強(qiáng)現(xiàn)有的的功能支持PERL縮寫提取第n個(gè)子表達(dá)達(dá)式替換大量量代碼行行可以簡(jiǎn)化化字符串串處理函函數(shù)性能考慮慮事項(xiàng)模式匹配配可能很很復(fù)雜需要編譯譯成狀態(tài)態(tài)機(jī)詞法分析析檢查所有有可能的的分支，直到發(fā)發(fā)現(xiàn)匹配配每條語(yǔ)句句編譯一一次對(duì)于復(fù)雜雜情景可可能比LIKE更快郵政編碼碼檢查快快 5倍倍通常常比等價(jià)價(jià)的PL/SQL更快編寫快速速執(zhí)行的的表達(dá)式式本地浮點(diǎn)點(diǎn)數(shù)據(jù)

5、類類型兩種新的的數(shù)值數(shù)數(shù)據(jù)類型型BINARY_FLOAT， BINARY_DOUBLE二進(jìn)制浮浮點(diǎn)運(yùn)算算的IEEE 754標(biāo)準(zhǔn)是許多其其他標(biāo)準(zhǔn)準(zhǔn)（例如如，Java、XML模式）和和硬件平平臺(tái)的一一部分普遍存在在于商務(wù)務(wù)智能、生命科科學(xué)、工工程/科科學(xué)計(jì)算算本地浮點(diǎn)點(diǎn)數(shù)據(jù)類類型與數(shù)數(shù)值數(shù)據(jù)據(jù)類型比NUMBER類型效率率更高硬件運(yùn)算算/數(shù)學(xué)學(xué)運(yùn)算快快 5 10 倍占用更少少的內(nèi)存存/磁盤盤空間（5/9 字節(jié)節(jié)與1 22字字節(jié)）BINARY_DOUBLE值范圍更更大（e308與e125）無(wú)需類型型轉(zhuǎn)換（使用與與字節(jié)順順序無(wú)關(guān)關(guān)的存儲(chǔ)儲(chǔ)格式）本地浮點(diǎn)點(diǎn)函數(shù)新的類型型轉(zhuǎn)換函函數(shù)TO_BINARY_FLO

6、AT、TO_BINARY_DOUBLETO_NUMBERSQL函數(shù)支持持?jǐn)?shù)值函數(shù)數(shù)（sin、cos等）聚合函數(shù)數(shù)（sum、avg、stddev等）分析函數(shù)數(shù)（sum、avg、stddev等）對(duì)SQL、PL/SQL、Java、XML模式注冊(cè)冊(cè)、ODP.NET和OCI/OCCI的無(wú)縫支支持本地浮點(diǎn)點(diǎn)約束createtablefloating_point_table1 (fltNnullbinary_floatconstraintflt_nullnotnull,dblNnullbinary_double constraintdbl_nullnotnull,fltUnqbinary_floatcons

7、traintflt_unqunique,dblUnqbinary_double constraintdbl_unqunique,fltChkbinary_floatconstraintflt_chkcheck( fltChkisnotNaN ),dblChkbinary_double constraintdbl_chkcheck( dblChkisnotinfinite),fltPrmbinary_floatconstraintflt_prmprimary key);* NaN(不是數(shù)字字)e.g. 0/0,infinity/infinity本地浮點(diǎn)點(diǎn)約束createtablefloating

8、_point_table2 (dblPrmbinary_doubleconstraintdbl_prmprimary key,fltFrnbinary_floatconstraint flt_frnreferences floating_point_table1(fltPrm)ondeletecascade);PL/SQL示例使用歐拉拉級(jí)數(shù)計(jì)計(jì)算=sqrt (6* (1+1/2*2+ 1/3*2 +. )使用牛頓頓方法近近似真值值的平方根賦值、加、乘、除、比較、絕絕對(duì)值procedureEuler_Pi_Series is.beginpi:= one;prev_pi:= zero;prod_o

9、ver_six := zero;step:= zero;whilepi- prev_pitolerloopprev_pi:= pi;step:= step+one;prod_over_six := prod_over_six+one/(step*step);prod:= six*prod_over_six;prev_root:= prod;root:= prod/two;whileAbs(root- prev_root)root_tolerloopprev_root:=root;root:=(root+prod/root)/two;endloop;pi:=root;endloop;endEul

10、er_Pi_Series;subtypeMy_Numberisnumber;zeroconstantMy_Number:=0.0;oneconstantMy_Number:=1.0;twoconstantMy_Number:=2.0;sixconstantMy_Number:=6.0;tolerconstantMy_Number:=0.00000000001;root_tolerconstantMy_Number:=toler/1000.0;rootMy_Number;prev_rootMy_Number;prod_over_sixMy_Number;prodMy_Number;piMy_Nu

11、mber;prev_piMy_Number;stepMy_Number;subtypeMy_Numberisbinary_double;zeroconstantMy_Number:=0.0d;oneconstantMy_Number:=1.0d;twoconstantMy_Number:=2.0d;sixconstantMy_Number:=6.0d;tolerconstantMy_Number:=0.00000000001d;root_tolerconstantMy_Number:=toler/1000.0d;rootMy_Number;prev_rootMy_Number;prod_ove

12、r_sixMy_Number;prodMy_Number;piMy_Number;prev_piMy_Number;stepMy_Number;PL/SQL示例使用歐拉拉級(jí)數(shù)計(jì)計(jì)算近似300,000 次迭迭代NUMBER花費(fèi)約27.7秒秒BINARY_DOUBLE花費(fèi)約3.7 秒提高倍數(shù)數(shù)：約7倍倍LOB增強(qiáng)LOB普遍用于于存儲(chǔ)非非結(jié)構(gòu)化化的數(shù)據(jù)據(jù)（文本本、AVI、基因/蛋蛋白序列列等）LOB性能改善善訪問(wèn)內(nèi)聯(lián)聯(lián) ( 4KB)LOB時(shí)獲得5倍倍的性能能增益（在 9iR2中）臨時(shí)LOB使用引用用計(jì)算提提供了幾個(gè)數(shù)量量級(jí)的性性能增益益在讀取時(shí)時(shí)引用(ReferenceonRead)在寫入時(shí)時(shí)復(fù)制(

13、Copy on Write)超大型LOBTB大小的Lob(8128TB)DB_BLOCK_SIZE (2 32KB) x(4GB1)新的DBMS_LOB.GET_STORAGE_LIMIT函數(shù)OCI、JDBC和DBMS_LOB現(xiàn)在支持持大于4GB的LOBOCILobRead2()、OCIWriteAppend2()和OCILobWrite2()函數(shù)為JDBC和DBMS_LOB提供相同同的API集合類型型增強(qiáng)用戶定義義的類型型、集合合類型和和引用類類型簡(jiǎn)化化了結(jié)構(gòu)構(gòu)復(fù)雜的的數(shù)據(jù)建建模集合類型型用于映映射實(shí)際際世界應(yīng)應(yīng)用程序序中的包包含關(guān)系系例如，一一個(gè)購(gòu)物物車包含含了許多多商品VARRAY增強(qiáng)V

14、ARRAY的類型發(fā)發(fā)展CREATETYPEemail_list_typASOBJECT(section_noNUMBER,emailsemail_list_arr);/CREATETYPEemail_varray_typ AS VARRAY(5)OFemail_list_typ;/ALTERTYPEemail_varray_typMODIFYLIMIT100CASCADE;在臨時(shí)表表中支持持VARRAY列提供應(yīng)用用程序開(kāi)開(kāi)發(fā)靈活活性嵌套表增增強(qiáng)將一個(gè)不不同的表表空間用用于嵌套套表的存存儲(chǔ)表帶嵌套表表的列的的表可以以在表空空間之間間分配I/O負(fù)載CREATETABLEpurchase_order

15、s (order_items_columnorder_items_typ )NESTEDTABLEorder_items_columnSTOREASorder_items_column_nt(TABLESPACE users);為嵌套表表提供的的ANSI SQL多集合操操作嵌套表存存儲(chǔ)無(wú)序序的元素素集合只包含唯唯一的元元素多集合可以包含含重復(fù)的的元素嵌套表上上的各種種多集合合操作為嵌套表表提供的的多集合合操作Cardinality、Collect、MultisetExcept、MultisetIntersection、MultisetUnion、Powermultiset、Powermulti

16、set_by_Cardinality和Set操作查找熱銷銷商品集集合的強(qiáng)強(qiáng)大工具具（市場(chǎng)場(chǎng)指數(shù)總總覽分析析）多集合操操作示例例createtypecategories as table of int;/ createtablecarts(c1 int,c2categories)nestedtablec2storeastb1_c2;insertintocartsvalues(1,categories(1,2,3,4);insertintocartsvalues(2,categories(2,4,6);selectt1.c2multisetintersectt2.c2from carts t1,c

17、artst2wheret1.c1=1 andt2.c1=2;categories(2,4)-frequentset嵌套表比比較Equal和NotEqual、In、Submultiset、MemberOf、Empty和IsNotA SetSELECT*FROM customer cWHEREitem_typ(2)MEMBEROFc.freq_set;在PL/SQL中也提供供declaretype Tab_t is table of pls_integer;a Tab_t := Tab_t (1,2,3,4,5);beginif3memberofathen Show(member);endif;e

18、nd;SQL小結(jié)Oracle數(shù)據(jù)庫(kù)10gSQL引擎聚合來(lái)自自不同的的分布式式數(shù)據(jù)源源的數(shù)據(jù)據(jù)網(wǎng)格信信息無(wú)縫地集集成Java、XML和基本數(shù)數(shù)據(jù)處理理功能一個(gè)全面面的高性性能和基基于標(biāo)準(zhǔn)準(zhǔn)（SQL-2003W3C、J2SE、POSIX等）的特特性集簡(jiǎn)化的應(yīng)應(yīng)用程序序開(kāi)發(fā)、部署和和管理RAS、可管理性性和安全全性 議程新的SQL特性新的PL/SQL特性PL/SQL的速度是是原來(lái)的的兩倍總結(jié)問(wèn)答新的PL/SQL特性剛才討論論的所有有SQL語(yǔ)言特性性FORALL語(yǔ)句中的的稀疏綁綁定數(shù)組組編譯器警警告新的Utl_Mail和Utl_Compress程序包FORALL語(yǔ)句中的的稀疏綁綁定數(shù)組組您的PL/SQ

19、L表最初是是密集的的，例如，是是由BULK COLLECT填充的您刪除了了一些元元素您想將這這些元素素插入到到某個(gè)表表中foralljinINDICES OFsparse_emp_recordsBETWEENAND - optionalinsertintonew_employees valuessparse_emp_records(j);FORALL語(yǔ)句中的的稀疏綁綁定數(shù)組組您有一個(gè)個(gè)PL/SQL表（密集集或稀疏疏）您想將這這些記錄錄的一個(gè)個(gè)指定的的子集插插入到某某個(gè)表中中foralljinVALUESOFpointer_tabinsertintonew_employees valuesemp

20、_records(j);編譯器警警告createorreplaceprocedurePisdebuggingboolean:=false;beginifdebuggingthenDbms_Output.Put_Line( debugging ); endif;endP;/SP2-0804:Procedure createdwithcompilationwarningsShow ErrorsPLW-06002: Unreachable codealtersession setplsql_warnings= ENABLE:ALL;編譯器警警告createorreplaceprocedureP(i

21、in outclob )isbegini := x;endP;/SP2-0804:Procedure createdwithcompilationwarningsShow ErrorsPLW-07203: parameterImay benefitfromuseofthe NOCOPYcompilerhintaltersession setplsql_warnings= ENABLE:PERFORMANCE;新提供的的程序包包Utl_Mail它的API反映了，比如說(shuō)說(shuō)，NetscapeCommunicator的最終用用戶的思思維模式式無(wú)需SMTP協(xié)議的知知識(shí)（與使用Utl_Smtp相比）確保簡(jiǎn)

22、單單化的一一些限制制Utl_Compress提供了一一個(gè)API來(lái)壓縮和和解壓縮縮RAW或BLOB （服務(wù)器端端）議程新的SQL特性新的PL/SQL特性PL/SQL的速度是是原來(lái)的的兩倍總結(jié)問(wèn)答PL/SQL編譯和執(zhí)執(zhí)行101PL/SQL 源代碼前端IR = Diana后端對(duì)象代碼 = MCodePVM全新的后后端10g引進(jìn)了一一個(gè)全新新的后端端，又叫叫做優(yōu)化化代碼生生成器它全面地地查看源源代碼所所表達(dá)的的內(nèi)容，然后刪刪除對(duì)最最終的正正確結(jié)果果確實(shí)沒(méi)沒(méi)有影響響的所有有計(jì)算提供了實(shí)實(shí)現(xiàn)巨大性能的潛潛力全新的后后端在 9iR2中的ORACLE可執(zhí)行文文件中，新的代代碼生成成器與舊舊的代碼碼生成器器并存

23、，一個(gè)允許許在兩者者之間進(jìn)進(jìn)行選擇擇的開(kāi)關(guān)關(guān)兩者都曾曾在回歸歸測(cè)試中中使用因此我們們保證它它是安全全的！我們沒(méi)有有將這個(gè)個(gè)開(kāi)關(guān)提提供給客客戶重要的pvm升級(jí)刪除了過(guò)過(guò)時(shí)的指指令增加了新新的指令令例如one-shotmulticoncatenate簡(jiǎn)化了用用于使用用PVM指令的系系統(tǒng)實(shí)施經(jīng)過(guò)過(guò)調(diào)整的的指令的的C例程改進(jìn)了本地編譯數(shù)據(jù)庫(kù)中中的DLL對(duì)于數(shù)據(jù)據(jù)庫(kù)管理理員，配配置更加加簡(jiǎn)單改進(jìn)了生生成的C代碼讓我們修修改其工工作方式式PL/SQL 源代碼前端IR = Diana后端對(duì)象代碼 = MCodePVMPL/SQL編譯和執(zhí)執(zhí)行101對(duì)象代碼 = MCode 或本地計(jì)算機(jī)代碼硬件性能實(shí)驗(yàn)驗(yàn)結(jié)果由

24、PL/SQL小組進(jìn)行行的測(cè)試試Beta程序測(cè)試試由Oracle的應(yīng)用程程序部進(jìn)進(jìn)行的測(cè)測(cè)試由PL/SQL小組進(jìn)行行的測(cè)試試17個(gè)個(gè)自包含含的PL/SQL程序的基基準(zhǔn)測(cè)試試套件設(shè)計(jì)用來(lái)來(lái)實(shí)踐大大范圍的的結(jié)構(gòu)和和編程習(xí)習(xí)慣一些測(cè)試試選擇用用來(lái)演示示特定的的優(yōu)化器器效果例如，從從一個(gè)循循環(huán)中刪刪除一個(gè)個(gè)常量賦賦值由PL/SQL小組進(jìn)行行的測(cè)試試下一張幻幻燈片顯顯示了每每一個(gè)程程序在9iR2INTERPRETED和 10gNATIVE中的速度度9iR2中速度相相同10g中顯示了了兩種測(cè)測(cè)量方式式的完成成CPU時(shí)間之比比由PL/SQL小組進(jìn)行行的測(cè)試試大多數(shù)測(cè)測(cè)試程序序速度提提高超過(guò)過(guò) 2倍倍一些測(cè)試試

25、程序速速度提高高遠(yuǎn)超過(guò)過(guò) 2倍倍速度提高高10倍的程序序使用BINARY_INTEGER，并擁有特特別易于于優(yōu)化的的編程習(xí)習(xí)慣procedurePis/*constrainedbinary_integersubtypes*/a positiven:=1;b positiven:=1;c positiven:=1;d positiven:=1;beginforjin1.i_nof_iterationsloopb := j/*constraint check*/;d := 42/*constantassignment withina loop*/;c := d+b/*canbecombined.*

26、/;a := b+c/*.c notused excepthere*/;endloop;endP;由PL/SQL小組進(jìn)行行的測(cè)試試下一張幻幻燈片顯顯示了更更多詳細(xì)細(xì)信息基準(zhǔn)是OracleVersion8.0.6顯示了8i9iR2INTERPRETED9iR2NATIVE10gINTERPRETED10gNATIVE速度提高高的倍數(shù)數(shù)由PL/SQL小組進(jìn)行行的測(cè)試試TMI？下一張幻幻燈片為為每一個(gè)個(gè)測(cè)試的的版本顯顯示了這這 17 個(gè)程程序的集集合的統(tǒng)統(tǒng)計(jì)信息息：中值最大和最最小值第 1和和第3四四分點(diǎn)（一半的觀觀察都落落在第1和和第3 四分分點(diǎn)之間間）由PL/SQL小組進(jìn)行行的測(cè)試試8i比 8.0.6 快9iR2比 8i快NATIVE始終比INTERPRETED快在 9iR2中在 10g中10g始終比9iR2快INTERPRETED與INTERPRETEDNATIVE與NATIVE由IFS進(jìn)行的Beta程序測(cè)試試由IFS進(jìn)行的Beta程序測(cè)試試IFS應(yīng)用程序序基于web和門戶技技術(shù)提供用于于制造、供應(yīng)鏈鏈管理、客戶關(guān)關(guān)系管理理、服務(wù)務(wù)供應(yīng)、財(cái)務(wù)管管理、產(chǎn)產(chǎn)品開(kāi)發(fā)發(fā)、維護(hù)護(hù)和