游标的定义:
1.显示游标 普通显示游标
带参数:
CURSOR c(pi_month varchar2,file_type varchar2) IS
SELECT item_id,item_title
FROM item
where month_id = pi_month;
不带参数:
CURSOR c IS
SELECT item_id,item_title
FROM item;
2.隐式游标
2.1 select into 隐式游标
select tname into l_tname from tab where rownum
=
1
;
2.2 for .. in 隐式游标
begin
for c in ( select tname from tab ) loop
dbms_output . put_line ( c . tname );
end loop ;
end ;
for c in ( select tname from tab ) loop
dbms_output . put_line ( c . tname );
end loop ;
end ;
游标使用语法:
open c ;
loop
fetch c into l_tname ;
exit when c%notfound ;
dbms_output.put_line(l_tname);
end loop;
close c;
游标的数据取法:
优点:
不需要去逐个定义游标内的数据的各个字段
CURSOR TEST IS
SELECT item_id,item_title
FROM item;
V_CURSOR TEST%ROWTYPE;
open TEST
;
loop
fetch TEST into
V_CURSOR ;
exit when c%notfound
;
dbms_output.put_line(
V_CURSOR.ITEM_ID)
;
dbms_output.put_line(
V_CURSOR.ITEM_TITLE)
;
end loop;
close TEST;