游标用于一次访问一组记录,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;