第十四章 PLSQL語言基礎

1第14章PL/SQL語言基礎2本章內(nèi)容PL/SQL概述PL/SQL基礎控制結構游標異常處理314.1PL/SQL概述14.1.1PL/SQL特點PL/SQL是Oracle對標準SQL語言進行了過程化擴展的程序設計語言14.1.3PL/SQL執(zhí)行過程與開發(fā)工具PL/SQL開發(fā)工具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper4Declarev_deptnonumber;Begin

Selectdeptnointov_deptnofromempwhereempno=7844Ifv_deptno=10Then

Updateempsetsal=sal+100whereempno=7844;Else

Updateempsetsal=sal+200whereempno=7844;Endif;End;514.2PL/SQL基礎14.2.1PL/SQL程序結構1.PL/SQL塊的組成PL/SQL程序的基本單元是語句塊一個完整的PL/SQL語句塊由聲明部分、執(zhí)行部分和異常處理3部分組成。6定義PL/SQL語句塊的語法：

DECLARE

聲明部分（可選）

BEGIN

執(zhí)行部分（必須）

EXCEPTION

異常處理部分（可選）

END;

聲明變量、常量、游標和自定義類型描述了所要完成的處理，可以使用SQL語句和控制語句當程序在BEGIN部分出錯時執(zhí)行分號7Declarev_deptnonumber;Begin

Selectdeptnointov_deptnofromscott.emp

whereempno=7844;Ifv_deptno=10Then

Updatescott.empsetsal=sal+100whereempno=7844;ElseUpdatescott.empsetsal=sal+200whereempno=7844;Endif;

DBMS_OUTPUT.PUT_LINE(v_deptno);EXCEPTIONWHENNO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE('沒有這個人！');END;SETSERVEROUTPUTON在SQL*Plus中需要在PL/SQLDeveloper中不需要8在SQL*PLUS中執(zhí)行PL/SQL程序1.連接數(shù)據(jù)庫92.輸入程序，結束后輸入“/”，執(zhí)行程序10新建測試窗口：調(diào)試PL/SQL程序腳本在PL/SQLDEVELOPER中執(zhí)行PL/SQL程序11查看結果輸入程序執(zhí)行程序12132.PL/SQL塊分類匿名塊匿名塊是指動態(tài)生成，只能執(zhí)行一次的塊，不能由其他應用程序調(diào)用。命名塊命名塊是指一次編譯可多次執(zhí)行的PL/SQL程序，包括函數(shù)、存儲過程、包、觸發(fā)器。編譯后放在服務器中，由應用程序或系統(tǒng)在特定條件下調(diào)用執(zhí)行。141.字符集PL/SQL的字符集包括：大小寫字母：A~Z，a~z數(shù)字：0~9空白：制表符、空格和回車數(shù)字符號：+-*/〈

〉=標點符號：~!@#$%^&*（）_|{}[]？；：,.“‘注意PL/SQL字符集不區(qū)分大小寫。14.2.2PL/SQL詞法單元152.標識符命名規(guī)則在PL/SQL程序中，標識符是以字母開頭的，后邊可以跟字母、數(shù)字、美元符號（$）、井號（#）或下劃線（_），其最大長度為30個字符，并且所有字符都是有效的。16+-*/=:=賦值<><=>=<>!=~=^=()/**/<<>>%;:.‘“..@||字符串連接=>**乘方-3.分隔符174.常量值字符型文字數(shù)字型文字布爾型文字TRUE，F(xiàn)ALSE，NULL三個值。日期型文字185.注釋單行注釋--多行注釋以“/*”開始，以“*/”結束。1914.2.3數(shù)據(jù)類型數(shù)字類型字符類型日期/區(qū)間類型行標識類型布爾類型原始類型LOB類型引用類型記錄類型集合類型%TYPE與%ROWTYPE201.數(shù)字類型：用來存儲整數(shù)、實數(shù)和浮點數(shù)，常用的數(shù)值類型有NUMBER、PLS_INTEGER和BINARY_INTEGER。⑴NUMBER[(P,S)]存儲整數(shù)或浮點數(shù)。其中P是精度（指數(shù)值中所有數(shù)字的個數(shù)），S是刻度(指小數(shù)點右邊數(shù)字的個數(shù))。P和S都是可選的，但是如果指定了刻度S，則必須指定精度P。例：sgradenumber(3,1)P的取值范圍為1~3821

(2)BINARY_INTEGER表示-2147483647~+2147483647之間的整數(shù)發(fā)生溢出時會自動轉換為Number類型。

（3）PLS_INTEGER表示范圍與BINARY_INTEGER相同發(fā)生溢出時會報錯BINARY_INTEGER和PLS_INTEGER所需的存儲空間要比NUMBER少，運算的速度要高于NUMBER。Oracle數(shù)據(jù)庫中只支持NUMBER222.字符類型變長字符串VARCHAR2定長字符串CHARPL/SQL中的字符類型與Oracle數(shù)據(jù)庫中允許字符串的長度不同。類型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB23

9．記錄類型記錄類型的內(nèi)部含有成員分量，類似于C語言中的結構體，聲明語法如下DECLARETYPE記錄名ISRECORD(字段名1類型,……

字段名n類型);

聲明記錄類型的變量：變量名記錄名引用元素格式:記錄變量名.字段名,如teacher1.tnameDeclareTypeJS

IsRecord(tidchar(4),tnamevarchar2(8),tagenumber(3));teacher1JS;編號姓名年齡7900王凡367844張松47表列的數(shù)據(jù)類型發(fā)生變化怎么辦？Scott.教師2411.%TYPE與%ROWTYPE%TYPE用于定義與某個變量或數(shù)據(jù)庫表中某個列的數(shù)據(jù)類型一致的變量變量名模式名.表名.列名%TYPE例如，tnscott.教師.姓名%TYPE;%ROWTYPE用于定義與數(shù)據(jù)庫中某個表結構一致的記錄類型的變量，可用于存儲表中一行記錄變量名模式名.表名%ROWTYPE例如，teacher2scott.教師%ROWTYPE;引用變量中的某個字段值:變量名.字段名例如，teacher2.姓名25例，定義變量v_sal和v_emp，分別用于存儲scott.emp表中編號為7844的職工的工資和7900的職工信息DECLAREv_salv_empBEGINSELECTsalFROMscott.empWHEREempno=7844;SELECT*FROMscott.empWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_sal);DBMS_OUTPUT.PUT_LINE(v_emp);END;empnoenamesal7900徐立20007844王鳳2500scott.emp.sal%TYPE;scott.emp%ROWTYPE;INTO

v_salINTOv_emp復合變量按字段分別輸出v_emp.empno||v_emp.ename||v_emp.sal26練習，定義變量num和變量stu，分別用來存儲sys.xuesheng表中“張紅”的學號和”王娜”的所有信息，并輸出

DeclarenumstuBeginSELECTsidintonumFROMsys.xueshengWHEREname=‘張紅’;SELECT*intostuFROMsys.xueshengWHEREname=‘王娜’;DBMS_OUTPUT.PUT_LINE(num);DBMS_OUTPUT.PUT_LINE(stu.sid||||stu.age);END;sys.xuesheng.sid%Type;sys.xuesheng%RowType;sidnameage101張紅18102王娜172728作業(yè)：P297第2題（1）（2）29作業(yè)：編寫PL/SQL程序，在scott.emp和scott.dept表中查找編號empno為7844的員工的姓名ename和部門名稱dname，并輸出結果declarev_escott.emp.ename%type;v_dscott.dept.dname%type;beginselectename,dnamefrom

scott.emp,scott.deptwhere

emp.deptno=dept.deptnoandempno=7844;dbms_output.put_line(v_e||v_d);end;intov_e,v_d30變量聲明14.2.4變量與常量1.變量與常量的定義變量名數(shù)據(jù)類型說明每行只能定義一個變量；如果加上關鍵字CONSTANT，則表示所定義的是一個常量，必須為它賦初值；如果定義變量時使用了NOTNULL關鍵字，則必須為變量賦初值；如果變量沒有賦初值，則默認為NULL；例如，v2NUMBER(4)NOTNULL:=10;[CONSTANT][NOTNULL][:=默認值];DEFAULT|312.變量的作用域如果PL/SQL塊相互嵌套，則在內(nèi)部塊中聲明的變量是局部的，只能在內(nèi)部塊中引用，而在外部塊中聲明的變量是全局的，既可以在外部塊中引用，也可以在內(nèi)部塊中引用。如果內(nèi)部塊與外部塊中定義了同名變量，則在內(nèi)部塊中引用外部塊的全局變量時需要使用外部塊名進行標識。

32<<OUTER>>DECLAREv_enameCHAR(16);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:='INNERV_ENAME';OUTER.v_ename:='OUTERV_ENAME';END;DBMS_OUTPUT.PUT_LINE(v_ename);END;

輸出結果為'OUTERV_ENAME'3314.2.5PL/SQL記錄

1.用戶定義記錄類型及變量2.利用%ROWTYPE獲取記錄類型定義變量3.記錄類型變量的應用在SELECT語句中使用記錄類型變量在INSERT語句中使用記錄類型變量在UPDATE語句中使用記錄類型變量在DELETE語句中使用記錄類型變量34（1）在SELECT語句中使用記錄類型變量①在SELECTINTO

語句中使用記錄類型變量例，在scott.emp表中查詢姓名SMITH員工信息DECLAREv_emp

BEGIN

END;empnoenamesal7844SMITH2500scott.emp%ROWTYPE;SELECT*

INTOv_emp

FROMscott.empWHEREename=‘SMITH’;SELECTempno,ename,salINTOv_emp.empno,v_emp.ename,v_emp.sal

FROMscott.empWHEREename=‘SMITH’;個數(shù)、順序、類型一一匹配35（2）在INSERT語句中使用記錄類型變量例，向scott.dept表中插入一條新記錄DECLAREv_deptBEGINv_dept.deptno:=50;v_dept.loc:=‘北京';v_dept.dname:=‘計算機';

INSERTINTOscott.deptVALUESv_dept;END;記錄類型變量中分量的個數(shù)、順序、類型應該與表中列的個數(shù)、順序、類型完全匹配。deptnolocdname48廣州研發(fā)49上海運維50北京計算機scott.dept%ROWTYPE;逐個字段賦值36(3)在UPDATE語句中使用記錄類型變量例，將deptno為50的部門名稱dname改為管理DECLAREv_deptscott.dept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:=‘北京';v_dept.dname:=‘管理';

UPDATEscott.deptSETdname=v_dept.dnameWHEREdeptno=50;END;DELETEFROMscott.deptWHEREdeptno=v_emp.deptno;deptnolocdname48廣州研發(fā)49上海運維50北京計算機管理37練習，定義變量v_sal初始值為2000，用于存儲scott.emp(empno,ename,sal)表中sal列的值，向scott.emp表中插入數(shù)據(jù)，其中empno列數(shù)據(jù)為7500，ename為’JOAN’，sal為v_sal的值。然后將該表中所有sal列值小于v_sal的員工sal增加100。最后將sal列值大于v_sal的員工信息刪除DECLAREv_salscott.emp.sal%TYPE:=2000;BEGININSERTINTOscott.emp(empno,ename,sal)VALUES(7500,'JOAN',v_sal);UPDATEscott.empSETsal=sal+100WHEREsal<v_sal;DELETEFROMscott.empWHEREsal>v_sal;END;在PL/SQL程序中可以動態(tài)指定變量的值例，將指定員工的工資增加400declare

v_empnonumber(4);begin

v_empno:=

updatescott.empsetsal=sal+400

whereempno=v_empno;end;38&x;在程序運行時輸入在SQL*PLUS中運行3914.2.7PL/SQL中SQL語句在PL/SQL中只允許出現(xiàn):

SELECT、UPDATE、DELETE、INSERT事務控制語句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL語句不可以直接使用401.SELECT語句在PL/SQL程序中，使用SELECT…INTO語句查詢一個記錄的信息。SELECT…INTO語句只能查詢得到一條結果記錄，如果沒有查詢到任何數(shù)據(jù)，會產(chǎn)生NO_DATA_FOUND異常；如果查詢到多個記錄，則會產(chǎn)生TOO_MANY_ROWS異常。412.DML語句允許使用變量3.WHERE語句WHERE條件中變量和列名的區(qū)分先匹配列名，后匹配變量如，Whereempno=v_empnoandename=‘JOAN’字符串比較①填充比較：Char類型添加空格使字符串等長，比較每個字符ASCII碼②非填充比較：Varchar2類型比較每個字符ASCII碼，最先結束的字符串小424.RETURNING語句在DML語句末尾使用RETURNING語句返回當前語句操作的記錄的信息例，將編號為7844的職工工資提高100并返回修改后的結果DECLAREv_salscott.emp.sal%TYPE;BEGINUPDATEscott.empSETsal=sal+100WHEREempno=7844

RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;4314.3控制結構選擇結構IF語句CASE語句循環(huán)結構LOOP循環(huán)While循環(huán)For循環(huán)跳轉結構GOTO4414.3.1選擇結構1.IF語句IF條件1THEN語句1;ENDIF;[ELSIF

條件2THEN語句2;]……[ELSE語句n+1;]45例如，輸入一個員工號，修改該員工的工資，如果該員工的部門號detpno為10，工資增加100，否則增加300。DECLAREv_empnoscott.emp.empno%type;--員工號

v_deptno

scott.emp.deptno%type;--部門號

v_incrementNUMBER(4);--增加工資BEGIN

--變量值由用戶在SQL*PLUS輸入

v_empno:=&x;--獲取該員工的部門號deptno

SELECTdeptnoINTO

v_deptnoFROMscott.empWHEREempno=v_empno;

46--判斷v_deptno是否為10，更新的值不同IFv_deptno=10THEN

v_increment:=100;ELSE

v_increment:=300;ENDIF;--更新該員工的salUPDATEscott.empSETsal=sal+v_increment

WHEREempno=v_empno;END;47(1)只進行等值比較的CASE語句CASE變量名

WHEN值1THEN語句1;……WHEN值nTHEN語句n;[ELSE語句n+1;]ENDCASE;判斷變量值與WHEN后面的值是否相等，如果相等，執(zhí)行對應THEN后面的語句注意：當遇到第一個WHEN成立時，執(zhí)行其后的操作，操作完后結束CASE語句。其他的WHEN不再判斷

2

CASE語句48(2)進行多種條件比較的CASE語句CASE

WHEN

條件1THEN語句1;……

WHEN條件nTHEN語句n;[ELSE

語句n+1;]ENDCASE;對WHEN后面的條件進行判斷，條件為真則執(zhí)行后面的語句49例，根據(jù)輸入的員工號，修改該員工工資。如果該員工工資低于1000，則工資增加200；如果工資在1000～2000之間，則增加150；否則增加50。DECLAREv_escott.emp.empno%type;v_salemp.sal%type;v_iNUMBER(4);BEGINv_e:=&x;SELECTsalINTOv_salFROMscott.empWHEREempno=v_e;CASEWHENv_sal<1000THENv_i:=200;WHENv_sal<2000THENv_i:=150;ELSEv_i:=50;ENDCASE;UPDATEscott.empSETsal=sal+v_iWHEREempno=v_e;END;501.簡單循環(huán)語法

LOOP

循環(huán)體;

EXITWHEN條件;ENDLOOP;注意在循環(huán)體中一定要包含EXIT語句，否則程序進入死循環(huán)14.3.2循環(huán)結構512.While循環(huán)基本語法WHILE條件

LOOP

循環(huán)體;ENDLOOP;51例3.4使用WHILE循環(huán)輸出1～10的數(shù)值。

declarenumnumber(2):=1;beginwhile____________loopdbms_output.put_line(num);_____________________endloop;end;num<=10num:=num+1;523.FOR循環(huán)基本語法FOR循環(huán)變量IN

下界..上界LOOP

循環(huán)體;ENDLOOP;注意：循環(huán)變量不需要顯式定義；如果使用REVERSE關鍵字，則表示循環(huán)變量從上界向下界遞減計數(shù)；循環(huán)變量只能在循環(huán)體中使用，不能在循環(huán)體外使用。[REVERSE]53

例3.5使用FOR循環(huán)輸出1～10的數(shù)值。

declarenumnumber(2);beginfor______________________dbms_output.put_line(num);____________end;如果改成fornuminreverse1..10loop輸出什么結果？numin1..10loopendloop;54練習：使用PL/SQL程序計算1到100的和declareinumber(3);snumber(5);begini:=0;s:=0;whilei<=100loops:=s+i;i:=i+1;endloop;dbms_output.put_line(s);end;Foriin1..100loops:=s+i;endloop;5514.4游標14.4.1游標的概念及類型游標的概念及類型執(zhí)行查詢語句和操作語句時，Oracle會在內(nèi)存中開辟一個緩沖區(qū)，用來存儲語句返回的數(shù)據(jù)行集。使用游標時，SELECT語句查詢的結果可以是單條記錄，多條記錄，也可以是零條記錄。游標有一個指針，最初指向查詢結果的首部，隨著游標指針的推進，就可以訪問相應的記錄。56001張紅網(wǎng)絡1班002宋佳佳網(wǎng)絡1班003王楚網(wǎng)絡1班004田宇樂網(wǎng)絡1班001張紅網(wǎng)絡1班變量p游標mySelect*fromstudent定義變量pmy%rowtype

游標名%rowtype57游標的類型顯式游標由用戶定義、操作，用于處理返回多行數(shù)據(jù)的SELECT查詢。隱式游標由系統(tǒng)自動進行定義和操作，用于處理DML語句和返回單行數(shù)據(jù)的SELECT查詢5814.4.2顯式游標1.顯式游標的操作定義游標打開游標檢索游標：循環(huán)關閉游標完畢59(1)定義游標CURSOR

游標名

IS

select語句;說明游標在declare部分進行定義；游標定義時若引用變量，則變量必須在游標定義之前定義；定義游標時并沒有生成數(shù)據(jù)，只是保存定義游標定義后，可以使用“游標名%ROWTYPE”定義游標類型變量和保存游標中的數(shù)據(jù)行。60（2）打開游標OPEN游標名;在內(nèi)存中分配緩沖區(qū)，執(zhí)行select語句在數(shù)據(jù)庫中檢索數(shù)據(jù)，將查詢結果緩存在緩沖區(qū)中。一旦游標打開，就無法再次打開，除非先關閉如果游標定義中的變量值發(fā)生變化，則只能在下次打開游標時才起作用。61例，定義游標mycus為從表scott.emp中取出的sal列的值大于1000的數(shù)據(jù)，并打開游標。DeclareCursorBegin

openmycus;End;mycusselect*fromscott.empwheresal>1000;Is7834張紅12007844宋佳佳15007859王楚11007934田宇樂200062（3）檢索游標使游標指針下移，指向下一個數(shù)據(jù)行,然后取出指針所指的數(shù)據(jù)行，將其存入到變量中。FETCH

游標名

INTO

變量列表|記錄變量;

游標指針只能向下移動，不能回退由于游標中取出的為一條記錄，因此變量需要是能夠存儲記錄的記錄變量，或者使用變量列表（多個變量）63兩種形式變量：

(1)變量列表：多個變量名，游標中各個字段依次存入各變量中如fetchmyintoa,b,c;變量個數(shù)、順序、數(shù)據(jù)類型必須與游標中每行記錄的字段數(shù)、順序以及數(shù)據(jù)類型一一對應。

(2)記錄變量：使用“游標名%rowtype”定義的記錄類型的變量如fetchmyintop;64001張紅網(wǎng)絡1班002宋佳佳網(wǎng)絡1班003王楚網(wǎng)絡1班001張紅網(wǎng)絡1班1.使用三個變量a,b,c分別存儲三個值asystem.student.sid%type;bsystem.student.sname%type；csystem.student.sclass%type；游標mySelectsid,sname,sclassfromsystem.student2.定義變量p,存儲游標的一行記錄p

my%rowtype；

變量p65（4）關閉游標語法格式

CLOSE游標名;說明游標所對應的內(nèi)存工作區(qū)變?yōu)闊o效，釋放與游標相關的系統(tǒng)資源。66例題，使用游標在scott.emp表中根據(jù)輸入的部門號deptno查詢某個部門的所有員工信息，部門號在程序運行時指定DECLARE

a

scott.emp.deptno%TYPE;CURSORc_empISSELECT*FROMscott.empWHEREdeptno=a;--變量要先定義

v1BEGINa:=&x;OPENc_emp;--打開游標c_emp%ROWTYPE;67--取游標中的記錄LOOPFETCHc_empINTOv1;

EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v1.empno||v1.ename||v1.sal||a);ENDLOOP;--關閉游標CLOSEc_emp;END;7844張紅1200107845宋佳佳1300107846王楚200010Fetch語句執(zhí)行失敗，退出循環(huán)682游標的屬性

%ISOPEN布爾型。如果游標已經(jīng)打開，返回TRUE,否則為FALSE。%FOUND布爾型，如果最近一次使用FETCH語句有返回結果，則為TRUE,否則為FALSE;%NOTFOUND布爾型，如果最近一次使用FETCH語句沒有返回結果，則為TRUE,否則為FALSE;%ROWCOUNT數(shù)值型，返回到目前為止從游標緩沖區(qū)檢索的元組數(shù)。69練習，定義游標mycus為從表scott.emp中取出的sal的值大于1000的數(shù)據(jù)，顯示第一條數(shù)據(jù)的empno字段內(nèi)容。Declare_________________--定義

select*fromscott.empwheresal>1000;a_________________--變量a存儲游標記錄Beginopenmycus;--打開

________

___--推進

dbms_output.put_line(

);closemycus;--關閉End;CursormycusIsfetch

mycusintoa;mycus%rowtype;a.empno70練習：利用游標統(tǒng)計并輸出scott.emp表中各個部門編號deptno和各部門工資sal的平均值。DECLARECURSORc1ISSELECTdeptno,avg(sal)asalFROMscott.empGROUPBYdeptno;v_deptc1%ROWTYPE;BEGINOPENc1;

102567202280301340deptnoasal游標c171scott.emp表72LOOPFETCHc1INTOv_dept;

EXITWHENc1%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_dept.deptno||

v_dept.asal);ENDLOOP;CLOSEc1;END;102567202280301340deptnoasal游標c1732.輸出所有比本部門平均工資高的員工信息DECLARECURSORc1ISselect*fromscott.empwheresal>(selectavg(sal)fromscott.empgroupbydeptnohaving);74bab.deptno=a.deptno復習：顯式游標的使用定義游標Cursor游標名IsSelect語句；打開游標

Open游標名；檢索游標：循環(huán)

Fetch游標名Into變量；關閉游標

Close游標名；75變量列表或游標名%Rowtype作業(yè)：1.輸出所有員工的姓名、員工號、工資和部門號DECLARECURSORc1ISSELECTename,empno,sal,deptnoFROMscott.emp;v_ec1%ROWTYPE;BEGINOPENc1;76LOOPFETCHc1INTOv_e;EXITWHENc1%NOTFOUND;DBMS_OUTPUT.PUT_LINE('姓名'||v_e.ename||'員工號'||v_e.empno||'工資'||v_e.sal||'部門號'||v_e.deptno);ENDLOOP;CLOSEc1;END;77各字段依次輸出enameempnosaldeptnoJOAN7844130010PETER7848180020SCOTT750020001078

4顯式游標的檢索利用簡單循環(huán)檢索游標利用WHILE循環(huán)檢索游標利用FOR循環(huán)檢索游標79（1）利用簡單LOOP循環(huán)檢索游標OPEN游標名;LOOPFETCH

游標名INTO變量名;

EXITWHEN游標名%NOTFOUND;……ENDLOOP;EXITWHEN子句應該是FETCH…INTO語句的下一條語句。

80（2）利用WHILE循環(huán)檢索游標OPEN游標名;FETCH游標名INTO

變量名;WHILELOOP……FETCH游標名INTO

變量名;……ENDLOOP;在While循環(huán)之前進行一次FETCH操作，作為第一次循環(huán)的條件。游標名%FOUND

81例：利用WHILE循環(huán)統(tǒng)計并輸出各個部門的平均工資。DECLARECURSORc2ISSELECTdeptno,avg(sal)asal

FROMscott.empv_deptc2%ROWTYPE;BEGINOPENc2;

GROUPBYdeptno;82FETCHc2INTOv_dept;WHILE

LOOP

DBMS_OUTPUT.PUT_LINE(v_dept.deptno||v_dept.asal);

FETCHc2INTOv_dept;ENDLOOP;CLOSEc2;END;102567202280301340deptnoasal游標c2c2%FOUND83（3）利用FOR循環(huán)檢索游標FOR循環(huán)變量INLOOP

……ENDLOOP;系統(tǒng)自動定義循環(huán)變量，并且其類型為“游標名%ROWTYPE”。系統(tǒng)自動打開游標，不用OPEN語句；系統(tǒng)自動不斷從游標中取數(shù)據(jù)并放入循環(huán)變量系統(tǒng)自動進行%FOUND屬性檢查以確定是否有數(shù)據(jù)當游標中所有的記錄都被提取完畢或循環(huán)中斷時，系統(tǒng)自動地關閉游標。游標名例，利用FOR循環(huán)統(tǒng)計并輸出各個部門的平均工資。DECLARECURSORc3ISSELECTdeptno,avg(sal)avgsalFROMscott.empGROUPBYdeptno;BEGIN

FOR

v_deptINc3

LOOP

DBMS_OUTPUT.PUT_LINE(v_dept.deptno||v_dept.avgsal);

ENDLOOP;

END;102567202280301340deptnoasal游標c3無需聲明，自動定義游標名85可以不在聲明部分定義游標，而在FOR語句中直接使用子查詢。DECLARECURSORc3ISSELECTdeptno,avg(sal)avgsalFROMscott.empGROUPBYdeptno;BEGINFORv_empINDBMS_OUTPUT.PUT_LINE(v_emp.empno||v_emp.ename);ENDLOOP;END;(SELECTdeptno,avg(sal)avgsalFROMscott.empGROUPBYdeptno)LOOPc3LOOP練習：輸出所有員工的姓名、員工號、工資和部門號，用For循環(huán)實現(xiàn)DECLARECURSORc1ISSELECTename,empno,sal,deptnoFROMscott.emp;BEGINForv_einc1LOOPDBMS_OUTPUT.PUT_LINE('姓名'||v_e.ename||'員工號'||v_e.empno||'工資'||v_e.sal||'部門號'||v_e.deptno);ENDLOOP;END;86873參數(shù)化顯式游標通過參數(shù)的取值來限制游標的SQL查詢結果參數(shù)值不同選取的數(shù)據(jù)行不同，從而能夠動態(tài)使用游標

DeclareCursor游標名IsSelect語句

BeginOpen游標名(參數(shù)值)(參數(shù)名數(shù)據(jù)類型)Where條件中體現(xiàn)參數(shù)88例，根據(jù)部門編號deptno輸出該部門員工姓名DECLARECURSORc4ISSELECT*FROMscott.empWHEREv_empc4%ROWTYPE;BEGINOPENc4(10);LOOPFETCHc4INTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.ename);ENDLOOP;CLOSEc4;END;deptno=p;(p

emp.deptno%TYPE)89注意：定義參數(shù)化游標時，只能指定參數(shù)的類型，而不能指定參數(shù)的長度、精度、刻度；打開帶參數(shù)的游標時，實參的個數(shù)和數(shù)據(jù)類型等必須與游標定義時形參個數(shù)和數(shù)據(jù)類型等相匹配。905利用游標更新或刪除數(shù)據(jù)游標定義語法CURSOR游標名ISSELECT列FROM表名FORUPDATE注意打開游標時對相應的表加鎖,其他用戶不能對該表進行更新操作；若數(shù)據(jù)對象已經(jīng)被其他會話加鎖，則當前會話掛起等待，若指定了NOWAIT子句，則不等待，返回ORACLE錯誤。當用戶執(zhí)行COMMIT或ROLLBACK操作時，數(shù)據(jù)上的鎖會自動被釋放。[OF列名][NOWAIT];

91例如，修改員工的工資，如果員工的部門號為10，則工資提高100；如果部門號為20，則工資提高150，否則工資提高250。DECLARECURSORc_empISSELECT*FROMempFORUPDATE;v_incrementNUMBER;BEGINFORv_empINc_empLOOPCASEv_emp.deptno

WHEN10THENv_increment:=100;WHEN20THENv_increment:=150;ELSEv_increment:=250;ENDCASE;

UPDATEempSETsal=sal+v_incrementWHERECURRENTOFc_emp;ENDLOOP;COMMIT;END;

9214.4.3隱式游標概念所有的SQL語句都有一個執(zhí)行的緩沖區(qū)，隱式游標就是指向該緩沖區(qū)的指針，由系統(tǒng)隱含地打開、處理和關閉。隱式游標又稱為SQL游標。隱式游標主要用于處理INSERT、UPDATE，DELETE以及單行的SELECT…INTO語句，沒有OPEN，F(xiàn)ETCH，CLOSE等操作命令。9314.5異常處理14.5.1異常概述1.Oracle錯誤處理機制Oracle中對運行時錯誤的處理采用了異常處理機制。一個錯誤對應一個異常，當錯誤產(chǎn)生時拋出相應的異常，并被異常處理器捕獲，程序控制權傳遞給異常處理器。942.異常的類型（1）預定義的Oracle異常（Oracle錯誤）錯誤產(chǎn)生時，與錯誤對應的預定義異常被自動拋出，通過捕獲該異?？梢詫﹀e誤進行處理。表14-4（2）非預定義的Oracle異常（Oracle錯誤）錯誤沒有預定義異常與其關聯(lián)，需要在語句塊的聲明部分聲明一個異常，通過編譯指示PRAGMAEXCEPTION_INIT將該異常與錯誤相關聯(lián)。此后，當執(zhí)行過程出現(xiàn)該錯誤時將自動拋出該異常。95（3）用戶自定義的異常有些操作并不會產(chǎn)生Oracle錯誤，但是從業(yè)務規(guī)則角度考慮認為是一種錯誤。用戶自定義異常必須在聲明部分進行聲明。當異常發(fā)生時，系統(tǒng)不能自動觸發(fā)，需要用戶使用RAISE語句。在EXCEPTION部分捕捉并處理異常。9614.5.2異常處理過程異常處理分3個步驟進行：在聲明部分為錯誤定義異常，包括非預定義異常和用戶定義異常。預定義異常無需聲明在執(zhí)行部分Begin中當錯誤產(chǎn)生時拋出與錯誤對應的異常，并跳轉到Exception部分。在異常處理Exception部分通過異常處理器捕獲異常，并進行異常處理。97預定義異常的處理例，查詢名為SMITH的員工工資，如果該員工不存在，輸出“不存在該員工!”；如果存在多個同名的員工，則輸出“存在多個同名員工!”DECLAREv_salscott.emp.sal%type;BEGINSELECTsalINTOv_salFROMscott.emp

WHEREename='SMITH';DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(‘不存在該員工!');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘存在多個同名員工!');END;異常名9899100非預定義異常的處理例，刪除dept表中部門號為10的部門信息，如果不能刪除則輸出“Therearesubrecordsinemptable!”DECLARE

e_deptno_fk

EXCEPTION;

PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINDELETEFROMdeptWHEREdeptno=10;EXCEPTIONWHENe_deptno_fkTHENDBMS_OUTPUT.PUT_LINE('Therearesubrecordsinemptable!');END;101修改7844員工的工資，保證修改后工資不超過6000。DECLAREe_highlimitEXCEPTION;v_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;END;102（1）異常的定義Oracle中的3種異常，其中預定義異常由系統(tǒng)定義，而其他兩種異常則需要用戶定義。定義異常方法e_exceptionEXCEPTION;如果是非預定義的異常，需要將異常與一個Oracle錯誤相關聯(lián)，其語法為：PRAGMAEXCEPTION_INIT(e_exception,-#####);注意Oracle內(nèi)部錯誤號用一個負的5位數(shù)表示，如-02292。其中

-20999～-20000為用戶定義錯誤的保留號。103（2）異常的拋出由于系統(tǒng)可以自動識別Oracle內(nèi)部錯誤，因此當錯誤產(chǎn)生時系統(tǒng)會自動拋出與之對應的預定義異?；蚍穷A定義異常。但是，系統(tǒng)無法識別用戶定義錯誤，因此當用戶定義錯誤產(chǎn)生時，需要用戶手動拋出與之對應的異常。用戶定義異常的拋出語法為RAISEuser_define_exception；104（3）異常的捕獲與處理異常處理器的基本形式為EXCEPTIONWHENexception1[ORexcetpion2…]THENsequence_of_statements1;WHENexception3[ORexception4…]THENsequence_of_statements2;……WHENOTHERSTHENsequence_of_statementsn;END;注意：一個異常處理器可以捕獲多個異常，只需在WHEN子句中用

OR連接即可；一個異常只能被一個異常處理器捕獲，并進行處理。105（4）OTHERS異常處理器OTHERS異常處理器是一個特殊的異常處理器，可以捕獲所有的異常。通常，OTHERS異常處理器總是作為異常處理部分的最后一個異常處理器，負責處理那些沒有被其他異常處理器捕獲的異常。

106DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thereissomewronginselecting!');END;107可以通過兩個函數(shù)來獲取錯誤相關信息。SQLCODE：返回當前錯誤代碼。如果是用戶定義錯誤返回值為1；如果是ORA-1403：NODATAFOUND錯誤，返回值為100其他Oracle內(nèi)部錯誤返回相應的錯誤號。

SQLERRM：返回當前錯誤的消息文本。如果是Oracle內(nèi)部錯誤，返回系統(tǒng)內(nèi)部的錯誤描述；如果是用戶定義錯誤，則返回信息文本為“User-definedException”。108DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;v_codeNUMBER(6);v_textVARCHAR2(200);BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENv_code:=SQLCODE;v_text:=SQLERRM;DBMS_OUTPUT.PUT_LINE(v_code||''||v_text);END;10914.5.3異常的傳播執(zhí)行部分的異常聲明部分和異常處理部分的異常110（1）執(zhí)行部分異常的傳播如果當前語句塊有該異常的處理器，則執(zhí)行之，并且成功完成該語句塊。然后，控制權傳遞到外層語句塊。

如果當前語句塊沒有該異常的處理器，則通過在外層語句塊中產(chǎn)生該異常來傳播該異常。然后，執(zhí)行對外層語句塊執(zhí)行步驟1。如果沒有外層語句塊，則該異常將傳播到調(diào)用環(huán)境。

111DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');END;/Thereisnotsuchanemployee!Nowthisisoutputtedbyouterblock!

112DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREdeptno=10;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanoneemployee!');END;/Therearemorethanoneemployee!113聲明部分和異常處理部分的異常會立刻傳播到外層語句塊的異常處理部分，即使當前語句塊有該異常的異常處理器。

（2）聲明部分和異常處理部分的異常114BEGINDECLAREv_numberNUMBER(6):='ABC';BEGINv_number:=10;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyinnerblock!');END;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyouterblock!');END;/Thisisoutputtedbyouterblock!115結論無論是執(zhí)行部分的異常，還是聲明部分或異常處理部分的異常，如果在本塊中沒有處理，最終都將向外層塊中傳播。通常在程序最外層塊的異常處理部分放置OTHERS異常處理器，以保證沒有錯誤被漏掉檢測，否則錯誤將傳遞到調(diào)用環(huán)境。116隱式游標屬性SQL%ISOPEN：布爾型值，判斷隱式游標是否已經(jīng)打開。對用戶而言，該屬性值始終為FALSE，因為操作時系統(tǒng)自動打開，操作完后立即自動關閉。SQL%FOUND：布爾型值，判斷當前的操作是否會對數(shù)據(jù)庫產(chǎn)生影響。如果有數(shù)據(jù)的插入、刪除、修改或查詢到數(shù)據(jù)，則返回TRUE，否則返回FALSE。SQL%NOTFOUND：布爾型值，判斷當前的操作是否對數(shù)據(jù)庫產(chǎn)生影響。如果沒有數(shù)據(jù)的插入、刪除、修改或沒有查詢到數(shù)據(jù)，則返回TRUE，否則返回FALSE。SQL%ROWCOUNT：數(shù)值型，返回當前操作所涉及的數(shù)據(jù)庫中的行數(shù)。117修改員工號為1000的員工工資，將其工資增加100。如果該員工不存在，則向emp表中插入一個員工號為1000，工資為1600的員工。118BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%NOTFOUNDTHENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;或BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%ROWCOUNT=0THENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;11914.4.4游標變量概念游標變量是一個指向多行查詢結果集的指針，不與特定的查詢綁定，因此具有非常大的靈活性，可以在打開游標變量時定義查詢，可以返回不同結構的結果集使用游標變量包括游標引用類型（REFCURSOR）聲明游標變量打開游標變量檢索游標變量關閉游標變量120（1）定義游標引用類型及游標變量

語法TYPEref_cursor_type_nameISREFCURSOR[RETURNreturn_type]RETURN子句用于指定定義的游標類型返回結果集的類型，該類型必須是記錄類型。如果定義游標引用類型時帶有RETURN子句，則用其定義的變量稱為強游標變量，否則稱為弱游標變量。在Oracle10g中，系統(tǒng)預定義了一個游標引用類型，稱為SYS_REFCURSOR，可以直接使用它定義游標變量。121語法ref_cursor_type_namevariable_name;例如TYPEemp_cursor_typeISREFCURSORRETURNemp%ROWTYPE;TYPEgeneral_cursor_typeISREFCURSOR;v_empemp_cursor_type;v_generalgeneral_cursor_type;my_cursorSYS_REFCURSOR;122（2）打開游標變量語法OPENcursor_variableFORselect_statement;注意如果打開的游標變量是強游標變量，則查詢語句的返回類型必須與游標引用類型定義中RETURN子句指定的返回類型相匹配。例如OPENv_empFORSELECT*FROMemp;OPENv_generalFORSELECTempno,ename,sal,deptnoFROMemp;OPENmy_cursorFORSELECT*FROMdept;123（3）檢索游標變量語法LOOPFETCHcursor_variableINTOvariable1,variable2,…;EXITWHENcursor_variable%NOTFOUND;……ENDLOOP;

注意檢索游標變量時只能使用簡單循環(huán)或WHILE循環(huán)，不能采用FOR循環(huán)。124（4）關閉游標變量語法CLOSEcursor_variable;125DECLARETYPEemp_cursor_typeISREFCURSORRETURNemp%ROWTYPE;TYPEgeneral_cursor_typeISREFCURSOR;v_empemp_cursor_type;v_generalgeneral_cursor_type;my_cursorSYS_REFCURSOR;v_empinfoemp%RO