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

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

2、據(jù)插件數(shù)據(jù)插件 SQL/Java 集成集成interMedia 類型類型數(shù)據(jù)和處理復(fù)雜性數(shù)據(jù)和處理復(fù)雜性新的新的 SQL 特性特性 正則表達(dá)式 本地?cái)?shù)值結(jié)構(gòu) LOB 增強(qiáng) 集合增強(qiáng)正則表達(dá)式簡(jiǎn)介正則表達(dá)式簡(jiǎn)介 起源于數(shù)學(xué) 首先在 UNIX 中計(jì)算機(jī)化從 ed、grep、perl、cgi、web 到任何地方 多種應(yīng)用程序HTML FORMS 中的驗(yàn)證 生物信息學(xué)服務(wù)器配置數(shù)據(jù)挖掘Oracle 正則表達(dá)式正則表達(dá)式 對(duì)數(shù)據(jù)庫(kù)的本地支持SQL 和 PL/SQL 中的接口基于 POSIX 標(biāo)準(zhǔn)與 GNU、PERL、Java、Xquery 等同步 描述數(shù)據(jù)的模式變?yōu)閿?shù)據(jù)的一個(gè)屬性 數(shù)據(jù)庫(kù)內(nèi)部強(qiáng)大的字符串

3、處理主要特性主要特性 POSIX 擴(kuò)展的正則表達(dá)式 接口REGEXP_LIKE模式是否匹配？REGEXP_SUBSTR它與什么匹配？REGEXP_INSTR它在哪里匹配？REGEXP_REPLACE替換匹配的記錄。 匹配選項(xiàng) 語(yǔ)言環(huán)境支持 LOB 支持REGEXP_LIKE 確定模式是否存在。 查找名稱 Jon Stevens 的變體John 或 JonSteven 或 Stevens 或 Stephen 或 StephensSELECT c1 FROM t1 WHERE REGEXP_LIKE(c1, Joh?n Ste(ph|v)ens?); Jon Stevens John Stephe

4、ns John Stevens和和 DDL 一起使用一起使用 允許使用過(guò)濾器的數(shù)據(jù)，并帶檢查約束郵政編碼 (ZIP code) 列是 VARCHAR2(5)，但可以包含任意的 5 個(gè)字符。 使用視圖的查詢子集和格式化將電話號(hào)碼格式化為 (xxx) xxx-xxxx 創(chuàng)建基于函數(shù)的索引與與 PL/SQL 一起使用一起使用 非常強(qiáng)大的字符串處理功能src := REGEXP_REPLACE (src, );src := REGEXP_REPLACE (src, );src := REGEXP_REPLACE (src, ); 可以增強(qiáng)現(xiàn)有的功能支持 PERL 縮寫(xiě)提取第 n 個(gè)子表達(dá)式 替換大量代

5、碼行可以簡(jiǎn)化字符串處理函數(shù)性能考慮事項(xiàng)性能考慮事項(xiàng) 模式匹配可能很復(fù)雜需要編譯成狀態(tài)機(jī)詞法分析檢查所有可能的分支，直到發(fā)現(xiàn)匹配 每條語(yǔ)句編譯一次對(duì)于復(fù)雜情景可能比 LIKE 更快 郵政編碼檢查快 5 倍通常比等價(jià)的 PL/SQL 更快 編寫(xiě)快速執(zhí)行的表達(dá)式本地浮點(diǎn)數(shù)據(jù)類型本地浮點(diǎn)數(shù)據(jù)類型 兩種新的數(shù)值數(shù)據(jù)類型 BINARY_FLOAT， BINARY_DOUBLE二進(jìn)制浮點(diǎn)運(yùn)算的 IEEE 754 標(biāo)準(zhǔn)是許多其他標(biāo)準(zhǔn)（例如，Java、XML 模式）和硬件平臺(tái)的一部分普遍存在于商務(wù)智能、生命科學(xué)、工程/科學(xué)計(jì)算本地浮點(diǎn)數(shù)據(jù)類型與數(shù)值數(shù)據(jù)類型本地浮點(diǎn)數(shù)據(jù)類型與數(shù)值數(shù)據(jù)類型 比 NUMBER 類型效

6、率更高硬件運(yùn)算/數(shù)學(xué)運(yùn)算快 5 10 倍占用更少的內(nèi)存/磁盤空間（5/9 字節(jié)與 1 22 字節(jié)）BINARY_DOUBLE 值范圍更大（e308 與 e125）無(wú)需類型轉(zhuǎn)換（使用與字節(jié)順序無(wú)關(guān)的存儲(chǔ)格式）本地浮點(diǎn)函數(shù)本地浮點(diǎn)函數(shù) 新的類型轉(zhuǎn)換函數(shù)TO_BINARY_FLOAT、TO_BINARY_DOUBLETO_NUMBER SQL 函數(shù)支持?jǐn)?shù)值函數(shù)（sin、cos 等）聚合函數(shù)（sum、avg、stddev 等）分析函數(shù)（sum、avg、stddev 等） 對(duì) SQL、PL/SQL、Java、XML 模式注冊(cè)、ODP.NET 和 OCI/OCCI 的無(wú)縫支持本地浮點(diǎn)約束本地浮點(diǎn)約束cre

7、ate table floating_point_table1 (fltNnull binary_float constraint flt_null not null,dblNnull binary_double constraint dbl_null not null,fltUnq binary_float constraint flt_unq unique,dblUnq binary_double constraint dbl_unq unique,fltChk binary_float constraint flt_chk check ( fltChk is not NaN ) ,dbl

8、Chk binary_double constraint dbl_chk check ( dblChk is not infinite) ,fltPrm binary_float constraint flt_prm primary key);* NaN (不是數(shù)字) e.g. 0/0, infinity/infinity本地浮點(diǎn)約束本地浮點(diǎn)約束create table floating_point_table2 (dblPrm binary_double constraint dbl_prm primary key,fltFrn binary_float constraint flt_frn

9、 references floating_point_table1(fltPrm) on delete cascade);PL/SQL 示例示例 使用歐拉級(jí)數(shù)計(jì)算 = sqrt ( 6 * ( 1 + 1/2*2 + 1/3*2 + . ) ) 使用牛頓方法近似真值的平方根 賦值、加、乘、除、比較、絕對(duì)值 procedure Euler_Pi_Series is . begin pi := one; prev_pi := zero; prod_over_six := zero; step := zero; while pi - prev_pi toler loop prev_pi := pi;

10、 step := step + one; prod_over_six := prod_over_six + one/(step*step); prod := six*prod_over_six; prev_root := prod; root := prod/two; while Abs(root - prev_root) root_toler loop prev_root := root; root := (root + prod/root)/two; end loop; pi := root; end loop; end Euler_Pi_Series; subtype My_Number

11、 is number; zero constant My_Number := 0.0; one constant My_Number := 1.0; two constant My_Number := 2.0; six constant My_Number := 6.0; toler constant My_Number := 0.00000000001; root_toler constant My_Number := toler/1000.0; root My_Number; prev_root My_Number; prod_over_six My_Number; prod My_Num

12、ber; pi My_Number; prev_pi My_Number; step My_Number; subtype My_Number is binary_double; zero constant My_Number := 0.0d; one constant My_Number := 1.0d; two constant My_Number := 2.0d; six constant My_Number := 6.0d; toler constant My_Number := 0.00000000001d; root_toler constant My_Number := tole

13、r/1000.0d; root My_Number; prev_root My_Number; prod_over_six My_Number; prod My_Number; pi My_Number; prev_pi My_Number; step My_Number;PL/SQL 示例示例 使用歐拉級(jí)數(shù)計(jì)算 近似 300,000 次迭代 NUMBER 花費(fèi)約 27.7 秒 BINARY_DOUBLE 花費(fèi)約 3.7 秒 提高倍數(shù)：約 7 倍LOB 增強(qiáng)增強(qiáng) LOB 普遍用于存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)（文本、AVI、基因/蛋白序列等）LOB 性能改善性能改善 訪問(wèn)內(nèi)聯(lián) ( 4KB) LOB 時(shí)獲得

14、 5 倍的性能增益 （在 9iR2 中）臨時(shí) LOB 使用引用計(jì)算提供了 幾個(gè)數(shù)量級(jí)的性能增益在讀取時(shí)引用 (Reference on Read)在寫(xiě)入時(shí)復(fù)制 (Copy on Write)超大型超大型 LOB TB 大小的 Lob (8 128 TB) DB_BLOCK_SIZE (2 32 KB) x (4GB 1)新的 DBMS_LOB.GET_STORAGE_LIMIT 函數(shù)OCI、JDBC 和 DBMS_LOB 現(xiàn)在支持大于 4 GB 的 LOB OCILobRead2()、OCIWriteAppend2() 和 OCILobWrite2() 函數(shù) 為 JDBC 和 DBMS_LOB

15、 提供相同的 API集合類型增強(qiáng)集合類型增強(qiáng) 用戶定義的類型、集合類型和引用類型簡(jiǎn)化了結(jié)構(gòu)復(fù)雜的數(shù)據(jù)建模 集合類型用于映射實(shí)際世界應(yīng)用程序中的包含關(guān)系例如，一個(gè)購(gòu)物車包含了許多商品VARRAY 增強(qiáng)增強(qiáng) VARRAY 的類型發(fā)展 CREATE TYPE email_list_typ AS OBJECT ( section_no NUMBER, emails email_list_arr);/CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;/ALTER TYPE email_varray_typ MODIFY LIMIT 1

16、00 CASCADE; 在臨時(shí)表中支持 VARRAY 列提供應(yīng)用程序開(kāi)發(fā)靈活性嵌套表增強(qiáng)嵌套表增強(qiáng) 將一個(gè)不同的表空間用于嵌套表的存儲(chǔ)表帶嵌套表的列的表可以在表空間之間分配 I/O 負(fù)載CREATE TABLE purchase_orders ( order_items_column order_items_typ )NESTED TABLE order_items_columnSTORE AS order_items_column_nt (TABLESPACE users);為嵌套表提供的為嵌套表提供的 ANSI SQL 多集多集合操作合操作 嵌套表存儲(chǔ)無(wú)序的元素集合集合 只包含唯一的元素多

17、集合多集合 可以包含重復(fù)的元素 嵌套表上的各種多集合操作為嵌套表提供的多集合操作為嵌套表提供的多集合操作 Cardinality、Collect、Multiset Except、Multiset Intersection、Multiset Union、Powermultiset、Powermultiset_by_Cardinality 和 Set 操作查找熱銷商品集合的強(qiáng)大工具（市場(chǎng)指數(shù)總覽分析）多集合操作示例多集合操作示例create type categories as table of int;/ create table carts (c1 int, c2 categories) ne

18、sted table c2 store as tb1_c2; insert into carts values(1, categories(1,2,3,4);insert into carts values(2, categories(2,4,6); select t1.c2 multiset intersect t2.c2 from carts t1, carts t2 where t1.c1=1 and t2.c1=2; categories(2,4) - frequent set嵌套表比較嵌套表比較 Equal 和 Not Equal、In、Submultiset、Member Of、E

19、mpty 和 Is Not A SetSELECT * FROM customer cWHERE item_typ(2) MEMBER OF c.freq_set; 在在 PL/SQL 中也提供中也提供declare type Tab_t is table of pls_integer; a Tab_t := Tab_t ( 1, 2, 3, 4, 5 );begin if 3 member of a then Show ( member ); end if;end;SQL 小結(jié)小結(jié) Oracle 數(shù)據(jù)庫(kù) 10g SQL 引擎 聚合來(lái)自不同的分布式數(shù)據(jù)源的數(shù)據(jù)網(wǎng)格信息無(wú)縫地集成 Java、XM

20、L 和基本數(shù)據(jù)處理功能一個(gè)全面的高性能和基于標(biāo)準(zhǔn)（SQL-2003 W3C、J2SE、POSIX 等）的特性集簡(jiǎn)化的應(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ù)組語(yǔ)句中的稀疏綁定數(shù)組 您的 PL/SQL 表最初是密集的，例如，是由 BULK COLLECT 填充的 您刪除了一些

21、元素 您想將這些元素插入到某個(gè)表中forall j in INDICES OF sparse_emp_records BETWEEN AND - optional insert into new_employees values sparse_emp_records(j);FORALL 語(yǔ)句中的稀疏綁定數(shù)組語(yǔ)句中的稀疏綁定數(shù)組 您有一個(gè) PL/SQL 表（密集或稀疏） 您想將這些記錄的一個(gè)指定的子集插入到某個(gè)表中forall j in VALUES OF pointer_tab insert into new_employees values emp_records(j);編譯器警告編譯器警告

22、create or replace procedure P is debugging boolean := false;begin if debugging then Dbms_Output.Put_Line ( debugging ); end if;end P;/SP2-0804: Procedure created with compilation warningsShow ErrorsPLW-06002: Unreachable codealter session set plsql_warnings = ENABLE:ALL;編譯器警告編譯器警告create or replace p

23、rocedure P ( i in out clob ) isbegin i := x;end P;/SP2-0804: Procedure created with compilation warningsShow ErrorsPLW-07203: parameter I may benefit from use of the NOCOPY compiler hintalter session set plsql_warnings = ENABLE:PERFORMANCE;新提供的程序包新提供的程序包 Utl_Mail它的 API 反映了，比如說(shuō)，Netscape Communicator

24、的最終用戶的思維模式無(wú)需 SMTP 協(xié)議的知識(shí)（與使用 Utl_Smtp 相比）確保簡(jiǎn)單化的一些限制 Utl_Compress提供了一個(gè) API 來(lái)壓縮和解壓縮 RAW 或 BLOB （服務(wù)器端）議程議程 新的 SQL 特性 新的 PL/SQL 特性 PL/SQL 的速度是原來(lái)的兩倍的速度是原來(lái)的兩倍 總結(jié) 問(wèn)答PL/SQL 編譯和執(zhí)行編譯和執(zhí)行 101PL/SQL 源代碼源代碼前端前端IR = Diana后端后端對(duì)象代碼對(duì)象代碼 = MCodePVM全新的后端全新的后端 10g 引進(jìn)了一個(gè)全新的后端，又叫做優(yōu)化代碼生成器 它全面地查看源代碼所表達(dá)的內(nèi)容，然后刪除對(duì)最終的正確結(jié)果確實(shí)沒(méi)有影響的

25、所有計(jì)算 提供了實(shí)現(xiàn)巨大 性能的潛力全新的后端全新的后端 在 9iR2 中的 ORACLE 可執(zhí)行文件中，新的代碼生成器與舊的代碼生成器并存，一個(gè)允許在兩者之間進(jìn)行選擇的開(kāi)關(guān) 兩者都曾在回歸測(cè)試中使用 因此我們保證它是安全的！ 我們沒(méi)有將這個(gè)開(kāi)關(guān)提供給客戶重要的重要的 pvm 升級(jí)升級(jí) 刪除了過(guò)時(shí)的指令 增加了新的指令例如 one-shot multi concatenate 簡(jiǎn)化了用于使用 PVM 指令的系統(tǒng) 實(shí)施經(jīng)過(guò)調(diào)整的指令的 C 例程改進(jìn)了改進(jìn)了本地本地 編譯編譯 數(shù)據(jù)庫(kù)中的 DLL 對(duì)于數(shù)據(jù)庫(kù)管理員，配置更加簡(jiǎn)單 改進(jìn)了生成的 C 代碼讓我們修改其工作方式PL/SQL 源代碼源代碼前

26、端前端IR = Diana后端后端對(duì)象代碼對(duì)象代碼 = MCodePVMPL/SQL 編譯和執(zhí)行編譯和執(zhí)行 101對(duì)象代碼對(duì)象代碼 = MCode 或本地計(jì)算機(jī)代碼或本地計(jì)算機(jī)代碼硬件硬件性能實(shí)驗(yàn)結(jié)果性能實(shí)驗(yàn)結(jié)果 由 PL/SQL 小組進(jìn)行的測(cè)試 Beta 程序測(cè)試 由 Oracle 的應(yīng)用程序部進(jìn)行的測(cè)試由由 PL/SQL 小組進(jìn)行的測(cè)試小組進(jìn)行的測(cè)試 17 個(gè)自包含的 PL/SQL 程序的基準(zhǔn)測(cè)試套件 設(shè)計(jì)用來(lái)實(shí)踐大范圍的結(jié)構(gòu)和編程習(xí)慣 一些測(cè)試選擇用來(lái)演示特定的優(yōu)化器效果例如，從一個(gè)循環(huán)中刪除一個(gè)常量賦值由由 PL/SQL 小組進(jìn)行的測(cè)試小組進(jìn)行的測(cè)試 下一張幻燈片顯示了每一個(gè)程序在 9

27、iR2 INTERPRETED 和 10g NATIVE 中的速度 9iR2 中速度相同 10g 中顯示了兩種測(cè)量方式的完成 CPU 時(shí)間之比由由 PL/SQL 小組進(jìn)行的測(cè)試小組進(jìn)行的測(cè)試 大多數(shù)測(cè)試程序速度提高超過(guò) 2 倍 一些測(cè)試程序速度提高遠(yuǎn)超過(guò) 2 倍 速度提高 10 倍的程序使用 BINARY_INTEGER，并擁有特別易于優(yōu)化的編程習(xí)慣 procedure P is /* constrained binary_integer subtypes */ a positiven := 1; b positiven := 1; c positiven := 1; d positiven:

28、=1; begin for j in 1.i_nof_iterations loop b := j /* constraint check */; d := 42 /* constant assignment within a loop */; c := d + b /* can be combined. */; a := b + c /* .c not used except here */; end loop; end P;由由 PL/SQL 小組進(jìn)行的測(cè)試小組進(jìn)行的測(cè)試 下一張幻燈片顯示了更多詳細(xì)信息 基準(zhǔn)是 Oracle Version 8.0.6 顯示了 8i9iR2 INTERPR

29、ETED9iR2 NATIVE10g INTERPRETED10g NATIVE 速度提高的倍數(shù)由由 PL/SQL 小組進(jìn)行的測(cè)試小組進(jìn)行的測(cè)試 TMI？ 下一張幻燈片為每一個(gè)測(cè)試的版本顯示了這 17 個(gè)程序的集合的統(tǒng)計(jì)信息：中值最大和最小值第 1 和第 3 四分點(diǎn)（一半的觀察都落在第 1 和第 3 四分點(diǎn)之間）由由 PL/SQL 小組進(jìn)行的測(cè)試小組進(jìn)行的測(cè)試 8i 比 8.0.6 快 9iR2 比 8i 快 NATIVE 始終比 INTERPRETED 快在 9iR2 中在 10g 中 10g 始終比 9iR2 快INTERPRETED 與 INTERPRETEDNATIVE 與 NATIVE由由 IFS 進(jìn)行的進(jìn)行的 Beta 程序測(cè)試程序測(cè)試由由 IFS 進(jìn)行的進(jìn)行的 Beta 程序測(cè)試程序測(cè)試 IFS 應(yīng)用程序基于 web 和門戶技術(shù)提供用于制造、供應(yīng)鏈管理、客戶關(guān)系管理、服務(wù)供應(yīng)、財(cái)務(wù)管理、產(chǎn)品開(kāi)發(fā)、維護(hù)和人力資源管理的 60 多種企