plsql存过声明游标_plsql编程学习之游标一

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

分类:数据库

评论