ME游标管理

1、游标类型

 （1） 隐式游标：在PL/SQL中执行DML SQL语句自动创建隐式游标

     隐式游标的属性有：

       %FOUND  -- SQL语句影响了一行或多行市为TRUE

       %NOTFOUND  --SQL语句没有影响任何时为TRUE

       %ROWCOUNT  --SQL语句影响的行数

       %ISOPEN--游标是否打，始终为FALSE

   例如：

   SET SERVEROUTPUT ON

   BEGIN

     update toys set tyoprice=270 where toyid='P005'

     if SQL%FOUND then  --只有DML语句影响一行或多行时，才返回true

       DBMS_OUTPUT.PUT_LINE('表已跟新');

     end if;

   END;

  

    SET SERVEROUTPUT ON

    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--只有DML语句不影响任何行时，才返回true

            dbms_output.put_line('bian hao wei zhao dao');

       else

           dbms_output.put_line('biao yi geng xin');

       end if;

    END;

 

    SET SERVEROUTPUT ON

    BEGIN

     update vendor_master

     set venname='Rob Mathew' where vencode='V004';

     dbms_output.put_line(SQL%ROWCOUNT); --返回DML语句影响的行数

    END;

    

  (2)显示游标： 用于处理返回多行的查询

    不带参数的游标

    SET SERVER OUTPUT ON

    DECLARE

       my_toy_price toys.toyprice%type;

      CURSOR toy_cur is

         select toyprice from toys where toyprice<250;

    BEGIN

      OPEN toy_cur;

      LOOP

      FETCH toy_cur INTO my_toy_price;

     EXIT WHEN toy_cur%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE('TOYPRICE=:WANJUDANJIAO=:'||my_toy_price);

     END LOOP;

     CLOSE toy_cur;

    END;

     带参数的游标：

   SET SERVEROUTPUT ON

   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:='&desig';

     OPEN emp_cur(desig);

     LOOP

      FETCH emp_cur INTO emp_code,empnm;

      EXIT WHEN emp_cur%notfound;

      dbms_outpu.put_line(emp_code||' ' || empnm);

     END LOOP;

    CLOSE emp_cur;

   END;

    使用显式游标更新行

  允许使用游标删除或更新活动集中的行

  声明游标时必须使用SELECT ...FOR UPDATE 语句

         CURSOR <curosr_name> IS

       SELECT statement FOR UPDATE; 

 

         UPDATE  <table-name>

          SET <set_name>

        WHERE current of <cursor_name>;

 

       DELETE FROM <table_name>

      WHERE current of <corsor_name>;

例如：

   SET SERVEROUTPUT ON

   DECLARE

     new_price NUMBER;

     CURSOR   cur_toy IS

       SELECT  toyprice from toys where toyprice<100

       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;

 

   循环游标：用于简化游标处理代码

例如：

    SET SERVER OUTPUT ON

    DECLARE

       CURSOR mytoy_cur is

       select toyid,toy_name,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;

 

  (3)REF游标：用于处理运行时才能确定的动态SQL查询的结果(用于处理运行时动态执行的SQL查询）

 

声明强类型的REF游标：

   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;

 

2、使用游标变量执行动态SQL

 

  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;

3.程序包中的游标

Create or replace package cur_pack as

Cursor ord_cur(vcode varchar2)

Return order_master%rowtype;

Procedure ord_pro(vcode varchar2);

 

SQL> CREATE OR REPLACE PACKAGE BODY cur_pack AS

 CURSOR ord_cur(vcode VARCHAR2)

 RETURN order_master%ROWTYPE IS

 SELECT * FROM order_master WHERE VENCODE=vcode;

 PROCEDURE ord_pro(vcode VARCHAR2) IS

   or_rec order_master%ROWTYPE;

 BEGIN

  OPEN ord_cur(vcode);

  LOOP

    FETCH ord_cur INTO or_rec;

    EXIT WHEN ord_cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LIne(’返回的值为' || or_rec.orderno);

  END LOOP;

 END ord_pro;

END cur_pack;

/