oralce plsql編程的游標

游標分類

1顯示游標

2隱式游標

隱式游標，oracle自動管理，不用聲明，打開和關閉，ORACLE自動處理，使用隱式游標%FOUND時，需要加上 SQL%FOUND

顯示游標，需要自己聲明，打開和關閉，使用%ROWCOUNT屬性時，需要在前面加上游標名字 ,student_cur%ROWCOUNT

2聲明游標

CURSOR cursor_name is select_statments;

打開游標

open cursor_name

讀取數據

fetch cursor_name into variable_name,....variable_namen;

關閉游標

close cursor_name;

3游標屬性

%ISOPEN

%FOUND

%NOTFOUND

%ROWCOUNT

4游標讀取數據實例

select * from students;

set serveroutput on;

declare

v_specialty students.specialty%type;

v_sname students.name%type;

v_dob students.dob%type;

cursor students_cur --聲明游標

is

select name ,dob from students where specialty=v_specialty; --游標體

begin

v_specialty:='&specialty';

open students_cur; --打開游標

dbms_output.put_line('學生姓名 出生日期');

loop

fetch students_cur into v_sname,v_dob ; --讀取游標的數據

exit when students_cur%NOTFOUND; --假如沒有數據那么退出

DBMS_OUTPUT.PUT_LINE(v_sname||' '||v_dob);

end loop;

close students_cur; --關閉游標

end;

5根據游標修改當前行數據，語法 update tablename set ....where current of cursor_name;

select * from teachers;

declare

v_title teachers.title%TYPE;

CURSOR teachers_cur

is

select title from teachers for update;

begin

open teachers_cur;

loop

fetch teachers_cur into v_title ;

exit when teachers_cur%NOTFOUND;

case

when v_title='教授' then

update teachers set wage=1.1*wage where current of teachers_cur;

when v_title='高工' or v_title='副教授' then

update teachers set wage=1.1*wage where current of teachers_cur;

else

update teachers set wage=wage+100 where current of teachers_cur;

end case;

end loop;

close teachers_cur;

commit;

end;

6根據游標刪除當前數據 delete from table where current of cursor_name;

select * from students;

declare

v_specialty students.specialty%TYPE;

v_sname students.name%TYPE;

CURSOR students_cur

is

select name,specialty from students for update;

begin

open students_cur;

fetch students_cur into v_sname, v_specialty ;

while students_cur%FOUND loop

if v_specialty ='計算機' THEN

delete from students where current of students_cur;

end if;

fetch students_cur into v_sname ,v_specialty;

end loop;

close students_cur;

end;

分享到：

2011-04-12 20:39

瀏覽 2631

分類:數據庫

評論