Oracle数据库游标管理.ppt

2回顾q PL/SQL 是一种可移植的高性能事务处理语言 q PL/SQL 引擎驻留在 Oracle 服务器中q PL/SQL 块由声明部分、可执行部分和异常处理部分组成 q PL/SQL 支持的数据类型包括标量数据类型、LOB 数据类型和属性类型 q 控制结构包括条件控制、循环控制和顺序控制q PL/SQL 支持动态 SQLq 运行时出现的错误叫做异常q 异常可以分为预定义异常和用户定义的异常3目标q掌握游标管理技巧 4游标简介 2-1Oracle 服务器服务器执行 PL/SQL 程序内存单元保存到游标中一次处理一行检索行提取行5游标简介 2-2q逐行处理查询结果，以编程的方式访问数据q游标的类型：隐式游标REF 游标显式游标在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标。 显式游标用于处理返回多行的查询。REF 游标用于处理运行时才能确定的动态 SQL 查询的结果游标类型6隐式游标 4-1q在PL/SQL中使用DML语句时自动创建隐式游标q隐式游标自动声明、打开和关闭，其名为 SQLq通过检查隐式游标的属性可以获得最近执行的DML 语句的信息q隐式游标的属性有：q%FOUND SQL 语句影响了一行或多行时为 TRUEq%NOTFOUND SQL 语句没有影响任何行时为TRUEq%ROWCOUNT SQL 语句影响的行数q%ISOPEN - 游标是否打开，始终为FALSE7隐式游标 4-2SQL SET SERVEROUTPUT ONSQL BEGINUPDATE toys SET toyprice=270WHERE toyid= P005;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE(表已更新);END IF; END;/只有在 DML 语句影响一行或多行时，才返回 True8隐式游标 4-3SQL SET SERVEROUTPUT ONSQL DECLARE v_TOYID TOYS.ID%type := &TOYID; v_TOYNAME TOYS.NAME%Type := &TOYNAME; BEGIN UPDATE TOYS SET NAME = v_TOYNAME WHERE toyid=v_TOYID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(编号未找到。); ELSEDBMS_OUTPUT.PUT_LINE(表已更新);END IF; END;/如果 DML 语句不影响任何行，则返回 True 9隐式游标 4-4SQL SET SERVEROUTPUT ON SQL BEGINUPDATE vendor_masterSET venname= Rob MathewWHERE vencode=V004;DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT); END; /返回 DML 语句影响的行数10SELECT INTO 语句 2-1SQL SET SERVEROUTPUT ONSQL DECLARE empid VARCHAR2(10);desig VARCHAR2(10); BEGINempid:= &Employeeid;SELECT designation INTO desig FROM employee WHERE empno=empid; EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(职员未找到); END; /如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常11SELECT INTO 语句 2-2SQL SET SERVEROUTPUT ONSQL DECLARE empid VARCHAR2(10); BEGINSELECT empno INTO empid FROM employee; EXCEPTIONWHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(该查询提取多行); END; /如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常12显式游标 2-1q 显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行q 显式游标的操作过程： 数据库打开游标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量 关闭游标 13显式游标 2-2SQLSET SERVER OUTPUT ONSQLDECLARE my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS SELECT toyprice FROM toys WHERE toyprice250; BEGIN OPEN toy_cur; LOOP FETCH toy_cur INTO my_toy_price; EXIT WHEN toy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (TOYPRICE=:玩具单价=：|my_toy_price); END LOOP; CLOSE toy_cur; END;声明游标打开游标提取行关闭游标14带参数的显式游标q声明显式游标时可以带参数以提高灵活性q声明带参数的显式游标的语法如下：CURSOR ( ) 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 designation=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;15使用显式游标更新行 2-1q允许使用游标删除或更新活动集中的行q声明游标时必须使用 SELECT FOR UPDATE语句 CURSOR IS SELECT statement FOR UPDATE;UPDATE SET WHERE CURRENT OF 更新的语法DELETE FROM WHERE CURRENT OF 删除的语法 16使用显式游标更新行 2-2SQL SET SERVEROUTPUT ONSQL DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice100 FOR UPDATE OF toyprice;BEGIN OPEN cur_toy; LOOP FETCH cur_toy INTO new_price; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE cur_toy; COMMIT;END;17循环游标 2-1q循环游标用于简化游标处理代码q当用户需要从游标中提取所有记录时使用q循环游标的语法如下：FOR IN LOOPEND LOOP;18循环游标 2-2SQL SET SERVER OUTPUT ONSQL DECLARE CURSOR mytoy_cur IS SELECT toyid, toyname, toyprice FROM toys; BEGIN FOR toy_rec IN mytoy_cur LOOP DBMS_OUTPUT.PUT_LINE( 玩具编号：| |toy_rec.toyid| |玩具名称：| |toy_rec.toyname| |玩具单价：| |toy_rec.toyprice); END LOOP; END;19REF 游标和游标变量 3-1qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询q创建游标变量需要两个步骤：q声明 REF 游标类型q声明 REF 游标类型的变量q用于声明 REF 游标类型的语法为：TYPE IS REF CURSORRETURN ;20REF 游标和游标变量 3-2TYPE my_curtype IS REF CURSOR RETURN stud_det%ROWTYPE;order_cur my_curtype; 声明强类型的 REF 游标q打开游标变量的语法如下： OPEN cursor_name FOR select_statement;TYPE my_ctype IS REF CURSOR;stud_cur my_ctype;声明弱类型的 REF 游标21REF 游标和游标变量 3-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游标类型声明游标变量22游标变量的优点和限制q游标变量的功能强大，可以简化数据处理。q游标变量的优点有：q可从不同的 SELECT 语句中提取结果集q可以作为过程的参数进行传递q可以引用游标的所有属性q可以进行赋值运算q使用游标变量的限制：q不能在程序包中声明游标变量qFOR UPDATE子句不能与游标变量一起使用q不能使用比较运算符23使用游标变量执行动态 SQLq可以使用游标变量执行动态构造的 SQL 语句q打开执行动态 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;24总结q游标用于处理查询结果集中的数据q游标类型有：隐式游标、显式游标和 REF 游标q隐式游标由 PL/SQL 自动定义、打开和关闭q显式游标用于处理返回多行的查询q显式游标可以删除和更新活动集中的行q要处理结果集中所有记录时，可使用循环游标q在声明 REF 游标时，不需要将 SELECT 语句与 其关联