130822游标使用样例

游标用于一次访问一组记录，select语句将列提取到游标中，然后根据游标取得记录；

1）声明一些变量，用于保存select语句返回的列值；

2）声明游标，并指定select语句为游标赋值；

3）打开游标；

4）从游标中循环获取记录；

5）关闭游标；

-- Created on 2013/8/22 by CALVIN 
declare 
  -- Local variables here
  v_product_id store.products.product_id%type;
  v_name       store.products.name%type;
  v_price      store.products.price%type;
  
  cursor cv_product_cursor is
  select product_id, name, price from store.products
  order by product_id;
  
begin
  -- Test statements here
  open cv_product_cursor;
  loop 
    fetch cv_product_cursor
    into v_product_id, v_name, v_price;
    exit when cv_product_cursor%notfound;
    
    dbms_output.put_line('v_product_id = ' || v_product_id || ', v_name = ' || v_name || ', v_price = ' || v_price); 
    
  end loop;
  
  close cv_product_cursor;
  
end;

或者用for循环，实现相同功能；

-- Created on 2013/8/22 by CALVIN 
declare 
  -- Local variables here
  cursor cv_product_cursor is
  select product_id, name, price from store.products
  order by product_id;
  
begin
  -- Test statements here
  for v_product in cv_product_cursor loop
    dbms_output.put_line('v_product_id = ' || v_product.product_id || ', v_name = ' || v_product.name || ', v_price = ' || v_product.price); 
  end loop;

end;