第十四章 PLSQL語言基礎(chǔ)

1第14章PL/SQL語言基礎(chǔ)2本章內(nèi)容PL/SQL概述PL/SQL基礎(chǔ)控制結(jié)構(gòu)游標(biāo)異常處理314.1PL/SQL概述14.1.1PL/SQL特點(diǎn)PL/SQL是Oracle對標(biāo)準(zhǔn)SQL語言進(jìn)行了過程化擴(kuò)展的程序設(shè)計語言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基礎(chǔ)14.2.1PL/SQL程序結(jié)構(gòu)1.PL/SQL塊的組成PL/SQL程序的基本單元是語句塊一個完整的PL/SQL語句塊由聲明部分、執(zhí)行部分和異常處理3部分組成。6定義PL/SQL語句塊的語法：

DECLARE

聲明部分（可選）

BEGIN

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

EXCEPTION

異常處理部分（可選）

END;

聲明變量、常量、游標(biāo)和自定義類型描述了所要完成的處理，可以使用SQL語句和控制語句當(dāng)程序在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.輸入程序，結(jié)束后輸入“/”，執(zhí)行程序10新建測試窗口：調(diào)試PL/SQL程序腳本在PL/SQLDEVELOPER中執(zhí)行PL/SQL程序11查看結(jié)果輸入程序執(zhí)行程序12132.PL/SQL塊分類匿名塊匿名塊是指動態(tài)生成，只能執(zhí)行一次的塊，不能由其他應(yīng)用程序調(diào)用。命名塊命名塊是指一次編譯可多次執(zhí)行的PL/SQL程序，包括函數(shù)、存儲過程、包、觸發(fā)器。編譯后放在服務(wù)器中，由應(yīng)用程序或系統(tǒng)在特定條件下調(diào)用執(zhí)行。141.字符集PL/SQL的字符集包括：大小寫字母：A~Z，a~z數(shù)字：0~9空白：制表符、空格和回車數(shù)字符號：+-*/〈

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

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

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

9．記錄類型記錄類型的內(nèi)部含有成員分量，類似于C語言中的結(jié)構(gòu)體，聲明語法如下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ù)庫中某個表結(jié)構(gòu)一致的記錄類型的變量，可用于存儲表中一行記錄變量名模式名.表名%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復(fù)合變量按字段分別輸出v_emp.empno||v_emp.ename||v_emp.sal26練習(xí)，定義變量num和變量stu，分別用來存儲sys.xuesheng表中“張紅”的學(xué)號和”王娜”的所有信息，并輸出

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，并輸出結(jié)果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ù)類型說明每行只能定義一個變量；如果加上關(guān)鍵字CONSTANT，則表示所定義的是一個常量，必須為它賦初值；如果定義變量時使用了NOTNULL關(guān)鍵字，則必須為變量賦初值；如果變量沒有賦初值，則默認(rèn)為NULL；例如，v2NUMBER(4)NOTNULL:=10;[CONSTANT][NOTNULL][:=默認(rèn)值];DEFAULT|312.變量的作用域如果PL/SQL塊相互嵌套，則在內(nèi)部塊中聲明的變量是局部的，只能在內(nèi)部塊中引用，而在外部塊中聲明的變量是全局的，既可以在外部塊中引用，也可以在內(nèi)部塊中引用。如果內(nèi)部塊與外部塊中定義了同名變量，則在內(nèi)部塊中引用外部塊的全局變量時需要使用外部塊名進(jìn)行標(biāo)識。

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;

輸出結(jié)果為'OUTERV_ENAME'3314.2.5PL/SQL記錄

1.用戶定義記錄類型及變量2.利用%ROWTYPE獲取記錄類型定義變量3.記錄類型變量的應(yīng)用在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:=‘計算機(jī)';

INSERTINTOscott.deptVALUESv_dept;END;記錄類型變量中分量的個數(shù)、順序、類型應(yīng)該與表中列的個數(shù)、順序、類型完全匹配。deptnolocdname48廣州研發(fā)49上海運(yùn)維50北京計算機(jī)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上海運(yùn)維50北京計算機(jī)管理37練習(xí)，定義變量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;在程序運(yùn)行時輸入在SQL*PLUS中運(yùn)行3914.2.7PL/SQL中SQL語句在PL/SQL中只允許出現(xiàn):

SELECT、UPDATE、DELETE、INSERT事務(wù)控制語句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL語句不可以直接使用401.SELECT語句在PL/SQL程序中，使用SELECT…INTO語句查詢一個記錄的信息。SELECT…INTO語句只能查詢得到一條結(jié)果記錄，如果沒有查詢到任何數(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碼，最先結(jié)束的字符串小424.RETURNING語句在DML語句末尾使用RETURNING語句返回當(dāng)前語句操作的記錄的信息例，將編號為7844的職工工資提高100并返回修改后的結(jié)果DECLAREv_salscott.emp.sal%TYPE;BEGINUPDATEscott.empSETsal=sal+100WHEREempno=7844

RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;4314.3控制結(jié)構(gòu)選擇結(jié)構(gòu)IF語句CASE語句循環(huán)結(jié)構(gòu)LOOP循環(huán)While循環(huán)For循環(huán)跳轉(zhuǎn)結(jié)構(gòu)GOTO4414.3.1選擇結(jié)構(gòu)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)只進(jìn)行等值比較的CASE語句CASE變量名

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

2

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

WHEN

條件1THEN語句1;……

WHEN條件nTHEN語句n;[ELSE

語句n+1;]ENDCASE;對WHEN后面的條件進(jìn)行判斷，條件為真則執(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語句，否則程序進(jìn)入死循環(huán)14.3.2循環(huán)結(jié)構(gòu)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關(guān)鍵字，則表示循環(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輸出什么結(jié)果？numin1..10loopendloop;54練習(xí)：使用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游標(biāo)14.4.1游標(biāo)的概念及類型游標(biāo)的概念及類型執(zhí)行查詢語句和操作語句時，Oracle會在內(nèi)存中開辟一個緩沖區(qū)，用來存儲語句返回的數(shù)據(jù)行集。使用游標(biāo)時，SELECT語句查詢的結(jié)果可以是單條記錄，多條記錄，也可以是零條記錄。游標(biāo)有一個指針，最初指向查詢結(jié)果的首部，隨著游標(biāo)指針的推進(jìn)，就可以訪問相應(yīng)的記錄。56001張紅網(wǎng)絡(luò)1班002宋佳佳網(wǎng)絡(luò)1班003王楚網(wǎng)絡(luò)1班004田宇樂網(wǎng)絡(luò)1班001張紅網(wǎng)絡(luò)1班變量p游標(biāo)mySelect*fromstudent定義變量pmy%rowtype

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

游標(biāo)名

IS

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

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

游標(biāo)名

INTO

變量列表|記錄變量;

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

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

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

my%rowtype；

變量p65（4）關(guān)閉游標(biāo)語法格式

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

a

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

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

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

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

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

________

___--推進(jìn)

dbms_output.put_line(

);closemycus;--關(guān)閉End;CursormycusIsfetch

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

102567202280301340deptnoasal游標(biāo)c171scott.emp表72LOOPFETCHc1INTOv_dept;

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

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

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

Fetch游標(biāo)名Into變量；關(guān)閉游標(biāo)

Close游標(biāo)名；75變量列表或游標(biāo)名%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顯式游標(biāo)的檢索利用簡單循環(huán)檢索游標(biāo)利用WHILE循環(huán)檢索游標(biāo)利用FOR循環(huán)檢索游標(biāo)79（1）利用簡單LOOP循環(huán)檢索游標(biāo)OPEN游標(biāo)名;LOOPFETCH

游標(biāo)名INTO變量名;

EXITWHEN游標(biāo)名%NOTFOUND;……ENDLOOP;EXITWHEN子句應(yīng)該是FETCH…INTO語句的下一條語句。

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

變量名;WHILELOOP……FETCH游標(biāo)名INTO

變量名;……ENDLOOP;在While循環(huán)之前進(jìn)行一次FETCH操作，作為第一次循環(huán)的條件。游標(biāo)名%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游標(biāo)c2c2%FOUND83（3）利用FOR循環(huán)檢索游標(biāo)FOR循環(huán)變量INLOOP

……ENDLOOP;系統(tǒng)自動定義循環(huán)變量，并且其類型為“游標(biāo)名%ROWTYPE”。系統(tǒng)自動打開游標(biāo)，不用OPEN語句；系統(tǒng)自動不斷從游標(biāo)中取數(shù)據(jù)并放入循環(huán)變量系統(tǒng)自動進(jìn)行%FOUND屬性檢查以確定是否有數(shù)據(jù)當(dāng)游標(biāo)中所有的記錄都被提取完畢或循環(huán)中斷時，系統(tǒng)自動地關(guān)閉游標(biāo)。游標(biāo)名例，利用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游標(biāo)c3無需聲明，自動定義游標(biāo)名85可以不在聲明部分定義游標(biāo)，而在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練習(xí)：輸出所有員工的姓名、員工號、工資和部門號，用For循環(huán)實(shí)現(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ù)化顯式游標(biāo)通過參數(shù)的取值來限制游標(biāo)的SQL查詢結(jié)果參數(shù)值不同選取的數(shù)據(jù)行不同，從而能夠動態(tài)使用游標(biāo)

DeclareCursor游標(biāo)名IsSelect語句

BeginOpen游標(biāo)名(參數(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ù)化游標(biāo)時，只能指定參數(shù)的類型，而不能指定參數(shù)的長度、精度、刻度；打開帶參數(shù)的游標(biāo)時，實(shí)參的個數(shù)和數(shù)據(jù)類型等必須與游標(biāo)定義時形參個數(shù)和數(shù)據(jù)類型等相匹配。905利用游標(biāo)更新或刪除數(shù)據(jù)游標(biāo)定義語法CURSOR游標(biāo)名ISSELECT列FROM表名FORUPDATE注意打開游標(biāo)時對相應(yīng)的表加鎖,其他用戶不能對該表進(jìn)行更新操作；若數(shù)據(jù)對象已經(jīng)被其他會話加鎖，則當(dāng)前會話掛起等待，若指定了NOWAIT子句，則不等待，返回ORACLE錯誤。當(dāng)用戶執(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隱式游標(biāo)概念所有的SQL語句都有一個執(zhí)行的緩沖區(qū)，隱式游標(biāo)就是指向該緩沖區(qū)的指針，由系統(tǒng)隱含地打開、處理和關(guān)閉。隱式游標(biāo)又稱為SQL游標(biāo)。隱式游標(biāo)主要用于處理INSERT、UPDATE，DELETE以及單行的SELECT…INTO語句，沒有OPEN，F(xiàn)ETCH，CLOSE等操作命令。9314.5異常處理14.5.1異常概述1.Oracle錯誤處理機(jī)制Oracle中對運(yùn)行時錯誤的處理采用了異常處理機(jī)制。一個錯誤對應(yīng)一個異常，當(dāng)錯誤產(chǎn)生時拋出相應(yīng)的異常，并被異常處理器捕獲，程序控制權(quán)傳遞給異常處理器。942.異常的類型（1）預(yù)定義的Oracle異常（Oracle錯誤）錯誤產(chǎn)生時，與錯誤對應(yīng)的預(yù)定義異常被自動拋出，通過捕獲該異?？梢詫﹀e誤進(jìn)行處理。表14-4（2）非預(yù)定義的Oracle異常（Oracle錯誤）錯誤沒有預(yù)定義異常與其關(guān)聯(lián)，需要在語句塊的聲明部分聲明一個異常，通過編譯指示PRAGMAEXCEPTION_INIT將該異常與錯誤相關(guān)聯(lián)。此后，當(dāng)執(zhí)行過程出現(xiàn)該錯誤時將自動拋出該異常。95（3）用戶自定義的異常有些操作并不會產(chǎn)生Oracle錯誤，但是從業(yè)務(wù)規(guī)則角度考慮認(rèn)為是一種錯誤。用戶自定義異常必須在聲明部分進(jìn)行聲明。當(dāng)異常發(fā)生時，系統(tǒng)不能自動觸發(fā)，需要用戶使用RAISE語句。在EXCEPTION部分捕捉并處理異常。9614.5.2異常處理過程異常處理分3個步驟進(jìn)行：在聲明部分為錯誤定義異常，包括非預(yù)定義異常和用戶定義異常。預(yù)定義異常無需聲明在執(zhí)行部分Begin中當(dāng)錯誤產(chǎn)生時拋出與錯誤對應(yīng)的異常，并跳轉(zhuǎn)到Exception部分。在異常處理Exception部分通過異常處理器捕獲異常，并進(jìn)行異常處理。97預(yù)定義異常的處理例，查詢名為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非預(yù)定義異常的處理例，刪除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種異常，其中預(yù)定義異常由系統(tǒng)定義，而其他兩種異常則需要用戶定義。定義異常方法e_exceptionEXCEPTION;如果是非預(yù)定義的異常，需要將異常與一個Oracle錯誤相關(guān)聯(lián)，其語法為：PRAGMAEXCEPTION_INIT(e_exception,-#####);注意Oracle內(nèi)部錯誤號用一個負(fù)的5位數(shù)表示，如-02292。其中

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

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

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ù)來獲取錯誤相關(guān)信息。SQLCODE：返回當(dāng)前錯誤代碼。如果是用戶定義錯誤返回值為1；如果是ORA-1403：NODATAFOUND錯誤，返回值為100其他Oracle內(nèi)部錯誤返回相應(yīng)的錯誤號。

SQLERRM：返回當(dāng)前錯誤的消息文本。如果是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í)行部分異常的傳播如果當(dāng)前語句塊有該異常的處理器，則執(zhí)行之，并且成功完成該語句塊。然后，控制權(quán)傳遞到外層語句塊。

如果當(dāng)前語句塊沒有該異常的處理器，則通過在外層語句塊中產(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聲明部分和異常處理部分的異常會立刻傳播到外層語句塊的異常處理部分，即使當(dāng)前語句塊有該異常的異常處理器。

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

語法TYPEref_cursor_type_nameISREFCURSOR[RETURNreturn_type]RETURN子句用于指定定義的游標(biāo)類型返回結(jié)果集的類型，該類型必須是記錄類型。如果定義游標(biāo)引用類型時帶有RETURN子句，則用其定義的變量稱為強(qiáng)游標(biāo)變量，否則稱為弱游標(biāo)變量。在Oracle10g中，系統(tǒng)預(yù)定義了一個游標(biāo)引用類型，稱為SYS_REFCURSOR，可以直接使用它定義游標(biāo)變量。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）打開游標(biāo)變量語法OPENcursor_variableFORselect_statement;注意如果打開的游標(biāo)變量是強(qiáng)游標(biāo)變量，則查詢語句的返回類型必須與游標(biāo)引用類型定義中RETURN子句指定的返回類型相匹配。例如OPENv_empFORSELECT*FROMemp;OPENv_generalFORSELECTempno,ename,sal,deptnoFROMemp;OPENmy_cursorFORSELECT*FROMdept;123（3）檢索游標(biāo)變量語法LOOPFETCHcursor_variableINTOvariable1,variable2,…;EXITWHENcursor_variable%NOTFOUND;……ENDLOOP;

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