Oracle 学习笔记11 —— 游标

在通过select语句检索结果时，返回的结果通常是多行记录组成的集合。但是程序设计语言并不能处理集合形式的数据。sql提供了游标来实现实现对集合数据的处理。

游标的作用相当于指针，通过游标程序设计语言可以一次处理查询结果集中的一行。在Oracle中，游标分为两大类：静态游标和REF游标。静态游标又可以分为显式游标和隐式游标。REF游标是一种引用类型，类似于指针。

一、隐式游标

在执行一个sql语句时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域，其中存储了执行sql语句的结果。通过游标的属性可获知sql语句的执行结果以及该游标的状态信息。
游标的主要类型如下。
%FOUND  布尔型属性，如果sql语句至少影响到一行数据，则该属性为true，否则为false。
%NOTFOUND  布尔型属性，与%FOUND相反。
%ISOPEN  布尔型属性，当游标以及打开时返回true，游标关闭时发挥false。
%ROWCOUNT  数字类型，返回受sql影响的行数。

如果执行了一个select语句，则可以通过SQL%ROWCOUNT来检查受影响的行数，还可以通过检查SQL%FOUND属性值来判断sql语句是否检索到数据。当使用隐式游标的属性时，需要在属性前加上SQL。因为Oracle在创建隐式游标时，默认的游标名为SQL。

下边的例子更新emp表，并通过游标查看被更新的记录数。

SQL> set serveroutput onSQL> begin  2    update emp  3    set sal = sal + 101  4    where empno = '7900';  5  6    if sql%found then  7      dbms_output.put_line('更新了 ' || sql%rowcount || ' 条记录。');  8    else  9      dbms_output.put_line('未更新记录'); 10    end if; 11  end; 12  /更新了 1 条记录。PL/SQL 过程已成功完成。

SQL> set serveroutput onSQL> declare  2    employee_row emp%rowtype;  3  begin  4    update emp set sal = sal + 101  5    where job = 'CLERK';  6  7    dbms_output.put_line('更新了 ' || sql%rowcount || ' 条记录。');  8  9    select * 10    into employee_row 11    from emp 12    where empno = '7369'; 13 14    dbms_output.put_line('检索到 ' || sql%rowcount || ' 条记录。'); 15 16  end; 17  /更新了 4 条记录。检索到 1 条记录。PL/SQL 过程已成功完成。

上面的update语句更新了4条记录，因此游标sql%rowcount 为4。select语句检索到一条记录，因此第二个sql%rowcount游标值为1。
当要处理sql语句返回的结果集时，就需要使用cursor for loop 语句通过隐式游标进行处理。下面的例子使用cursor for loop语句循环输出检索到的员工信息。

SQL> set serveroutput on;SQL> begin  2    for employee in (select empno, ename, job, sal  3                 from emp where deptno = '20')  4    loop  5      dbms_output.put('员工编号：' || employee.empno);  6      dbms_output.put('姓名：' || employee.ename);  7      dbms_output.put('职位：' || employee.job);  8      dbms_output.put_line('薪水：' || employee.sal);  9    end loop; 10  end; 11  /员工编号：7369姓名：SMITH职位：CLERK薪水：901员工编号：7566姓名：JONES职位：MANAGER薪水：2975员工编号：7788姓名：SCOTT职位：ANALYST薪水：3000员工编号：7876姓名：ADAMS职位：CLERK薪水：1201员工编号：7902姓名：FORD职位：ANALYST薪水：3000PL/SQL 过程已成功完成。

二、显式游标
在PL/SQL程序中处理结果集时，用户也可以通过显示定义游标，然后手动操作该游标处理结果集。使用显示游标处理数据需要4个步骤：定义游标、打开游标、提取游标和关闭游标。

1、定义游标

cursor cursor_name[(parameter [,parameter])][return return_type]is select_statement;

其中，cursor_name参数表示游标的名称。return_type表示返回值类型。select_statement表示游标将要包括的结果集。parameter参数作为游标的输入参数，它允许用户在打开游标时向游标传递值。parameter 参数的形式如下：

parameter_name [in] datatype [{:= | default} expression]

需要注意的是，在指定数据类型时，不能使用长度约束。如number(4)、varchar(10)等都是错误的。
2、打开游标
要使用定义好的 游标，用户还必须显式地打开游标。打开游标的语法格式如下：

open cursor_name[(value, [value]...)]

3、提取游标数据
提取游标中的数据就是将检索到的结果集中的数据保存到变量中，以便在程序中进行处理。

fetch cursor_name into {variable_list | record_variable}

4、关闭游标
使用完游标后，用户必须显式关闭游标，释放select语句的查询结果。关闭游标所使用的close语句形式如下：

close cursor_name;

SQL> set serveroutput onSQL> declare  2  cursor emp_cursor(department in number default 20)  3  is select empno, ename, job, sal  4     from emp  5     where deptno = department;  6  type employee is record(  7    id emp.empno%type,  8    name emp.ename%type,  9    job emp.job%type, 10    sal emp.sal%type); 11    emp_row employee; 12  begin 13    open emp_cursor(30); 14 15    fetch emp_cursor into emp_row; 16 17    while emp_cursor%found loop 18      dbms_output.put('员工编号：' || emp_row.id); 19      dbms_output.put('姓名：' || emp_row.name); 20      dbms_output.put('职位：' || emp_row.job); 21      dbms_output.put_line('薪水：' || emp_row.sal); 22      fetch emp_cursor into emp_row; 23    end loop; 24 25     close emp_cursor; 26 27  end; 28  /员工编号：7499姓名：ALLEN职位：SALESMAN薪水：1600员工编号：7521姓名：WARD职位：SALESMAN薪水：1250员工编号：7654姓名：MARTIN职位：SALESMAN薪水：1250员工编号：7698姓名：BLAKE职位：MANAGER薪水：2850员工编号：7844姓名：TURNER职位：SALESMAN薪水：1500员工编号：7900姓名：JAMES职位：CLERK薪水：950PL/SQL 过程已成功完成。

三、游标for游标
游标for循环是显式游标的一种快捷使用方式，它使用for循环依法读取结果集中的数据。当for循环开始时，游标会自动打开（不需要使用open方法），没循环一次系统就会自动读取游标当前行的数据（不需要使用fetch），当退出for循环时，游标被自动关闭（不需要使用close）。

for循环的语法如下：

for cursor_record id cursor_name loop  statements;end loop;

下面的实例使用游标for循环实现查询emp表中的数据。

SQL> set serveroutput onSQL> declare  2    cursor emp_cursor is  3      select * from emp  4      where deptno = 10;  5  begin  6    for emp_row in emp_cursor loop  7      dbms_output.put('员工编号：' || emp_row.empno);  8      dbms_output.put('姓名：' || emp_row.ename);  9      dbms_output.put('职位：' || emp_row.job); 10      dbms_output.put_line('薪水：' || emp_row.sal); 11    end loop; 12  end; 13  /员工编号：7782姓名：CLARK职位：MANAGER薪水：2450员工编号：7839姓名：KING职位：PRESIDENT薪水：5000员工编号：7934姓名：MILLER职位：CLERK薪水：1300PL/SQL 过程已成功完成。

使用游标for循环时，一定不要使用open语句，fetch语句和close语句，否则将产生错误。