MySQL游标

返回主页

回到顶端

目录

使用游标

一、什么是游标a>

二、使用显式游标

    2.1 声明游标

    2.2 打开游标

    2.3 从游标中取得结果

    2.4 关闭游标

    2.5 使用BULK COLLECT子句批量绑定数据

    2.6 在游标中使用子查询

三、游标属性

    3.1 显式游标属性

---

使用游标

在PL/SQL中可以使用游标处理数据。

通过使用游标可以大大提高PL/SQL程序对数据处理的能力。

在Oracle 9i及其以后的版本中，还增加了使用BULK COLLECT子句批量绑定数据和使用CURSOR表达式实现嵌套游标的功能。

这里将主要介绍如何使用显式游标进行多行数据的查询、游标FOR循环以及游标变量的使用，另外还将介绍游标属性以及嵌套游标的使用等内容。

重点：

❑游标的概念 

❑显式游标的使用方法

❑游标属性以及显式游标和隐式游标属性之间的比较

❑游标循环的使用

❑游标变量的使用

❑嵌套游标的使用

一、什么是游标

在Oracle中，使用SQL语句（例如，SELECT、INSERT、UPDATE、DELETE等）进行查询、修改、插入、删除等操作时，数据库管理系统会在内存中为其分配一个区域，这个区域是一段上下文的缓冲区。

在这段区域中包含了SQL语句处理过程的必要信息。

游标就是指向该段上下文缓冲区中数据的指针。

在PL/SQL中使用游标可以方便地控制上下文缓冲区以及语句处理过程中数据的变化。

如果希望对结果集中的数据进行处理，就需要声明一个指向这个结果集的游标。

通过使用游标，不仅可以查询数据库中的记录，对查询的结果集中的每一行记录执行不同的操作，也可以基于游标的位置对数据表中的记录进行更新操作（例如，修改数据、删除数据等）。

游标有两种类型，一种是隐式游标，一种是显式游标。

对所有的SQL数据操作语句，PL/SQL都会为其声明一个隐式游标。

使用游标可以从含有多条记录的结果集中提取一条数据记录。

当然，也可以使用游标从结果集中返回多条数据记录。

如果希望从结果集中返回多条数据记录，就需要声明一个显式游标，并通过游标FOR循环或者使用BULK COLLECT子句批量绑定数据的方式得到多条数据记录。

二、使用显式游标

在使用SQL查询语句进行查询时，其返回的结果集可能有零行，可能有一行，也可能有多行。

如果SELECT语句返回的是一个含有多行数据记录的结果集，就可以通过声明一个显式游标来处理结果集中的每一行数据。

也可以使用BULK COLLECT子句批量处理多行数据。

使用显式游标需要包括4个过程：声明游标、打开游标、提取数据和关闭游标。

这一节就以上述4个过程为基础，讲解使用显式游标处理数据的方法。

2.1 声明游标

要想使用游标提取数据，首先需要声明一个游标。

声明游标时，需要为游标定义一个名字，并为其指定一个对应的SELECT语句。

声明游标的语法规则如下：

CURSOR cursor_name IS select_statement;

其中，

cursor_name表示游标的名字；

select_statement是一个与cursor_name游标对应的查询语句。

这里的SELECT查询语句中不能含有INTO子句。

游标的声明可以放到PL/SQL语句块、子程序或者包的声明部分。

注意在引用游标之前，必须使用声明游标的语句对其进行声明。

如果在引用之前没有对游标进行声明，则这样的引用就是非法的。

下面来看一个声明游标的例子。

DECLARE

  CURSOR c_result IS                                   -- 声明游标

  SELECT R.stuID,C.curID, C.curName,R.result

  FROM t_result R,T_curriculum C

  WHERE R.curID=C.curID

  ORDER BY R.stuID

这段PL/SQL代码片段中，在DECLARE部分声明了一个游标。

其中，c_result表示游标的名字，IS关键字后面的SELECT语句是：查询学生所选课程的成绩信息。

通过这样一个游标声明，就把c_result游标与查询学生所选课程的成绩信息的SELECT语句相互关联了起来。

游标声明也可以引用PL/SQL的变量。

如果在声明游标时，其对应的SELECT语句中引用了变量，则这些变量被称为绑定变量。

例如，下面的游标声明也是合法的。

DECLARE

  v_ stuID t_student . stuID %TYPE;               -- 学生编号

  CURSOR cursor_student IS           -- 声明游标

  SELECT stuName, age, sex

  FROM t_student

  WHERE stuID = v_ stuID;

这段PL/SQL代码片段中，在DECLARE部分，定义了一个表示学生编号的变量，在声明游标的语句中使用了这个变量。

这里的SQL语句是用来查询学生的基本信息。

这里需要说明的一点是，游标的名字是一个未声明的标识符，而不是PL/SQL中的变量名。

因此，不能把值赋给一个游标名，也不能在一个表达式中使用游标。

但是，游标和变量作用域规则是相同的。

游标也是可以接收参数的，但是游标参数的值只在打开游标的时候才可以被使用。有关游标接受参数的内容可以参看20.4.5小节。

2.2 打开游标

在声明完一个游标之后，就可以使用OPEN语句打开这个游标了。

只有在打开了游标之后，才可以执行相关数据提取操作。

打开游标的语法规则如下：

OPEN cursor_name;

其中，cursor_name表示前面已经声明的游标的名字。

当游标打开之后，就可以执行其对应的SELECT语句的操作，并可以将数据查询的结果放入到游标的缓冲区中。

使用OPEN语句打开一个游标，其对应的SELECT语句中的结果集中的行并没有被选取，如果想使游标取得数据，需要使用FETCH语句。
下面来看一个打开游标的例子。

DECLARE
  v_ stuID         t_student . stuID %TYPE;              -- 学生编号
  v_ stuName       t_student . stuName%TYPE;             -- 学生姓名
  v_age             t_student . age%TYPE;                -- 学生年龄
  v_ sex            t_student . sex%TYPE E;              -- 学生性别
  CURSOR cursor_student IS
  SELECT stuName, age, sex
  FROM t_student
  WHERE stuID = v_ stuID;
BEGIN
  v_ stuID := 's102203';                                   -- 变量赋值
  OPEN cursor_student；                                    -- 打开游标
  ...
END;

这段PL/SQL代码片段中，在BEGIN部分，为表示学生编号的变量v_stuID赋值，并使用OPEN语句打开了一个游标。

如果使用OPEN语句打开了游标，那么它就不能再次打开，要想再次打开该游标，只能把它关闭之后再打开。

当打开一个游标时，会创建一个执行该结果集的指针。

绑定变量只有在游标被打开时，其值才会被计算。

例如下面的这个例子。

DECLARE
  v_ stuID         t_student . stuID %TYPE;            -- 学生编号
  v_ stuName       t_student . stuName%TYPE;   -- 学生姓名
  v_age             t_student . age%TYPE;               -- 学生年龄
  v_ sex            t_student . sex%TYPE E;            -- 学生性别
  CURSOR cursor_student IS
  SELECT stuName, age, sex
  FROM t_student
  WHERE stuID = v_ stuID;
BEGIN
    v_ stuID := 's102203';                                   -- 变量赋值
    OPEN cursor_student；                               -- 打开游标
     ...
    v_ stuID := 's206363';                                   -- 修改v_ stuID变量，不影响第一次v_ stuID赋的值
END;

这段PL/SQL代码片段中，在BEGIN部分，首先为表示学生编号的变量赋值为s102203，之后打开了游标cursor_student，此时查询的结果集中就是学生编号为s102203对应的学生信息，即使学生编号的值在打开游标之后发生了变化，其查询的结果集也不会变化，还是学生编号为s102203对应的学生信息。

如果想得到修改后的学生编号对应的学生信息，必须先将游标关闭，然后再将其打开，此时看到的就是学生编号修改后对应的学生信息。
如果对一个已经打开的游标再使用OPEN语句，Oracle数据库管理系统就会给出下面的一个错误提示。

 ORA-06511:PL/SQL:cursor already open

2.3 从游标中取得结果

在完成了声明游标和打开游标的操作之后，就可以使用游标检索结果集中的数据了。

使用游标检索结果集中的数据是使用FETCH语句来完成。

其语法规则如下：

 FETCH cursor_name INTO variable1 [ variable2...]

其中，cursor_name为游标的名字，该游标需要是已经在前面声明并打开的游标。

variable1、variable2表示的是声明的变量。

这里的变量可以是一个标量变量，也可以是一个记录或者PL/SQL中的集合变量。

多个变量之间需要用逗号分隔。

注意FETCH语句中需要包含INTO子句。

而声明游标时，select_statement查询语句不能包含INTO子句。

下面来看一个使用FETCH语句的例子。

例如，如果要使用FETCH语句提取学生信息表中的数据记录，可以使用下面的方法完成。

FETCH cursor_student INTO v_ stuName,v_age.v_ sex

这里的cursor_student是在前面已经声明并打开的游标，v_stuName、v_age、v_sex分别是游标接收结果集中数据的变量。

在使用FETCH语句提取数据记录时，其INTO子句中的变量列表中的变量必须与游标查询返回的列值的数据类型相兼容。

如果INTO列表中变量的数据类型与游标查询返回的列值的数据类型不互相兼容，则Oracle数据库管理系统会给出下面的一个错误提示。

PLS-394:wrong number of values

使用FETCH语句提取数据，可以使用一个简单的LOOP循环方式（可以参看20.4.1节）。

FETCH语句每次只能从结果集中提取一条记录，如果希望在FETCH语句中取得多条记录，可以使用BULK COLLECT子句。

在使用FETCH语句提取数据记录时，它每次只能从结果集中提取一条记录，同时将游标下移，指向当前记录的下一条记录，以此类推。

因此当检索结果集最后一次执行FETCH语句时，会取不到数据，数据库管理系统也不会产生异常，此时就需要使用游标的%FOUND或者%NOTFOUND属性（游标游标属性的内容可以参看20.3节）。

2.4 关闭游标

当查询的结果集检索完成之后，就可以关闭游标了。

关闭游标是使用CLOSE语句来完成的。

关闭游标后，与游标相关的资源将会被释放。

关闭游标的语法规则如下：

CLOSE cursor_name;

其中，cursor_name表示关闭游标的名字。

这里的cursor_name是前面已经打开的游标名。

游标被关闭后还可以重新将其打开。

如果一个游标关闭之后，还要使用它来检索数据，那么Oracle数据库管理系统会给出下面的一个错误提示。

ORA-1001:Invalid Cursor

同样，如果要对一个已经关闭了的游标进行操作也是非法的，此时Oracle数据库管理系统会抛出INVALID_CURSOR异常。

2.5 使用BULK COLLECT子句批量绑定数据

FETCH语句每次只能从结果集中提取一条记录，Oracle 9i及其以后的版本中，如果希望在FETCH语句中取得多条记录，可以使用BULK COLLECT子句。

BULK COLLECT子句可以批量绑定数据，将结果集中的所有行一次性地都提取出来。

下面来看一个使用BULK COLLECT子句批量绑定数据的例子。

这个例子中，将计算机系（院系编号为t_10）的教师的信息全部输出。

DECLARE

  TYPE teacher_table_type IS TABLE OF t_teacher %ROWTYPE;
  teacher_table   teacher_table_type;

  CURSOR cursor_teacher IS                            -- 声明游标
  SELECT teaName dept
  FROM t_teacher
  WHERE deptID = 't_10';
BEGIN
  OPEN cursor_teacher;                                     -- 打开游标
  FETCH cursor_teach;

  BULK COLLECT INTO teacher_table;            -- 使用BULK COLLECT子句提取数据

  CLOSE cursor_teacher;                                   -- 关闭游标
  FOR i IN 1.. teacher_table.COUNT LOOP       -- 显示教师信息
  DBMS_OUTPUT.PUT_LINE ('教师姓名：'|| teacher_table(i). teaName  || ' 所在院系：'|| teacher_table(i). dept);
  END LOOP;
END;

这段PL/SQL语句块是将院系编号为t_10的计算机系中的所有教师的信息显示输出。

为了在FETCH语句中将游标结果集中的所有行一次性地都提取出来，这里使用了BULK COLLECT子句。

最后将查询到的教师信息全部显示出来。其显示结果如下：

        教师姓名：张昌 所在院系：计算机系
        教师姓名：赵伟 所在院系：计算机系
        教师姓名：毛翠 所在院系：计算机系
        教师姓名：于波 所在院系：计算机系

2.6 在游标中使用子查询

在游标中还可以使用子查询。

所谓子查询，是指将一个SELECT查询语句块嵌套在另一个SQL查询语句中。

下面来看一个在游标中使用子查询的例子。

DECLARE
  CURSOR cursor_teacher IS
  SELECT teaID,teaName,age,sex,dept,profession,salary
  FROM T_teacher
  WHERE salary > ANY
  (SELECT salary FROM T_teacher WHERE dept = '数学系')

  AND dept != '数学系' ORDER BY salary ASC
BEGIN
   --   执行游标操作代码
END;

这段PL/SQL语句块中，在游标中使用子查询，这里的查询语句是查询其他院系的教师中工资比任意一个数学系教师的工资都高的教师信息。

三、游标属性

游标属性主要是用来确定有关数据操作的执行信息。

在PL/SQL中，游标属性主要包括%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT四个属性。

在PL/SQL语句块中可以使用游标属性，但是游标属性在SQL语句中却不能使用。

本节就来介绍这几个游标属性的用法。最后还会对显式游标属性和隐式游标属性做一个比较。

3.1 显式游标属性

显式游标的属性主要包括%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT四种。

通过使用显式游标属性可以返回多行查询结果。

下面分别来介绍显式游标的这四种属性。
1. %FOUND属性
%FOUND属性用来判断在游标的结果集中是否有数据记录存在。

如果在FETCH语句中取得了一行数据记录，则%FOUND就会返回TRUE；

如果FETCH语句中提取数据失败，则%FOUND会返回FALSE；

如果在游标已经打开但是还没有提取数据之前，则%FOUND会返回NULL。

下面来看一个使用%FOUND属性的例子。

BEGIN
  OPEN cursor_teacher;                                  -- 打开游标
  LOOP
    FETCH cursor_teacher                               -- 提取数据
    INTO v_teaID, v_teaName,
    IF cursor_teacher %FOUND THEN            -- 如果游标结果集中存在数据
      DBMS_OUTPUT.PUT_LINE ('教师编号：'|| v_teaID|| '教师姓名：'|| v_ teaName );
    ELSE                                                           -- 如果游标结果集中没有数据
      EXIT;                                                         -- 退出循环
    END IF;
  END LOOP;
  CLOSE cursor_teacher;                               -- 关闭游标
END;

在这个PL/SQL的代码片段中，说明了%FOUND属性的用法。

在BEGIN部分中，在LOOP循环里使用%FOUND属性对cursor_teacher游标中的结果集进行判断，如果游标结果集中存在数据记录，则将该信息取出并显示；如果游标结果集中不存在数据记录，则退出LOOP循环。

注意

在游标尚未打开，或者游标已经关闭的情况下使用了%FOUND属性，则会产生INVALID_CURSOR的预定义异常信息。

2. %NOTFOUND
%NOTFOUND属性的作用和%FOUND属性的作用正好相反。

如果在FETCH语句中取得了一行数据记录，则%NOTFOUND就会返回FALSE；

如果FETCH语句中提取数据失败，则%NOTFOUND会返回TRUE；

如果在游标已经打开但是还没有提取数据之前，则%NOTFOUND会返回NULL。

下面来看一个使用% NOTFOUND属性的例子。

BEGIN
  OPEN cursor_teacher;                                     -- 打开游标
  LOOP
    FETCH cursor_teacher                                 -- 提取数据
    INTO v_teaID, v_teaName,
  EXIT WHEN cursor_teacher %NOTFOUND;                  -- 如果游标结果集中没有数据就退出循环
    DBMS_OUTPUT.PUT_LINE ('教师编号：'|| v_teaID|| '教师姓名：'|| v_ teaName );
  END LOOP;
  CLOSE cursor_teacher;                                    -- 关闭游标
END;

在这个PL/SQL的代码片段中，说明了%NOTFOUND属性的用法。

在BEGIN部分中，在LOOP循环里使用%NOTFOUND属性对cursor_teacher游标中的结果集进行判断，

如果cursor_teacher %NOTFOUND返回的结果为TRUE，则说明FETCH语句中提取数据失败，此时就需要退出LOOP循环；

否则，如果cursor_teacher %NOTFOUND返回的结果为FALSE，则说明游标结果集中含有数据记录，此时就将游标结果集中的信息显示输出。

读者可能会发现，在上面的PL/SQL的代码片段中，EXIT WHEN cursor_teacher %NOTFOUND语句放在FETCH语句之后，数据处理之前，这样就保证了PL/SQL在整个处理过程中不会处理NOTFOUND属性值为NULL的情况。
有时，为了保证PL/SQL程序运行的安全性，需要cursor_teacher %NOTFOUND返回的结果为NULL的情况，这时可以对上面的判断语句做如下的改进。

EXIT WHEN cursor_teacher %NOTFOUND OR cursor_teacher %NOTFOUND IS NULL;

在这个LOOP循环EXIT WHEN的判断语句中，在原来的EXIT WHEN cursor_teacher %NOTFOUND的基础上又添加了一个cursor_teacher %NOTFOUND IS NULL的判断条件，并使用OR关键字将这两个判断条件联系在一起。

这段改进后的判断语句表示，如果cursor_teacher %NOTFOUND返回的结果为TRUE或者为NULL，就退出LOOP循环。

注意

在游标尚未打开，或者游标已经关闭的情况下使用了%NOTFOUND属性，则会产生INVALID_CURSOR的预定义异常信息。

3. %ISOPEN属性
%ISOPEN属性用来判断游标是否已经被打开。

如果对应的游标被打开，则%ISOPEN返回TRUE；

如果对应的游标没有被打开，则返回FALSE。

下面来看一个使用% ISOPEN属性的例子。

BEGIN
  IF cursor_teacher %ISOPEN THEN      -- 如果游标已经打开
    ...                                                          -- 执行相应的操作
  ELSE
    OPEN cursor_teacher;                         -- 打开游标
  END IF;
END;

在这个PL/SQL的代码片段中，说明了%ISOPEN属性的用法。

在BEGIN部分中，IF-ELSE语句对cursor_teacher游标是否打开做出判断。

如果cursor_teacher游标已经打开，就执行相应的操作；

如果cursor_teacher游标没有打开，就使用OPEN语句将cursor_teacher游标打开。

除了可以使用上面的IF-ELSE语句对cursor_teacher游标是否打开做出判断以外，还可以使用下面的形式判断cursor_teacher游标是否打开。

BEGIN
  IF NOT cursor_teacher %ISOPEN THEN     -- 如果游标没有打开
    OPEN cursor_teacher;                                -- 打开游标
  END IF
   ...                                                                  -- 执行相应的操作
END;

4. %ROWCOUNT属性
%ROWCOUNT属性用来取得游标取得的实际数据记录的行数。

它返回的是游标已经检索的数据记录的行数。

在游标变量已经打开但是没有检索数组之前，%ROWCOUNT值为0；

当FETCH语句取出了第一条数据记录时，%ROWCOUNT的值为1；

当FETCH语句取出了第二条数据记录时，%ROWCOUNT的值就会加1，变为2，依次类推，每当从FETCH语句中成功取出一条数据记录后，%ROWCOUNT的值就加1。

下面来看一个使用%ROWCOUNT属性的例子。

BEGIN
  OPEN cursor_teacher;                                       -- 打开游标
  LOOP
    FETCH cursor_teacher                                    -- 提取数据
    INTO v_teaID, v_teaName,
    EXIT WHEN cursor_teacher %NOTFOUND OR cursor_teacher %ROWCOUNT > 10;
    DBMS_OUTPUT.PUT_LINE ('教师编号：'|| v_teaID|| '教师姓名：'|| v_ teaName );
  END LOOP;
  CLOSE cursor_teacher;                                     -- 关闭游标
END;

 

在这个PL/SQL的代码片段中，说明了% ROWCOUNT属性的用法。

在BEGIN部分中，对cursor_teacher游标进行判断，如果cursor_teacher游标中没有取出数据记录或者取得的数据记录超过了10行，就退出LOOP循环。

注意

在游标尚未打开，或者游标已经关闭的情况下使用了%ROWCOUNT属性，则会产生INVALID_CURSOR的预定义异常信息。