游標

• 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;