您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据库 > 第十四课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%rowcount0thendbms_output.put_line('共有员工人数:'||c_e_dept%rowcount);ELSEdbms_output.put_line('您输入的编号不存在!');ENDIF;endloop;END;练习:使用参数
本文标题:第十四课Oracle游标和异常处理
链接地址:https://www.777doc.com/doc-14464 .html