第十三章-游标管理

第十三章游标管理

知识点回顾顺序结构控制语句选择结构控制语句if语句Case语句循环结构控制语句基本loop循环while循环for循环2本章目标掌握并使用游标管理技巧隐式游标(掌握)显式游标(掌握)Ref游标(了解)3游标简介2-1Oracle服务器执行PL/SQL程序内存单元保存到游标中一次处理一行检索行提取行4游标简介2-2逐行处理查询结果，以编程的方式访问数据游标的类型：隐式游标REF游标显式游标在PL/SQL程序中执行DMLSQL语句时自动创建隐式游标。显式游标用于处理返回多行的查询。REF游标用于处理运行时才能确定的动态SQL查询的结果游标类型5隐式游标4-1在PL/SQL中使用DML语句时自动创建隐式游标隐式游标自动声明、打开和关闭，其名为SQL通过检查隐式游标的属性可以获得最近执行的DML语句的信息隐式游标的属性有：%FOUND–SQL语句影响了一行或多行时为TRUE%NOTFOUND–SQL语句没有影响任何行时为TRUE%ROWCOUNT–SQL语句影响的行数%ISOPEN-游标是否打开，始终为FALSE6隐式游标4-2SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEt_studentSETf_name='张三丰' WHEREf_id='001'; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE('表已更新'); ENDIF;END; /只有在DML语句影响一行或多行时，才返回True7隐式游标4-3SQL>SETSERVEROUTPUTONSQL>DECLARE v_idt_student.f_id%type:='&id'; v_namet_student.f_name%Type:='&name';BEGIN UPDATEt_studentSETf_name=v_name WHEREf_id=v_id; IFSQL%NOTFOUNDTHEN DBMS_OUTPUT.PUT_LINE('编号未找到。'); ELSE DBMS_OUTPUT.PUT_LINE(‘表已更新'); ENDIF;END;/如果DML语句不影响任何行，则返回True8隐式游标4-4SQL>SETSERVEROUTPUTONSQL>beginupdatet_studentsetf_department='CHE'wheref_class=2;ifSQL%Foundthendbms_output.put_line('修改记录数为：'||SQL%RowCount);elsedbms_output.put_line('未找到相应记录');endif;end;/返回DML语句影响的行数9SELECTINTO语句2-1SQL>SETSERVEROUTPUTONSQL>DECLARE v_idt_course.f_id%type; v_namet_course.f_name%type;BEGIN v_id:='&id'; SELECTf_nameINTOv_name FROMt_courseWHEREf_id=v_id;EXCEPTION WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('课程未找到');END;/如果没有与SELECTINTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常10SELECTINTO语句2-2SQL>SETSERVEROUTPUTONSQL>DECLARE v_birtht_student.f_birth%type;BEGIN SELECTf_birthINTOv_birthFROMt_student;DBMS_OUTPUT.PUT_LINE(v_birth);EXCEPTION WHENTOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE('该查询提取多行');END;/如果SELECTINTO语句返回多个值，将引发TOO_MANY_ROWS异常11显式游标2-1显式游标在PL/SQL块的声明部分定义查询，该查询可以返回多行显式游标的操作过程：数据库打开游标CS王五3PHY李四2CS张三1F_departmentf_namef_id提取行变量关闭游标12显式游标2-2SQL>setserveroutputondeclarenamet_student.f_name%type;Cursorcur_stuIsselectf_namefromt_studentwheref_department='CS';beginOpencur_stu;LoopFetchcur_stuIntoname;ExitWhencur_stu%NotFound;dbms_output.put_line('学生姓名：'||name);EndLoop;Closecur_stu;end;/声明游标打开游标提取行关闭游标13带参数的显式游标声明显式游标时可以带参数以提高灵活性声明带参数的显式游标的语法如下：

CURSOR<cursor_name>(<param_name><param_type>)ISselect_statement;SQL>SETSERVEROUTPUTONSQL>DECLARE desigVARCHAR2(20); emp_codeVARCHAR2(5); empnmVARCHAR2(20); CURSORemp_cur(desigparamVARCHAR2)IS SELECTempno,enameFROMemployee WHEREdesignation=desig;BEGIN desig:='&desig'; OPENemp_cur(desig); LOOP FETCHemp_curINTOemp_code,empnm; EXITWHENemp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm); ENDLOOP; CLOSEemp_cur;END;14循环游标2-1循环游标用于简化游标处理代码当用户需要从游标中提取所有记录时使用循环游标的语法如下：

FOR<record_index>IN<cursor_name>LOOP <executablestatements>ENDLOOP;15循环游标2-2SQL>SETSERVEROUTPUTONSQL>declarestut_student%RowType;Cursorcur_stuIsselectf_id,f_name,f_birthfromt_studentwheref_department='CS';beginForstuIncur_stuLoopdbms_output.put_line('学号：'||stu.f_id||'学生姓名：'||stu.f_name||'出生日期：'||stu.f_birth);EndLoop;end;/16显式游标属性2-3显式游标属性值的语法是cursor_name，后面紧跟属性（例如，c1％ISOPEN）。显式游标和游标变量具有相同的属性。%ISOPEN属性%FOUND属性%NOTFOUND属性%ROWCOUNT属性17显式游标属性2-3对记录进行计数并打印,在获取第3行后打印一条消息DECLARECURSORc1ISSELECTlast_nameFROMemployeesWHEREROWNUM<5ORDERBYlast_name;nameemployees.last_name%TYPE;BEGINOPENc1;LOOPFETCHc1INTOname;EXITWHENc1%NOTFOUNDORc1%NOTFOUNDISNULL;DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT||'.'||name);IFc1%ROWCOUNT=3THENDBMS_OUTPUT.PUT_LINE('---Fetched3rdrow---');ENDIF;ENDLOOP;CLOSEc1;END;18REF游标和游标变量3-1REF游标和游标变量用于处理运行时动态执行的SQL查询创建游标变量需要两个步骤：声明REF

游标类型声明REF

游标类型的变量用于声明REF游标类型的语法为：

TYPE<ref_cursor_name>ISREFCURSOR [RETURN<return_type>];19REF游标和游标变量3-2打开游标变量的语法如下：

OPENcursor_nameFORselect_statement;TYPEmy_curtypeISREFCURSORRETURNstud_det%ROWTYPE;order_curmy_curtype;声明强类型的REF游标TYPEmy_ctypeISREFCURSOR;stud_curmy_ctype;声明弱类型的REF游标20REF游标和游标变量3-3SQL>DECLARE TYPEtoys_curtypeISREFCURSORRETURNtoys%ROWTYPE; toys_curvartoys_curtype; toys_rectoys%ROWTYPE;BEGIN OPENtoys_curvarFOR SELECT*FROMtoys; FETCHtoys_curvarINTOtoys_rec;... CLOSEtoys_curvar;END;声明REF游标类型声明游标变量21游标变量的优点和限制游标变量的功能强大，可以简化数据处理。游标变量的优点有：可从不同的SELECT

语句中提取结果集可以作为过程的参数进行传递可以引用游标的所有属性可以进行赋值运算使用游标变量的限制：不能在程序包中声明游标变量FORUPDATE子句不能与游标变量一起使用不能使用比较运算符22使用游标变量执行动态SQL可以使用游标变量执行动态构造的SQL语句打开执行动态SQL的游标变量的语如下：

OPENcursor_nameFORdynamic_sqlstring[USINGbind_argument_list];DECLAREr_empemp%ROWTYPE;TYPEc_typeISREFCURSOR;curc_type;p_salaryNUMBER;BEGINp_salary:=2500;OPENcurFOR'select*fromempwheresal>:1orderbysaldesc'USINGp_salary;DBMS_OUTPUT.PUT_LINE('薪水大于'||p