第十三章-游标管理.ppt

单击此处编辑母版标题样式,第一级,第二级,第三级,第四级,第五级,*,Click to edit the title text format,2012,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,版权所有：,浪潮优派教育科技有限公司,版权所有：,浪潮优派教育科技有限公司,谢谢大家,!,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,Inspur,Education,版权所有：,浪潮优派教育科技有限公司,版权所有：,浪潮优派教育科技有限公司,谢谢大家,!,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,第十三章 游标管理,知识点回顾,顺序结构控制语句,选择结构控制语句,if,语句,Case,语句,循环结构控制语句,基本,loop,循环,while,循环,for,循环,2,本章目标,掌握并使用游标管理技巧,隐式游标,(,掌握,),显式游标,(,掌握,),Ref,游标,(,了解,),3,游标简介,2-1,Oracle,服务器,执行,PL/SQL,程序,内存单元,保存到游标中,一次处理一行,检索行,提取行,4,游标简介,2-2,逐行处理查询结果，以编程的方式访问数据,游标的类型：,隐式游标,REF,游标,显式游标,在,PL/SQL,程序中执行,DML SQL,语句时自动创建隐式游标。,显式游标用于处理返回多行的查询。,REF,游标用于处理运行时才能确定的动态,SQL,查询的结果,游标类型,5,隐式游标,4-1,在,PL/SQL,中使用,DML,语句时,自动创建隐式游标,隐式游标自动声明、打开和关闭，其名为,SQL,通过检查隐式游标的属性可以获得最近执行的,DML,语句的信息,隐式游标的属性有：,%FOUND SQL,语句影响了一行或多行时为,TRUE,%NOTFOUND SQL,语句没有影响任何行时为,TRUE,%ROWCOUNT SQL,语句影响的行数,%ISOPEN -,游标是否打开，始终为,FALSE,6,隐式游标,4-2,SQL SET SERVEROUTPUT ON,SQL BEGIN,UPDATE t_student SET f_name=,张三丰,WHERE f_id=001;,IF SQL%FOUND THEN,DBMS_OUTPUT.PUT_LINE(,表已更新,);,END IF;,END;,/,只有在,DML,语句影响一行,或多行时，才返回,True,7,隐式游标,4-3,SQL SET SERVEROUTPUT ON,SQL DECLARE,v_id t_student.f_id%type:=,v_name t_student.f_name%Type:=,BEGIN,UPDATE t_student SET f_name=v_name,WHERE f_id=v_id;,IF SQL%NOTFOUND THEN,DBMS_OUTPUT.PUT_LINE(,编号未找到。,);,ELSE,DBMS_OUTPUT.PUT_LINE(,表已更新,);,END IF;,END;,/,如果,DML,语句不影响任何行，则返回,True,8,隐式游标,4-4,SQL 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.put_line(,未找到相应记录,);,end if;,end;,/,返回,DML,语句影响的行数,9,SELECT INTO,语句,2-1,SQL SET SERVEROUTPUT ON,SQL DECLARE,v_id t_course.f_id%type;,v_name t_course.f_name%type;,BEGIN,v_id:=,SELECT f_name INTO v_name,FROM t_course WHERE f_id=v_id;,EXCEPTION,WHEN NO_DATA_FOUND THEN,DBMS_OUTPUT.PUT_LINE(,课程未找到,);,END;,/,如果没有与,SELECT INTO,语句中的条件匹配的行，将引发,NO_DATA_FOUND,异常,10,SELECT INTO,语句,2-2,SQL SET SERVEROUTPUT ON,SQL DECLARE,v_birth t_student.f_birth%type;,BEGIN,SELECT f_birth INTO v_birth FROM t_student;,DBMS_OUTPUT.PUT_LINE(v_birth);,EXCEPTION,WHEN TOO_MANY_ROWS THEN,DBMS_OUTPUT.PUT_LINE(,该查询提取多行,);,END;,/,如果,SELECT INTO,语句返回多个值，,将引发,TOO_MANY_ROWS,异常,11,显式游标,2-1,显式游标在,PL/SQL,块的声明部分定义查询，该查询可以返回多行,显式游标的操作过程：,数据库,打开游标,CS,王五,3,PHY,李四,2,CS,张三,1,F_department,f_name,f_id,提取行,变量,关闭游标,12,显式游标,2-2,SQL,set serveroutput on,declare,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;,/,声明游标,打开游标,提取行,关闭游标,13,带参数的显式游标,声明显式游标时可以带参数以提高灵活性,声明带参数的显式游标的语法如下：,CURSOR(),IS select_statement;,SQL SET SERVEROUTPUT ON,SQL DECLARE,desig VARCHAR2(20);,emp_code VARCHAR2(5);,empnm VARCHAR2(20);,CURSOR emp_cur(desigparam VARCHAR2)IS,SELECT empno,ename FROM employee,WHERE designation=desig;,BEGIN,desig:=,OPEN emp_cur(desig);,LOOP,FETCH emp_cur INTO emp_code,empnm;,EXIT WHEN emp_cur%NOTFOUND;,DBMS_OUTPUT.PUT_LINE(emp_code|empnm);,END LOOP;,CLOSE emp_cur;,END;,14,循环游标,2-1,循环游标用于简化游标处理代码,当用户需要从游标中提取所有记录时使用,循环游标的语法如下：,FOR IN,LOOP,END LOOP;,15,循环游标,2-2,SQL SET SERVER OUTPUT ON,SQL 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 Loop;,end;,/,16,显式游标属性,2-3,显式游标属性值的语法是,cursor_name,，后面紧跟属性（例如，,c1,ISOPEN,）。,显式游标和游标变量具有相同的属性。,%ISOPEN,属性,%FOUND,属性,%NOTFOUND,属性,%ROWCOUNT,属性,17,显式游标属性,2-3,对,记录,进行计数并打印,在获取第,3,行后打印一条消息,DECLARE,CURSOR c1 IS,SELECT last_name FROM employees,WHERE ROWNUM 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,游标类型,声明游标变量,21,游标变量的优点和限制,游标变量的功能强大，可以简化数据处理。,游标变量的优点有：,可从不同的,SELECT,语句中提取结果集,可以作为过程的参数进行传递,可以引用游标的所有属性,可以进行赋值运算,使用游标变量的限制：,不能在程序包中声明游标变量,FOR UPDATE,子句不能与游标变量一起使用,不能使用比较运算符,22,使用游标变量执行动态,SQL,可以使用游标变量执行动态构造的,SQL,语句,打开执行动态,SQL,的游标变量的语如下：,OPEN cursor_name FOR dynamic_sqlstring,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;,23,总结,掌握并使用游标管理技巧,隐式游标,(,掌握,),显式游标,(,掌握,),Ref,游标,(,了解,),24,谢谢,!,25,