Oracle学习笔记二十：游标的简介和使用

一、游标概念

• 在 PL/SQL 块执行 SELECT/INSERT/UPDATE/DELETE 语句时，Oracle 会在内存中为其分配上下文区，而游标是指向该区域的指针。

• 游标为应用程序提供了一种对具有多行数据查询结果集中的每一行单独处理的方案，是设计交互式应用程序的编程接口。

二、游标优点

• 游标允许应用程序对查询语句返回的行结果集中的每一行进行相同或者不同的操作。

• 游标提供了对基于游标位置而对表中数据行进行修改或者更新的能力。

• 游标把作为面向集合数据库管理系统和面向行的程序设计两者联系起来，使两个不同的数据处理方式能够进行沟通。

三、游标分类

1）隐式游标

　　• 非用户显式声明的游标。

　　• PL/SQL为所有的DML语句都自动声明了隐式游标，如 SELECT INTO语句、INSERT语句、DELETE语句和UPDATE语句。

2）显式游标

　　• 是由用户声明和操作的游标。

3）Ref游标

　　• 用于处理运行时才确定的动态查询语句的结果。

四、游标使用

4.1、显示游标

1）显示游标的使用步骤：

　　• 定义游标

　　• 打开游标

　　• 使用游标

　　• 关闭游标

2）定义显示游标的语法：

　　• 定义显式游标在 PL/SQL 语句块 DECLARE 中完成：

　　cursor cursor_name [(参数名 参数数据类型,……)] [return 数据类型]

　　　　is 查询名句

3）打开游标

　　• open 游标名（输入变量列表）

　　• 打开游标语句执行与游标关联的查询，绑定输入变量，识别活动集并将游标指针置于第一行。

4）取出数据

　　• fetch 游标名 into 变量名

5）游标使用完毕之后需要关闭

　　• close 游标名

declare
    cursor c_emp is select empno,ename from emp;
    v_empno emp.empno%type;
    v_ename emp.ename%type;
begin
    if not c_emp %isopen then
        open c_emp;
    end if;
    --抓取游标中的第一条记录
    fetch c_emp into v_empno,v_ename;
    dbms_output.put_line(v_empno || '-' || v_ename);
    --游标使用完毕后关闭游标
    close c_emp;
end;

4.2、游标属性

用户可以通过检查游标属性来确定游标的当前状态：

　　• %FOUND

　　　　• FETCH语句成功返回时，则%FOUND的值为TRUE。

　　• %NOTFOUND

　　　　• FETCH语句未能提取行时，则%NOTFOUND的值为TRUE。

　　• %ISOPEN

　　　　• 如果游标已经打开，则返回TRUE，否则返回FALSE。

　　• %ROWCOUNT

　　　　• 返回目前为止游标提取的行数，%ROWCOUNT为数值型属性。在第一次获取之前，%ROWCOUNT为0。当FETCH语句返回一行时，则该数加1。

4.3、遍历游标

循环取出游标中的值：

declare
    cursor c_emp is select empno,ename from emp;
    v_empno emp.empno%type;
    v_ename emp.ename%type;
begin
    if not c_emp %isopen then
        open c_emp;
    end if;
    
    loop
        fetch c_emp into v_empno,v_ename;
        exit when c_emp %notfound;
        dbms_output.put_line(v_empno || '-' || v_ename);
    end loop;
    close c_emp;
end;

4.4、游标循环

• 为了简化游标的应用，Oracle提供了游标的 for 循环语句。

• 工作原理：

　　• 游标的for循环可以隐式地 open，fetch，close 游标以及循环处理结果集。

declare
    cursor c_emp is select empno,ename from emp;
begin
    for emp in c_emp loop
        dbms_output.put_line(emp.empno || '-' || emp.ename);
    end loop;
end;