学习PL/SQL 之五：游标

来源：互联网发布：centos 双网卡驱动编辑：程序博客网时间：2024/05/19 04:29

目标：
使用显示游标以及游标属性；
使用参数游标；
使用显示游标更新或删除数据；
使用游标FOR循环；
使用右边变量；
使用FETCH .. BULK COLLECT INTO 语句 CURSOR表达式。
一、显示游标
专门用于处理SELECT 语句返回的多行数据。
    1、定义游标：CURSOR cursor_name IS select_statement;
    2、打开游标 open cursor_name;
    3、提取数据
       FETCH cursor_name INTO variable1,variable2,...;
       FETCH cursor_name INTO collect1,collect2,...[LIMIT rows];
    4、关闭游标 close cursor_name;
游标属性：
     %OPEN;
     %FOUND;
     %NOTFOUND;
     %ROWCOUNT;
在显示游标中使用FETCH..INTO语句
    declare
       cursor emp_cursor is
         select ename,sal from emp where deptno=10;
       v_ename emp.ename%type;
       v_sal emp.sal%type;
     begin
       open emp_cursor;
       loop
         fecth emp_cursor into v_ename,v_sal;
         exit when emp_cursor%notfound;
         ……
       end loop;
       close emp_cursor;
     end;
使用FECTH ... BULK COLLECT INTO 语句提取所有数据
     declare
       cursor emp_cursor is
         select ename from emp where deptno=10;
       type ename_type_name is table of varchar2(10);
       ename_table ename_type_type;
     begin
       open emp_cursor;
       fecth emp_cursor BULK COLLECT INTO ename_table;
       FOR i in i..emp_cursor.count loop
         ........emp_cursor(i)
       end loop；
       close emp_cursor;
     end;
使用FECTH ... BULK COLLECT INTO..LIMIT 语句提取部分数据
    以每次提取5行数据为例
    declare
       cursor emp_cursor is select ename from emp;
      type emp_varray_type is varray(5) of emp.ename%type;
      rows int:=5;
      v_count:=0;
      emp_varray   emp_varray_type;
    begin
      open      emp_cursor;
      loop
        FETCH emp_cursor BULK COLLECT INTO emp_varray LIMIT rows; --此时游标指针已经指向了rows
        dbms_output.put('雇员：');
        for i in 1..(emp_cursor%rowcount-v_count) loop
           dbms_output.put(emp_cursor(i)||' ');
        end loop;
        v_count:=emp_cursor%rowcount;
        dbms_output.new_line;
        exit when emp_cursor%notfound;
      end loop;
      close emp_cursor;
    end;
基于游标定义记录变量建议使用
    使用%rowcount属性不仅可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名或列别名。
    declare
       cursor emp_cursor is select ename,sal from emp;
      v_emp emp_cursor%rowtype;
    begin
      open      emp_cursor;
      loop
        FETCH emp_cursor INTO v_emp;
        exit when emp_cursor%notfound;
        dbms_output.put('雇员：'||v_emp.ename||'工资是：'||v_emp.sal);

      end loop;
      close emp_cursor;
    end;
参数游标
    CURSOR cursor_name(parameter_name datatype) IS select_statement;
    注意：游标参数只能指定数据类型，而不能指定长度。
使用游标更新或删除数据
   通过使用显示游标，不仅可以一行一行的处理SELECT语句的结果，而且也可以更新或者删除当前游标行的数据。但是在定义游标时必须有FOR UPDATE 字句；
   CURSOR cursor_name(param_type datatype) is select_statement FOR UPDATE [OF column_reference] [NOWAIT];
   FOR UPDATE 字句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作；当SELECT语句引用到多张表时，使用OF字句可以确定哪些表要加锁，
   如果没有OF字句，则会在SELECT语句所引用的全部表上加锁；NOWAIT 字句用于指定不等待锁。在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE
   或DELETE语句中引用WHERE CURRENT OF字句。
   语句如下：
      UPDATE table_name SET column=..WHERE CURRENT OF cursor_name;
      DELETE table_name WHERE CURRENT OF cursor_name;
    使用游标更新数据
        declare
         cursor emp_cursor is select ename,sal from emp FOR UPDATE;
        v_emp emp_cursor%rowtype;
      begin
        open     emp_cursor;
        loop
          FETCH emp_cursor INTO v_emp;
          EXIT WHEN emp_cursor%notfound;
          IF v_emp.sal<2000 THEN
             update emp set sal=sal+1000 WHERE CURRENT OF emp_cursor;
          end if;
        end loop;
        close emp_cursor;
      end;
  使用游标删除数据
   使用OF字句在特定表伤加行共享锁
    CURSOR emp_surspor IS SELECT ename,sal,dname,emp.deptno FORM emp,dept WHERE emp.depno=dept.depno FOR UPDATE OF emp.depno;
   使用NOWAIT 字句
    使用FOR UPDATE 语句对被作用行加锁，如果其他绘画已经在被作用行上加锁，那么在默认情况下当前绘画会要一直等到对方释放锁。使用NOWAIT 如果其他会话已经在被作用行上加锁，则抛异常。

使用游标变量
    1 定义REF CURSOR 类型和游标变量
      TYPE ref_type_name IS REF CURSOR [RETURN return_type];--加return 字句，在打开游标时select 语句的返回结果必须与RETURN 字句所指定的记录类型相匹配。
      cursor_variable ref_type_name;
    2 打开游标
      OPEN cursor_variable FOR select_statement;
    3 提取游标数据
    4 关闭游标变量
      close cursor_variable;

    DECLARE
      TYPE emp_record_type IS RECORD(
         name VARCHAR2(1O),salary NUMBER(6,2)
      );
      TYPE emp_cursor_type IS REF CURSOR RETURN emp_record_type;
      emp_cursor emp_cursor_type;
      emp_record emp_record_type;
    BEGIN
       OPEN emp_cursor FOR SELECT ename,sal FORM emp WHERE depno=20;
       loop
         FECTH emp_cursor INTO emp_record;
         EXIT WHEN emp_cursor%NOTFOUND;
         ......DO SOMETHING
       END LOOP;
       CLOSE   emp_cursor;

    END;
使用CURSOR表达式--结果集中包含游标。
    处理块中更加复杂的基于多张表的关联数据。
    DECLARE
      TYPE refcursor IS REF CURSOR;
      CURSOR dept_cursor(no NUMBER) IS
         SELECT a.dname,CURSOR(SELECT ename,sal FORM emp WHERE deptno=a.deptno)
         FROM dept a WHERE A.DEPTNO=no;
      empcur refcursor;
      v_dname dept.dname%type;
      v_ename emp.ename%type;
      v_sal emp.sal%type;
     BEGIN
       open dept_cursor(&no);
       LOOP
         FETCH   dept_cursor INTO v_dname, empcur;
         EXIT WHEN dept_cursor%NOTFOUND;
         dbms_output.put('部门：');
         open empcur;
         LOOP
            FETCH empcur INTO v_ename,v_sal;
            EXIT WHEN empcur%NOTFOUND;
            dbms_output.put('雇员名：'||v_ename||'工资:'||v_sal);
         END LOOP;
         CLOSE empcur;
       END LOOP;
     CLOSE dept_cursor;
   END;
-----------------------------
DECLARE
TYPE order_items_cursor IS REF CURSOR;
CURSOR order_cursor(ID NUMBER) IS SELECT o.id,
     CURSOR(SELECT oi.id,oi,total,oi,pro_name FROM order_item oi where oi.orderId=o.id) from orders o ;
order_itens_ref order_items_cursor ;
v_oid orders.id%type;
v_ooid order_item.id%type;
v_total order_item.total%type;
v_name order_item.pro_name%type;
BEGIN
OPEN order_cursor(&orderid);
LOOP
    FETCH order_cursor INTO v_oid,order_itens_ref;
    EXIT WHEN order_cursor%NOTFOUND;
     dbms_output.put_line('订单号：'||v_oid);
     OPEN order_itens_ref;
     LOOP
       FETCH order_itens_ref INTO v_ooid, v_total,v_name;
       EXIT WHEN order_itens_ref%NOTFOUND;
         dbms_output.put_line('条款号：'||v_ooid||'总价'||v_total||'商品名：'||v_name);
     END LOOP;
     CLOSE order_itens_ref;
END LOOP;
CLOSE order_cursor;
END;