学习PL/SQL 之五:游标

目标:
  使用显示游标以及游标属性;
  使用参数游标;
  使用显示游标更新或删除数据;
  使用游标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;         
        

阅读更多
文章标签: table delete reference
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭