oracledatabase11gplsql編程實(shí)戰(zhàn)筆記

1、Chap1DML語句是select 、insert、update、delete和mergeDDL語句是create、alter、drop、rename、truncate、commentDCL語句是grant、revokeTCL語句是commit、rollback和savepointsql16個(gè)基本命令參考書OCA認(rèn)證考試指南（IZ0-051）清華大學(xué)出版社oracle database sql language reference 11g有非遵循格式字符串依賴于格式掩碼chap22.1.3 關(guān)于語句中有多個(gè)單引號(hào)時(shí)處理：1、select Its a bird,no plan cant be a

2、s pharsefrom dual; 此處兩個(gè)單引號(hào)即為一個(gè)單引號(hào)2、只能用q 再加(語句)select q(Its a bird,no plan cant be) as pharsefrom dual;均輸出PHARSE-Its a bird,no plan cant be2.1.4定義變量與申明變量的區(qū)別：定義變量即為變量分配名稱并指定數(shù)據(jù)類型；申明變量首先需要定義變量，然后為其賦值。(賦值也稱為初始化)替代變量前面要加&前綴 且若替代變量為字符型時(shí)要加兩個(gè)單引號(hào) 如&adeclare lv_whom varchar2(20);/*lv-whom為申明變量，a為替代變量，a沒有變量類型*/

3、 begin lv_whom := &a; end; 或者declare lv_whom varchar2(20); begin lv_whom := &a; end;但是要在輸入框中字符加兩個(gè)單引號(hào)替代變量用define申明，且定義時(shí)不可以指定類型，默認(rèn)為char型Define x=emp;Select * from &x; /*調(diào)用要用&，此處不加單引號(hào)，解析后即為emp表*/ define x=adasd;select &x z from dual;/*此處解析后變?yōu)閟elect adasd z from dual ，此處必須要有單引號(hào)，使adasd作為直接變量，否則出錯(cuò)*/所以替代變量

4、是否加單引號(hào)要根據(jù)解析的值來確定注意：避免在聲明塊中將任何實(shí)參賦給局部變量 （constant變量除外），且替代變量與綁定變量都不應(yīng)該在聲明塊中賦值定義為constant的變量，必須在申明塊中申明，這意味著常量必須先定義，再給他賦一個(gè)不變的值綁定變量（bind）用var或variable申明使用冒號(hào)（:）作為前綴var a numberbegin :a := 22;end;上面也可以輸出結(jié)果，下面也可以輸出結(jié)果，但二者不能同時(shí)放一塊print a; /*此處a前面不能加冒號(hào)，print可用來輸出所有類型的變量*/另外也可通過exec直接給綁定變量賦值：SQL var x number /*必須

5、要先定義再賦值，而替代變量定義時(shí)不需指定類型，直接賦值即可*/SQL var y numberSQL exec :x :=1;:y :=2;/*exec 等價(jià)于begin end*/ PL/SQL procedure successfully completedx-1y-2也可以先定義好綁定變量，再將其賦給其他變量：var a numberbegin :a := 22;end;現(xiàn)將其運(yùn)行再declarelv_a number;begin lv_a:= :a; dbms_output.put_line(hello，|lv_a|.); end;結(jié)果：hello,22.將上面的代碼放入到d:q.sq

6、l中用d:q.sql;調(diào)用即可，但是里面的代碼要正確2.1.5命名塊1、過程塊create or replace procedure abc (aa varchar2) isbegin dbms_output.put_line(sdhl|aa);end;exec abc(dkjdhhdj);可能出現(xiàn)錯(cuò)誤一：【ORA-00955: 名稱已由現(xiàn)有對(duì)象使用】這個(gè)錯(cuò)誤說的不僅僅是有可能你有存儲(chǔ)過程使用了【abc】，還有可能是你有表名叫【abc】或者其他oracle對(duì)象叫【abc】。說明：其實(shí)由于你已經(jīng)使用了【create or repalace procedure】即使有存儲(chǔ)過程叫【abc】也會(huì)被覆蓋

7、掉，你用下面的命令查詢一下，有什么對(duì)象叫【abc】，刪掉它或者換一個(gè)存儲(chǔ)過程名字?！維ELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME=STUDENT2;】錯(cuò)誤二：過程中的參數(shù)不能指定具體長(zhǎng)度2、函數(shù)塊錯(cuò)誤一:函數(shù)里面要有return，不是dbms_output.put_line（但實(shí)驗(yàn)之后二者均可以成功創(chuàng)建函數(shù)，但是調(diào)用時(shí)只有return的能沒有錯(cuò)誤的執(zhí)行）create or replace function abc (aa varchar2)return varchar2 isbegin dbms_outpu

8、t.put_line(sdhl|aa);end;改為create or replace function abc(aa varchar2) return varchar2 isbegin return sdhl | aa;end;但函數(shù)不能像過程一樣來執(zhí)行，因?yàn)閑xecute命令不允許管理函數(shù)返回值。必須要用call或begin dbms. end命令將返回值放入一個(gè)綁定變量（要先定義）中，在輸出。（必須要先執(zhí)行函數(shù)，在執(zhí)行下面語句，不能一起運(yùn)行）variable result varchar2(20);call abc(akdhak) into :result; -call后面只能用綁定變量

9、？或者：SQL begin 2 dbms_output.put_line(abc(du); 3 end; 4 / sdhldu此處若用print則綁定變量不能加冒號(hào)只能為print result;但可以查詢select :result from dual;（exec是sqlplus的命令，只能在sqlplus中使用。call是sql命令，任何工具都可以使用）2.1.6嵌套塊命名塊還可以嵌套在其他命名塊或匿名塊中，但嵌套命名塊并不是已經(jīng)發(fā)布的，這意味著在調(diào)用一個(gè)命名塊時(shí)，被調(diào)用的命名塊可能還沒有定義declare procedure a is begin dbms_output.put_line

10、(b|hello!); /*此處b為被調(diào)用函數(shù)，但之前沒有被解析，所以運(yùn)行會(huì)出錯(cuò)*/ end a; function b return varchar2 is begin return hello!; end b;begin a;end;注：所有匿名塊都會(huì)在程序都會(huì)在實(shí)際執(zhí)行前進(jìn)行分析，分析是一個(gè)編譯過程。分析過程將會(huì)識(shí)別標(biāo)識(shí)符（保留字）、預(yù)定義標(biāo)識(shí)符、引用標(biāo)識(shí)符、用戶定義變量、子例程或UDT。命名塊也是標(biāo)識(shí)符，PL/SQL按照自頂向下的次序，將標(biāo)識(shí)符讀取到內(nèi)存中。上例中函數(shù)b因?yàn)樵谶^程a的下面還沒被解析，所以會(huì)出錯(cuò)。 使用“前向引用”可以修正這個(gè)問題，函數(shù)或過程的前向引用只需要函數(shù)或過程的署

11、名，不需要同時(shí)包括署名和實(shí)現(xiàn)，這些署名在PL/SQL中叫做“占位程序（stub）”。占位程序允許編譯過程在實(shí)現(xiàn)命名塊之前接受其標(biāo)識(shí)符名稱。更正：declare procedure a;function b return varchar2;procedure a is begin dbms_output.put_line(b|hello!); end a; function b return varchar2 is begin return hello!; end b;begin a;end;2.2 變量：類型、賦值和運(yùn)算符2.2.1文本數(shù)據(jù)類型用偽列來隱式地定義數(shù)據(jù)類型。偽列如%typeCha

12、r(20)為一個(gè)定長(zhǎng)的類型，不管其中變量的長(zhǎng)度有沒有達(dá)到20，最后顯示長(zhǎng)度均為20字符Varchar2（20）則要根據(jù)實(shí)際情況來確定其長(zhǎng)度Clob（character large object 字符大對(duì)象）同varchar22.2.2日期和時(shí)間戳類型1、日期有兩種字符串字面值賦值支持到date類型的隱式轉(zhuǎn)換Lv_date date := 22-mon-75；或者Lv_date date := 22-mon-1975；除了上面兩種，任何其他字符串字面值都要求使用to_date內(nèi)置SQL函數(shù)來覆蓋格式掩碼如lv_date_1 date := to_date(,YYYYMMDD);2、間隔間隔子類型

13、允許將天的間隔表示秒，將年的間隔表示月份數(shù)Interval day to second 數(shù)據(jù)類型的默認(rèn)值在兩個(gè)date相減時(shí)能起作用，只要在執(zhí)行減法之前將其轉(zhuǎn)化為timestamp，因?yàn)閠o_timestamp函數(shù)保留了date的精度，而該精度低于timestamp將天的間隔轉(zhuǎn)換為秒的數(shù)據(jù)類型為interval day/day(4或其他數(shù)) to seconddeclare lv_interval interval day to second; lv_end_day date := sysdate; -sysdate與date對(duì)應(yīng) lv_start_day date := 18-4月-2012

14、; begin lv_interval := to_timestamp(lv_end_day)-to_timestamp(lv_start_day); dbms_output.put_line(lv_interval); end;結(jié)果-20 14:56:54. PL/SQL procedure successfully completedDate數(shù)據(jù)類型默認(rèn)支持2位數(shù)字表示的天，timestamp的精度要求使用9位數(shù)字表示的天或者declare lv_interval interval day(9) to second; lv_end_day timestamp := systimestamp

15、; -systimestamp與timestamp對(duì)應(yīng) lv_start_day timestamp := 18-4月-2012; begin lv_interval := lv_end_day-lv_start_day; dbms_output.put_line(lv_interval); end;結(jié)果- 02:52:41. PL/SQL procedure successfully completed將年的間隔轉(zhuǎn)化為月份數(shù)的數(shù)據(jù)類型為interval year to month從一個(gè)日期中提取年 用 to_char(extract(year from lv_end_day)完整代碼：dec

16、lare lv_a date := 20-4月-2009; a varchar2(12);begin a := to_char(extract(year from lv_a); dbms_output.put_line(a);end;結(jié)果為：20093、時(shí)間戳 （timestamp）Timestamp數(shù)據(jù)類型精度要比date數(shù)據(jù)類型精度高22.3 數(shù)值類型可以將number數(shù)據(jù)類型隱性轉(zhuǎn)化為intger類型，可能會(huì)丟失一些小數(shù)點(diǎn)Binary_float 32位浮點(diǎn)數(shù)Binary_double 是一個(gè)64位浮點(diǎn)數(shù)2.2.4復(fù)合變量類型SQL UDT用于保存一個(gè)數(shù)據(jù)結(jié)構(gòu)2.3控制結(jié)構(gòu)1、If語句，

17、if elsif else2、case語句 分為簡(jiǎn)單的case語句 case when then else end case (break隱式存在)與搜索型的case語句case when then else end case簡(jiǎn)單case語句可使用char、nchar、varchar2數(shù)據(jù)類型，而搜索case語句可以使用任意布爾表達(dá)式，搜索case語句不局限于等值匹配2.3.3、循環(huán)結(jié)構(gòu)Loop循環(huán)退出要借助exit或exit when語句For loop循環(huán) ：分為范圍循環(huán)和游標(biāo)循環(huán)插入：關(guān)于游標(biāo)（見E:sassql各類知識(shí)要點(diǎn)游標(biāo)）A、 范圍循環(huán)即for i in 1.4 loopB、 游

18、標(biāo)循環(huán) 1、隱式 for i in (select 語句)loop 2、顯式 for i in cur_v(游標(biāo))loop其中第2種要比第一種要有更好的可讀性（以后用這種）C、 where current of 字句while循環(huán)while (.)loop內(nèi)可含continue/goto語句Simple 循環(huán)語句即利用隱式游標(biāo)屬性來進(jìn)行循環(huán)判斷 如if SQL%FOUND then .else .end if ;24批量操作（見E:sassql各類知識(shí)要點(diǎn)游標(biāo)）批量處理是進(jìn)行成批處理和大批量處理的默認(rèn)選擇本章小結(jié)：1、始終在執(zhí)行塊中進(jìn)行賦值或初始化。除非局部變量被視為常量2、在實(shí)現(xiàn)任何局部塊命

19、名之前，始終未其定義前向引用規(guī)范，從而確保這些局部命名塊能夠互相調(diào)用3、應(yīng)盡可能使用表集合。避免用varray，因?yàn)榍罢卟皇芄潭ù笮〉南拗?。Varray通常需要更改大小限制，這將花費(fèi)時(shí)間和金錢4、顯式游標(biāo)應(yīng)盡可能使用形參5、應(yīng)盡可能使用SQL數(shù)據(jù)類型作為函數(shù)的返回值。SQL數(shù)據(jù)類型不需要編寫管道化表函數(shù)對(duì)其進(jìn)行封裝就可以在SQL中使用，并且數(shù)據(jù)類型總是可檢測(cè)的，因?yàn)樗悄夸浿械囊粋€(gè)命名集合或結(jié)構(gòu)本章的問題1、在程序塊中至少包括一個(gè)null語句，否則會(huì)編譯錯(cuò)誤2、PL/SQL中必須定義一個(gè)record結(jié)構(gòu)，因?yàn)樵摻Y(jié)構(gòu)是僅適用于PL/SQL環(huán)境的變量3、for循環(huán)必須知道其范圍ide上界，而游標(biāo)集

20、返回的行數(shù)設(shè)置了游標(biāo)for循環(huán)的上邊界4、可以使用bulk collect into 語句講一個(gè)完整游標(biāo)或游標(biāo)的某些行選擇到一個(gè)記錄結(jié)構(gòu)集合中?？梢允褂胠imit關(guān)鍵字來限制行集5、在循環(huán)內(nèi)部可以使用forall語句，但該語句沒有結(jié)束塊語句，所以需要end forall或end loop或end forall loop語句來結(jié)束forall語句。Chap3事物作用域3.1數(shù)據(jù)庫(kù)ACID測(cè)試Atomicity 原子性、consistency一致性、isolation隔離性、durability持久性3.2多版本并發(fā)控制MVCC（multiversioned concurrency control

21、）使用數(shù)據(jù)庫(kù)快照為用戶提供數(shù)據(jù)庫(kù)的永久內(nèi)存副本3.3 3.3.1保存點(diǎn)、提交和事務(wù)回滾若有2個(gè)保存點(diǎn)，且回滾到了第一個(gè)保存點(diǎn)，則第二個(gè)保存點(diǎn)就不存在了3.3.2提交 注意：A、 數(shù)據(jù)字典作強(qiáng)制隱式的提交B、 Commit命令中的注釋將寫入到dba_pc2_pending字典視圖中C、 必須具有force transaction 或 force any transaction 系統(tǒng)權(quán)限才能強(qiáng)制提交D、 Nowait選項(xiàng)不會(huì)驗(yàn)證對(duì)重做和歸檔日志文件的寫入E、 Work選項(xiàng)將會(huì)在所有commit語句中有效例1、標(biāo)準(zhǔn)commit UPDATE hr.employees SET salary = sal

22、ary * 1.03 WHERE department_id in ( 20, 30, 40 );Commit或?qū)ommit改為 COMMIT WORK WRITE IMMEDIATE WAIT;例2、含有nowait和batch選項(xiàng)的commit使用nowait選項(xiàng)將不會(huì)驗(yàn)證對(duì)重做和歸檔日志的寫入避免重寫oracle的提交等待過程。這樣做可能會(huì)使自己的事物陷入危險(xiǎn)例3、強(qiáng)制commit語句并寫入注釋INSERT INTO hr.job_historyVALUES ( 100, 01-Jan-2000, trunc( sysdate ), AD_PRES, 90 );COMMIT COMME

23、NT In-doubt transaction forced by process xyz on date 123;Commit force 2.33.192;發(fā)出commit force需要一個(gè)具有dba權(quán)限的賬戶。單引號(hào)（）內(nèi)的信息代表可疑的事物id。3.4DML鎖定和隔離控制為了防止不可重復(fù)讀取或影子讀取a、 設(shè)置事物級(jí)別為讀取一致（在跟蹤分布式事物時(shí)，設(shè)置事物語句命名將會(huì)非常有益）set transaction name 可更好地監(jiān)控長(zhǎng)時(shí)間的事務(wù)。SET TRANSACTION READ ONLY NAME Distributed to NYC;-添加到該設(shè)置事物語句的distribu

24、ted to NYC注釋將會(huì)保存在dba_pc2_pending字典視圖中（/dept/itss/docs/oracle/10g/server.101/b10759/statements_10005.htm）SELECT product_id , warehouse_id , quantity_on_hand FROM oe.inventoriesnyc_001 WHERE product_id = 3246;COMMIT;b、 鎖定用戶具有鎖定自己所擁有的全部表的權(quán)限，如果想要鎖定另一個(gè)模式中的表，則必須具有l(wèi)ock any table系統(tǒng)權(quán)限B1

25、在游標(biāo)內(nèi)使用for updateDECLARE CURSOR c_employees IS SELECT * FROM hr.employees FOR UPDATE WAIT 10;-該行鎖定c_employees游標(biāo)中所引用的行。其中wait 10表示，若游標(biāo)所引用的行已經(jīng)存在鎖定，則等待10秒，若前一次鎖定持續(xù)時(shí)間大于10秒，則此次更新將會(huì)被取消，也可以用nowait關(guān)鍵字，表示存在鎖定的情況下立即返回事物BEGIN FOR r_employees IN c_employees LOOP UPDATE hr.employees SET salary = salary * 1.025 WH

26、ERE CURRENT OF C_employees;-該語句引用游標(biāo)中的最新行，最后鎖定會(huì)一直持續(xù)，直至發(fā)出commit或rollback命令 END LOOP;END;B2發(fā)起lock table語句LOCK TABLE hr.employees IN EXCLUSIVE MODE NOWAIT;UPDATE hr.employees SET salary = salary * 1.025 WHERE department_id = 10;COMMIT;與for update語句不同，lock table命令將會(huì)鎖定表中的每一行。此外，短語in exclusive mode 將會(huì)鎖定除se

27、lect語句之外的全部操作有效的鎖定模式有 ：row exclusive模式， 限制最少的鎖定級(jí)別，允許行共享，并防止用戶鎖定整個(gè)表或鎖定到row share模式row share模式， 除了沒有獨(dú)占共享的限制之外，該模式等同于row exclusiveshare模式， 允許select但是不允許其他更新share row exclusive 模式，等同于share模式，但是同時(shí)禁止用戶鎖定到share模式exclusive模式 限制最多的級(jí)別，阻止除select之外的所有DML操作C、 改善事物性能 利用批量DML操作的功能3.5調(diào)用者和定義者權(quán)限以程序所有者的身份來保留程序執(zhí)行的全部權(quán)限：

28、在命名程序頭使用authid definer 關(guān)鍵字例1發(fā)出定義者權(quán)限CREATE OR REPLACE FUNCTION hr.quarterly_sales( pi_employee_id in number, pi_quarter in date)AUTHID DEFINERAS. declarative code goes here .BEGIN. do something here and return .EXCEPTION. handle the exception here and return .END;例2.發(fā)出調(diào)用者權(quán)限以程序調(diào)用者的身份來調(diào)用程序，用關(guān)鍵字authid c

29、urrent_userCREATE OR REPLACE FUNCTION hr.give_raise( pi_employee_id in number )AUTHID CURRENT_USERAS. declarative code goes here .BEGIN. do something here and return .EXCEPTION. handle the exception here and return .END;本章小結(jié)1、確保自己有一個(gè)良好的備份。僅僅激活像rman這樣的備份程序是不夠的2、使用簡(jiǎn)潔而有意義的名稱來命名保存點(diǎn)3、強(qiáng)制可疑事務(wù)要求深入了解oracle系統(tǒng)

30、更改號(hào)（system change number,SCN）和數(shù)據(jù)字典。只有當(dāng)事務(wù)無法再次嘗試時(shí)才使用commit force命令4、使用set transaction name 參數(shù)對(duì)分布式事務(wù)命名測(cè)驗(yàn)小結(jié)1、 原子性意味著一個(gè)事務(wù)全部寫入到永久存儲(chǔ)中或任何部分都沒有寫入到永久存儲(chǔ)中2、 一致性意味著一個(gè)并發(fā)多用戶系統(tǒng)中為所有事務(wù)分配相同的服務(wù)cpu時(shí)間量和內(nèi)存3、 隔離性意味著一個(gè)事務(wù)的任何部分在事務(wù)完成并提交之前都不可見4、 持久性意味著事務(wù)在完成之后寫入到冗余磁盤陣列5、 Undo_retention 參數(shù)（還不是很熟悉），設(shè)置語句在內(nèi)存中運(yùn)行的時(shí)間長(zhǎng)度Chap4 錯(cuò)誤管理數(shù)據(jù)庫(kù)強(qiáng)化的兩

31、種方法是使用DBMS_ASSERT包和綁定變量。DBMS_ASSERT包用于驗(yàn)證輸入?yún)?shù)的形式是否正確，以及模式和對(duì)象名稱是否實(shí)際存在。綁定變量防止匿名PL/SQL塊的嵌套4.1錯(cuò)誤類型4.1.1編譯時(shí)錯(cuò)誤（即語法上有錯(cuò)誤）很多程序員基于編寫較大的代碼塊，而不愿意停下來執(zhí)行臨時(shí)編譯。一次性調(diào)試很多錯(cuò)誤可能會(huì)花費(fèi)大量時(shí)間查看不重要的信息，尤其當(dāng)錯(cuò)誤棧中打印出許多不必要的信息時(shí)。一種更高效的編程方法是按邏輯分組來編寫每個(gè)程序塊，在函數(shù)單元完成后執(zhí)行編譯。例：循環(huán)式編碼方法可以避免不必要的調(diào)試4.1.2語義錯(cuò)誤語義錯(cuò)誤只在程序內(nèi)部發(fā)生的邏輯錯(cuò)誤。如加減乘除的錯(cuò)誤1、 Oracle提供的錯(cuò)誤條件Ora

32、cle提供了超過2300頁的預(yù)定義錯(cuò)誤條件，程序員可以參考他們來編寫或直接調(diào)用2、 Oracle sqlcode和sqlerrmSQLERRM是系統(tǒng)內(nèi)置變量保存了當(dāng)前錯(cuò)誤的詳細(xì)信息。Oracle databases error messages 11g guide 中的每個(gè)錯(cuò)誤都有唯一的代號(hào)和消息。與錯(cuò)誤條件相關(guān)聯(lián)的數(shù)字值稱為sqlcode?？梢允褂胮ragma exception_init關(guān)鍵字將變量與sqlcode代號(hào)關(guān)聯(lián)。當(dāng)希望在發(fā)生oracle錯(cuò)誤的情況下執(zhí)行特定任務(wù)時(shí)，該功能特別有用。例：用pragma exception_init捕獲系統(tǒng)錯(cuò)誤CREATE TABLE hr.emer

33、gency_contact ( employee_id NUMBER , full_name VARCHAR2(50) , phone_home VARCHAR2(15) , phone_cell VARCHAR2(15) , phone_pager VARCHAR2(15) );ALTER TABLE emergency_contact ADD ( CONSTRAINT ec_employee_id_unk UNIQUE ( employee_id, full_name );BEGIN FOR i IN 1 . 2 LOOP -此處因?yàn)檠h(huán)兩次，導(dǎo)致插入的數(shù)據(jù)一樣，所以會(huì)導(dǎo)致違反unique

34、約束- INSERT INTO emergency_contact VALUES ( 1 , Jane Doe , +1.123.456.7890 , NULL , +1.123.567.8901 ); END LOOP;END;輸出ORA-00001: 違反唯一約束條件 (SCOTT.EC_EMPLOYEE_ID_UNK)ORA-06512: 在 line 4用pragma exception_init來捕獲（為什么要捕獲錯(cuò)誤，因?yàn)榘l(fā)現(xiàn)錯(cuò)誤時(shí)oracle則不執(zhí)行其他代碼了，為了對(duì)其執(zhí)行額外的任務(wù)，則就需要進(jìn)行捕獲錯(cuò)誤，此處當(dāng)程序遇到ORA-00001錯(cuò)誤時(shí)，運(yùn)行執(zhí)行dbms，而不會(huì)立即中斷程

35、序）declare unique_constraint exception;pragma exception_init (unique_constraint,-00001);BEGIN FOR i IN 1 . 2 LOOP INSERT INTO emergency_contact VALUES ( 1 , Jane Doe , +1.123.456.7890 , NULL , +1.123.567.8901 ); END LOOP; exception when unique_constraint then dbms_output.put_line(o,you throw the uniq

36、ue_constraint error);END;輸出o,you throw the unique_constraint error PL/SQL procedure successfully completed3、 異常作用域Pl/sql塊的連續(xù)性通過在匿名子塊中封裝可能的錯(cuò)誤代碼來維護(hù)。封裝技術(shù)有：標(biāo)準(zhǔn)子塊封裝、循環(huán)子塊封裝、保存點(diǎn)重定向、goto跳轉(zhuǎn)例1、 封裝子塊DECLARE ln_parent NUMBER; ln_child_level1 NUMBER; ln_child_level2 NUMBER; ln_random_0_1 NUMBER;BEGIN BEGIN DBMS_O

37、UTPUT.PUT_LINE ( Made it past Parent. ); ln_random_0_1 := ROUND ( DBMS_RANDOM.VALUE ( 0, 1 ); ln_parent := 1 / ln_random_0_1; BEGIN DBMS_OUTPUT.PUT_LINE ( Made it past Child Level 1. ); ln_random_0_1 := ROUND ( DBMS_RANDOM.VALUE ( 0, 1 ); ln_child_level1 := 1 / ln_random_0_1; BEGIN DBMS_OUTPUT.PUT_L

38、INE ( Made it past Child Level 2. ); ln_random_0_1 := ROUND ( DBMS_RANDOM.VALUE ( 0, 1 ); ln_child_level2 := 1 / ln_random_0_1; END; END; END;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( SQLERRM );END;例2、 在循環(huán)內(nèi)封裝DECLARE ln_employee_id NUMBER; ln_order_total NUMBER; CURSOR c_employee IS SELECT *

39、FROM hr.employees;BEGIN FOR r_employee IN c_employee LOOP ln_employee_id := r_employee.employee_id; DECLARE no_salesman_found exception; BEGIN SELECT SUM ( order_total ) INTO ln_order_total FROM oe.orders WHERE sales_rep_id = ln_employee_id; IF ln_order_total IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE (

40、ln_order_total ); ELSE RAISE no_salesman_found; END IF; EXCEPTION WHEN no_salesman_found THEN DBMS_OUTPUT.PUT_LINE ( Caught NO_SALESMAN_FOUND ); END; END LOOP;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( SQLERRM );END;例3、 保存點(diǎn)異常重試4、 定義自定義錯(cuò)誤條件為了定義自己的錯(cuò)誤條件，首先必須創(chuàng)建一個(gè)異常變量。最后在代碼的異常塊內(nèi)必須說明這個(gè)錯(cuò)誤的處理方法例：DECL

41、ARE ln_order_total number; ln_promotion_id number := 1; ln_order_count number; no_promo_found exception;BEGIN SELECT COUNT(*) INTO ln_order_count FROM oe.orders WHERE promotion_id = ln_promotion_id; IF ln_order_count 0 THEN SELECT SUM ( order_total ) INTO ln_order_total FROM oe.orders WHERE promotio

42、n_id = ln_promotion_id; ELSE raise no_promo_found; END IF;EXCEPTION WHEN no_promo_found THEN DBMS_OUTPUT.PUT_LINE ( No Sales found for Promotion: |ln_promotion_id);END;開發(fā)人員總是使用這個(gè)邏輯，問題在于oracle已經(jīng)使用no_data_found條件處理這種情況。下面對(duì)其精簡(jiǎn)：DECLARE ln_order_total number; ln_promotion_id number := 1;BEGIN SELECT orde

43、r_total INTO ln_order_total FROM oe.orders WHERE promotion_id = ln_promotion_id;IF ln_order_count 0 THEN SELECT SUM ( order_total ) INTO ln_order_total FROM oe.orders WHERE promotion_id = ln_promotion_id;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (No Sales found for Promotion: |ln_promot

44、ion_id);END;注：掌握并適當(dāng)利用內(nèi)置錯(cuò)誤，以簡(jiǎn)化自己的代碼Raise_application_error 過程是DBMS_STANDARD內(nèi)置包的組成成分，該過程允許生成ORA-相關(guān)信息，而不需要首先聲明異常變量或使用exception_init指令（可以簡(jiǎn)化代碼）例使用Raise_application_error捕獲錯(cuò)誤條件DECLARE CURSOR c_rental IS SELECT c.member_id , c.first_name| |c.last_name full_name , t.transaction_amount FROM video_store.trans

45、action t , video_store.rental r , video_store.contact c WHERE r.rental_id = t.rental_id AND r.customer_id = c.contact_id;BEGIN FOR r_rental IN c_rental LOOP IF r_rental.transaction_amount 75 THEN RAISE_APPLICATION_ERROR ( -20001, No transaction may be more than $75, TRUE ); END IF; END LOOP;END;注：上述

46、程序并未創(chuàng)建異常變量。此外，將用戶定義20001 SQLCODE與錯(cuò)誤消息關(guān)聯(lián)。其中的true參數(shù)通知過程在錯(cuò)誤棧中包含該錯(cuò)誤。第三個(gè)參數(shù)的默認(rèn)值是false，通知RAISE_APPLICATION_ERROR過程清除錯(cuò)誤棧，并只打印錯(cuò)誤消息。4.2 PL/SQL工具用戶編寫的許多PL/SQL應(yīng)用程序?qū)⑿枰ㄙM(fèi)一定的時(shí)間來執(zhí)行，一種最常見但卻存在問題的技術(shù)是在提交時(shí)使用時(shí)間戳。更新的時(shí)間戳列標(biāo)記哪些行已經(jīng)被程序修改。然而問題在于，提交本身基于需呀耗費(fèi)不少時(shí)間，將其放在應(yīng)用程序的中間會(huì)引發(fā)如下問題：減慢應(yīng)用程序?qū)е耾racle服務(wù)器上的內(nèi)存和cpu占用率突然增高生成大量的重做日志信息 我們需要的

47、是一種記錄程序活動(dòng)而不需要額外消耗太多系統(tǒng)資源的方法。這種輸出也稱為程序工具（利用內(nèi)置程序結(jié)合實(shí)際需要寫的代碼）本章小結(jié)：1、如果PL/SQL程序在創(chuàng)建時(shí)沒有進(jìn)行適當(dāng)?shù)腻e(cuò)誤處理和工具化，則開發(fā)人員需要花很多時(shí)間來維護(hù)并反復(fù)檢查程序結(jié)果。通過工具化代碼并提供適當(dāng)?shù)腻e(cuò)誤管理結(jié)果，將會(huì)節(jié)省大量的維護(hù)和排錯(cuò)時(shí)間。2、運(yùn)行錯(cuò)誤消息回傳到其主調(diào)用應(yīng)用程序，而不要使用when others短語3、使用保存點(diǎn)回滾部分完成的事務(wù)，可以編碼產(chǎn)生孤立數(shù)據(jù)，從而維護(hù)數(shù)據(jù)完整性。4、避免使用goto語句5、掌握并適當(dāng)采用內(nèi)置錯(cuò)誤，以簡(jiǎn)化自己的代碼6、盡可能工具化自己的pl/sql程序測(cè)驗(yàn)小結(jié)：1、 DBMS_ASSET

48、包允許驗(yàn)證輸入?yún)?shù)來過濾傳入的web參數(shù)2、 PLS-錯(cuò)誤是PL-SQl錯(cuò)誤3、 ORA-錯(cuò)誤與常規(guī)數(shù)據(jù)庫(kù)錯(cuò)誤和SQL有關(guān)4、 RAISE_APPLICATION_ERROR允許引發(fā)一個(gè)自定義異常，但是不等價(jià)于RAISE語句。RAISE語句允許調(diào)用申明塊中預(yù)定義的EXCEPTION變量5、 pragma exception_init允許將一個(gè)默認(rèn)錯(cuò)誤號(hào)映射到用戶定義變量，然后可以使用RAISE語句引發(fā)該錯(cuò)誤。6、 SQLCODE對(duì)于PL/SQL塊中引入的錯(cuò)誤返回錯(cuò)誤代碼號(hào)7、 使用在-20000和-20999范圍之間的錯(cuò)誤號(hào)才能引發(fā)RAISE_APPLICATION_ERROR調(diào)用Chap5

49、函數(shù)5.1函數(shù)構(gòu)架5.1.1傳值函數(shù)傳值函數(shù)在調(diào)用時(shí)接受值，在完成時(shí)返回單個(gè)值。形參只有in模式5.1.2傳址函數(shù)在調(diào)用傳址函數(shù)時(shí)，至少發(fā)送一個(gè)或多個(gè)局部變量引用作為實(shí)參。形參有三種可能模式（in、in out、out）傳址函數(shù)并不像傳值函數(shù)那樣將形參全部消耗，雖然in模式參數(shù)被消耗，但in out模式變量通常會(huì)在更改狀態(tài)后返回5.1.3函數(shù)模型選擇當(dāng)希望消耗輸入并產(chǎn)生某種結(jié)果時(shí)，應(yīng)該將函數(shù)實(shí)現(xiàn)為傳值函數(shù)當(dāng)需要驗(yàn)證客戶端或web交互程序的完成情況或返回結(jié)果時(shí)，應(yīng)該使用傳址模式。5.1.5調(diào)用參數(shù)說明create or replace function three(a number :=0,b

50、number :=0,c number := 1) return number isbegin return (a-b)/c;end;位置參數(shù)說明實(shí)際調(diào)用參數(shù)依次映射到形參，但是必須為列表中的每個(gè)形參提供實(shí)參或調(diào)用值，當(dāng)希望跳過某個(gè)形參時(shí)，可以賦值為nullbegin dbms_output.put_line(three(3,4,5);end;結(jié)果為-.2 PL/SQL procedure successfully completed命名參數(shù)說明實(shí)際調(diào)用參數(shù)不需要映射到形參序列begin dbms_output.put_line(three(c =4,b =3,a = 5);end;混合參數(shù)說

51、明位置參數(shù)必須位于命名參數(shù)之前。且在第一個(gè)位置參數(shù)之后，只能省略可選參數(shù)。begin dbms_output.put_line(three(8,c =4);end;第一個(gè)參數(shù)是形參a，所以結(jié)果是8減去0并除以4結(jié)果：2 PL/SQL procedure successfully completed5.2函數(shù)開發(fā)限制：PL/SQL函數(shù)不能包含“數(shù)據(jù)操作語言”（DML）語言，或調(diào)用另一個(gè)包含DML語句的pl/sql單元，否則將會(huì)引發(fā)ORA-14551異常。該錯(cuò)誤表示不能再查詢內(nèi)部執(zhí)行DML操作。一個(gè)問題：雖然可以再SQL語句或PL/SQL塊內(nèi)部調(diào)用不帶參數(shù)列表并且不適用括號(hào)的函數(shù)，但是在CALL語

52、句中調(diào)用相同程序時(shí)必須使用空括號(hào)。例：利用之前的three函數(shù)variable a number;call three() into :a; -沒有括號(hào)則會(huì)出錯(cuò)結(jié)果：Method calleda-0select :a from dual;結(jié)果： :A- 0a-0當(dāng)然，也可以通過使用SQl來調(diào)用該函數(shù)，以節(jié)約時(shí)間（加不加括號(hào)均可以）SQL select three() from dual; THREE() - 良好的編程需要括號(hào)- 0 SQL select three from dual; THREE- 05.2.1確定性字句確定性函數(shù)在return number后面加上關(guān)鍵字deterministic (見各類知識(shí)要點(diǎn))能夠確保對(duì)于任何輸入都可以相同的工作方式工作(插入：設(shè)置輸出列