第十三章-游标管理.ppt

《第十三章-游标管理.ppt》由会员分享，可在线阅读，更多相关《第十三章-游标管理.ppt（25页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Inspur GroupInspur Education 第十三章第十三章 游标管理游标管理Inspur Education 知识点回顾知识点回顾u顺序结构控制语句顺序结构控制语句u选择结构控制语句选择结构控制语句if语句Case语句u循环结构控制语句循环结构控制语句基本loop循环while循环for循环2Inspur Education 本章目标本章目标u掌握并使用游标管理技巧掌握并使用游标管理技巧 隐式游标(掌握)显式游标(掌握)Ref游标(了解)3Inspur Education 游标简介游标简介 2-12-1Oracle 服务器服务器执行 PL/SQL 程序内存单元保存到游标中一次处

2、理一行检索行提取行4Inspur Education 游标简介游标简介 2-22-2u逐行处理查询结果，以编程的方式访问数据逐行处理查询结果，以编程的方式访问数据u游标的类型：游标的类型：隐式游标REF 游标显式游标在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标。显式游标用于处理返回多行的查询。REF 游标用于处理运行时才能确定的动态 SQL 查询的结果游标类型5Inspur Education 隐式游标隐式游标 4-14-1u在在PL/SQLPL/SQL中使用中使用DMLDML语句时语句时自动创建隐式游标自动创建隐式游标u隐式游标自动声明、打开和关闭，其名为隐式游标自动声

3、明、打开和关闭，其名为 SQLSQLu通过检查隐式游标的属性可以获得最近执行的通过检查隐式游标的属性可以获得最近执行的DMLDML语句语句的信息的信息u隐式游标的属性有：隐式游标的属性有：%FOUND SQL 语句影响了一行或多行时为 TRUE%NOTFOUND SQL 语句没有影响任何行时为TRUE%ROWCOUNT SQL 语句影响的行数%ISOPEN -游标是否打开，始终为FALSE6Inspur Education 隐式游标隐式游标 4-24-2SQL SET SERVEROUTPUT ONSQL BEGINUPDATE t_student SET f_name=张三丰WHERE f_

4、id=001;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE(表已更新);END IF;END;/只有在 DML 语句影响一行或多行时，才返回 True7Inspur Education 隐式游标隐式游标 4-34-3SQL SET SERVEROUTPUT ONSQL DECLARE v_id t_student.f_id%type:=&id;v_name t_student.f_name%Type:=&name;BEGIN UPDATE t_student SET f_name=v_name WHERE f_id=v_id;IF SQL%NOTFOUND THE

5、N DBMS_OUTPUT.PUT_LINE(编号未找到。);ELSEDBMS_OUTPUT.PUT_LINE(表已更新);END IF;END;/如果 DML 语句不影响任何行，则返回 True 8Inspur Education 隐式游标隐式游标 4-44-4SQL SET SERVEROUTPUT ON SQL begin update t_student set f_department=CHE where f_class=2;if SQL%Found then dbms_output.put_line(修改记录数为：|SQL%RowCount);else dbms_output.pu

6、t_line(未找到相应记录);end if;end;/返回 DML 语句影响的行数9Inspur Education SELECT INTO SELECT INTO 语句语句 2-12-1SQL SET SERVEROUTPUT ONSQL DECLARE v_id t_course.f_id%type;v_name t_course.f_name%type;BEGINv_id:=&id;SELECT f_name INTO v_name FROM t_course WHERE f_id=v_id;EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PU

7、T_LINE(课程未找到);END;/如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常10Inspur Education SELECT INTO SELECT INTO 语句语句 2-22-2SQL SET SERVEROUTPUT ONSQL DECLARE v_birth t_student.f_birth%type;BEGINSELECT f_birth INTO v_birth FROM t_student;DBMS_OUTPUT.PUT_LINE(v_birth);EXCEPTIONWHEN TOO_MANY_ROWS THEN DBMS_

8、OUTPUT.PUT_LINE(该查询提取多行);END;/如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常11Inspur Education 显式游标显式游标 2-12-1u显式游标在显式游标在 PL/SQL PL/SQL 块的声明部分定义查询，该查询可块的声明部分定义查询，该查询可以返回多行以返回多行u显式游标的操作过程：显式游标的操作过程：数据库打开游标CS王五王五3PHY李四李四2CS张三张三1F_departmentf_namef_id提取行变量 关闭游标 12Inspur Education 显式游标显式游标 2-22-2SQLset serve

9、routput ondeclare name t_student.f_name%type;Cursor cur_stu Is select f_name from t_student where f_department=CS;begin Open cur_stu;Loop Fetch cur_stu Into name;Exit When cur_stu%NotFound;dbms_output.put_line(学生姓名：|name);End Loop;Close cur_stu;end;/声明游标打开游标提取行关闭游标13Inspur Education 带参数的显式游标带参数的显式游标

10、u声明显式游标时可以带参数以提高灵活性声明显式游标时可以带参数以提高灵活性u声明带参数的显式游标的语法如下：声明带参数的显式游标的语法如下：CURSOR()CURSOR()IS select_statement;IS select_statement;SQL SET SERVEROUTPUT ONSQL DECLAREdesig VARCHAR2(20);emp_code VARCHAR2(5);empnm VARCHAR2(20);CURSOR emp_cur(desigparam VARCHAR2)IS SELECT empno,ename FROM employee WHERE desi

11、gnation=desig;BEGINdesig:=&desig;OPEN emp_cur(desig);LOOPFETCH emp_cur INTO emp_code,empnm;EXIT WHEN emp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_code|empnm);END LOOP;CLOSE emp_cur;END;14Inspur Education 循环游标循环游标 2-12-1u循环游标用于简化游标处理代码循环游标用于简化游标处理代码u当用户需要从游标中提取所有记录时使用当用户需要从游标中提取所有记录时使用u循环游标的语法如下：循环游标的语法

12、如下：FOR IN LOOPEND LOOP;15Inspur Education 循环游标循环游标 2-22-2SQL SET SERVER OUTPUT ONSQL declare stu t_student%RowType;Cursor cur_stu Is select f_id,f_name,f_birth from t_student where f_department=CS;begin For stu In cur_stu Loop dbms_output.put_line(学号：|stu.f_id|学生姓名：|stu.f_name|出生日期：|stu.f_birth);End

13、 Loop;end;/16Inspur Education 显式游标属性显式游标属性 2-3 2-3u显式游标属性值的语法是显式游标属性值的语法是cursor_namecursor_name，后面紧跟属性，后面紧跟属性（例如，（例如，c1c1ISOPENISOPEN）。）。u显式游标和游标变量具有相同的属性。显式游标和游标变量具有相同的属性。%ISOPEN属性%FOUND属性%NOTFOUND属性%ROWCOUNT属性17Inspur Education 显式游标属性显式游标属性 2-32-3u对对记录记录进行计数并打印进行计数并打印,在获取第在获取第3 3行后打印一条消息行后打印一条消息DE

14、CLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM 5 ORDER BY last_name;name employees.last_name%TYPE;BEGIN OPEN c1;LOOP FETCH c1 INTO name;EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT|.|name);IF c1%ROWCOUNT=3 THEN DBMS_OUTPUT.PUT_LINE(-Fetched 3rd row-)

15、;END IF;END LOOP;CLOSE c1;END;18Inspur Education REF REF 游标和游标变量游标和游标变量 3-13-1uREF REF 游标和游标变量用于处理运行时动态执行的游标和游标变量用于处理运行时动态执行的 SQL SQL 查询查询u创建游标变量需要两个步骤：创建游标变量需要两个步骤：声明 REF 游标类型声明 REF 游标类型的变量u用于声明用于声明 REF REF 游标类型的语法为：游标类型的语法为：TYPE IS REF CURSORTYPE IS REF CURSOR RETURN;RETURN;19Inspur Education REF

16、REF 游标和游标变量游标和游标变量 3-23-2u打开游标变量的语法如下：打开游标变量的语法如下：OPEN cursor_name FOR select_statement;OPEN cursor_name FOR select_statement;TYPE my_curtype IS REF CURSOR RETURN stud_det%ROWTYPE;order_cur my_curtype;声明强类型的 REF 游标TYPE my_ctype IS REF CURSOR;stud_cur my_ctype;声明弱类型的 REF 游标20Inspur Education REF REF

17、游标和游标变量游标和游标变量 3-33-3SQL DECLARE TYPE toys_curtype IS REF CURSOR RETURN toys%ROWTYPE;toys_curvar toys_curtype;toys_rec toys%ROWTYPE;BEGIN OPEN toys_curvar FOR SELECT*FROM toys;FETCH toys_curvar INTO toys_rec;.CLOSE toys_curvar;END;声明REF游标类型声明游标变量21Inspur Education 游标变量的优点和限制游标变量的优点和限制u游标变量的功能强大，可以简化

18、数据处理。游标变量的功能强大，可以简化数据处理。u游标变量的优点有：游标变量的优点有：可从不同的 SELECT 语句中提取结果集可以作为过程的参数进行传递可以引用游标的所有属性可以进行赋值运算u使用游标变量的限制：使用游标变量的限制：不能在程序包中声明游标变量FOR UPDATE子句不能与游标变量一起使用不能使用比较运算符22Inspur Education 使用游标变量执行动态使用游标变量执行动态 SQLSQLu可以使用游标变量执行动态构造的可以使用游标变量执行动态构造的 SQL SQL 语句语句u打开执行动态打开执行动态 SQL SQL 的游标变量的语如下：的游标变量的语如下：OPEN c

19、ursor_name FOR dynamic_sqlstringOPEN cursor_name FOR dynamic_sqlstring USING bind_argument_list;USING bind_argument_list;DECLARE r_emp emp%ROWTYPE;TYPE c_type IS REF CURSOR;cur c_type;p_salary NUMBER;BEGIN p_salary:=2500;OPEN cur FOR select*from emp where sal:1 order by sal desc USING p_salary;DBMS_OUTPUT.PUT_LINE(薪水大于|p_salary|的员工有：);LOOP FETCH cur INTO r_emp;EXIT WHEN cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(编号：|r_emp.empno|姓名：|r_emp.ename|薪水：|r_emp.sal);END LOOP;CLOSE cur;END;23Inspur Education 总结总结u掌握并使用游标管理技巧掌握并使用游标管理技巧 隐式游标(掌握)显式游标(掌握)Ref游标(了解)24Inspur GroupInspur Education 谢谢谢谢!25