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;
plsql编程学习之游标一
最新推荐文章于 2023-03-03 18:40:05 发布