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;