游標按以下操作進行
parse 解析
bind 綁定
open 打開
execute 執行
fetch 回取
close 關閉
1.寫自己第一個游標PL/SQL
declare
cursor c_s is select * from user_tables;
begin
open c_s; --打開游標
close c_s;--關閉游標
end;
游標的4個屬性 %found,%notfound,%rowcount,%isopen
1.%found 游標有記錄則返回true否則false
declare
cursor c_s is select * from user_tables;
cc c_s%rowtype;
begin
open c_s; --打開游標
fetch c_s into cc;
while c_s%found loop
fetch c_s into cc;
end loop;
close c_s;--關閉游標
end;
2.%notfound 游標沒記錄則返回true否則false(個人感覺有點多余)
declare
cursor c_s is select * from user_tables;
cc c_s%rowtype;//游標變量
begin
open c_s; --打開游標
fetch c_s into cc;
while c_s%found loop
exit when c_s%notfound;
end loop;
close c_s;--關閉游標
end;
3.%rowcount 返回游標取回的記錄數目
declare
cursor c_s is select * from user_tables;
cc c_s%rowtype;
begin
open c_s; --打開游標
fetch c_s into cc;
while c_s%found loop
dbms_output.put_line(c_s%rowcount);
end loop;
close c_s;--關閉游標
end;
4.%isopen 如果游標打開就返回true 否則false
declare
cursor c_s is select * from user_tables;
begin
if c_s%isopen then
dbms_output.put_line('cursor is open');
else
open c_s; --打開游標
end if;
close c_s;--關閉游標
end;
游標參數
declare
cursor c_s(cs_id number) is select * from admin id=cs_id;
begin
open c_s(10); --打開帶參數的游標
close c_s;--關閉游標
end;
游標中的for update
declare
cursor c_s is select id from admin
for update of id //查詢時鎖定 id列
begin
open c_s;
commit;//提交釋放鎖 或者可以用 rollback
close c_s;
end;
游標中的where cursor of
UPDATE table_name SET set_clause WHERE CURSOR OF cursor_name; //更新游標所指向的那條記錄
DELETE FROM table_name WHERE CURSOR OF cursor_name; //刪除游標所指向的那條記錄
游標中的ref cursor類型
TYPE c_s IS REF CURSOR RETRUN table%rowtype; //這種形式為強類型 在聲明的時候就定了為行類型
TYPE c_s IS REF CURSOR;//弱類型 不與記錄的數據類型關聯
例子:
declare
TYPE c_s IS REF CURSOR RETRUN table%rowtype;
TYPE c_s2 IS REF CURSOR;
var_cs c_s;//聲明為游標變量的類型
begin
OPEN c_s FOR select * from admin;
CLOSE c_s;
end;
