oracle-游标

参考：

http://blog.youkuaiyun.com/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;