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

復(fù)習(xí)動態(tài)SQL包括本地動態(tài)態(tài)SQL和DBMS_SQL動態(tài)SQL兩種實現(xiàn)現(xiàn)方法。。動態(tài)SQL簡介為了在動動態(tài)SQL中處理DDL、DCL、DML以及單行行selectinto語句，需需要使用用executeimmediate語句。Executeimmediate語句語法如下下：executeimmediatedyn__string1、executeimmediate語句處理理DDL和DCL語句注：dyn__string：用于指指定存放放DDL或DCL文本的字字符串變變量。(1)處理無占占位符和和returning子句的DML語句語法如下下：executeimmediatedyn__string2、使用executeimmediate語句處理理DML語句注：dyn__string：用于指指定存放放DML文本的字字符串變變量。(2)處理包含含占位符符的DML語句注：1、占位符符必須以以冒號開始，可可以使用用任何名名稱。2、bind_argument用于為占占位符提提供數(shù)據(jù)據(jù)。語法如下下：executeimmediatedyn__stringusingbind_argument[,………](3)處理包含含returning子句的DML語句注：1、當(dāng)使用用returninginto子句接收收數(shù)據(jù)時時，只能能接受DML語句返回回的單行行數(shù)據(jù)。。2、當(dāng)在動動態(tài)DML語句中指指定returninginto子句時，，需要在在into后使用占占位符。。語法如下下：executeimmediatedyn__stringreturningintovar1[,………]語法如下下：executeimmediatedyn__stringintovar1,………3、使用executeimmediate語句處理理單行語語句游標(biāo)變量量是基于于REFCURSOR類型所定定義的變變量。使使用顯示示游標(biāo)只只能定義義靜態(tài)游游標(biāo)，而而通過使使用游標(biāo)標(biāo)變量可可以在打打開游標(biāo)標(biāo)時指定定游標(biāo)所所對應(yīng)的的select語句，從從而實現(xiàn)現(xiàn)動態(tài)游游標(biāo)。游標(biāo)變量量游標(biāo)變量量包括定義游標(biāo)標(biāo)變量、打開游標(biāo)標(biāo)變量、提取數(shù)據(jù)據(jù)和關(guān)閉游標(biāo)標(biāo)四個階段段。游標(biāo)變量量1、定義REFCURSOR類型和游游標(biāo)變量量語法如下下：TYPEref__type_nameISREFCURSOR[RETURNreturn_type]];cursor__variableref_type_name;;2、打開游游標(biāo)變量量語法如下下：OPENcursor__variableFORselect………3、提取數(shù)數(shù)據(jù)語法如下下：FETCHcursor_variableINTOvar1,………4、關(guān)閉游游標(biāo)變量量語法如下下：CLOSEcursor__variable游標(biāo)標(biāo)NEW為什么要要使用游游標(biāo)？關(guān)系數(shù)據(jù)據(jù)庫管理理系統(tǒng)實實質(zhì)是面面向集合合的，在在Oracle中并沒有有一種描描述表中中單一記記錄的表表達形式式，除非非使用where子句來限限制只有有一條記記錄被選選中。游游標(biāo)允許許應(yīng)用程程序?qū)Σ椴樵冋Z句句返回的的行結(jié)果果集中的的每一行行進行相相同或不不同的操操作，而而不是一一次對整整個結(jié)果果集進行行同一種種操作。。什么是游游標(biāo)？就本質(zhì)而而言，游游標(biāo)實際際上是一一種能從從包括多多條數(shù)據(jù)據(jù)記錄的的結(jié)果集集中每次次提取一一條記錄錄的機制制。游標(biāo)由結(jié)結(jié)果集和和結(jié)果集集中指向向特定記記錄的游游標(biāo)位置置組成，，游標(biāo)充充當(dāng)指針針的作用用。盡管管游標(biāo)能能夠遍歷歷查詢結(jié)結(jié)果中的的所有行行，但它它一次只只能指向向一行。。游標(biāo)的構(gòu)構(gòu)成Oracle中游標(biāo)的的分類在Oracle中游標(biāo)可可以分為為兩種類類型：顯式游標(biāo)標(biāo)和隱式游標(biāo)標(biāo)。顯式游標(biāo)標(biāo)：用于處處理select語句返回回的多行行數(shù)據(jù)。。隱式游標(biāo)標(biāo)：用于處處理單行行selectinto語句和DML語句。兩者區(qū)別別（p129）顯式游標(biāo)標(biāo)的使用用使用顯式式游標(biāo)分分四個步步驟：2.打開游標(biāo)標(biāo)OPENcursor__name1.聲明游標(biāo)標(biāo)CURSORcursor__nameISselect__statement3.從游標(biāo)中中查找信信息FETCHcursor__nameINTOvar1,var2,,……4.關(guān)閉游標(biāo)標(biāo)CLOSEcursor__name例:聲明游標(biāo)標(biāo)，對應(yīng)應(yīng)的集合合是表emp中所有的的員工姓姓名。DECLAREcursorc_emp_enameisselectenamefromscott..emp;顯式游標(biāo)標(biāo)的使用用例:顯示游標(biāo)標(biāo)c_emp_ename中的第一一行信息息。DECLAREcursorc_emp_enameisselectenamefromscott.emp;v_enamescott.emp..ename%%type;BEGINopenc_emp_ename;fetchc_emp_enameintov_ename;dbms_output.put__line(v_ename);closec_emp_ename;END;;顯式游標(biāo)標(biāo)的使用用思考:如何顯示游標(biāo)標(biāo)c_emp_ename中的所有有信息？？練習(xí):顯示游標(biāo)標(biāo)c_emp_ename中的所有有信息。。DECLAREcursorc_emp_enameisselectenamefromscott..emp;v_enameemp.ename%type;v_countbinary_integer;BEGINselectcount((rowid))intov__countfromscott.emp;openc__emp_ename;foriin1..v_countloopfetchc_emp_enameintov_ename;dbms_output.put__line(v_ename);endloop;closec_emp_ename;END;;顯式游標(biāo)標(biāo)的使用用思考：如如何獲取取指定職職工編號號的員工工姓名？？參數(shù)游標(biāo)標(biāo)參數(shù)游標(biāo)標(biāo)是指帶帶有參數(shù)數(shù)的游標(biāo)標(biāo)。2.打開游標(biāo)標(biāo)OPENcursor__name（參數(shù)值值）1.聲明參數(shù)數(shù)游標(biāo)CURSORcursor__name（參數(shù)名名參參數(shù)數(shù)據(jù)據(jù)類型，，..）ISselect……from…….where………注：1、定義參數(shù)數(shù)只能指指定數(shù)據(jù)據(jù)類型，，不能指指定長度度。2、必須在游游標(biāo)select語句的where子句中引引用游標(biāo)標(biāo)參數(shù)，，否則失失去了定定義參數(shù)數(shù)游標(biāo)的的意義。。DECLAREcursorc_emp(v_empnonumber))isselect*fromscott.empwhereempno=v_empno;v_empscott.emp%%rowtype;BEGINopenc_emp(7369);fetchc_empintov_emp;dbms_output.put__line(v_emp.ename);closec_emp;END;;例：使用參數(shù)數(shù)游標(biāo)獲獲取7369員工的所所有信息息。思考：使用游標(biāo)標(biāo)獲取任任何編號號員工的的所有信信息？openc_emp(&&number)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)（替替代變量量）獲取取任何編編號員工工的所有有信息。。思考：如何處理理編號不不存在的的情況？？顯式游標(biāo)標(biāo)屬性當(dāng)使用顯顯示游標(biāo)標(biāo)時，需需要使用用游標(biāo)屬屬性確定定顯示游游標(biāo)的執(zhí)執(zhí)行信息息，顯示示游標(biāo)包包括%isopen、%found、%notfound、%rowcount四種屬性性。注：當(dāng)引用顯顯示游標(biāo)標(biāo)屬性時時，需要要帶有游游標(biāo)名作作為前綴綴（例：：emp__cursor%rowcount)顯式游標(biāo)標(biāo)屬性%found是是一個布布爾屬性性。如果果前一個個fetch語語句返一一個行，，那么它它返回true，否否則返回回false。。如果在在相關(guān)聯(lián)聯(lián)的游標(biāo)標(biāo)還沒有有打開進進行引用用，那么么會返回回錯誤。。%notfound的屬性與與%found相反。%isopen也是布爾爾屬性，，用來確確定相關(guān)關(guān)的游標(biāo)標(biāo)是否被被打開。。%rowcount此數(shù)字屬屬性返回回目前為為止由游游標(biāo)返回回行的數(shù)數(shù)目，即即fetch語句后的的得到數(shù)數(shù)字。如如果在相相關(guān)聯(lián)的的游標(biāo)還還沒有打打開或者者已經(jīng)關(guān)關(guān)閉的時時候進行行引用，，會返回回錯誤。。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(''您輸入的的編號不不存在??！');ENDIF;;END;;例：使用游游標(biāo)獲取取任何編編號員工工的所有有信息，，對于不不存在的的編號顯顯示“您您輸入的的編號不不存在””。練習(xí)：使用參參數(shù)游標(biāo)標(biāo)獲取任任何部門門編號的的員工姓姓名信息息，并返返回記錄錄條數(shù)。。對于不不存在的的編號顯顯示“您您輸入的的編號不不存在””。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(''您輸入的的編號不不存在??！');ENDIF;;closec_e__dept;END;;必須先獲獲取數(shù)據(jù)據(jù)才能有有found的返回值值。cursorfor循環(huán)forrecord__namein(corsor_name[[(參數(shù)1[,參數(shù)2]....)]]loopstatementsendloop;為了簡化化游標(biāo)處處理，可可以使用用游標(biāo)for循環(huán)，當(dāng)當(dāng)使用游游標(biāo)for循環(huán)時，，Oracle會隱含的的打開游游標(biāo)、提提取數(shù)據(jù)據(jù)并關(guān)閉閉游標(biāo)。。注：record__name是基于游游標(biāo)隱含含定義的的記錄變變量。在在執(zhí)行循循環(huán)之前前，Oracle會隱含的的打開游游標(biāo)，并并且每循循環(huán)一次次自動提提取一行行數(shù)據(jù)，，當(dāng)所有有數(shù)據(jù)提提取后自自動退出出循環(huán)并并關(guān)閉游游標(biāo)。例：編寫PL/SQL塊，定義義游標(biāo)，，使用替替代變量量輸入部部門號，，并以工工資降序序顯示該該部門所所有雇員員的名稱稱和工資資。DECLAREcursoremp__cursorisselectename,,salfromscott..empwheredeptno==&noorderbysaldesc;BEGINforemp__recordinemp__cursorloopdbms_output.put__line(''姓名：'||emp__record.ename|||',工資：'||emp__record.sal);;endloop;END;;練習(xí)：編寫PL/SQL塊，定義義參數(shù)游游標(biāo)（參參數(shù)：崗崗位），，使用替替代變量量輸入崗崗位，并并顯示該該崗位所所有雇員員的名稱稱和工資資。DECLAREcursoremp__cursor(titlevarchar2)isselectename,,salfromscott..empwherejob==title;BEGINforemp__recordinemp__cursor('&job')loopdbms_output.put__line(''姓名：'||emp__record.ename|||',工資：'||emp__record.sal);;endloop;END;;分析：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(''您輸入的的編號不不存在??！');ENDIF;;endloop;END;;練習(xí)：使用參參數(shù)游標(biāo)標(biāo)獲取任任何部門門編號的的員工姓姓名信息息，并返返回記錄錄條數(shù)。。對于不不存在的的編號顯顯示“您您輸入的的編號不不存在””。更新或刪刪除游標(biāo)標(biāo)行通過使用用顯式游游標(biāo)，不不僅可以以取得游游標(biāo)結(jié)果果集的數(shù)數(shù)據(jù)，而而且可以以更新或或刪除游游標(biāo)結(jié)果果集的當(dāng)當(dāng)前行。。DELETEFROMtable__nameWHERECURRENTOFcursor__name；CURSORcursor_nameISselect__statementFORUPDATE;UPDATEtable_nameSET………...WHERECURRENTOFcursor__name;例:要求輸入入部門號號，輸出出該部門門所有雇雇員姓名名及原工工資，并并將雇員員的工資資提高10%。DECLARECURSORemp_cursorISSELECTename,,sal,deptnoFROMscott.empWHEREdeptno=&noFORUPDATE;BEGINFORemp_recordINemp__cursorLOOPdbms_output.put__line(''姓名:'|||emp_record.ename||',,原工資:'|||emp_record.sal));UPDATEscott..empSETsal=sal*1.1WHERECURRENTOFemp_cursor;;ENDLOOP;END;;練習(xí):要求輸入入雇員號號，輸出出該雇員員姓名及及原工資資，并將將雇員刪刪除。DECLARECURSORemp_cursorISSELECTename,,salFROMscott..empWHEREempno==&noFORupdate;BEGINFORemp_recordINemp__cursorLOOPdbms_output.put__line(''姓名:'|||emp_record.ename||',,原工資:'|||emp_record.sal));DELETEscott..empWHERECURRENTOFemp_cursor;;ENDLOOP;END;;隱式游標(biāo)標(biāo)屬性隱式游標(biāo)標(biāo)也稱SQL游標(biāo)，用用于處理理單行selectinto語句和DML語句。一個SQL游標(biāo)不能能使用open、fetch、close來操作。。與顯式游游標(biāo)相同同，SQL游標(biāo)也有有%isopen、%found、%notfound、%rowcount四種屬性性。具體體看書。。注：當(dāng)引用隱隱式游標(biāo)標(biāo)屬性時時，需要要帶有SQL作為前綴綴（例：：SQL%%rowcount)書例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.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.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.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;;/練習(xí)：1、當(dāng)使用顯顯示游標(biāo)標(biāo)時，在在執(zhí)行了了哪條語語句后應(yīng)應(yīng)該檢查查游標(biāo)是是否包含含行？A、openB、fetchC、closeD、cursor2、在以下哪哪些語句句中可以以包含wherecurrentof子句？A、openB、fetchC、delete

D、selectE、updateF、cursor√√√練習(xí)：3、請查看以以下游標(biāo)標(biāo)定義語語句，哪哪行會引引起錯誤誤？DECLARECURSORcust__cursor(p__cust_id,,p_last_name))ISSELECTcust__id,,first_name,,last__nameFROMcustomerWHEREcust_id=p_cust__idANDlast_name=p__last_name;

定義游標(biāo)標(biāo)變量需需指明變變量的數(shù)數(shù)據(jù)類型型A、2B、3C、4D、5E、64、課后習(xí)題題第二、、三題（（課本149頁）√小結(jié)游標(biāo)概念念游標(biāo)分類類顯示游標(biāo)標(biāo)的使用用參數(shù)游標(biāo)標(biāo)游標(biāo)屬性性Cursorfor循環(huán)更新、刪刪除游標(biāo)標(biāo)行隱式游標(biāo)標(biāo)的使用用異常處處理理為了提高高應(yīng)用程程序的健健壯性，，使得應(yīng)應(yīng)用程序序可以安安全正常常的運行行，應(yīng)用用開發(fā)人人員應(yīng)該該考慮到到PL/SQL塊可能出出現(xiàn)的各各種異常常情況，，并進行行相應(yīng)的的處理。。通常使用異常常（Exception），可以以處理PL/SQL塊的各種種異常情情況。引言異常（Exception）是一種種PL/SQL標(biāo)識符，，它包括括預(yù)定義異異常、非預(yù)定義義異常、自定義異異常。異常簡介介當(dāng)編寫PL/SQL塊時，應(yīng)應(yīng)該捕捉捉并處理理各種可可能出現(xiàn)現(xiàn)的異常常。如果果不捕捉捉和處理理異常，，那么Oracle會將錯誤誤傳遞到到調(diào)用環(huán)環(huán)境；如如果捕捉捉并處理理異常，，那么Oracle會在PL/SQL塊內(nèi)解決決運行錯錯誤。DECLAREv_enameemp.ename%TYPE;BEGINSELECTenameINTOv_enameFROMscott.empWHEREempno=&&no;;dbms_output.put__line(''雇員名:'|||v_ename);;END;;未捕捉異異常，傳傳遞錯誤誤到調(diào)用用環(huán)境DECLAREv_enameemp.ename%TYPE;BEGINSELECTenameINTOv_enameFROMempWHEREempno=&&no;;dbms_output.put__line(''雇員名:'|||v_ename);;EXCEPTIONWHENNO_DATA__FOUNDTHENdbms_output.put__line(''雇員號不不正確，，請核實實雇員號號！');END;;捕捉異常常并處理理異常處理理部分的語語法如下下：BEGIN……EXCEPTIONwhenexception__namethencodeforhandingexception_name[whenanother_exceptionthencodeforhandinganother__exception][whenothersthencodeforhandinganyotherexception..]END;;預(yù)定義異異常是指指PL/SQL所提供的的系統(tǒng)異異常。Oracle為應(yīng)用開開發(fā)人員員提供了了21個預(yù)定義義異常，，每個預(yù)預(yù)定義異異常對應(yīng)應(yīng)一個特特定的Oracle錯誤，當(dāng)當(dāng)PL/SQL塊出現(xiàn)這這些Oracle錯誤時，，會隱含含的處罰罰相應(yīng)的的預(yù)定義義異常。。一、預(yù)定定義異常常應(yīng)用預(yù)定定義異常常非預(yù)定義義異常用用于處理理與預(yù)定定義異常常無關(guān)的的Oracle錯誤。預(yù)定義異異常只能能用于處處理21種Oracle錯誤，而而PL/SQL塊還會遭遭遇其他他Oracle錯誤（例例如：完整性約約束ORA--02291）二、非預(yù)預(yù)定義異異常例：beginupdatescott..empsetdeptno=&nowhereempno=&&eno;end;;1.在declare部分定義義異常標(biāo)標(biāo)識符。。2.在declare部分建立立Oracle錯誤號與與異常之之間的關(guān)關(guān)聯(lián)。3.捕捉并處處理異常常。使用非預(yù)預(yù)定義異異常的步步驟：語法如下下：declareexception__nameexception;pragmaexception__init(exception_name,,-2291));……………….例如：DECLAREe_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;;為了在PL/SQL塊處理Oracle錯誤，應(yīng)應(yīng)用開發(fā)發(fā)人員可可以使用用預(yù)定義義異?；蚧蛘叻穷A(yù)預(yù)定義異異常。注：當(dāng)執(zhí)行內(nèi)內(nèi)嵌update和delete語句時，，如果沒沒有更新新或刪除除行，那那么PL/SQL塊不會給給出任何何提示信信息，為為了使得得用戶可可以獲得得更有用用，更有有意義的的消息，，需要使使用自定定義異常常。三、自定定義異常常1.在declare部分定義義異常標(biāo)標(biāo)識符。。2.在執(zhí)行部部分使用用raise語句顯示示觸發(fā)異異常。3.捕捉并處處理異常常。使用自定定義異常常步驟：：語法：declareexception__nameexception;begin……………….raiseexception__name；exception……...end；例如：DECLAREe_integrityEXCEPTION;;e_no_rowsEXCEPTION;PRAGMAEXCEPTION_INIT((e_integrity,,-2291));namescott.emp.ename%TYPE:=LOWER(''&name'');dnoscott..emp.deptno%TYPE:=&&dno;BEGINUPDATEscott..empSETdeptno==dnoWHERELOWER(ename)==name;IFSQL%%NOTFOUNDTHENRAISEe_no_rows;;ENDIF;;EXCEPTIONWHENe__integrityTHENdbms_output.put__line(''該部