Oracle_03_游标(2020年12月)

游标

零基础学Oracle资源地址: 零基础学oracle（百度云盘资源）

本章目标：

游标的概念

游标的类型

通过显示和隐式游标获取数据

---

1.游标概述

1.1 概念

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的作用相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。
在Oracle中，通过游标操作数据主要使用显式游标和隐式游标，另外，还有具有引用类型的ref游标。

游标属性：

• %found：布尔型，最近的FETCH语句返回一行数据则为真，否则为假；
• %notfound：布尔型，与%found属性相反；
• %rowcount：整数，获得FETCH语句返回的行数；
• %isopen：布尔型，游标已经打开时值为真，否则为假；

2.隐式游标

---

在执行一个SQL语句时，Oracle会自动创建一个隐式游标，这个游标是内存中处理该语句的工作区域，隐式游标主是DML语句的执行结果 （参见04文档）

3. 显示游标

---

显式游标由用户自己定义和操作游标，通常所说的游标都是指显式游标。

3.1 使用步骤

声明游标 → 打开游标 → 提取数据 → 关闭游标

declare 
	book_rec book%rowtype; 
	cursor book_cur is 
	select id,title from book; 
begin 
	open book_cur; 
	fetch book_cur into book_rec; 
	close book_cur;
end;

declare 
	book_rec book%rowtype; 
	cursor book_cur is select id,title from book; 
begin 
	open book_cur; 
	fetch book_cur into book_rec;--提取一行记录到变量book_rec 
	while book_cur%found loop 
		dbms_output.put_line(book_rec.id || ' -- ' || book_rec.title); 
		fetch book_cur into book_rec; 
	end loop; 
	close book_cur; 
end;

3.2 使用显示游标修改数据

1. forupdate：行级排它锁锁住影响行

declare 
	book_rec book%rowtype; 
	cursor book_cur is 
	select id,title from book for update; 
begin 
	open book_cur; 
	fetch book_cur into book_rec; 
	while book_cur%found loop 
		dbms_output.put_line(book_rec.id || ' -- ' || book_rec.title); 
		fetch book_cur into book_rec; 
	end loop; 
	close book_cur; 
end;

2. 使用where current of子句修改数据

如果你想删除或者更新被Select For Update引用的记录，你可以使用Where Current Of语句

declare
	book_rec book%rowtype; 
	cursor book_cur is 
	select id, title from book for update; 
begin 
	open book_cur; 
	loop
		fetch book_cur 
			into book_rec; 
		if book_cur%notfound then 
			exit; 
		else
			update book set title=title || '*' where current of book_cur; 			
			dbms_output.put_line(book_rec.id || ' -- ' || book_rec.title); 
		end if; 
	end loop; 
	--commit;
	close book_cur; 
end;

3. 游标FOR循环

declare 
	cursor book_cur is 
	select id, title from book for update; 
beginfor 
	book_rec in book_cur loop 
		dbms_output.put_line(book_rec.id || ' -- ' || book_rec.title); 
	end loop; 
end;

4. 游标变量

declare 
	type book_cur_type is ref cursor; 
	book_cur book_cur_type; 
	book_rec book%rowtype; 
begin 
	open book_cur for 
		select * from book ; 
	loop
		fetch book_cur into book_rec; 
		exit when book_cur%notfound; 
		dbms_output.put_line(book_rec.id || ' -- ' || book_rec.title); 
	end loop; 
end;

5. 游标表达式（嵌套游标）

declare 
	type ref_cur_type is ref cursor;
	emp_cur ref_cur_type; 
	cursor dept_cur(p_deptno number) is 
select a.dname, cursor(select * from emp where deptno = a.dno) 
from dept a where a.dno = p_deptno;
	 v_dname dept.dname%type; 
	 emp_rec emp%rowtype; 
begin 
	open dept_cur(&no); 
	loop
		fetch dept_cur into v_dname,emp_cur; 
		exit when dept_cur%notfound; 
		dbms_output.put_line('部门名称：' || v_dname); 
		loop
			fetch emp_cur into emp_rec; 
			exit when emp_cur%notfound; 
			dbms_output.put_line(' 姓名：' || emp_rec.ename || ' ,工资：'||emp_rec.sal);
		end loop; 
	end loop; 
	close dept_cur; 
end;