PL/SQL游标详解

在PL/SQL中，针对多行多列的数据类型，可以使用游标变量。当在PL/SQL中执行SELECT, INSERT, UPDATE, DELETE语句时，Oracle服务器会为这些语句分配相应的上下文区域(Context Area)，Oracle使用该上下文区域解析和处理相应的SQL语句。PL/SQL游标就是指向该上下文区域的指针，通过该指针，开发者可以获取语句的执行状态，还可以对查询语句返回的多条记录进行逐条的访问和处理。

1、PL/SQL游标的基本操作

游标包括两种游标:隐含游标和显式游标。隐含游标用于处理SELECT INTO和DML语句，而显式游标则专门用于处理SELECT语句返回的多行数据。

游标的基本操作有:声明游标、打开游标、提取游标和关闭游标。

下面的代码块展示了这四个基本操作：

DECLARE

    CURSOR c_emp IS SELECT * FROM EMP; --声明游标

    emp_record emp%ROWTYPE;BEGIN

    OPEN c_emp; --打开游标    LOOP

        FETCH c_emp INTO emp_record; --提取游标

        DBMS_OUTPUT.PUT_LINE('雇员名称:'||emp_record.ename);

    EXIT WHEN c_emp%NOTFOUND;

    END LOOP;

    CLOSE c_emp; --关闭游标END;/

2、PL/SQL游标属性

游标作为一个临时表，可以通过游标的属性来获取游标状态。游标有4个常用的属性:

1）%ISOPEN:用于判断游标是否已经打开

2）%FOUND:用于判断游标是否找到记录

3）%NOTFOUND:与%FOUND相反.

4）%ROWCOUNT:返回到当前为止已经提取到的实际行数.

3、参数化游标

参数化游标是指带有参数的游标，在定义了参数游标之后，当使用不同的参数值多次打开游标时，可以生成不同的结果集。参数化游标的声明语法如下:

CURSOR cursor_name(parameter) IS SELECE ...

Example:参数化游标的使用

DECLARE

    CURSOR emp_cursor(dno NUMBER) IS

    SELECT ename FROM emp WHERE deptno=dno;

    v_ename emp.ename%TYPE;BEGIN

    OPEN emp_cursor(10);

    LOOP

        FETCH emp_cursor INTO v_ename;

        EXIT WHEN emp_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_ename);

    END LOOP;

    CLOSE emp_cursor;END;/

4、隐式游标就是指非PL/SQL程序中定义的,而且是在PL/SQL中使用UPDATE、DELETE或SELECT INTO语句时，Oracle系统自动分配的游标。隐式游标名称固定为SQL。隐式游标无须声明和打开，使用完后也不用关闭，所有这一切都由系统自动维护。

Example:SELECT INTO的隐式游标

 

DECLARE

    v_emp emp%ROWTYPE;BEGIN

    SELECT * INTO v_emp FROM emp WHERE empno=7788;

    IF SQL%FOUND THEN

        DBMS_OUTPUT.PUT_LINE('7788的雇员名称：'||v_emp.ename);

    END IF;END;/

Example:UPDATE的隐式游标

BEGIN

    UPDATE emp SET ename='SCOTT' WHERE empno=7788;

    IF SQL%FOUND THEN

        DBMS_OUTPUT.PUT_LINE('更新成功！');

    END IF;END;/

5、使用PL/SQL游标更新或删除数据

通过使用显式游标，不仅可以一行一行地处理SELECT语句的结果，而且也可以更新或删除当前游标行的数据。在使用游标更新或删除数据时有两点需要注意的地方：

声明游标是必须带有FOR UPDATE子句，如：

CURSOR cursor_name IS SELECT ... FOR UPDATE

在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。如:

UPDATE table_name SET column=... WHERE CURRENT OF cursor_name;DELETE table_name WHERE CURRENT OF cursor_name;

Example:使用游标更新数据

DECLARE

    CURSOR emp_cursor IS SELECT ename,sal FROM emp FOR UPDATE;

    v_ename emp.ename%TYPE;

    v_sal emp.sal%TYPE;BEGIN

    OPEN emp_cursor;

    LOOP

        FETCH emp_cursor INTO v_ename,v_sal;

        EXIT WHEN emp_cursor%NOTFOUND;

        IF v_sal<2000 THEN

            UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;

        END IF;

    END LOOP;

    CLOSE emp_cursor;END;/

6、PL/SQL游标FOR循环

当使用游标FOR循环时，Oracle会隐含地打开游标、提取游标数据并关闭游标。

Example：使用游标FOR循环

DECLARE

    CURSOR emp_cursor IS SELECT * FROM emp;

    v_emp emp%ROWTYPE;BEGIN

    FOR v_emp IN emp_cursor LOOP

        DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT||'个雇员'||v_emp.ename);

    END LOOP;END;/

Example: 在游标FOR循环中直接使用子查询

DECLARE

    v_emp emp%ROWTYPE;BEGIN

    FOR v_emp IN (SELECT * FROM EMP) LOOP

        DBMS_OUTPUT.PUT_LINE('编号'||v_emp.empno||'的雇员名称：'||v_emp.ename);

    END LOOP;END;/

7、PL/SQL游标变量

上面提到的显式游标和隐式游标都与固定的查询语句相关联，所以称之为静态游标。游标变量与静态游标不同，它是一种动态游标，在运行期间可以与不同的查询语句相关联。

要声明游标变量，首先得创建一个游标数据类型，创建游标数据类型的语法如下：

TYPE cursor_data_type_name IS REF CURSOR [RETURN return_type];

 Example:操作游标变量

DECLARE

    TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;

    emp_cursor emp_cursor_type;

    emp_record emp%ROWTYPE;BEGIN

    IF NOT emp_cursor%ISOPEN THEN

        OPEN emp_cursor FOR SELECT * FROM emp WHERE deptno=10;

    END IF;

    LOOP

        FETCH emp_cursor INTO emp_record;

        EXIT WHEN emp_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('雇员名：'||emp_record.ename);

    END LOOP;END;/

本文非常全面的介绍了PL/SQL游标的各种操作，帮助我们有效的掌握PL/SQL游标的相关知识。在本站的PL/SQL教程中还涉及到了其他的PL/SQL的专业知识，帮助我们开拓新的知识体系，展开对新知识和新事物的探究和学习。