第十四课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中并没有一种描述 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中单一记录的表达形式，除非使用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四种属性。具体看 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 。注：当引用隐式游标属性时，需要带有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、deleteD、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……EXCEPTION　whenexception_namethen　　codeforhandingexception_name　[whenanother_exceptionthen　　codeforhandinganother_exception]　[whenothersthen　　codeforhandinganyotherexception.]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('该部门不存在');WHENe_no_rowsTHENdbms_output.put_line('该雇员不存在');END;练习：1、RAISE语句应该放在PL/SQL块的哪个部分？A、定义部分B、执行部分C、异常处理部分2、当在异常和Oracle错误之间建立关联时，应该在哪个部分完成？A、定义部分B、执行部分C、异常处理部分3、假定在emp表上定义了check约束要求雇员的工资不能高于6000，为了处理工资超过6000可能出现的错误，应该使用哪种异常？A、预定义异常B、非预定义异常C、自定义异常√√√练习：4、编写PL/SQL块，使用替代变量输入雇员号，显示雇员名及其工资。如果雇员不存在，则显示“请输入正确的雇员号”5、编写PL/SQL块，使用替代变量输入雇员号和新部门号，更新雇员部门，如果部门不存在，则显示“请输入正确的部门号！”；如果雇员不存在，则显示“请输入正确的雇员号”在一个异常中，SQLCODE返回Oracle错误的序号，而SQLERRM返回的是相应的错误消息，错误消息首先显示是错误代码。SQLCODE和SQLERRM注：1.SQLCODE通常返回的是负数。对于用户自定义的异常，SQLCODE返回的是+1，而SQLERRM返回的是自定义错误信息。2.如果没有异常被触发，则SQLCODE返回0，SQLERRM返回ORA-0000:normal,successfulcompletion。declarev_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;/演讲完毕，谢谢观看！