PLSQL基礎(chǔ)教程必看

/PL/SQL程序設(shè)計(jì)

目錄TOC\o"1-3"\h\z第一章 PL/SQL程序設(shè)計(jì)簡介 4§1.2SQL與PL/SQL 4§1.2.1什么是PL/SQL? 4§1.2.1PL/SQL的好處 4§1.2.2PL/SQL可用的SQL語句 5§1.3運(yùn)行PL/SQL程序 5第二章PL/SQL塊結(jié)構(gòu)和組成元素 6§2.1PL/SQL塊 6§2.2PL/SQL結(jié)構(gòu) 6§2.3標(biāo)識(shí)符 6§2.4PL/SQL變量類型 7§2.4.1變量類型 7§2.4.2復(fù)合類型 9§2.4.3使用%ROWTYPE 11§2.4.4LOB類型* 11§2.4.5Bind變量 11§2.4.6INDEXBYTABLES 12§2.4.7數(shù)據(jù)類型的轉(zhuǎn)換* 13§2.5運(yùn)算符和表達(dá)式(數(shù)據(jù)定義) 13§2.5.1關(guān)系運(yùn)算符 13§2.5.2一般運(yùn)算符 13§2.5.3邏輯運(yùn)算符 13§2.6變量賦值 13§2.6.1字符和數(shù)字運(yùn)算特點(diǎn) 13§2.6.2BOOLEAN賦值 13§2.6.3數(shù)據(jù)庫賦值 13§2.6.4可轉(zhuǎn)換的類型賦值 13§2.7變量作用范圍和可見性 13§2.8注釋 13§2.9簡單例子 13§2.9.1簡單數(shù)據(jù)插入例子 13§2.9.2簡單數(shù)據(jù)刪除例子 13第三章PL/SQL流程控制語句 13§3.1條件語句 13§3.2CASE表達(dá)式 13§3.3循環(huán) 13§3.3標(biāo)號(hào)和GOTO 13§3.4NULL語句 13第四章游標(biāo)的使用 13§4.1游標(biāo)概念 13§4.1.1處理顯式游標(biāo) 13§4.1.2處理隱式游標(biāo) 13§4.1.3游標(biāo)修改和刪除操作 13第五章異常錯(cuò)誤處理 13§5.1異常處理概念 13§5.1.1預(yù)定義的異常處理 13§5.1.2非預(yù)定義的異常處理 13§5.1.3用戶自定義的異常處理 13§5.1.4用戶定義的異常處理 13§5.2異常錯(cuò)誤傳播 13§5.2.1在執(zhí)行部分引發(fā)異常錯(cuò)誤 13§5.2.2在聲明部分引發(fā)異常錯(cuò)誤 13§5.3異常錯(cuò)誤處理編程 13§5.4在PL/SQL中使用SQLCODE,SQLERRM 13第六章存儲(chǔ)函數(shù)和過程 13§6.1引言 13§6.2創(chuàng)建函數(shù) 13§6.3存儲(chǔ)過程 13§6.3.1創(chuàng)建過程 13§6.3.2調(diào)用存儲(chǔ)過程 13§6.3.3開發(fā)存儲(chǔ)過程步驟 13§6.3.4與過程相關(guān)數(shù)據(jù)字典 13第七章包的創(chuàng)建和應(yīng)用 13§7.1引言 13§7.2包的定義 13§7.3包的開發(fā)步驟 13§7.4包定義的說明 13§7.5子程序重載 13§7.6刪除過程、函數(shù)和包 13§7.7包的管理 13第八章觸發(fā)器 13§8.1觸發(fā)器類型 13§8.1.1DML觸發(fā)器 13§8.1.2替代觸發(fā)器 13§8.1.3系統(tǒng)觸發(fā)器 13§8.2創(chuàng)建觸發(fā)器 13§8.2.1觸發(fā)器觸發(fā)次序 13§8.2.2創(chuàng)建DML觸發(fā)器 13§8.2.3創(chuàng)建替代(Instead_of)觸發(fā)器 13§8.2.3創(chuàng)建系統(tǒng)事件觸發(fā)器 13§8.2.4系統(tǒng)觸發(fā)器事件屬性 13§8.2.5使用觸發(fā)器謂詞 13§8.2.6重新編譯觸發(fā)器 13§8.3刪除和使能觸發(fā)器 13§8.4觸發(fā)器和數(shù)據(jù)字典 13§8.5數(shù)據(jù)庫觸發(fā)器的應(yīng)用舉例 13

PL/SQL程序設(shè)計(jì)簡介PL/SQL是一種高級(jí)數(shù)據(jù)庫程序設(shè)計(jì)語言，該語言專門用于在各種環(huán)境下對(duì)ORACLE數(shù)據(jù)庫進(jìn)行訪問。由于該語言集成于數(shù)據(jù)庫服務(wù)器中，所以PL/SQL代碼可以對(duì)數(shù)據(jù)進(jìn)行快速高效的處理。除此之外，可以在ORACLE數(shù)據(jù)庫的某些客戶端工具中，使用PL/SQL語言也是該語言的一個(gè)特點(diǎn)。本章的主要內(nèi)容是討論引入PL/SQL語言的必要性和該語言的主要特點(diǎn)，以和了解PL/SQL語言的重要性和數(shù)據(jù)庫版本問題。還要介紹一些貫穿全書的更詳細(xì)的高級(jí)概念，并在本章的最后就我們?cè)诒緯咐惺褂玫臄?shù)據(jù)庫表的若干約定做一說明。本章主要重點(diǎn)：PL/SQL概述PL/SQL塊結(jié)構(gòu)PL/SQL流程運(yùn)算符和表達(dá)式游標(biāo)異常處理數(shù)據(jù)庫存儲(chǔ)過程和函數(shù)包觸發(fā)器§1.2SQL與PL/SQL§1.2.1什么是PL/SQL?PL/SQL是ProcedureLanguage&StructuredQueryLanguage的縮寫。ORACLE的SQL是支持ANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)標(biāo)準(zhǔn)的產(chǎn)品。PL/SQL是對(duì)SQL語言存儲(chǔ)過程語言的擴(kuò)展。從ORACLE6以后，ORACLE的RDBMS附帶了PL/SQL。它現(xiàn)在已經(jīng)成為一種過程處理語言，簡稱PL/SQL。目前的PL/SQL包括兩部分，一部分是數(shù)據(jù)庫引擎部分；另一部分是可嵌入到許多產(chǎn)品（如C語言，JAVA語言等）工具中的獨(dú)立引擎?？梢詫⑦@兩部分稱為：數(shù)據(jù)庫PL/SQL和工具PL/SQL。兩者的編程非常相似。都具有編程結(jié)構(gòu)、語法和邏輯機(jī)制。工具PL/SQL另外還增加了用于支持工具（如ORACLEForms）的句法，如：在窗體上設(shè)置按鈕等。本章主要介紹數(shù)據(jù)庫PL/SQL內(nèi)容。§1.2.1PL/SQL的好處§有利于客戶/服務(wù)器環(huán)境應(yīng)用的運(yùn)行對(duì)于客戶/服務(wù)器環(huán)境來說，真正的瓶頸是網(wǎng)絡(luò)上。無論網(wǎng)絡(luò)多快，只要客戶端與服務(wù)器進(jìn)行大量的數(shù)據(jù)交換。應(yīng)用運(yùn)行的效率自然就回受到影響。如果使用PL/SQL進(jìn)行編程，將這種具有大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來執(zhí)行。自然就省去了數(shù)據(jù)在網(wǎng)上的傳輸時(shí)間。§適合于客戶環(huán)境PL/SQL由于分為數(shù)據(jù)庫PL/SQL部分和工具PL/SQL。對(duì)于客戶端來說，PL/SQL可以嵌套到相應(yīng)的工具中，客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向服務(wù)發(fā)SQL命令或激活服務(wù)器端的PL/SQL程序運(yùn)行。§1.2.2PL/SQL可用的SQL語句PL/SQL是ORACLE系統(tǒng)的核心語言，現(xiàn)在ORACLE的許多部件都是由PL/SQL寫成。在PL/SQL中可以使用的SQL語句有：INSERT，UPDATE，DELETE，SELECTINTO，COMMIT，ROLLBACK，SAVEPOINT。提示：在PL/SQL中只能用SQL語句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL(如CREATEtable等)的話，只能以動(dòng)態(tài)的方式來使用。ORACLE的PL/SQL組件在對(duì)PL/SQL程序進(jìn)行解釋時(shí)，同時(shí)對(duì)在其所使用的表名、列名和數(shù)據(jù)類型進(jìn)行檢查。PL/SQL可以在SQL*PLUS中使用。PL/SQL可以在高級(jí)語言中使用。PL/SQL可以在ORACLE的開發(fā)工具中使用。其它開發(fā)工具也可以調(diào)用PL/SQL編寫的過程和函數(shù)，如PowerBuilder等都可以調(diào)用服務(wù)器端的PL/SQL過程?！?.3運(yùn)行PL/SQL程序PL/SQL程序的運(yùn)行是通過ORACLE中的一個(gè)引擎來進(jìn)行的。這個(gè)引擎可能在ORACLE的服務(wù)器端，也可能在ORACLE應(yīng)用開發(fā)的客戶端。引擎執(zhí)行PL/SQL中的過程性語句，然后將SQL語句發(fā)送給數(shù)據(jù)庫服務(wù)器來執(zhí)行。再將結(jié)果返回給執(zhí)行端。

第二章PL/SQL塊結(jié)構(gòu)和組成元素§2.1PL/SQL塊PL/SQL程序由三個(gè)塊組成，即聲明部分、執(zhí)行部分、異常處理部分。PL/SQL塊的結(jié)構(gòu)如下：DECLARE/*聲明部分:在此聲明PL/SQL用到的變量,類型和游標(biāo)，以和局部的存儲(chǔ)過程和函數(shù)*/BEGIN/*執(zhí)行部分:過程和SQL語句,即程序的主要部分*/EXCEPTION/*執(zhí)行異常部分:錯(cuò)誤處理*/END;其中執(zhí)行部分是必須的。PL/SQL塊可以分為三類：無名塊：動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次。子程序：存儲(chǔ)在數(shù)據(jù)庫中的存儲(chǔ)過程、函數(shù)和包等。當(dāng)在數(shù)據(jù)庫上建立好后可以在其它程序中調(diào)用它們。觸發(fā)器：當(dāng)數(shù)據(jù)庫發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，從而自動(dòng)執(zhí)行相應(yīng)的程序。§2.2PL/SQL結(jié)構(gòu)PL/SQL塊中可以包含子塊；子塊可以位于PL/SQL中的任何部分；子塊也即PL/SQL中的一條命令；§2.3標(biāo)識(shí)符PL/SQL程序設(shè)計(jì)中的標(biāo)識(shí)符定義與SQL的標(biāo)識(shí)符定義的要求相同。要求和限制有：標(biāo)識(shí)符名不能超過30字符；第一個(gè)字符必須為字母；不分大小寫；不能用’-‘(減號(hào));不能是SQL保留字。提示:一般不要把變量名聲明與表中字段名完全一樣,如果這樣可能得到不正確的結(jié)果.例如：下面的例子將會(huì)刪除所有的紀(jì)錄，而不是KING的記錄；DECLAREEnamevarchar2(20):=’KING’;BEGIN DELETEFROMempWHEREename=ename;END;變量命名在PL/SQL中有特別的講究，建議在系統(tǒng)的設(shè)計(jì)階段就要求所有編程人員共同遵守一定的要求，使得整個(gè)系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議的命名方法：標(biāo)識(shí)符命名規(guī)則例子程序變量V_nameV_name程序常量C_NameC_company_name游標(biāo)變量Name_cursorEmp_cursor異常標(biāo)識(shí)E_nameE_too_many表類型Name_table_typeEmp_record_type表Name_tableEmp記錄類型Name_recordEmp_recordSQL*Plus替代變量P_nameP_sal綁定變量G_nameG_year_sal§2.4PL/SQL變量類型在前面的介紹中，有系統(tǒng)的數(shù)據(jù)類型，也可以自定義數(shù)據(jù)類型。下表是ORACLE類型和PL/SQL中的變量類型的合法使用列表：§2.4.1變量類型在ORACLE8i中可以使用的變量類型有：類型子類說明范圍ORACLE限制CHARCharacterStringRowidNchar定長字符串民族語言字符集032767可選,確省=12000VARCHAR2Varchar,StringNVARCHAR2可變字符串民族語言字符集03276740004000BINARY_INTEGER帶符號(hào)整數(shù),為整數(shù)計(jì)算優(yōu)化性能NUMBER(p,s)DecDoubleprecisionIntegerIntNumericRealSmallint小數(shù),NUMBER的子類型高精度實(shí)數(shù)整數(shù),NUMBER的子類型整數(shù),NUMBER的子類型與NUMBER等價(jià)與NUMBER等價(jià)整數(shù),比integer小LONG變長字符串0->2,767字節(jié)DATE日期型公元前4712年1月1日至公元后4712年12月31日BOOLEAN布爾型TRUE,FALSE,NULL不使用ROWID存放數(shù)據(jù)庫行號(hào)UROWID通用行標(biāo)識(shí)符，字符類型插入一條記錄并顯示；DECLARERow_idUROWID;infoVARCHAR2(40);BEGIN INSERTINTOdeptVALUES(90,‘SERVICE’,‘BEIJING’) RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子句用于檢索INSERT語句中所影響的數(shù)據(jù)行數(shù)，當(dāng)INSERT語句使用VALUES子句插入數(shù)據(jù)時(shí)，RETURNING字句還可將列表達(dá)式、ROWID和REF值返回到輸出變量中。在使用RETURNING子句是應(yīng)注意以下幾點(diǎn)限制：不能并行DML語句和遠(yuǎn)程對(duì)象一起使用；不能檢索LONG類型信息；當(dāng)通過視圖向基表中插入數(shù)據(jù)時(shí)，只能與單基表視圖一起使用。例2.修改一條記錄并顯示DECLARERow_idUROWID;infoVARCHAR2(40);BEGIN UPDATEdeptSETdeptno=80WHEREDNAME=‘SERVICE’ RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子句用于檢索被修改行信息：當(dāng)UPDATE語句修改單行數(shù)據(jù)時(shí)，RETURNING子句可以檢索被修改行的ROWID和REF值，以和行中被修改列的列表達(dá)式，并可將他們存儲(chǔ)到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語句修改多行數(shù)據(jù)時(shí)，RETURNING子句可以將被修改行的ROWID和REF值，以和列表達(dá)式值返回到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING子句的限制與INSERT語句中對(duì)RETURNING子句的限制相同。例3.刪除一條記錄并顯示DECLARERow_idUROWID;infoVARCHAR2(40);BEGIN DELETEdeptWHEREDNAME=‘SERVICE’ RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子句用于檢索被修改行信息：當(dāng)UPDATE語句修改單行數(shù)據(jù)時(shí)，RETURNING子句可以檢索被修改行的ROWID和REF值，以和行中被修改列的列表達(dá)式，并可將他們存儲(chǔ)到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語句修改多行數(shù)據(jù)時(shí)，RETURNING子句可以將被修改行的ROWID和REF值，以和列表達(dá)式值返回到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING子句的限制與INSERT語句中對(duì)RETURNING子句的限制相同。§2.4.2復(fù)合類型ORACLE在PL/SQL中除了提供象前面介紹的各種類型外,還提供一種稱為復(fù)合類型的類型記錄和表.§記錄類型記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來，它必須包括至少一個(gè)標(biāo)量型或RECORD數(shù)據(jù)類型的成員，稱作PL/SQLRECORD的域(FIELD)，其作用是存放互不相同但邏輯相關(guān)的信息。定義記錄類型語法如下:TYPErecord_typeISRECORD(Field1type1[NOTNULL][:=exp1],Field2type2[NOTNULL][:=exp2],......Fieldntypen[NOTNULL][:=expn]);例4：DECLARETYPEtest_recISRECORD(CodeVARCHAR2(10),NameVARCHAR2(30)NOTNULL:=’abook’);V_booktest_rec;BEGINV_book.code:=’123’;V_:=’C++Programming’;DBMS_OUTPUT.PUT_LINE(v_book.code||v_);END;可以用SELECT語句對(duì)記錄變量進(jìn)行賦值,只要保證記錄字段與查詢結(jié)果列表中的字段相配即可?！焓褂?TYPE定義一個(gè)變量，其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某個(gè)列的數(shù)據(jù)類型相同，這時(shí)可以使用%TYPE。使用%TYPE特性的優(yōu)點(diǎn)在于：所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以不必知道；所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以實(shí)時(shí)改變。例5：DECLARE--用%TYPE類型定義與表相配的字段TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);--聲明接收數(shù)據(jù)的變量v_empt_Record;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_emp.t_no)||v_emp.t_name||TO_CHAR(v_emp.t_sal));END;例6：DECLAREv_empnoemp.empno%TYPE:=&empno;Typer_recordisrecord(v_nameemp.ename%TYPE,v_salemp.sal%TYPE,v_dateemp.hiredate%TYPE);Recr_record;BEGINSELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE(Rec.v_name||''||Rec.v_sal||'--'||Rec.v_date);END;§2.4.3使用%ROWTYPEPL/SQL提供%ROWTYPE操作符,返回一個(gè)記錄類型,其數(shù)據(jù)類型和數(shù)據(jù)庫表的數(shù)據(jù)結(jié)構(gòu)相一致。使用%ROWTYPE特性的優(yōu)點(diǎn)在于：所引用的數(shù)據(jù)庫中列的個(gè)數(shù)和數(shù)據(jù)類型可以不必知道；所引用的數(shù)據(jù)庫中列的個(gè)數(shù)和數(shù)據(jù)類型可以實(shí)時(shí)改變。例7：DECLAREv_empnoemp.empno%TYPE:=&empno;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工資:'||rec.sal||'工作時(shí)間:'||rec.hiredate);END;§2.4.4LOB類型*ORACLE提供了LOB(LargeOBject)類型，用于存儲(chǔ)大的數(shù)據(jù)對(duì)象的類型。ORACLE目前主要支持BFILE,BLOB,CLOB和NCLOB類型。BFILE(Movie)存放大的二進(jìn)制數(shù)據(jù)對(duì)象，這些數(shù)據(jù)文件不放在數(shù)據(jù)庫里，而是放在操作系統(tǒng)的某個(gè)目錄里，數(shù)據(jù)庫的表里只存放文件的目錄。BLOB(Photo)存儲(chǔ)大的二進(jìn)制數(shù)據(jù)類型。變量存儲(chǔ)大的二進(jìn)制對(duì)象的位置。大二進(jìn)制對(duì)象的大小<=4GB。CLOB(Book)存儲(chǔ)大的字符數(shù)據(jù)類型。每個(gè)變量存儲(chǔ)大字符對(duì)象的位置，該位置指到大字符數(shù)據(jù)塊。大字符對(duì)象的大小<=4GB。NCLOB存儲(chǔ)大的NCHAR字符數(shù)據(jù)類型。每個(gè)變量存儲(chǔ)大字符對(duì)象的位置，該位置指到大字符數(shù)據(jù)塊。大字符對(duì)象的大小<=4GB。§2.4.5Bind變量綁定變量是在主機(jī)環(huán)境中定義的變量。在PL/SQL程序中可以使用綁定變量作為他們將要使用的其它變量。為了在PL/SQL環(huán)境中聲明綁定變量，使用命令VARIABLE。例如：VARIABLEreturn_codeNUMBERVARIABLEreturn_msgVARCHAR2(20)可以通過SQL*Plus命令中的PRINT顯示綁定變量的值。例如：PRINTreturn_codePRINTreturn_msg例7：VARIABLEresultNUMBERBEGIN SELECT(sal*12)+nvl(comm,0)INTO:resultFROMempWHEREempno=7788;END;PRINTresult§2.4.6INDEXBYTABLES包括兩個(gè)基本成分:．?dāng)?shù)據(jù)處理類型為BINARY_INTEGER主鍵;．標(biāo)量或記錄數(shù)據(jù)類型的列．TYPEtype_nameISTABLEOF {column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPE [INDEXBYBINARY_INTEGER];方法描述EXISTS(n)ReturnTRUEifthenthelementinaPL/SQLtableexists;COUNTReturnsthenumberofelementsthataPL/SQLtablecurrentlycontains;FIRSTLASTReturnthefirstandlast(smallestandlastest)indexnumbersinaPL/SQLtable.ReturnsNULLifthePL/SQLtableisempty.PRIOR(n)ReturnstheindexnumberthatprecedesindexninaPL/SQLtable;NEXT(N)ReturnstheindexnumberthatsucceedsindexninaPL/SQLtable;TRIMTRIMremovesoneelementfromtheendofaPL/SQLtable.TRIM(n)removesnelementfromtheendofaPL/SQLtable.DELETEDELETEremovesallelementsfromaPL/SQLtable.DELETE(n)removesthenthelementsfromaPL/SQLtable.DELETE(m,n)removesallelementsintherangemtonfromaPL/SQLtable.例8：DECLARE TYPEdept_table_typeISTABLEOF dept%ROWTYPEINDEXBYBINARY_INTEGER; my_dname_tabledept_table_type; v_countnumber(2):=4;BEGIN FORintIN1..v_countLOOP SELECT*INTOmy_dname_table(int)FROMdeptWHEREdeptno=int*10; ENDLOOP; FORintINmy_dname_table.FIRST..my_dname_table.LASTLOOP DBMS_OUTPUT.PUT_LINE(‘Departmentnumber:‘||my_dname_table(int).deptno); DBMS_OUTPUT.PUT_LINE(‘Departmentname:‘||my_dname_table(int).dname); ENDLOOP;END;§2.4.7數(shù)據(jù)類型的轉(zhuǎn)換*隱式類型轉(zhuǎn)換BIN_INTCHARDATELONGNUMBERPLS_INTUROWIDVARCHAR2BIN_INT

CHAR

DATELONGNUMBERRAWUROWIDVARCHAR2§2.5運(yùn)算符和表達(dá)式(數(shù)據(jù)定義)§2.5.1關(guān)系運(yùn)算符運(yùn)算符意義=等于<>,!=,~=,^=不等于<小于>大于<=小于或等于>=大于或等于§2.5.2一般運(yùn)算符運(yùn)算符意義+加號(hào)-減號(hào)*乘號(hào)/除號(hào):=賦值號(hào)=>關(guān)系號(hào)..范圍運(yùn)算符||字符連接符§2.5.3邏輯運(yùn)算符運(yùn)算符意義ISNULL是空值

BETWEEN介于兩者之間IN在一列值中間

AND邏輯與OR邏輯或NOT取返,如ISNOTNULL,NOTIN§2.6變量賦值在PL/SQL編程中，變量賦值是一個(gè)值得注意的地方，它的語法如下：variable:=expression;variable是一個(gè)PL/SQL變量,expression是一個(gè)PL/SQL表達(dá)式.§2.6.1字符和數(shù)字運(yùn)算特點(diǎn)空值加數(shù)字仍是空值：NULL+<數(shù)字>=NULL空值加（連接）字符，結(jié)果為字符：NULL||<字符串>=<字符串>§2.6.2BOOLEAN賦值布爾值只有TRUE,FALSE和NULL三個(gè)值。如：DECLAREdoneBOOLEAN;/*thefollowingstatementsarelegal:*/BEGINdone:=FALSE;WHILENOTdoneLOOPNull;ENDLOOP;END;§2.6.3數(shù)據(jù)庫賦值數(shù)據(jù)庫賦值是通過SELECT語句來完成的，每次執(zhí)行SELECT語句就賦值一次，一般要求被賦值的變量與SELECT中的列名要一一對(duì)應(yīng)。如：例9：DECLAREemp_idemp.empno%TYPE:=7788;emp_nameemp.ename%TYPE;wagesemp.sal%TYPE;BEGINSELECTename,NVL(sal,0)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=emp_id;Dbms_output.put_line(emp_name||’‘||to_char(wages));END;提示：不能將SELECT語句中的列賦值給布爾變量?！?.6.4可轉(zhuǎn)換的類型賦值CHAR轉(zhuǎn)換為NUMBER：使用TO_NUMBER函數(shù)來完成字符到數(shù)字的轉(zhuǎn)換，如：v_total:=TO_NUMBER(‘100.0’NUMBER轉(zhuǎn)換為CHAR：使用TO_CHAR函數(shù)可以實(shí)現(xiàn)數(shù)字到字符的轉(zhuǎn)換，如：v_comm:=TO_CHAR(‘123.45’)||’元’字符轉(zhuǎn)換為日期：使用TO_DATE函數(shù)可以實(shí)現(xiàn)字符到日期的轉(zhuǎn)換，如：v_date:=TO_DATE('2001.07.03','yyyy.mm.dd');日期轉(zhuǎn)換為字符使用TO_CHAR函數(shù)可以實(shí)現(xiàn)日期到字符的轉(zhuǎn)換，如：v_to_day:=TO_CHAR(SYSDATE,'yyyy.mm.ddhh24:mi:ss');§2.7變量作用范圍和可見性在PL/SQL編程中，如果在變量的定義上沒有做到統(tǒng)一的話，可能會(huì)隱藏一些危險(xiǎn)的錯(cuò)誤，這樣的原因主要是變量的作用范圍所致。與其它高級(jí)語言類似，PL/SQL的變量作用范圍特點(diǎn)是：變量的作用范圍是在你所引用的程序單元（塊、子程序、包）內(nèi)。即從聲明變量開始到該塊的結(jié)束。一個(gè)變量（標(biāo)識(shí)）只能在你所引用的塊內(nèi)是可見的。當(dāng)一個(gè)變量超出了作用范圍，PL/SQL引擎就釋放用來存放該變量的空間（因?yàn)樗赡懿挥昧耍Ｔ谧訅K中重新定義該變量后，它的作用僅在該塊內(nèi)。例10：DECLAREEmesschar(80);BEGINDECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’president’; DBMS_OUTPUT.PUT_LINE(V1);EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonepresident’);END;DECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’manager’;EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonemanager’);END;EXCEPTIONWhenothersTHENEmess:=substr(SQLERRM,1,80);DBMS_OUTPUT.PUT_LINE(emess);END;§2.8注釋在PL/SQL里，可以使用兩種符號(hào)來寫注釋，即：使用雙‘-‘(減號(hào))加注釋PL/SQL允許用–來寫注釋，它的作用范圍是只能在一行有效。如：V_SalNUMBER(12,2);--工資變量。使用/**/來加一行或多行注釋，如：/***********************************************//*文件名：statistcs_sal.sql*//***********************************************/提示：被解釋存放在數(shù)據(jù)庫中的PL/SQL程序，一般系統(tǒng)自動(dòng)將程序頭部的注釋去掉。只有在PROCEDURE之后的注釋才被保留；另外程序中的空行也自動(dòng)被去掉。§2.9簡單例子§2.9.1簡單數(shù)據(jù)插入例子例11：/*本例子僅是一個(gè)簡單的插入，不是實(shí)際應(yīng)用。*/DECLAREv_enameVARCHAR2(20):=‘Bill’;v_salNUMBER(7,2):=1234.56;v_deptnoNUMBER(2):=10;v_empnoNUMBER(4):=8888;BEGININSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,‘Manager’,v_sal,v_deptno,TO_DATE(’1954.06.09’,’yyyy.mm.dd’COMMIT;END;§2.9.2簡單數(shù)據(jù)刪除例子例12：/*本例子僅是一個(gè)簡單的刪除例子，不是實(shí)際應(yīng)用。*/DECLAREv_empnonumber(4):=8888;BEGINDELETEFROMempWHEREempno=v_empno;COMMIT;END;

第三章PL/SQL流程控制語句介紹PL/SQL的流程控制語句,包括如下三類:控制語句:IF語句循環(huán)語句:LOOP語句,EXIT語句順序語句:GOTO語句,NULL語句§3.1條件語句IF<布爾表達(dá)式>THENPL/SQL和SQL語句ENDIF;IF<布爾表達(dá)式>THENPL/SQL和SQL語句ELSE其它語句ENDIF;IF<布爾表達(dá)式>THENPL/SQL和SQL語句ELSIF<其它布爾表達(dá)式>THEN其它語句ELSIF<其它布爾表達(dá)式>THEN其它語句ELSE其它語句ENDIF;提示:ELSIF不能寫成ELSEIF例1:DECLAREv_empnoemp.empno%TYPE:=&empno;V_salaryemp.sal%TYPE;V_commentVARCHAR2(35);BEGINSELECTsalINTOv_salaryFROMempWHEREempno=v_empno;IFv_salary<1500THENV_comment:=‘Fairlyless’;ELSIFv_salary<3000THENV_comment:=‘Alittlemore’;ELSEV_comment:=‘Lotsofsalary’;ENDIF;DBMS_OUTPUT.PUT_LINE(V_comment);END;§3.2CASE表達(dá)式CASEselector WHENexpression1THENresult1 WHENexpression2THENresult2 WHENexpressionNTHENresultN [ELSEresultN+1]END;例2:DECLARE V_gradechar(1):=UPPER(‘&p_grade’); V_appraisalVARCHAR2(20);BEGIN V_appraisal:= CASEv_grade WHEN‘A’THEN‘Excellent’ WHEN‘B’THEN‘VeryGood’ WHEN‘C’THEN‘Good’ ELSE‘Nosuchgrade’ END; DBMS_OUTPUT.PUT_LINE(‘Grade:‘||v_grade||’Appraisal:‘||v_appraisal);END;§3.3循環(huán)1.簡單循環(huán)LOOP要執(zhí)行的語句;EXITWHEN<條件語句>/*條件滿足，退出循環(huán)語句*/ENDLOOP;例3.DECLAREintNUMBER(2):=0;BEGINLOOPint:=int+1;DBMS_OUTPUT.PUT_LINE('int的當(dāng)前值為:'||int);EXITWHENint=10;ENDLOOP;END;2.WHILE循環(huán)WHILE<布爾表達(dá)式>LOOP要執(zhí)行的語句;ENDLOOP;例4.DECLARExNUMBER;BEGINx:=1;WHILEx<10LOOPDBMS_OUTPUT.PUT_LINE('X的當(dāng)前值為:'||x); x:=x+1;ENDLOOP;END;3.數(shù)字式循環(huán)FOR循環(huán)計(jì)數(shù)器IN[REVERSE]下限..上限LOOP要執(zhí)行的語句;ENDLOOP;每循環(huán)一次，循環(huán)變量自動(dòng)加1；使用關(guān)鍵字REVERSE，循環(huán)變量自動(dòng)減1。跟在INREVERSE后面的數(shù)字必須是從小到大的順序，而且必須是整數(shù)，不能是變量或表達(dá)式。可以使用EXIT退出循環(huán)。例5.BEGINFORintin1..10LOOPDBMS_OUTPUT.PUT_LINE('int的當(dāng)前值為:'||int);ENDLOOP;END;例6.CREATETABLEtemp_table(num_colNUMBER);DECLAREV_counterNUMBER:=10;BEGININSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterIN20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterINREVERSE20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;END;DROPTABLEtemp_table;§3.3標(biāo)號(hào)和GOTOPL/SQL中GOTO語句是無條件跳轉(zhuǎn)到指定的標(biāo)號(hào)去的意思。語法如下：GOTOlabel;......<<label>>/*標(biāo)號(hào)是用<<>>括起來的標(biāo)識(shí)符*/例7:DECLAREV_counterNUMBER:=1;BEGINLOOPDBMS_OUTPUT.PUT_LINE('V_counter的當(dāng)前值為:'||V_counter);V_counter:=v_counter+1;IFv_counter>10THENGOTOl_ENDofLOOP;ENDIF;ENDLOOP;<<l_ENDofLOOP>>DBMS_OUTPUT.PUT_LINE('V_counter的當(dāng)前值為:'||V_counter);END;§3.4NULL語句在PL/SQL程序中，可以用null語句來說明“不用做任何事情”的意思，相當(dāng)于一個(gè)占位符，可以使某些語句變得有意義，提高程序的可讀性。如：DECLARE...BEGIN…IFv_numISNULLTHENGOTOprint1;ENDIF;…<<print1>>NULL;--不需要處理任何數(shù)據(jù)。END;

第四章游標(biāo)的使用在PL/SQL程序中，對(duì)于處理多行記錄的事務(wù)經(jīng)常使用游標(biāo)來實(shí)現(xiàn)?！?.1游標(biāo)概念為了處理SQL語句，ORACLE必須分配一片叫上下文(contextarea)的區(qū)域來處理所必需的信息，其中包括要處理的行的數(shù)目，一個(gè)指向語句被分析以后的表示形式的指針以和查詢的活動(dòng)集(activeset)。游標(biāo)是一個(gè)指向上下文的句柄(handle)或指針。通過游標(biāo)，PL/SQL可以控制上下文區(qū)和處理語句時(shí)上下文區(qū)會(huì)發(fā)生些什么事情。 對(duì)于不同的SQL語句，游標(biāo)的使用情況不同：SQL語句游標(biāo)非查詢語句隱式的結(jié)果是單行的查詢語句隱式的或顯示的結(jié)果是多行的查詢語句顯示的§4.1.1處理顯式游標(biāo)顯式游標(biāo)處理顯式游標(biāo)處理需四個(gè)PL/SQL步驟:定義游標(biāo)：就是定義一個(gè)游標(biāo)名，以和與其相對(duì)應(yīng)的SELECT語句。格式：CURSORcursor_name[(parameter[,parameter]…)]ISselect_statement; 游標(biāo)參數(shù)只能為輸入?yún)?shù)，其格式為： parameter_name[IN]datatype[{:=|DEFAULT}expression] 在指定數(shù)據(jù)類型時(shí)，不能使用長度約束。如NUMBER(4)、CHAR(10)等都是錯(cuò)誤的。打開游標(biāo)：就是執(zhí)行游標(biāo)所對(duì)應(yīng)的SELECT語句，將其查詢結(jié)果放入工作區(qū)，并且指針指向工作區(qū)的首部，標(biāo)識(shí)游標(biāo)結(jié)果集合。如果游標(biāo)查詢語句中帶有FORUPDATE選項(xiàng)，OPEN語句還將鎖定數(shù)據(jù)庫表中游標(biāo)結(jié)果集合對(duì)應(yīng)的數(shù)據(jù)行。格式：OPENcursor_name[([parameter=>]value[,[parameter=>]value]…)];在向游標(biāo)傳遞參數(shù)時(shí)，可以使用與函數(shù)參數(shù)相同的傳值方法，即位置表示法和名稱表示法。PL/SQL程序不能用OPEN語句重復(fù)打開一個(gè)游標(biāo)。提取游標(biāo)數(shù)據(jù)：就是檢索結(jié)果集合中的數(shù)據(jù)行，放入指定的輸出變量中。格式：FETCHcursor_nameINTO{variable_list|record_variable};對(duì)該記錄進(jìn)行處理；繼續(xù)處理，直到活動(dòng)集合中沒有記錄；關(guān)閉游標(biāo)：當(dāng)提取和處理完游標(biāo)結(jié)果集合數(shù)據(jù)后，應(yīng)和時(shí)關(guān)閉游標(biāo)，以釋放該游標(biāo)所占用的系統(tǒng)資源，并使該游標(biāo)的工作區(qū)變成無效，不能再使用FETCH語句取其中數(shù)據(jù)。關(guān)閉后的游標(biāo)可以使用OPEN語句重新打開。格式：CLOSEcursor_name;注：定義的游標(biāo)不能有INTO子句。例1.游標(biāo)參數(shù)的傳遞方法。DECLARE DeptRecdept%ROWTYPE; Dept_namedept.dname%TYPE; Dept_locdept.loc%TYPE; CURSORc1ISSELECTdname,locFROMdeptWHEREdeptno<=30; CURSORc2(dept_noNUMBERDEFAULT10)IS SELECTdname,locFROMdeptWHEREdeptno<=dept_no; CURSORc3(dept_noNUMBERDEFAULT10)IS SELECT*FROMdeptWHEREdeptno<=dept_no;BEGIN OPENc1; LOOP FETCHc1INTOdept_name,dept_loc; EXITWHENc1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name||’‘||dept_loc); ENDLOOP; CLOSEc1; OPENc2; LOOP FETCHc2INTOdept_name,dept_loc; EXITWHENc2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name||’‘||dept_loc); ENDLOOP; CLOSEc2; OPENc3(dept_no=>20); LOOP FETCHc3INTOdeptrec; EXITWHENc3%NOTFOUND; DBMS_OUTPUT.PUT_LINE(deptrec.deptno||’‘||deptrec.dname||’‘||deptrec.loc); ENDLOOP; CLOSEc3;END;2.游標(biāo)屬性%FOUND布爾型屬性，當(dāng)最近一次讀記錄時(shí)成功返回,則值為TRUE；%NOTFOUND布爾型屬性，與%FOUND相反；%ISOPEN布爾型屬性，當(dāng)游標(biāo)已打開時(shí)返回TRUE；%ROWCOUNT數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)。例2：給工資低于1200的員工增加工資50。DECLAREv_empnoemp.empno%TYPE;v_salemp.sal%TYPE;CURSORcISSELECTempno,salFROMemp;BEGINOPENc;LOOPFETCHcINTOv_empno,v_sal;EXITWHENC%NOTFOUND;IFv_sal<=1200THENUPDATEempSETsal=sal+50WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'工資已更新!');ENDIF;DBMS_OUTPUT.PUT_LINE('記錄數(shù):'||C%ROWCOUNT);ENDLOOP;CLOSEc;END;3.游標(biāo)的FOR循環(huán)PL/SQL語言提供了游標(biāo)FOR循環(huán)語句，自動(dòng)執(zhí)行游標(biāo)的OPEN、FETCH、CLOSE語句和循環(huán)語句的功能；當(dāng)進(jìn)入循環(huán)時(shí)，游標(biāo)FOR循環(huán)語句自動(dòng)打開游標(biāo)，并提取第一行游標(biāo)數(shù)據(jù)，當(dāng)程序處理完當(dāng)前所提取的數(shù)據(jù)而進(jìn)入下一次循環(huán)時(shí)，游標(biāo)FOR循環(huán)語句自動(dòng)提取下一行數(shù)據(jù)供程序處理，當(dāng)提取完結(jié)果集合中的所有數(shù)據(jù)行后結(jié)束循環(huán)，并自動(dòng)關(guān)閉游標(biāo)。格式： FORindex_variableINcursor_name[value[,value]…]LOOP --游標(biāo)數(shù)據(jù)處理代碼 ENDLOOP;其中： index_variable為游標(biāo)FOR循環(huán)語句隱含聲明的索引變量，該變量為記錄變量，其結(jié)構(gòu)與游標(biāo)查詢語句返回的結(jié)構(gòu)集合的結(jié)構(gòu)相同。在程序中可以通過引用該索引記錄變量元素來讀取所提取的游標(biāo)數(shù)據(jù)，index_variable中各元素的名稱與游標(biāo)查詢語句選擇列表中所制定的列名相同。如果在游標(biāo)查詢語句的選擇列表中存在計(jì)算列，則必須為這些計(jì)算列指定別名后才能通過游標(biāo)FOR循環(huán)語句中的索引變量來訪問這些列數(shù)據(jù)。注：不要在程序中對(duì)游標(biāo)進(jìn)行人工操作；不要在程序中定義用于控制FOR循環(huán)的記錄。例3：DECLARECURSORc_salISSELECTempno,ename,salFROMemp;BEGIN--隱含打開游標(biāo)FORv_salINc_salLOOP--隱含執(zhí)行一個(gè)FETCH語句 DBMS_OUTPUT.PUT_LINE(to_char(v_sal.empno)||’‘||v_sal.ename||’‘||to_char(v_sal.sal));--隱含監(jiān)測(cè)c_sal%NOTFOUNDENDLOOP;--隱含關(guān)閉游標(biāo)END;例4：當(dāng)所聲明的游標(biāo)帶有參數(shù)時(shí)，通過游標(biāo)FOR循環(huán)語句為游標(biāo)傳遞參數(shù)。DECLARE CURSORc1(dept_noNUMBERDEFAULT10)IS SELECTdname,locFROMdeptWHEREdeptno<=dept_no;BEGIN DBMS_OUTPUT.PUT_LINE(‘dept_no參數(shù)值為30：’); FORc1_recINc1(30)LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’‘||c1_rec.loc); ENDLOOP; DBMS_OUTPUT.PUT_LINE(CHR(10)||’使用默認(rèn)的dept_no參數(shù)值10：’); FORc1_recINc1LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’‘||c1_rec.loc); ENDLOOP;END;例5：PL/SQL還允許在游標(biāo)FOR循環(huán)語句中使用子查詢來實(shí)現(xiàn)游標(biāo)的功能。BEGIN FORc1_recIN(SELECTdname,locFROMdept)LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’‘||c1_rec.loc); ENDLOOP;END;§4.1.2處理隱式游標(biāo)顯式游標(biāo)主要是用于對(duì)查詢語句的處理，尤其是在查詢結(jié)果為多條記錄的情況下；而對(duì)于非查詢語句，如修改、刪除操作，則由ORACLE系統(tǒng)自動(dòng)地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱為隱式游標(biāo)，隱式游標(biāo)的名字為SQL，這是由ORACLE系統(tǒng)定義的。對(duì)于隱式游標(biāo)的操作，如定義、打開、取值和關(guān)閉操作，都由ORACLE系統(tǒng)自動(dòng)地完成，無需用戶進(jìn)行處理。用戶只能通過隱式游標(biāo)的相關(guān)屬性，來完成相應(yīng)的操作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶自定義的顯示游標(biāo)無關(guān)的、最新處理的一條SQL語句所包含的數(shù)據(jù)。格式調(diào)用為：SQL%注：INSERT,UPDATE,DELETE,SELECT語句中不必明確定義游標(biāo)。隱式游標(biāo)屬性SQL%FOUND布爾型屬性,當(dāng)最近一次讀記錄時(shí)成功返回，則值為true；SQL%NOTFOUND布爾型屬性,與%found相反；SQL%ROWCOUNT數(shù)字型屬性,返回已從游標(biāo)中讀取得記錄數(shù)；SQL%ISOPEN布爾型屬性,取值總是FALSE。SQL命令執(zhí)行完畢立即關(guān)閉隱式游標(biāo)。例6:刪除EMP表中某部門的所有員工，如果該部門中已沒有員工，則在DEPT表中刪除該部門。DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno;BEGIN DELETEFROMempWHEREdeptno=v_deptno; IFSQL%NOTFOUNDTHEN DELETEFROMdeptWHEREdeptno=v_deptno; ENDIF;END;§4.1.3游標(biāo)修改和刪除操作游標(biāo)修改和刪除操作是指在游標(biāo)定位下，修改或刪除表中指定的數(shù)據(jù)行。這時(shí)，要求游標(biāo)查詢語句中必須使用FORUPDATE選項(xiàng)，以便在打開游標(biāo)時(shí)鎖定游標(biāo)結(jié)果集合在表中對(duì)應(yīng)數(shù)據(jù)行的所有列和部分列。為了對(duì)正在處理(查詢)的行不被另外的用戶改動(dòng)，ORACLE提供一個(gè)FORUPDATE子句來對(duì)所選擇的行進(jìn)行鎖住。該需求迫使ORACLE鎖定游標(biāo)結(jié)果集合的行，可以防止其他事務(wù)處理更新或刪除相同的行，直到您的事務(wù)處理提交或回退為止。語法：SELECT...FROM…FORUPDATE[OFcolumn[,column]…][NOWAIT]如果另一個(gè)會(huì)話已對(duì)活動(dòng)集中的行加了鎖，那么SELECTFORUPDATE操作一直等待到其它的會(huì)話釋放這些鎖后才繼續(xù)自己的操作，對(duì)于這種情況，當(dāng)加上NOWAIT子句時(shí)，如果這些行真的被另一個(gè)會(huì)話鎖定，則OPEN立即返回并給出：ORA-0054：resourcebusyandacquirewithnowaitspecified.如果使用FORUPDATE聲明游標(biāo)，則可在DELETE和UPDATE語句中使用WHERECURRENTOFcursor_name子句，修改或刪除游標(biāo)結(jié)果集合當(dāng)前行對(duì)應(yīng)的數(shù)據(jù)庫表中的數(shù)據(jù)行。例7：從EMP表中查詢某部門的員工情況，將其工資最低定為1500；DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno; CURSORemp_cursorISSELECTempno,salFROMempWHEREdeptno=v_deptnoFORUPDATEOFsalNOWAIT;BEGIN FORemp_recordINemp_cursorLOOPIFemp_record.sal<1500THEN UPDATEempSETsal=1500WHERECURRENTOFemp_cursor;ENDIF; ENDLOOP;-- COMMIT;END;

第五章異常錯(cuò)誤處理一個(gè)優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯(cuò)情況，并盡可能從錯(cuò)誤中恢復(fù)。ORACLE提供異常情況(EXCEPTION)和異常處理(EXCEPTIONHANDLER)來實(shí)現(xiàn)錯(cuò)誤處理。§5.1異常處理概念異常情況處理(EXCEPTION)是用來處理正常執(zhí)行過程中未預(yù)料的事件,程序塊的異常處理預(yù)定義的錯(cuò)誤和自定義錯(cuò)誤,由于PL/SQL程序塊一旦產(chǎn)生異常而沒有指出如何處理時(shí),程序就會(huì)自動(dòng)終止整個(gè)程序運(yùn)行.有三種類型的異常錯(cuò)誤：預(yù)定義(Predefined)錯(cuò)誤ORACLE預(yù)定義的異常情況大約有24個(gè)。對(duì)這種異常情況的處理，無需在程序中定義，由ORACLE自動(dòng)將其引發(fā)。非預(yù)定義(Predefined)錯(cuò)誤即其他標(biāo)準(zhǔn)的ORACLE錯(cuò)誤。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后由ORACLE自動(dòng)將其引發(fā)。用戶定義(User_define)錯(cuò)誤程序執(zhí)行過程中，出現(xiàn)編程人員認(rèn)為的非正常情況。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后顯式地在程序中將其引發(fā)。異常處理部分一般放在PL/SQL程序體的后半部,結(jié)構(gòu)為:EXCEPTIONWHENfirst_exceptionTHEN<codetohandlefirstexception>WHENsecond_exceptionTHEN<codetohandlesecondexception>WHENOTHERSTHEN<codetohandleothersexception>END;異常處理可以按任意次序排列,但OTHERS必須放在最后.§5.1.1預(yù)定義的異常處理預(yù)定義說明的部分ORACLE異常錯(cuò)誤錯(cuò)誤號(hào)異常錯(cuò)誤信息名稱說明ORA-0001Dup_val_on_index試圖破壞一個(gè)唯一性限制ORA-0051Timeout-on-resource在等待資源時(shí)發(fā)生超時(shí)ORA-0061Transaction-backed-out由于發(fā)生死鎖事務(wù)被撤消ORA-1001Invalid-CURSOR試圖使用一個(gè)無效的游標(biāo)ORA-1012Not-logged-on沒有連接到ORACLEORA-1017Login-denied無效的用戶名/口令ORA-1403No_data_foundSELECTINTO沒有找到數(shù)據(jù)ORA-1422Too_many_rowsSELECTINTO返回多行ORA-1476Zero-divide試圖被零除ORA-1722Invalid-NUMBER轉(zhuǎn)換一個(gè)數(shù)字失敗ORA-6500Storage-error內(nèi)存不夠引發(fā)的內(nèi)部錯(cuò)誤ORA-6501Program-error內(nèi)部錯(cuò)誤ORA-6502Value-error轉(zhuǎn)換或截?cái)噱e(cuò)誤ORA-6504Rowtype-mismatch縮主游標(biāo)變量與PL/SQL變量有不兼容行類型ORA-6511CURSOR-already-OPEN試圖打開一個(gè)已存在的游標(biāo)ORA-6530Access-INTO-null試圖為null對(duì)象的屬性賦值ORA-6531Collection-is-null試圖將Exists以外的集合(collection)方法應(yīng)用于一個(gè)nullpl/sql表上或varray上ORA-6532Subscript-outside-limit對(duì)嵌套或varray索引得引用超出聲明范圍以外ORA-6533Subscript-beyond-count對(duì)嵌套或varray索引得引用大于集合中元素的個(gè)數(shù).對(duì)這種異常情況的處理，只需在PL/SQL塊的異常處理部分，直接引用相應(yīng)的異常情況名，并對(duì)其完成相應(yīng)的異常錯(cuò)誤處理即可。例1：更新指定員工工資，如工資小于1500，則加100；DECLAREv_empnoemp.empno%TYPE:=&empno;v_salemp.sal%TYPE;BEGINSELECTsalINTOv_salFROMempWHEREempno=v_empno;IFv_sal<=1500THENUPDATEempSETsal=sal+100WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已更新!');ELSEDBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已經(jīng)超過規(guī)定值!');ENDIF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('數(shù)據(jù)庫中沒有編碼為'||v_empno||'的員工');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('程序運(yùn)行錯(cuò)誤!請(qǐng)使用游標(biāo)');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('發(fā)生其它錯(cuò)誤!');END;§5.1.2非預(yù)定義的異常處理 對(duì)于這類異常情況的處理，首先必須對(duì)非定義的ORACLE錯(cuò)誤進(jìn)行定義。步驟如下：在PL/SQL塊的定義部分定義異常情況：<異常情況>EXCEPTION;將其定義好的異常情況，與標(biāo)準(zhǔn)的ORACLE錯(cuò)誤聯(lián)系起來，使用EXCEPTION_INIT語句：PRAGMAEXCEPTION_INIT(<異常情況>,<錯(cuò)誤代碼>)；在PL/SQL塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理。例2：刪除指定部門的記錄信息，以確保該部門沒有員工。INSERTINTOdeptVALUES(50,‘FINANCE’,‘CHICAGO’);DECLAREv_deptnodept.deptno%TYPE:=&deptno;e_deptno_remainingEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_remaining,-2292);/*-2292是違反一致性約束的錯(cuò)誤代碼*/BEGINDELETEFROMdeptWHEREdeptno=v_deptno;EXCEPTIONWHENe_deptno_remainingTHENDBMS_OUTPUT.PUT_LINE('違反數(shù)據(jù)完整性約束!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('發(fā)生其它錯(cuò)誤!');END;§5.1.3用戶自定義的異常處理當(dāng)與一個(gè)異常錯(cuò)誤相關(guān)的錯(cuò)誤出現(xiàn)時(shí)，就會(huì)隱含觸發(fā)該異常錯(cuò)誤。用戶定義的異常錯(cuò)誤是通過顯式使用RAISE語句來觸發(fā)。當(dāng)引發(fā)一個(gè)異常錯(cuò)誤時(shí)，控制就轉(zhuǎn)向到EXCEPTION塊異常錯(cuò)誤部分，執(zhí)行錯(cuò)誤處理代碼。 對(duì)于這類異常情況的處理，步驟如下：在PL/SQL塊的定義部分定義異常情況：<異常情況>EXCEPTION;RAISE<異常情況>；在PL/SQL塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理。例3：更新指定員工工資，增加100；DECLAREv_empnoemp.empno%TYPE:=&empno;no_resultEXCEPTION;BEGINUPDATEempSETsal=sal+100WHEREempno=v_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你的數(shù)據(jù)更新語句失敗了!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('發(fā)生其它錯(cuò)誤!');END;§5.1.4用戶定義的異常處理調(diào)用DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過程，可以重新定義異常錯(cuò)誤消息，它為應(yīng)用程序提供了一種與ORACLE交互的方法。RAISE_APPLICATION_ERROR的語法如下：RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);這里的error_number是從–20,000到–20,999之間的參數(shù)，error_message是相應(yīng)的提示信息(<2048字節(jié))，keep_errors為可選，如果keep_errors=TRUE,則新錯(cuò)誤將被添加到已經(jīng)引發(fā)的錯(cuò)誤列表中。如果keep_errors=FALSE(缺省),則新錯(cuò)誤將替換當(dāng)前的錯(cuò)誤列表。例4：創(chuàng)建一個(gè)函數(shù)get_salary,該函數(shù)檢索指定部門的工資總和，其中定義了-20991和-20992號(hào)錯(cuò)誤，分別處理參數(shù)為空和非法部門代碼兩種錯(cuò)誤：CREATETABLEerrlog( ErrcodeNUMBER, ErrtextCHAR(40));CREATEORREPLACEFUNCTIONget_salary(p_deptnoNUMBER) RETURNNUMBERAS V_salNUMBER;BEGIN IFp_deptnoISNULLTHEN RAISE_APPLICATION_ERROR(-20991,’部門代碼為空’); ELSIFp_deptno<0THEN RAISE_APPLICATION_ERROR(-20992,’無效的部門代碼’); ELSE SELECTSUM(sal)INTOv_salFROMEMPWHEREdeptno=p_deptno; RETURNV_sal; ENDIF;END;DECLARE V_salaryNUMBER(7,2); V_sqlcodeNUMBER; V_sqlerrVARCHAR2(512); Null_deptnoEXCEPTION; Invalid_deptnoEXCEPTION; PRAGMAEXCEPTION_INIT(null_deptno,-20991); PRAGMAEXCEPTION_INIT(invalid_deptno,-20992);BEGIN V_salary:=get_salary(10); DBMS_OUTPUT.PUT_LINE(’10號(hào)部門工資：’||TO_CHAR(V_salary)); BEGIN V_salary:=get_salary(-10); EXCEPTION WHENinvalid_deptnoTHEN V_sqlcode:=SQLCODE; V_sqlerr:=SQLERRM; INSERTINTOerrlog(errcode,errtext)VALUES(v_sqlcode,v_sqlerr); COMMIT; ENDinner1; V_salary:=get_salary(20); DBMS_OUTPUT.PUT_LINE(’20號(hào)部門工資：’||TO_CHAR(V_salary)); BEGIN V_salary:=get_salary(NULL); ENDinner2; V_salary:=get_salary(30); DBMS_OUTPUT.PUT_LINE(’30號(hào)部門工資：’||TO_CHAR(V_salary)); EXCEPTION WHENnull_deptnoTHEN V_sqlcode:=SQLCODE; V_sqlerr:=SQLERRM; INSERTINTOerrlog(errcode,errtext)VALUES(v_sqlcode,v_sqlerr); COMMIT; WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('發(fā)生其它錯(cuò)誤!');ENDouter;§5.2異常錯(cuò)誤傳播由于異常錯(cuò)誤可以在聲明部分和執(zhí)行部分以和異常錯(cuò)誤部分出現(xiàn)，因而在不同部分引發(fā)的異常錯(cuò)誤也不一樣?！?.2.1在執(zhí)行部分引發(fā)異常錯(cuò)誤當(dāng)一個(gè)異常錯(cuò)誤在執(zhí)行部分引發(fā)時(shí)，有下列情況：如果當(dāng)前塊對(duì)該異常錯(cuò)誤設(shè)置了處理，則執(zhí)行它并成功完成該塊的執(zhí)行，然后控制轉(zhuǎn)給包含塊。如果沒有對(duì)當(dāng)前塊異常錯(cuò)誤設(shè)置定義處理器，則通過在包含塊中引發(fā)它來傳播異常錯(cuò)誤。然后對(duì)該包含塊執(zhí)行步驟1)。§5.2.2在聲明部分引發(fā)異常錯(cuò)誤如果在聲明部分引起異常情況，即在聲明部分出現(xiàn)錯(cuò)誤，那么該錯(cuò)誤就能影響到其它的塊。比如在有如下的PL/SQL程序：DECLAREAbcnumber(3):=’abc’;其它語句BEGIN其它語句EXCEPTIONWHENOTHERSTHEN其它語句END;例子中，由于Abcnumber(3)=’abc’;出錯(cuò)，盡管在EXCEPTION中說明了WHENOTHERSTHEN語句，但WHENOTHERSTHEN也不會(huì)被執(zhí)行。但是