第十四課Oracle游標(biāo)和異常處理課件

復(fù)習(xí)復(fù)習(xí)1

動(dòng)態(tài)SQL包括本地動(dòng)態(tài)SQL和DBMS_SQL動(dòng)態(tài)SQL兩種實(shí)現(xiàn)方法。

動(dòng)態(tài)SQL簡(jiǎn)介動(dòng)態(tài)SQL包括本地動(dòng)態(tài)SQL和DBMS_SQL動(dòng)態(tài)2為了在動(dòng)態(tài)SQL中處理DDL、DCL、DML以及單行selectinto語句，需要使用executeimmediate語句。Executeimmediate語句為了在動(dòng)態(tài)SQL中處理DDL、DCL、DML以及單行s3語法如下：

executeimmediatedyn_string1、executeimmediate語句處理DDL和DCL語句注：

dyn_string：用于指定存放DDL或DCL文本的字符串變量。語法如下：1、executeimmediate語句處理DD4(1)處理無占位符和returning子句的DML語句語法如下：

executeimmediatedyn_string2、使用executeimmediate語句處理DML語句注：dyn_string：用于指定存放DML文本的字符串變量。(1)處理無占位符和returning子句的DML語句2、使5(2)處理包含占位符的DML語句注：

1、占位符必須以冒號(hào)開始，可以使用任何名稱。

2、bind_argument用于為占位符提供數(shù)據(jù)。語法如下：

executeimmediatedyn_string

usingbind_argument[,……](2)處理包含占位符的DML語句注：1、占位符必須以冒號(hào)開6(3)處理包含returning子句的DML語句注：

1、當(dāng)使用returninginto子句接收數(shù)據(jù)時(shí)，只能接受DML語句返回的單行數(shù)據(jù)。

2、當(dāng)在動(dòng)態(tài)DML語句中指定returninginto子句時(shí)，需要在into后使用占位符。語法如下：

executeimmediatedyn_string

returningintovar1[,……](3)處理包含returning子句的DML語句注：17語法如下：

executeimmediatedyn_string

intovar1,……3、使用executeimmediate語句處理單行語句語法如下：3、使用executeimmediate語句處理8游標(biāo)變量是基于REFCURSOR類型所定義的變量。使用顯示游標(biāo)只能定義靜態(tài)游標(biāo)，而通過使用游標(biāo)變量可以在打開游標(biāo)時(shí)指定游標(biāo)所對(duì)應(yīng)的select語句，從而實(shí)現(xiàn)動(dòng)態(tài)游標(biāo)。游標(biāo)變量游標(biāo)變量是基于REFCURSOR類型所定義的變量。使9游標(biāo)變量包括定義游標(biāo)變量、打開游標(biāo)變量、提取數(shù)據(jù)和關(guān)閉游標(biāo)四個(gè)階段。游標(biāo)變量1、定義REFCURSOR類型和游標(biāo)變量語法如下：

TYPEref_type_nameISREFCURSOR[RETURNreturn_type];cursor_variableref_type_name;游標(biāo)變量包括定義游標(biāo)變量、打開游標(biāo)變量、提取數(shù)據(jù)和關(guān)閉102、打開游標(biāo)變量語法如下：

OPENcursor_variableFORselect……3、提取數(shù)據(jù)語法如下：

FETCHcursor_variableINTOvar1,……4、關(guān)閉游標(biāo)變量語法如下：

CLOSEcursor_variable2、打開游標(biāo)變量語法如下：3、提取數(shù)據(jù)語法如下：4、關(guān)閉游標(biāo)11游標(biāo)NEW游標(biāo)NEW12為什么要使用游標(biāo)？關(guān)系數(shù)據(jù)庫管理系統(tǒng)實(shí)質(zhì)是面向集合的，在Oracle中并沒有一種描述表中單一記錄的表達(dá)形式，除非使用where子句來限制只有一條記錄被選中。游標(biāo)允許應(yīng)用程序?qū)Σ樵冋Z句返回的行結(jié)果集中的每一行進(jìn)行相同或不同的操作，而不是一次對(duì)整個(gè)結(jié)果集進(jìn)行同一種操作。為什么要使用游標(biāo)？關(guān)系數(shù)據(jù)庫管理系統(tǒng)實(shí)質(zhì)是面向集13什么是游標(biāo)？就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。什么是游標(biāo)？就本質(zhì)而言，游標(biāo)實(shí)際上是一14游標(biāo)由結(jié)果集和結(jié)果集中指向特定記錄的游標(biāo)位置組成，游標(biāo)充當(dāng)指針的作用。盡管游標(biāo)能夠遍歷查詢結(jié)果中的所有行，但它一次只能指向一行。游標(biāo)的構(gòu)成游標(biāo)由結(jié)果集和結(jié)果集中指向特定記錄的游標(biāo)位15Oracle中游標(biāo)的分類在Oracle中游標(biāo)可以分為兩種類型：顯式游標(biāo)和隱式游標(biāo)。

顯式游標(biāo)：用于處理select語句返回的多行數(shù)據(jù)。隱式游標(biāo)：用于處理單行selectinto語句和DML語句。兩者區(qū)別（p129）Oracle中游標(biāo)的分類在Oracle中游標(biāo)可以分為兩16顯式游標(biāo)的使用使用顯式游標(biāo)分四個(gè)步驟：

2.打開游標(biāo)

OPENcursor_name1.聲明游標(biāo)

CURSORcursor_nameISselect_statement3.從游標(biāo)中查找信息

FETCHcursor_nameINTOvar1,var2,……4.關(guān)閉游標(biāo)

CLOSEcursor_name顯式游標(biāo)的使用使用顯式游標(biāo)分四個(gè)步驟：2.打開游17例:聲明游標(biāo)，對(duì)應(yīng)的集合是表emp中所有的員工姓名。DECLAREcursorc_emp_enameisselectenamefromscott.emp;顯式游標(biāo)的使用例:聲明游標(biāo)，對(duì)應(yīng)的集合是表emp中所有的員工姓名。DEC18例:顯示游標(biāo)c_emp_ename中的第一行信息。DECLARE

cursorc_emp_enameisselectenamefromscott.emp;v_enamescott.emp.ename%type;BEGIN

openc_emp_ename;

fetchc_emp_enameintov_ename;dbms_output.put_line(v_ename);

closec_emp_ename;END;顯式游標(biāo)的使用思考:如何顯示游標(biāo)c_emp_ename中的所有信息？例:顯示游標(biāo)c_emp_ename中的第一行信息。DECLA19練習(xí):顯示游標(biāo)c_emp_ename中的所有信息。DECLARE

cursorc_emp_enameisselectenamefromscott.emp;v_enameemp.ename%type;v_countbinary_integer;BEGINselectcount(rowid)intov_countfromscott.emp;

openc_emp_ename;

foriin1..v_countloop

fetchc_emp_enameintov_ename;dbms_output.put_line(v_ename);

endloop;

closec_emp_ename;END;顯式游標(biāo)的使用思考：如何獲取指定職工編號(hào)的員工姓名？練習(xí):顯示游標(biāo)c_emp_ename中的所有信息。DECLA20參數(shù)游標(biāo)參數(shù)游標(biāo)是指帶有參數(shù)的游標(biāo)。2.打開游標(biāo)

OPENcursor_name（參數(shù)值）1.聲明參數(shù)游標(biāo)CURSORcursor_name（參數(shù)名參數(shù)數(shù)據(jù)類型，..）

ISselect…from….where……注：1、定義參數(shù)只能指定數(shù)據(jù)類型，不能指定長(zhǎng)度。2、必須在游標(biāo)select語句的where子句中引用游標(biāo)參數(shù)，否則失去了定義參數(shù)游標(biāo)的意義。參數(shù)游標(biāo)參數(shù)游標(biāo)是指帶有參數(shù)的游標(biāo)。2.打開游標(biāo)1.聲21DECLARE

cursorc_emp(v_empnonumber)

is

select*fromscott.empwhereempno=v_empno;v_empscott.emp%rowtype;BEGIN

openc_emp(7369);fetchc_empintov_emp;dbms_output.put_line(v_emp.ename);closec_emp;END;例：使用參數(shù)游標(biāo)獲取7369員工的所有信息。思考：使用游標(biāo)獲取任何編號(hào)員工的所有信息？openc_emp(&number)DECLARE例：使用參數(shù)游標(biāo)獲取7369員工的所有信息。思22

DECLAREcursorc_empisselect*fromscott.empwhereempno=&v_empno;v_empscott.emp%rowtype;BEGINopenc_emp;fetchc_empintov_emp;dbms_output.put_line(v_emp.ename);END;練習(xí)：使用非參數(shù)游標(biāo)（替代變量）獲取任何編號(hào)員工的所有信息。思考：如何處理編號(hào)不存在的情況？DECLARE練習(xí)：使用非參數(shù)游標(biāo)（替代變量）獲取任何編號(hào)23顯式游標(biāo)屬性

當(dāng)使用顯示游標(biāo)時(shí)，需要使用游標(biāo)屬性確定顯示游標(biāo)的執(zhí)行信息，顯示游標(biāo)包括%isopen、%found、%notfound、%rowcount四種屬性。注：當(dāng)引用顯示游標(biāo)屬性時(shí)，需要帶有游標(biāo)名作為前綴（例：emp_cursor%rowcount)顯式游標(biāo)屬性當(dāng)使用顯示游標(biāo)時(shí)，需要使用游標(biāo)24顯式游標(biāo)屬性

%found是一個(gè)布爾屬性。如果前一個(gè)fetch語句返一個(gè)行，那么它返回true，否則返回false。如果在相關(guān)聯(lián)的游標(biāo)還沒有打開進(jìn)行引用，那么會(huì)返回錯(cuò)誤。%notfound的屬性與%found相反。%isopen也是布爾屬性，用來確定相關(guān)的游標(biāo)是否被打開。%rowcount此數(shù)字屬性返回目前為止由游標(biāo)返回行的數(shù)目，即fetch語句后的得到數(shù)字。如果在相關(guān)聯(lián)的游標(biāo)還沒有打開或者已經(jīng)關(guān)閉的時(shí)候進(jìn)行引用，會(huì)返回錯(cuò)誤。顯式游標(biāo)屬性%found是一個(gè)布爾屬性。如果前一個(gè)fetc25

DECLAREcursorc_empisselect*fromscott.empwhereempno=&v_empno;v_empscott.emp%rowtype;BEGINopenc_emp;fetchc_empintov_emp;

IFc_emp%foundthendbms_output.put_line(v_emp.ename);ELSEdbms_output.put_line('您輸入的編號(hào)不存在！');ENDIF;END;例：使用游標(biāo)獲取任何編號(hào)員工的所有信息，對(duì)于不存在的編號(hào)顯示“您輸入的編號(hào)不存在”。DECLARE例：使用游標(biāo)獲取任何編號(hào)員工的所有信息，對(duì)于26練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返回記錄條數(shù)。對(duì)于不存在的編號(hào)顯示“您輸入的編號(hào)不存在”。練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返回記錄27

DECLAREcursorc_e_dept(dnonumber)isselect*fromscott.empwheredeptno=dno;v_emp_deptscott.emp%rowtype;v_dnoemp.deptno%type:=&deno;v_countbinary_integer;BEGINselectcount(rowid)intov_countfromempwheredeptno=v_dno;openc_e_dept(v_dno);

fetchc_e_deptintov_emp_dept;IFc_e_dept%foundthenforiin1..v_countloopdbms_output.put_line(v_emp_dept.ename);fetchc_e_deptintov_emp_dept;endloop;dbms_output.put_line('共有員工人數(shù)：'||c_e_dept%rowcount);ELSEdbms_output.put_line('您輸入的編號(hào)不存在！');ENDIF;closec_e_dept;END;必須先獲取數(shù)據(jù)才能有found的返回值。DECLARE必須先獲取數(shù)據(jù)才能有found的返回值。28

cursorfor循環(huán)forrecord_namein(corsor_name[(參數(shù)1[,參數(shù)2]...)]loopstatementsendloop;

為了簡(jiǎn)化游標(biāo)處理，可以使用游標(biāo)for循環(huán)，當(dāng)使用游標(biāo)for循環(huán)時(shí)，Oracle會(huì)隱含的打開游標(biāo)、提取數(shù)據(jù)并關(guān)閉游標(biāo)。注：

record_name是基于游標(biāo)隱含定義的記錄變量。在執(zhí)行循環(huán)之前，Oracle會(huì)隱含的打開游標(biāo)，并且每循環(huán)一次自動(dòng)提取一行數(shù)據(jù)，當(dāng)所有數(shù)據(jù)提取后自動(dòng)退出循環(huán)并關(guān)閉游標(biāo)。cursorfor循環(huán)forrecord_namei29

例：編寫PL/SQL塊，定義游標(biāo)，使用替代變量輸入部門號(hào)，并以工資降序顯示該部門所有雇員的名稱和工資。DECLARE

cursoremp_cursorisselectename,salfromscott.empwheredeptno=&noorderbysaldesc;BEGIN

foremp_recordinemp_cursorloopdbms_output.put_line('姓名：'||emp_record.ename||',工資：'||emp_record.sal);

endloop;END;例：編寫PL/SQL塊，定義游標(biāo)，使用替代變量輸入部門號(hào)，30練習(xí)：編寫PL/SQL塊，定義參數(shù)游標(biāo)（參數(shù)：崗位），使用替代變量輸入崗位，并顯示該崗位所有雇員的名稱和工資。DECLARE

cursoremp_cursor(titlevarchar2)

isselectename,salfromscott.empwherejob=title;BEGIN

foremp_recordin

emp_cursor('&job')

loopdbms_output.put_line('姓名：'||emp_record.ename||',工資：'||emp_record.sal);

endloop;END;練習(xí)：編寫PL/SQL塊，定義參數(shù)游標(biāo)（參數(shù)：崗位），使用替31分析：DECLAREcursorc_e_dept(dnonumber)isselect*fromscott.empwheredeptno=dno;BEGINforemp_recordinc_e_dept(&dno)loopdbms_output.put_line(emp_record.ename);IFc_e_dept%rowcount>0thendbms_output.put_line('共有員工人數(shù)：'||c_e_dept%rowcount);ELSEdbms_output.put_line('您輸入的編號(hào)不存在！');ENDIF;endloop;END;練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返回記錄條數(shù)。對(duì)于不存在的編號(hào)顯示“您輸入的編號(hào)不存在”。分析：練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返32更新或刪除游標(biāo)行通過使用顯式游標(biāo)，不僅可以取得游標(biāo)結(jié)果集的數(shù)據(jù)，而且可以更新或刪除游標(biāo)結(jié)果集的當(dāng)前行。DELETEFROMtable_nameWHERECURRENTOFcursor_name；CURSORcursor_nameISselect_statementFORUPDATE;UPDATEtable_nameSET……..WHERECURRENTOFcursor_name;更新或刪除游標(biāo)行通過使用顯式游標(biāo)，不僅可以取得游標(biāo)結(jié)果集的數(shù)33例:要求輸入部門號(hào)，輸出該部門所有雇員姓名及原工資，并將雇員的工資提高10%。DECLARECURSORemp_cursorISSELECTename,sal,deptnoFROMscott.empWHEREdeptno=&noFORUPDATE;BEGINFORemp_recordINemp_cursorLOOP dbms_output.put_line('姓名:'||emp_record.ename ||',原工資:'||emp_record.sal);

UPDATEscott.empSETsal=sal*1.1WHERECURRENTOFemp_cursor;ENDLOOP;END;例:要求輸入部門號(hào)，輸出該部門所有雇員姓名及原工資，并將雇員34練習(xí):要求輸入雇員號(hào)，輸出該雇員姓名及原工資，并將雇員刪除。

DECLARECURSORemp_cursorISSELECTename,salFROMscott.empWHEREempno=&noFORupdate;BEGINFORemp_recordINemp_cursorLOOPdbms_output.put_line('姓名:'||emp_record.ename||',原工資:'||emp_record.sal);DELETEscott.empWHERECURRENTOFemp_cursor;ENDLOOP;END;練習(xí):要求輸入雇員號(hào)，輸出該雇員姓名及原工資，并將雇員刪除。35隱式游標(biāo)屬性

隱式游標(biāo)也稱SQL游標(biāo)，用于處理單行selectinto語句和DML語句。一個(gè)SQL游標(biāo)不能使用open、fetch、close來操作。與顯式游標(biāo)相同，SQL游標(biāo)也有%isopen、%found、%notfound、%rowcount四種屬性。具體看書。注：當(dāng)引用隱式游標(biāo)屬性時(shí)，需要帶有SQL作為前綴（例：SQL%rowcount)隱式游標(biāo)屬性隱式游標(biāo)也稱SQL游標(biāo)，用于處理單行selec36書例6.11declaretypeename_typeistableofemp.ename%typenotnullindexbybinary_integer;typedname_typeistableofdept.dname%typeindexbybinary_integer;enamelistename_type;dnamelistdname_type;subscriptbinary_integer:=1;beginforvarin(selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno)loopenamelist(subscript):=var.ename;dnamelist(subscript):=var.dname;dbms_output.put_line('employeenameis'||enamelist(subscript)||',departmentnameis'||dnamelist(subscript));subscript:=subscript+1;endloop;dbms_output.put_line('totalnumberofemployeesare'||subscript);end;/cursorfor循環(huán)書例6.11declarecursorfor循環(huán)37書例6.12SQL%found屬性declarev_empnoemp.empno%type:=&v_empno;v_empemp%rowtype;beginselect*intov_empfromempwhereempno=v_empno;ifSQL%foundthendbms_output.put_line(v_emp.empno);dbms_output.put_line(v_emp.ename);endif;end;/書例6.12SQL%found屬性declare38書例6.13SQL%notfound屬性declarev_empnoemp.empno%type:=7000;beginupdateemp1setename='sem108'whereempno=v_empno;ifSQL%foundthendbms_output.put_line('DeletedOK!');endif;ifSQL%notfoundthendbms_output.put_line('employeenumber:'||v_empno||'doesnotexists!');endif;end;/書例6.13SQL%notfound屬性declare39書例6.14SQL%isopen屬性SQL%rowcount屬性declarev_deptnoemp.deptno%type:=10;begindeleteemp1wheredeptno=v_deptno;ifSQL%foundthendbms_output.put_line(SQL%rowcount||'deletedOK!');endif;ifSQL%notfoundthendbms_output.put_line('deptnonumber:'||v_deptno||'doesnotexists!');endif;end;/書例6.14SQL%isopen屬性declare40練習(xí)：1、當(dāng)使用顯示游標(biāo)時(shí)，在執(zhí)行了哪條語句后應(yīng)該檢查游標(biāo)是否包含行？

A、openB、fetchC、closeD、cursor2、在以下哪些語句中可以包含wherecurrentof子句？

A、openB、fetchC、delete

D、selectE、updateF、cursor√√√練習(xí)：1、當(dāng)使用顯示游標(biāo)時(shí)，在執(zhí)行了哪條語句后應(yīng)該檢查游標(biāo)是41練習(xí)：3、請(qǐng)查看以下游標(biāo)定義語句，哪行會(huì)引起錯(cuò)誤？DECLARECURSORcust_cursor(p_cust_id,p_last_name)ISSELECTcust_id,first_name,last_nameFROMcustomer

WHEREcust_id=p_cust_idANDlast_name=p_last_name;

定義游標(biāo)變量需指明變量的數(shù)據(jù)類型A、2B、3C、4D、5E、64、課后習(xí)題第二、三題（課本149頁）√練習(xí)：3、請(qǐng)查看以下游標(biāo)定義語句，哪行會(huì)引起錯(cuò)誤？4、課后習(xí)42小結(jié)游標(biāo)概念游標(biāo)分類顯示游標(biāo)的使用參數(shù)游標(biāo)游標(biāo)屬性Cursorfor循環(huán)更新、刪除游標(biāo)行隱式游標(biāo)的使用小結(jié)游標(biāo)概念43異常處理異常處理44

為了提高應(yīng)用程序的健壯性，使得應(yīng)用程序可以安全正常的運(yùn)行，應(yīng)用開發(fā)人員應(yīng)該考慮到PL/SQL塊可能出現(xiàn)的各種異常情況，并進(jìn)行相應(yīng)的處理。通常使用異常（Exception），可以處理PL/SQL塊的各種異常情況。引言為了提高應(yīng)用程序的健壯性，使得應(yīng)用程序可以安全正常45

異常（Exception）是一種PL/SQL標(biāo)識(shí)符，它包括預(yù)定義異常、非預(yù)定義異常、自定義異常。異常簡(jiǎn)介

當(dāng)編寫PL/SQL塊時(shí)，應(yīng)該捕捉并處理各種可能出現(xiàn)的異常。如果不捕捉和處理異常，那么Oracle會(huì)將錯(cuò)誤傳遞到調(diào)用環(huán)境；如果捕捉并處理異常，那么Oracle會(huì)在PL/SQL塊內(nèi)解決運(yùn)行錯(cuò)誤。異常（Exception）是一種PL/46DECLAREv_enameemp.ename%TYPE;BEGINSELECTenameINTOv_enameFROMscott.empWHEREempno=&no;dbms_output.put_line('雇員名:'||v_ename);END;未捕捉異常，傳遞錯(cuò)誤到調(diào)用環(huán)境DECLARE未捕捉異常，傳遞錯(cuò)誤到調(diào)用環(huán)境47DECLAREv_enameemp.ename%TYPE;BEGINSELECTenameINTOv_enameFROMempWHEREempno=&no;dbms_output.put_line('雇員名:'||v_ename);EXCEPTIONWHENNO_DATA_FOUNDTHENdbms_output.put_line('雇員號(hào)不正確，請(qǐng)核實(shí)雇員號(hào)！');END;捕捉異常并處理DECLARE捕捉異常并處理48異常處理部分的語法如下：

BEGIN……EXCEPTION

whenexception_namethen

codeforhandingexception_name

[whenanother_exceptionthen

codeforhandinganother_exception]

[whenothersthen

codeforhandinganyotherexception.]END;異常處理部分的語法如下：BEGIN49預(yù)定義異常是指PL/SQL所提供的系統(tǒng)異常。Oracle為應(yīng)用開發(fā)人員提供了21個(gè)預(yù)定義異常，每個(gè)預(yù)定義異常對(duì)應(yīng)一個(gè)特定的Oracle錯(cuò)誤，當(dāng)PL/SQL塊出現(xiàn)這些Oracle錯(cuò)誤時(shí)，會(huì)隱含的處罰相應(yīng)的預(yù)定義異常。一、預(yù)定義異常應(yīng)用預(yù)定義異常預(yù)定義異常是指PL/SQL所提供的系50

非預(yù)定義異常用于處理與預(yù)定義異常無關(guān)的Oracle錯(cuò)誤。預(yù)定義異常只能用于處理21種Oracle錯(cuò)誤，而PL/SQL塊還會(huì)遭遇其他Oracle錯(cuò)誤（例如：完整性約束ORA-02291）二、非預(yù)定義異常例：beginupdatescott.empsetdeptno=&nowhereempno=&eno;end;非預(yù)定義異常用于處理與預(yù)定義異常無關(guān)的Oracle錯(cuò)511.在declare部分定義異常標(biāo)識(shí)符。2.在declare部分建立Oracle錯(cuò)誤號(hào)與異常之間的關(guān)聯(lián)。3.捕捉并處理異常。使用非預(yù)定義異常的步驟：語法如下：declareexception_nameexception;

pragma

exception_init(exception_name,-2291);…………….1.在declare部分定義異常標(biāo)識(shí)符。使用非預(yù)定義異常的步52例如：DECLARE

e_integrityEXCEPTION;PRAGMAEXCEPTION_INIT(e_integrity,-2291);namescott.emp.ename%TYPE:=LOWER('&name');dnoscott.emp.deptno%TYPE:=&dno;BEGINUPDATEscott.empSETdeptno=dnoWHERELOWER(ename)=name;EXCEPTIONWHENe_integrityTHENdbms_output.put_line('該部門不存在');END;例如：53

為了在PL/SQL塊處理Oracle錯(cuò)誤，應(yīng)用開發(fā)人員可以使用預(yù)定義異?；蛘叻穷A(yù)定義異常。

注：當(dāng)執(zhí)行內(nèi)嵌update和delete語句時(shí)，如果沒有更新或刪除行，那么PL/SQL塊不會(huì)給出任何提示信息，為了使得用戶可以獲得更有用，更有意義的消息，需要使用自定義異常。三、自定義異常為了在PL/SQL塊處理Oracle錯(cuò)誤，應(yīng)用開發(fā)人541.在declare部分定義異常標(biāo)識(shí)符。2.在執(zhí)行部分使用raise語句顯示觸發(fā)異常。3.捕捉并處理異常。使用自定義異常步驟：語法：declare

exception_nameexception;begin…………….

raiseexception_name；

exception……..end；1.在declare部分定義異常標(biāo)識(shí)符。使用自定義異常步驟：55例如：DECLARE

e_integrityEXCEPTION; e_no_rowsEXCEPTION; PRAGMAEXCEPTION_INIT(e_integrity,-2291); namescott.emp.ename%TYPE:=LOWER('&name'); dnoscott.emp.deptno%TYPE:=&dno;BEGIN UPDATEscott.empSETdeptno=dnoWHERELOWER(ename)=name;

IFSQL%NOTFOUNDTHEN RAISEe_no_rows; ENDIF;EXCEPTION WHENe_integrityTHEN dbms_output.put_line('該部門不存在'); WHENe_no_rowsTHEN dbms_output.put_line('該雇員不存在');END;例如：56練習(xí)：1、RAISE語句應(yīng)該放在PL/SQL塊的哪個(gè)部分？

A、定義部分B、執(zhí)行部分C、異常處理部分2、當(dāng)在異常和Oracle錯(cuò)誤之間建立關(guān)聯(lián)時(shí)，應(yīng)該在哪個(gè)部分完成？A、定義部分B、執(zhí)行部分C、異常處理部分3、假定在emp表上定義了check約束要求雇員的工資不能高于6000，為了處理工資超過6000可能出現(xiàn)的錯(cuò)誤，應(yīng)該使用哪種異常？A、預(yù)定義異常B、非預(yù)定義異常C、自定義異?！獭獭叹毩?xí)：1、RAISE語句應(yīng)該放在PL/SQL塊的哪個(gè)部分？257練習(xí)：4、編寫PL/SQL塊，使用替代變量輸入雇員號(hào)，顯示雇員名及其工資。如果雇員不存在，則顯示“請(qǐng)輸入正確的雇員號(hào)”5、編寫PL/SQL塊，使用替代變量輸入雇員號(hào)和新部門號(hào)，更新雇員部門，如果部門不存在，則顯示“請(qǐng)輸入正確的部門號(hào)！”；如果雇員不存在，則顯示“請(qǐng)輸入正確的雇員號(hào)”練習(xí)：4、編寫PL/SQL塊，使用替代變量輸入雇員號(hào)，顯示雇58

在一個(gè)異常中，SQLCODE返回Oracle錯(cuò)誤的序號(hào)，而SQLERRM返回的是相應(yīng)的錯(cuò)誤消息，錯(cuò)誤消息首先顯示是錯(cuò)誤代碼。SQLCODE和SQLERRM注：1.SQLCODE通常返回的是負(fù)數(shù)。對(duì)于用戶自定義的異常，SQLCODE返回的是+1，而SQLERRM返回的是自定義錯(cuò)誤信息。2.如果沒有異常被觸發(fā)，則SQLCODE返回0，SQLERRM返回ORA-0000:normal,successfulcompletion。在一個(gè)異常中，SQLCODE返回Oracle錯(cuò)誤的序號(hào)，59declarev_empscott.emp%rowtype;empno_out_of_rangeexception;beginselect*intov_empfromscott.emp;ifSQL%foundthendbms_output.put_line(v_emp.empno);dbms_output.put_line(v_emp.ename);endif;exceptionwhenempno_out_of_rangethendbms_output.put_line('empnoisoutofrange');whenno_data_foundthendbms_output.put_line('notfoundrecord');whentoo_many_rowsthendbms_output.put_line('toomanyrecord');

dbms_output.put_line(SQLCode||','||SQLERRM);end;/declare60復(fù)習(xí)復(fù)習(xí)61

動(dòng)態(tài)SQL包括本地動(dòng)態(tài)SQL和DBMS_SQL動(dòng)態(tài)SQL兩種實(shí)現(xiàn)方法。

動(dòng)態(tài)SQL簡(jiǎn)介動(dòng)態(tài)SQL包括本地動(dòng)態(tài)SQL和DBMS_SQL動(dòng)態(tài)62為了在動(dòng)態(tài)SQL中處理DDL、DCL、DML以及單行selectinto語句，需要使用executeimmediate語句。Executeimmediate語句為了在動(dòng)態(tài)SQL中處理DDL、DCL、DML以及單行s63語法如下：

executeimmediatedyn_string1、executeimmediate語句處理DDL和DCL語句注：

dyn_string：用于指定存放DDL或DCL文本的字符串變量。語法如下：1、executeimmediate語句處理DD64(1)處理無占位符和returning子句的DML語句語法如下：

executeimmediatedyn_string2、使用executeimmediate語句處理DML語句注：dyn_string：用于指定存放DML文本的字符串變量。(1)處理無占位符和returning子句的DML語句2、使65(2)處理包含占位符的DML語句注：

1、占位符必須以冒號(hào)開始，可以使用任何名稱。

2、bind_argument用于為占位符提供數(shù)據(jù)。語法如下：

executeimmediatedyn_string

usingbind_argument[,……](2)處理包含占位符的DML語句注：1、占位符必須以冒號(hào)開66(3)處理包含returning子句的DML語句注：

1、當(dāng)使用returninginto子句接收數(shù)據(jù)時(shí)，只能接受DML語句返回的單行數(shù)據(jù)。

2、當(dāng)在動(dòng)態(tài)DML語句中指定returninginto子句時(shí)，需要在into后使用占位符。語法如下：

executeimmediatedyn_string

returningintovar1[,……](3)處理包含returning子句的DML語句注：167語法如下：

executeimmediatedyn_string

intovar1,……3、使用executeimmediate語句處理單行語句語法如下：3、使用executeimmediate語句處理68游標(biāo)變量是基于REFCURSOR類型所定義的變量。使用顯示游標(biāo)只能定義靜態(tài)游標(biāo)，而通過使用游標(biāo)變量可以在打開游標(biāo)時(shí)指定游標(biāo)所對(duì)應(yīng)的select語句，從而實(shí)現(xiàn)動(dòng)態(tài)游標(biāo)。游標(biāo)變量游標(biāo)變量是基于REFCURSOR類型所定義的變量。使69游標(biāo)變量包括定義游標(biāo)變量、打開游標(biāo)變量、提取數(shù)據(jù)和關(guān)閉游標(biāo)四個(gè)階段。游標(biāo)變量1、定義REFCURSOR類型和游標(biāo)變量語法如下：

TYPEref_type_nameISREFCURSOR[RETURNreturn_type];cursor_variableref_type_name;游標(biāo)變量包括定義游標(biāo)變量、打開游標(biāo)變量、提取數(shù)據(jù)和關(guān)閉702、打開游標(biāo)變量語法如下：

OPENcursor_variableFORselect……3、提取數(shù)據(jù)語法如下：

FETCHcursor_variableINTOvar1,……4、關(guān)閉游標(biāo)變量語法如下：

CLOSEcursor_variable2、打開游標(biāo)變量語法如下：3、提取數(shù)據(jù)語法如下：4、關(guān)閉游標(biāo)71游標(biāo)NEW游標(biāo)NEW72為什么要使用游標(biāo)？關(guān)系數(shù)據(jù)庫管理系統(tǒng)實(shí)質(zhì)是面向集合的，在Oracle中并沒有一種描述表中單一記錄的表達(dá)形式，除非使用where子句來限制只有一條記錄被選中。游標(biāo)允許應(yīng)用程序?qū)Σ樵冋Z句返回的行結(jié)果集中的每一行進(jìn)行相同或不同的操作，而不是一次對(duì)整個(gè)結(jié)果集進(jìn)行同一種操作。為什么要使用游標(biāo)？關(guān)系數(shù)據(jù)庫管理系統(tǒng)實(shí)質(zhì)是面向集73什么是游標(biāo)？就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。什么是游標(biāo)？就本質(zhì)而言，游標(biāo)實(shí)際上是一74游標(biāo)由結(jié)果集和結(jié)果集中指向特定記錄的游標(biāo)位置組成，游標(biāo)充當(dāng)指針的作用。盡管游標(biāo)能夠遍歷查詢結(jié)果中的所有行，但它一次只能指向一行。游標(biāo)的構(gòu)成游標(biāo)由結(jié)果集和結(jié)果集中指向特定記錄的游標(biāo)位75Oracle中游標(biāo)的分類在Oracle中游標(biāo)可以分為兩種類型：顯式游標(biāo)和隱式游標(biāo)。

顯式游標(biāo)：用于處理select語句返回的多行數(shù)據(jù)。隱式游標(biāo)：用于處理單行selectinto語句和DML語句。兩者區(qū)別（p129）Oracle中游標(biāo)的分類在Oracle中游標(biāo)可以分為兩76顯式游標(biāo)的使用使用顯式游標(biāo)分四個(gè)步驟：

2.打開游標(biāo)

OPENcursor_name1.聲明游標(biāo)

CURSORcursor_nameISselect_statement3.從游標(biāo)中查找信息

FETCHcursor_nameINTOvar1,var2,……4.關(guān)閉游標(biāo)

CLOSEcursor_name顯式游標(biāo)的使用使用顯式游標(biāo)分四個(gè)步驟：2.打開游77例:聲明游標(biāo)，對(duì)應(yīng)的集合是表emp中所有的員工姓名。DECLAREcursorc_emp_enameisselectenamefromscott.emp;顯式游標(biāo)的使用例:聲明游標(biāo)，對(duì)應(yīng)的集合是表emp中所有的員工姓名。DEC78例:顯示游標(biāo)c_emp_ename中的第一行信息。DECLARE

cursorc_emp_enameisselectenamefromscott.emp;v_enamescott.emp.ename%type;BEGIN

openc_emp_ename;

fetchc_emp_enameintov_ename;dbms_output.put_line(v_ename);

closec_emp_ename;END;顯式游標(biāo)的使用思考:如何顯示游標(biāo)c_emp_ename中的所有信息？例:顯示游標(biāo)c_emp_ename中的第一行信息。DECLA79練習(xí):顯示游標(biāo)c_emp_ename中的所有信息。DECLARE

cursorc_emp_enameisselectenamefromscott.emp;v_enameemp.ename%type;v_countbinary_integer;BEGINselectcount(rowid)intov_countfromscott.emp;

openc_emp_ename;

foriin1..v_countloop

fetchc_emp_enameintov_ename;dbms_output.put_line(v_ename);

endloop;

closec_emp_ename;END;顯式游標(biāo)的使用思考：如何獲取指定職工編號(hào)的員工姓名？練習(xí):顯示游標(biāo)c_emp_ename中的所有信息。DECLA80參數(shù)游標(biāo)參數(shù)游標(biāo)是指帶有參數(shù)的游標(biāo)。2.打開游標(biāo)

OPENcursor_name（參數(shù)值）1.聲明參數(shù)游標(biāo)CURSORcursor_name（參數(shù)名參數(shù)數(shù)據(jù)類型，..）

ISselect…from….where……注：1、定義參數(shù)只能指定數(shù)據(jù)類型，不能指定長(zhǎng)度。2、必須在游標(biāo)select語句的where子句中引用游標(biāo)參數(shù)，否則失去了定義參數(shù)游標(biāo)的意義。參數(shù)游標(biāo)參數(shù)游標(biāo)是指帶有參數(shù)的游標(biāo)。2.打開游標(biāo)1.聲81DECLARE

cursorc_emp(v_empnonumber)

is

select*fromscott.empwhereempno=v_empno;v_empscott.emp%rowtype;BEGIN

openc_emp(7369);fetchc_empintov_emp;dbms_output.put_line(v_emp.ename);closec_emp;END;例：使用參數(shù)游標(biāo)獲取7369員工的所有信息。思考：使用游標(biāo)獲取任何編號(hào)員工的所有信息？openc_emp(&number)DECLARE例：使用參數(shù)游標(biāo)獲取7369員工的所有信息。思82

DECLAREcursorc_empisselect*fromscott.empwhereempno=&v_empno;v_empscott.emp%rowtype;BEGINopenc_emp;fetchc_empintov_emp;dbms_output.put_line(v_emp.ename);END;練習(xí)：使用非參數(shù)游標(biāo)（替代變量）獲取任何編號(hào)員工的所有信息。思考：如何處理編號(hào)不存在的情況？DECLARE練習(xí)：使用非參數(shù)游標(biāo)（替代變量）獲取任何編號(hào)83顯式游標(biāo)屬性

當(dāng)使用顯示游標(biāo)時(shí)，需要使用游標(biāo)屬性確定顯示游標(biāo)的執(zhí)行信息，顯示游標(biāo)包括%isopen、%found、%notfound、%rowcount四種屬性。注：當(dāng)引用顯示游標(biāo)屬性時(shí)，需要帶有游標(biāo)名作為前綴（例：emp_cursor%rowcount)顯式游標(biāo)屬性當(dāng)使用顯示游標(biāo)時(shí)，需要使用游標(biāo)84顯式游標(biāo)屬性

%found是一個(gè)布爾屬性。如果前一個(gè)fetch語句返一個(gè)行，那么它返回true，否則返回false。如果在相關(guān)聯(lián)的游標(biāo)還沒有打開進(jìn)行引用，那么會(huì)返回錯(cuò)誤。%notfound的屬性與%found相反。%isopen也是布爾屬性，用來確定相關(guān)的游標(biāo)是否被打開。%rowcount此數(shù)字屬性返回目前為止由游標(biāo)返回行的數(shù)目，即fetch語句后的得到數(shù)字。如果在相關(guān)聯(lián)的游標(biāo)還沒有打開或者已經(jīng)關(guān)閉的時(shí)候進(jìn)行引用，會(huì)返回錯(cuò)誤。顯式游標(biāo)屬性%found是一個(gè)布爾屬性。如果前一個(gè)fetc85

DECLAREcursorc_empisselect*fromscott.empwhereempno=&v_empno;v_empscott.emp%rowtype;BEGINopenc_emp;fetchc_empintov_emp;

IFc_emp%foundthendbms_output.put_line(v_emp.ename);ELSEdbms_output.put_line('您輸入的編號(hào)不存在！');ENDIF;END;例：使用游標(biāo)獲取任何編號(hào)員工的所有信息，對(duì)于不存在的編號(hào)顯示“您輸入的編號(hào)不存在”。DECLARE例：使用游標(biāo)獲取任何編號(hào)員工的所有信息，對(duì)于86練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返回記錄條數(shù)。對(duì)于不存在的編號(hào)顯示“您輸入的編號(hào)不存在”。練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返回記錄87

DECLAREcursorc_e_dept(dnonumber)isselect*fromscott.empwheredeptno=dno;v_emp_deptscott.emp%rowtype;v_dnoemp.deptno%type:=&deno;v_countbinary_integer;BEGINselectcount(rowid)intov_countfromempwheredeptno=v_dno;openc_e_dept(v_dno);

fetchc_e_deptintov_emp_dept;IFc_e_dept%foundthenforiin1..v_countloopdbms_output.put_line(v_emp_dept.ename);fetchc_e_deptintov_emp_dept;endloop;dbms_output.put_line('共有員工人數(shù)：'||c_e_dept%rowcount);ELSEdbms_output.put_line('您輸入的編號(hào)不存在！');ENDIF;closec_e_dept;END;必須先獲取數(shù)據(jù)才能有found的返回值。DECLARE必須先獲取數(shù)據(jù)才能有found的返回值。88

cursorfor循環(huán)forrecord_namein(corsor_name[(參數(shù)1[,參數(shù)2]...)]loopstatementsendloop;

為了簡(jiǎn)化游標(biāo)處理，可以使用游標(biāo)for循環(huán)，當(dāng)使用游標(biāo)for循環(huán)時(shí)，Oracle會(huì)隱含的打開游標(biāo)、提取數(shù)據(jù)并關(guān)閉游標(biāo)。注：

record_name是基于游標(biāo)隱含定義的記錄變量。在執(zhí)行循環(huán)之前，Oracle會(huì)隱含的打開游標(biāo)，并且每循環(huán)一次自動(dòng)提取一行數(shù)據(jù)，當(dāng)所有數(shù)據(jù)提取后自動(dòng)退出循環(huán)并關(guān)閉游標(biāo)。cursorfor循環(huán)forrecord_namei89

例：編寫PL/SQL塊，定義游標(biāo)，使用替代變量輸入部門號(hào)，并以工資降序顯示該部門所有雇員的名稱和工資。DECLARE

cursoremp_cursorisselectename,salfromscott.empwheredeptno=&noorderbysaldesc;BEGIN

foremp_recordinemp_cursorloopdbms_output.put_line('姓名：'||emp_record.ename||',工資：'||emp_record.sal);

endloop;END;例：編寫PL/SQL塊，定義游標(biāo)，使用替代變量輸入部門號(hào)，90練習(xí)：編寫PL/SQL塊，定義參數(shù)游標(biāo)（參數(shù)：崗位），使用替代變量輸入崗位，并顯示該崗位所有雇員的名稱和工資。DECLARE

cursoremp_cursor(titlevarchar2)

isselectename,salfromscott.empwherejob=title;BEGIN

foremp_recordin

emp_cursor('&job')

loopdbms_output.put_line('姓名：'||emp_record.ename||',工資：'||emp_record.sal);

endloop;END;練習(xí)：編寫PL/SQL塊，定義參數(shù)游標(biāo)（參數(shù)：崗位），使用替91分析：DECLAREcursorc_e_dept(dnonumber)isselect*fromscott.empwheredeptno=dno;BEGINforemp_recordinc_e_dept(&dno)loopdbms_output.put_line(emp_record.ename);IFc_e_dept%rowcount>0thendbms_output.put_line('共有員工人數(shù)：'||c_e_dept%rowcount);ELSEdbms_output.put_line('您輸入的編號(hào)不存在！');ENDIF;endloop;END;練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返回記錄條數(shù)。對(duì)于不存在的編號(hào)顯示“您輸入的編號(hào)不存在”。分析：練習(xí)：使用參數(shù)游標(biāo)獲取任何部門編號(hào)的員工姓名信息，并返92更新或刪除游標(biāo)行通過使用顯式游標(biāo)，不僅可以取得游標(biāo)結(jié)果集的數(shù)據(jù)，而且可以更新或刪除游標(biāo)結(jié)果集的當(dāng)前行。DELETEFROMtable_nameWHERECURRENTOFcursor_name；CURSORcursor_nameISselect_statementFORUPDATE;UPDATEtable_nameSET……..WHERECURRENTOFcursor_name;更新或刪除游標(biāo)行通過使用顯式游標(biāo)，不僅可以取得游標(biāo)結(jié)果集的數(shù)93例:要求輸入部門號(hào)，輸出該部門所有雇員姓名及原工資，并將雇員的工資提高10%。DECLARECURSORemp_cursorISSELECTename,sal,deptnoFROMscott.empWHEREdeptno=&noFORUPDATE;BEGINFORemp_recordINemp_cursorLOOP dbms_output.put_line('姓名:'||emp_record.ename ||',原工資:'||emp_record.sal);

UPDATEscott.empSETsal=sal*1.1WHERECURRENTOFemp_cursor;ENDLOOP;END;例:要求輸入部門號(hào)，輸出該部門所有雇員姓名及原工資，并將雇員94練習(xí):要求輸入雇員號(hào)，輸出該雇員姓名及原工資，并將雇員刪除。

DECLARECURSORemp_cursorISSELECTename,salFROMscott.empWHEREempno=&noFORupdate;BEGINFORemp_recordINemp_cursorLOOPdbms_output.put_line('姓名:'||emp_record.ename||',原工資:'||emp_record.sal);DELETEscott.empWHERECURRENTOFemp_cursor;ENDLOOP;END;練習(xí):要求輸入雇員號(hào)，輸出該雇員姓名及原工資，并將雇員刪除。95隱式游標(biāo)屬性

隱式游標(biāo)也稱SQL游標(biāo)，用于處理單行selectinto語句和DML語句。一個(gè)SQL游標(biāo)不能使用open、fetch、close來操作。與顯式游標(biāo)相同，SQL游標(biāo)也有%isopen、%found、%notfound、%rowcount四種屬性。具體看書。注：當(dāng)引用隱式游標(biāo)屬性時(shí)，需要帶有SQL作為前綴（例：SQL%rowcount)隱式游標(biāo)屬性隱式游標(biāo)也稱SQL游標(biāo)，用于處理單行selec96書例6.11declaretypeename_typeistableofemp.ename%typenotnullindexbybinary_integer;typedname_typeistableofdept.dname%typeindexbybinary_integer;enamelistename_type;dnamelistdname_type;subscriptbinary_integer:=1;beginforvarin(selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno)loopenamelist(subscript):=var.ename;dnamelist(subscript):=var.dname;dbms_output.put_line('employeenameis'||enamelist(subscript)||',departmentnameis'||dnamelist(subscript));subscript:=subscript+1;endloop;dbms_output.put_line('totalnumberofemployeesare'||subscript);end;/cursorfor循環(huán)書例6.11declarecursorfor循環(huán)97書例6.12SQL%found屬性declarev_empnoemp.empno%type:=&v_empno;v_empemp%rowtype;beginselect*intov_empfromempwhereempno=v_empno;ifSQL%foundthendbms_output.put_line(v_emp.empno);dbms_output.put_line(v_emp.ename);endif;end;/書例6.12SQL%found屬性declare98書例6.13SQL%notfound屬性declarev_empnoemp.empno%type:=7000;beginupdateemp1setename='sem108'whereempno=v_empno;ifSQL%foundthendbms_output.put_line('DeletedOK!');endif;ifSQL%notfoundthendbms_output.put_line('employeenumber:'||v_empno||'doesnotexists!');endif;end;/書例6.13SQL%notfound屬性declare99書例6.14SQL%isopen屬性SQL%rowcount屬性declarev_deptnoemp.deptno%type:=10;begindeleteemp1wheredeptno=v_deptno;ifSQL%foundthendbms_output.put_line(SQL%rowcount||'deletedOK!');endif;ifSQL%notfoundthendbms_output.put_line('deptnonumber:'||v_deptno||'doesnotexists!');endif;end;/書例6.14SQL%isopen屬性declare100練習(xí)：1、當(dāng)使用顯示游標(biāo)時(shí)，在執(zhí)行了哪條語句后應(yīng)該檢查游標(biāo)是否包含行？

A、open