第十四课Oracle游标和异常处理

复习动态SQL包括本地动态态SQL和DBMS_SQL动态SQL两种实现现方法。。动态SQL简介为了在动动态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用于为占占位符提提供数据据。语法如下下：executeimmediatedyn__stringusingbind_argument[,………](3)处理包含含returning子句的DML语句注：1、当使用用returninginto子句接收收数据时时，只能能接受DML语句返回回的单行行数据。。2、当在动动态DML语句中指指定returninginto子句时，，需要在在into后使用占占位符。。语法如下下：executeimmediatedyn__stringreturningintovar1[,………]语法如下下：executeimmediatedyn__stringintovar1,………3、使用executeimmediate语句处理理单行语语句游标变量量是基于于REFCURSOR类型所定定义的变变量。使使用显示示游标只只能定义义静态游游标，而而通过使使用游标标变量可可以在打打开游标标时指定定游标所所对应的的select语句，从从而实现现动态游游标。游标变量量游标变量量包括定义游标标变量、打开游标标变量、提取数据据和关闭游标标四个阶段段。游标变量量1、定义REFCURSOR类型和游游标变量量语法如下下：TYPEref__type_nameISREFCURSOR[RETURNreturn_type]];cursor__variableref_type_name;;2、打开游游标变量量语法如下下：OPENcursor__variableFORselect………3、提取数数据语法如下下：FETCHcursor_variableINTOvar1,………4、关闭游游标变量量语法如下下：CLOSEcursor__variable游标标NEW为什么要要使用游游标？关系数据据库管理理系统实实质是面面向集合合的，在在Oracle中并没有有一种描描述表中中单一记记录的表表达形式式，除非非使用where子句来限限制只有有一条记记录被选选中。游游标允许许应用程程序对查查询语句句返回的的行结果果集中的的每一行行进行相相同或不不同的操操作，而而不是一一次对整整个结果果集进行行同一种种操作。。什么是游游标？就本质而而言，游游标实际际上是一一种能从从包括多多条数据据记录的的结果集集中每次次提取一一条记录录的机制制。游标由结结果集和和结果集集中指向向特定记记录的游游标位置置组成，，游标充充当指针针的作用用。尽管管游标能能够遍历历查询结结果中的的所有行行，但它它一次只只能指向向一行。。游标的构构成Oracle中游标的的分类在Oracle中游标可可以分为为两种类类型：显式游标标和隐式游标标。显式游标标：用于处处理select语句返回回的多行行数据。。隐式游标标：用于处处理单行行selectinto语句和DML语句。两者区别别（p129）显式游标标的使用用使用显式式游标分分四个步步骤：2.打开游标标OPENcursor__name1.声明游标标CURSORcursor__nameISselect__statement3.从游标中中查找信信息FETCHcursor__nameINTOvar1,var2,,……4.关闭游标标CLOSEcursor__name例:声明游标标，对应应的集合合是表emp中所有的的员工姓姓名。DECLAREcursorc_emp_enameisselectenamefromscott..emp;显式游标标的使用用例:显示游标标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;;显式游标标的使用用思考:如何显示游标标c_emp_ename中的所有有信息？？练习:显示游标标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;;显式游标标的使用用思考：如如何获取取指定职职工编号号的员工工姓名？？参数游标标参数游标标是指带带有参数数的游标标。2.打开游标标OPENcursor__name（参数值值）1.声明参数数游标CURSORcursor__name（参数名名参参数数据据类型，，..）ISselect……from…….where………注：1、定义参数数只能指指定数据据类型，，不能指指定长度度。2、必须在游游标select语句的where子句中引引用游标标参数，，否则失失去了定定义参数数游标的的意义。。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;;例：使用参数数游标获获取7369员工的所所有信息息。思考：使用游标标获取任任何编号号员工的的所有信信息？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;;练习：使用非非参数游游标（替替代变量量）获取取任何编编号员工工的所有有信息。。思考：如何处理理编号不不存在的的情况？？显式游标标属性当使用显显示游标标时，需需要使用用游标属属性确定定显示游游标的执执行信息息，显示示游标包包括%isopen、%found、%notfound、%rowcount四种属性性。注：当引用显显示游标标属性时时，需要要带有游游标名作作为前缀缀（例：：emp__cursor%rowcount)显式游标标属性%found是是一个布布尔属性性。如果果前一个个fetch语语句返一一个行，，那么它它返回true，否否则返回回false。。如果在在相关联联的游标标还没有有打开进进行引用用，那么么会返回回错误。。%notfound的属性与与%found相反。%isopen也是布尔尔属性，，用来确确定相关关的游标标是否被被打开。。%rowcount此数字属属性返回回目前为为止由游游标返回回行的数数目，即即fetch语句后的的得到数数字。如如果在相相关联的的游标还还没有打打开或者者已经关关闭的时时候进行行引用，，会返回回错误。。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;;例：使用游游标获取取任何编编号员工工的所有有信息，，对于不不存在的的编号显显示“您您输入的的编号不不存在””。练习：使用参参数游标标获取任任何部门门编号的的员工姓姓名信息息，并返返回记录录条数。。对于不不存在的的编号显显示“您您输入的的编号不不存在””。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(''共有员工工人数：：'||c_e_dept%%rowcount));ELSEdbms_output.put__line(''您输入的的编号不不存在！！');ENDIF;;closec_e__dept;END;;必须先获获取数据据才能有有found的返回值值。cursorfor循环forrecord__namein(corsor_name[[(参数1[,参数2]....)]]loopstatementsendloop;为了简化化游标处处理，可可以使用用游标for循环，当当使用游游标for循环时，，Oracle会隐含的的打开游游标、提提取数据据并关闭闭游标。。注：record__name是基于游游标隐含含定义的的记录变变量。在在执行循循环之前前，Oracle会隐含的的打开游游标，并并且每循循环一次次自动提提取一行行数据，，当所有有数据提提取后自自动退出出循环并并关闭游游标。例：编写PL/SQL块，定义义游标，，使用替替代变量量输入部部门号，，并以工工资降序序显示该该部门所所有雇员员的名称称和工资资。DECLAREcursoremp__cursorisselectename,,salfromscott..empwheredeptno==&noorderbysaldesc;BEGINforemp__recordinemp__cursorloopdbms_output.put__line(''姓名：'||emp__record.ename|||',工资：'||emp__record.sal);;endloop;END;;练习：编写PL/SQL块，定义义参数游游标（参参数：岗岗位），，使用替替代变量量输入岗岗位，并并显示该该岗位所所有雇员员的名称称和工资资。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(''共有员工工人数：：'||c_e_dept%%rowcount));ELSEdbms_output.put__line(''您输入的的编号不不存在！！');ENDIF;;endloop;END;;练习：使用参参数游标标获取任任何部门门编号的的员工姓姓名信息息，并返返回记录录条数。。对于不不存在的的编号显显示“您您输入的的编号不不存在””。更新或删删除游标标行通过使用用显式游游标，不不仅可以以取得游游标结果果集的数数据，而而且可以以更新或或删除游游标结果果集的当当前行。。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;;练习:要求输入入雇员号号，输出出该雇员员姓名及及原工资资，并将将雇员删删除。DECLARECURSORemp_cursorISSELECTename,,salFROMscott..empWHEREempno==&noFORupdate;BEGINFORemp_recordINemp__cursorLOOPdbms_output.put__line(''姓名:'|||emp_record.ename||',,原工资:'|||emp_record.sal));DELETEscott..empWHERECURRENTOFemp_cursor;;ENDLOOP;END;;隐式游标标属性隐式游标标也称SQL游标，用用于处理理单行selectinto语句和DML语句。一个SQL游标不能能使用open、fetch、close来操作。。与显式游游标相同同，SQL游标也有有%isopen、%found、%notfound、%rowcount四种属性性。具体体看书。。注：当引用隐隐式游标标属性时时，需要要带有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循循环书例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;;/练习：1、当使用显显示游标标时，在在执行了了哪条语语句后应应该检查查游标是是否包含含行？A、openB、fetchC、closeD、cursor2、在以下哪哪些语句句中可以以包含wherecurrentof子句？A、openB、fetchC、delete

D、selectE、updateF、cursor√√√练习：3、请查看以以下游标标定义语语句，哪哪行会引引起错误误？DECLARECURSORcust__cursor(p__cust_id,,p_last_name))ISSELECTcust__id,,first_name,,last__nameFROMcustomerWHEREcust_id=p_cust__idANDlast_name=p__last_name;

定义游标标变量需需指明变变量的数数据类型型A、2B、3C、4D、5E、64、课后习题题第二、、三题（（课本149页）√小结游标概念念游标分类类显示游标标的使用用参数游标标游标属性性Cursorfor循环更新、删删除游标标行隐式游标标的使用用异常处处理理为了提高高应用程程序的健健壮性，，使得应应用程序序可以安安全正常常的运行行，应用用开发人人员应该该考虑到到PL/SQL块可能出出现的各各种异常常情况，，并进行行相应的的处理。。通常使用异常常（Exception），可以以处理PL/SQL块的各种种异常情情况。引言异常（Exception）是一种种PL/SQL标识符，，它包括括预定义异异常、非预定义义异常、自定义异异常。异常简介介当编写PL/SQL块时，应应该捕捉捉并处理理各种可可能出现现的异常常。如果果不捕捉捉和处理理异常，，那么Oracle会将错误误传递到到调用环环境；如如果捕捉捉并处理理异常，，那么Oracle会在PL/SQL块内解决决运行错错误。DECLAREv_enameemp.ename%TYPE;BEGINSELECTenameINTOv_enameFROMscott.empWHEREempno=&&no;;dbms_output.put__line(''雇员名:'|||v_ename);;END;;未捕捉异异常，传传递错误误到调用用环境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;;预定义异异常是指指PL/SQL所提供的的系统异异常。Oracle为应用开开发人员员提供了了21个预定义义异常，，每个预预定义异异常对应应一个特特定的Oracle错误，当当PL/SQL块出现这这些Oracle错误时，，会隐含含的处罚罚相应的的预定义义异常。。一、预定定义异常常应用预定定义异常常非预定义义异常用用于处理理与预定定义异常常无关的的Oracle错误。预定义异异常只能能用于处处理21种Oracle错误，而而PL/SQL块还会遭遭遇其他他Oracle错误（例例如：完整性约约束ORA--02291）二、非预预定义异异常例：beginupdatescott..empsetdeptno=&nowhereempno=&&eno;end;;1.在declare部分定义义异常标标识符。。2.在declare部分建立立Oracle错误号与与异常之之间的关关联。3.捕捉并处处理异常常。使用非预预定义异异常的步步骤：语法如下下：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错误，应应用开发发人员可可以使用用预定义义异常或或者非预预定义异异常。注：当执行内内嵌update和delete语句时，，如果没没有更新新或删除除行，那那么PL/SQL块不会给给出任何何提示信信息，为为了使得得用户可可以获得得更有用用，更有有意义的的消息，，需要使使用自定定义异常常。三、自定定义异常常1.在declare部分定义义异常标标识符。。2.在执行部部分使用用raise语句显示示触发异异常。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(''该部