oracle-游标

参考:

http://blog.csdn.net/whhitgen/article/details/12511395

1、使用游标for循环

-- Created on 2015/7/31 by ADMINISTRATOR 
declare
  CURSOR cust_cursor IS
    SELECT id FROM t_cust_customers;
begin
  FOR cust_record IN cust_cursor LOOP
    dbms_output.put_line('第'||cust_cursor%ROWCOUNT||'个客户:'||cust_record.id);
  END LOOP;
end;

 2、在使用游标时直接使用子查询

-- Created on 2015/7/31 by ADMINISTRATOR 
declare
 
begin
  FOR cust_record IN (SELECT id FROM t_cust_customers) LOOP
    dbms_output.put_line(cust_record.id);
  END LOOP;

end;

 3、bulk into

  

  1. 通过bulk collect减少loop处理的开销   
  2.   
  3. 采用bulk collect可以将查询结果一次性地加载到collections中。   
  4. 而不是通过cursor一条一条地处理。   
  5. 可以在select into,fetch into,returning into语句使用bulk collect。   
  6. 注意在使用bulk collect时,所有的into变量都必须是collections.  
declare
  t_list type_name;
begin
  select distinct (rs.id) bulk collect
    into t_list
    from t_cust_customers rs;
  FOR cust_record IN (select vtable.column_value custid
                        from table(t_list) vtable) LOOP
    dbms_output.put_line(cust_record.custid);
  END LOOP;
end;

相关推荐