?一）游標：
??????????? 1）每次聲明、打開、推進、關閉游標
declare
cursor c is select .........;--聲明
begin
open c;--打開
loop
exit when..........;
fetch c into .........;--推進
......
.....
end loop;
close c;--關閉
end;
???????????? 2）使用游標變量更加簡潔
declare
cursor c is select..........;聲明
begin
for rec_c in c loop????? --打開、推進、關閉 全包括
exit when......
end loop;
end;

???????????? 3)動態游標；

???????????? declare
type c_ref is ref cursor;??? ---------------------聲明動態游標
v_c_ref c_ref;????????????????? -----------------------定義一個動態游標
type rec is record(????????? ------------------------聲明記錄
id?? sun_department.id%type,??????????????????????
department? sun_department.department%type);
v_rec rec;????????????????????????????? --------------------定義一個記錄；
begin
open v_c_ref for????????????????????????? --------------------打開動態游標并付集合值
select id,department from sun_department where id<100;
fetch v_c_ref into v_rec;???????????????????????????????? -------------將游標起到記錄中
while v_c_ref%found loop?????????????????????????????? ----------------------只要游標中有值就一直輸出；
dbms_output.put_line(v_rec.id||'?? '||v_rec.department);
fetch? v_c_ref into v_rec;???????????????????????????????? ---------------------游標的取出值放到記錄中；
end loop;?????????????????????????????????????????????????
close v_c_ref;
end;

?

二）過程

? create or replace? procedure???? test_p(id in number,name? out? varchar2)----參數in? out??? in out

??? is

????? i number:=0;????????????????????????????????? -----------------------------------------------無declare?? 為局部變量；

????? cursor? c is? select? sun.name from sun where sun.id=id;

????? begin???????????????????????????????????????????????

????? for v_c in c???????????????????????????????????? ----------------------------------------------------------游標變量；

???? ?loop

???????? name:=v_c.name;

??????????-- dbms_output.put_line('name'||name);??

????? end loop;

?????? end test_p;?????????????????????????????????????????

三）函數

?????? create? or replace function test_f(id in number,name out varchar2)??

???????? return? ?number? is?????????????????????????????????????? ----------------------------------------------return? 類型

?????????? i?? number:=0;?

??????????? val?? number;

??????????? cursor????c? ?is? select s.id?? ?from sun.department? s?? where id<10 for update of id;

?????????? begin

????????????? open c;

?????????????? loop

?????????????? fetch c into val;

??????????????? if (c%rowcount<>0)then

??????????????? update?? sun_department set?? id:=100? where?? current of? c;

?????????????? else

??????????????????? update?? sun_department set?? id:=1 where current of?? c;

??????????????? end? if;

??????????????????i:=c%rowcount

??????????????? end loop;

end;

?

?

????????? end;

?

四）包

?????
create or replace package pack_test??????? --package specification start--
is
?? type get_test_cursor is ref cursor;????? --定義動態游標
?? function getcur return get_test_cursor;? --函數聲明【函數返回類型為動態游標】
end pack_test;????????????????????????????
/????????????????????????????????????????? --package specification? end--

create or replace package body pack_test?? --package body start --
is
? function getcur return get_test_cursor
? is
??? mycur get_test_cursor;???????????????? --用動態游標定義變量
? begin
??? open mycur???????????????????????????? --打開并與具體數據關聯
??? for select * from? sun_department;
??? return mycur;????????????????????????? --返回游標
? end getcur;
end pack_test;
/????????????????????????????????????????? --package body end --

declare????????????????????????????????? -- package test start --
??? testcur pack_test.get_test_cursor;
??? temp? sun_department%rowtype;??????????????????? --記錄變量定義
? begin
??? testcur := pack_test.getcur();?????? --方法調用
??? loop
????? fetch testcur? into temp;
???????? dbms_output.put_line(temp.id || ' : ' ||? temp.department);
????? exit when testcur%notfound;
??? end loop;
??? close testcur;
end;
/??????????????????????????????????????? -- package test end --

轉載于:https://www.cnblogs.com/sunxiangfu/archive/2008/08/27/4261150.html

總結

以上是生活随笔為你收集整理的游标、过程、函数、包的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。