游标

• 2021 年 12 月 24 日
• 筆記
• Oracle实验

游标

概念

提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

显式游标

不带参数的显式游标

声明一个游标，读取学生的学号。

declare
   cursor my_cursor is
	 select xh from xs;
	 v_xh xs.xh%type;

begin
	
  open my_cursor;
	fetch my_cursor into v_xh;
	dbms_output.put_line('学号:'||v_xh);
	dbms_output.put_line(my_cursor%rowcount);
	close my_cursor;

   exception
		 when others then 
			 dbms_output.put_line(sqlcode||sqlerrm);
end;  


学号:061101
1

带参数的显式游标

declare 

   varid number;
	 varname varchar2(50);
	 cursor mycur(v_xb xs.xb%type) is 
	 select xh,xm from xs
	 where xb=v_xb;
begin
	
   open mycur('男');
	 fetch mycur into varid,varname;
	 dbms_output.put_line('学生编号:'||varid||'学生姓名:'||varname);
	 close mycur;

end;

学生编号:61101学生姓名:王林

游标的属性

declare 
   cursor c_1 is select * from xs;
	 v_1 c_1%rowtype;
	  
begin
   open c_1;
	 fetch c_1 into v_1;
	 dbms_output.put_line('学号:'||v_1.xh||'姓名:'||v_1.xm||'总学分:'||v_1.zxf);
	 fetch c_1 into v_1;
	 dbms_output.put_line('当前游标指向第'||c_1%rowcount||'行');		 
	 close c_1;
end;

学号:061101姓名:王林总学分:50
当前游标指向第2行

游标遍历

使用游标分别遍历xs表中的xh,zxf

declare 
   v_xh varchar2(6);
   v_zxf number(2);
	 cursor xs_cur3 is select  xh,zxf from xs;
begin
	open xs_cur3;
	fetch xs_cur3 into v_xh,v_zxf;
	while xs_cur3%found
		loop
			dbms_output.put_line('学号:'||v_xh||'总学分'||v_zxf);
			fetch xs_cur3 into v_xh,v_zxf;
		end loop;
   close xs_cur3;
end;


学号:061101总学分50
学号:101112总学分36
学号:001总学分45
学号:121112总学分36
学号:007总学分36
学号:007总学分55

利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资；若平均工资大于2000,则输出“该部门平均工资较高”

declare 

  cursor c_dept_stat is select deptno,avg(sal) avgsal
	from scott.emp group by deptno;
	
	v_dept c_dept_stat%rowtype;
begin
	open c_dept_stat;
	fetch c_dept_stat into v_dept;
	while c_dept_stat%found
		loop
			dbms_output.put_line('部门号:'||v_dept.deptno||'--'||'平均工资:'||trunc(v_dept.avgsal,1));
			if(v_dept.avgsal >= 2000) then 
			  dbms_output.put_line(v_dept.deptno||'号部门工资较高');
				end if;
			fetch c_dept_stat into v_dept;
		end loop;
end;


部门号:30--平均工资:1566.6
部门号:20--平均工资:2175
20号部门工资较高
部门号:10--平均工资:2916.6
10号部门工资较高

利用FOR循环统计并输出各个部门的平均工资

declare 
   cursor c_1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
	 
	 v_dept c_1%rowtype;
begin
	for v_dept in c_1
	loop
		dbms_output.put_line('部门号:'||v_dept.deptno||'--'||'平均工资:'||v_dept.avgsal);
	end loop;

end;

部门号:30--平均工资:1566.666666666666666666666666666666666667
部门号:20--平均工资:2175
部门号:10--平均工资:2916.666666666666666666666666666666666667

带update 的游标

Scott.emp表，利用游标，给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新”。

declare

  v_empno scott.emp.empno%type;
	v_sal scott.emp.sal%type;
	cursor c_cursor is select empno,sal from scott.emp where sal <1200 for update;
begin
	open c_cursor;
	loop
		fetch c_cursor into v_empno,v_sal;
		exit when c_cursor%notfound;
		 update scott.emp set sal=sal+50 where current of c_cursor;-- 前面sal<1200
		 dbms_output.put_line('编码为'||v_empno||'工资已更新!!');
		 DBMS_output.put_line('记录数:'||c_cursor%rowcount);
	end loop;
	close c_cursor;

end;

编码为7369工资已更新!!
记录数:1
编码为7876工资已更新!!
记录数:2
编码为7900工资已更新!!
记录数:3

修改scott.emp表员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。

declare 
    cursor c_emp is select * from scott.emp for update;
     v_zl number;
		 v_emp c_emp%rowtype;
begin
	for v_emp in c_emp loop
		case v_emp.deptno
			when 10 then v_zl:=100;
			when 20 then v_zl:=150;
			when 30 then v_zl:=200;
			else v_zl:=250;
			end case;
			update scott.emp set sal= sal+v_zl where current of c_emp;
	end loop;
end;