Oracle 数据库游标

一、游标

• SQL语言是面向集合的是对指定列的操作。如果要对列中的指定行进行操作就必须使用游标。
• 当在PL/SQL块中执行查询语句SELECT和数据操纵语句DML时Oracle会为其分配一个上下文区Context Area
• 游标是指向上下文区的指针它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

1.1 游标显示游标

游标分为显式游标和隐含游标两种

• 隐含游标用于处理 SELECT INTO和DML语句
• 显式游标则用于处理SELECT语句返回的多行数据

1.1.1 使用显示游标

-- 定义游标
CURSOR cursor_name IS select_statement;
-- 打开游标 
OPEN cursor_name;
-- 提取数据 
FETCH cursor_name INTO variable1,variable2,...;
FETCH cursor_name bulk collect into collect1…;
-- 关闭游标 
CLOSE cursor_name;  

1.1.2 显示游标属性

• 显式游标属性用于返回显式游标的执行信息
• 游标属性使用格式为游标名 + 属性名
• %ISOPEN
  • 用于确定游标是否已经打开。如果游标已经打开则返回值为TRUE否则返回FALSE
• %FOUND
  • 检查是否从结果集中提取到数据。如果提取到数据则返回值为TRUE否则返回FALSE
• %NOTFOUND
  • 与%FOUND属性恰好相反如果提取到数据则返回值为FALSE否则返回TRUE
• %ROWCOUNT
  • 返回到当前行为止已经提取到的实际行数

注意

• 显示游标在 PL/SQL 块的声明部分定义查询该查询可以返回多行
• 显示游标的操作过程

数据库 ——> 打开游标——> 得到数据库的内容 ——> 提取行得到变量——> 关闭游标

示例

declare
	type v_dept is table of dept%rowtype  index by binary_integer;
	cursor dept_cursor is select * from dept;
	v_dept_table v_dept;
begin
  	open dept_cursor; -- 将游标中的数据一次性提取到表结构中
  	fetch dept_cursor bulk collect into v_dept_table;
  	close dept_cursor;
  	for i in v_dept_table.first..v_dept_table.last loop
    		dbms_output.put_line(v_dept_table(i).deptno||'  
			'||v_dept_table(i).dname||'  '||v_dept_table(i).loc);
  	end loop;  
end;

1.1.3 游标 参数游标

• 带有参数的游标
• 在定义了参数游标之后当使用了不同参数值多次打开游标时可以生成不同的结果集

语法

-- 创建一个带参数的游标
CURSOR cursor_name(param_name datatype) -- 定义参数游标时游标参数只能指定数据类型而不能指定长度。
 IS select_statement; 
 
-- 查询指定部门的员工信息
declare
	cursor emp_cursor(param_dept  number) is -- 定义游标参数名及类型
	select empno,ename from emp where deptno=param_dept; -- 参数作为条件
	emp_record emp_cursor%rowtype; -- 定义游标集合相同的数据结构
begin
  	open emp_cursor(20);  -- 打开游标并传参
  	loop
    		fetch emp_cursor into emp_record;
    		exit when emp_cursor%notfound;
    		dbms_output.put_line(emp_record.empno||'  '||emp_record.ename);  
  	end loop;
 	close emp_cursor;
end;

1.1.4 游标 游标 for 循环

游标for循环简化游标处理

• 使用游标for循环时oracle隐含打开游标提取数据并关闭游标。

示例

declare
	cursor dept_cursor is select * from dept;
begin
  	for dept_row in dept_cursor loop -- for 循环迭代游标数据
  		dbms_output.put_line('第'||dept_cursor%rowcount|| -- 游标迭代的次数
				'个部门'||dept_row.dname);
  	end loop;
end;

游标for循环简化游标处理

• 当使用游标for循环时可以直接使用子查询。
  示例

-- 获取每个部门的名称
begin
  	for dept_row in(select deptno,dname from dept) loop
  		dbms_output.put_line('第'||dept_dept.deptno||
				'个部门'||dept_row.dname);
  	end loop;
end;

1.1.5 游标游标变量

• 使用显式游标时需要在定义部分指定其所对应的静态SELECT语句
• 使用游标变量时开发人员可以在打开游标变量时指定其所对应的SELECT语句

语法

TYPE  ref_type_name  IS  REF  CURSOR;
cursor_variable  ref_type_name;

-- 游标变量示例
-- 显示部门编号为 10 的所有雇员姓名
declare
	type emp_cursor_type is ref cursor; -- 定义游标变量 cursor 是游标的关键字
	emp_cursor emp_cursor_type;
	v_emp_row emp%rowtype;
begin
  	open emp_cursor for select * from emp where deptno=10; -- 打开游标时用 for 定义游标内容
  	loop
    		fetch emp_cursor into v_emp_row;
    		exit when emp_cursor%notfound;
    		dbms_output.put_line('第'||emp_cursor%rowcount|| -- 用游标变量获取已经循环的行数
				'个员工'||v_emp_row.ename);
  	end loop;
  	close emp_cursor;
end;

1.1.6 游标 隐含游标

当执行一条DML语句或者SELECT…INTO语句时都会创建一个隐含游标 隐含游标的名称是SQL不能对SQL

• 游标显式执行OPEN、FETCH和CLOSE语句。
• Oracle隐式地打开、提取并总是自动地关闭SQL游标
  隐式游标属性包括

1. SQL%FOUND、
2. SQL%NOTFOUND
3. SQL%ROWCOUNT
4. SQL%ISOPEN

declare
	v_empno number(4):=7369;
begin
  	delete from emp where empno=v_empno;
  	if sql%found then
    		dbms_output.put_line('存在该员工');
  	else
    		dbms_output.put_line('不存在该员工');
  	end if;
end;

1.2 总结

定义游标
CURSOR cursor_name IS select_statement;
打开游标 
OPEN cursor_name;
提取数据 
FETCH cursor_name INTO variable1,variable2,...;
关闭游标 
CLOSE cursor_name;