游标

📄字数 4.7K

👁️阅读量 加载中...

在 PL/SQL 中，游标（Cursor）是用于处理多行查询结果的机制，它允许用户逐行访问查询结果集。

一、关于游标

1.1 游标类型

XuguDB支持三种游标类型：

类型	说明	关闭方式
隐式游标	由数据库自动创建并管理，最后一次执行的 SELECT 或 DML 语句都会打开一个名为 SQL 的隐式游标	由系统自动关闭
显式游标	由用户显式声明、打开、提取、关闭	手动关闭
游标变量	以变量形式保存的游标句柄，支持动态绑定查询，使用方式类似于显式游标	手动关闭

注意

在一次会话连接中，可创建游标的最大数量由系统参数max_cursor_num进行控制。
除隐式游标外，显式游标与游标变量在使用后需要手动关闭。

1.2 游标的使用场景

游标通常在以下场景中使用：

• PL/SQL语句
• 与DML语句配合使用
• 与PREPARE语句配合使用

1.3 游标属性

游标属性可以通过cursor_name%attribute的语法进行获取，游标属性(attribute)具体有以下几类：

• FOUND：布尔类型属性，当使用游标最近一次成功获取记录时，则返回TRUE。
• NOTFOUND：布尔类型属性，与FOUND相反。
• ISOPEN：布尔类型属性，当游标处于打开状态时，返回TRUE。
• ROWCOUNT：整数类型属性，返回游标已经读取的记录个数。

二、游标的分类、定义与使用

2.1 隐式游标

2.1.1 定义

隐式游标是在 PL/SQL 中由系统构建和管理的游标。每次运行 PL/SQL 中的SELECT或DML语句时，PL/SQL 都会打开一个隐式游标，并由系统自动命名为SQL，可在语句中获取该游标的属性信息。

注意

隐式游标的相关操作，例如打开、读取记录、关闭操作，都由数据库系统完成，无需手动进行管理。隐式游标的工作区中，保存最近一次SQL命令的数据。

2.1.2 属性

隐式游标的属性如下所示：

• SQL%ISOPEN：对于隐式游标来说，该属性将会一直返回FALSE。因为隐式游标在命令执行后自动关闭。
• SQL%FOUND：返回一个布尔值，以指示隐式游标是否读取到记录，当最近一次读记录时成功则返回TRUE。
• SQL%NOTFOUND：与SQL%FOUND相反。
• SQL%ROWCOUNT：游标已经读取的记录个数。

2.1.3 使用示例

更新 test_cur 表中 id 大于1的所有记录，将 id 增加1并输出受影响的行数。SQL%ROWCOUNT返回上一条 DML 语句影响的行数

-- 创建表并插入数据
SQL> CREATE TABLE test_cur(id INT,name VARCHAR(20));

SQL> INSERT INTO test_cur VALUES(1,'TEST1')(2,'TEST2')(3,'TEST3');

SQL> DECLARE
     -- 更新test_cur表中复合条件的记录
     BEGIN
       UPDATE test_cur SET id = id + 1 WHERE id > 1;
     -- 打印游标属性
       SEND_MSG('ISOPEN: ' || to_char(SQL%ISOPEN));
       SEND_MSG('ISFOUND: ' || to_char(SQL%FOUND));
       SEND_MSG('ISNOTFOUND: ' || to_char(SQL%NOTFOUND));
       SEND_MSG('TOTAL UPDATE:' || SQL%ROWCOUNT || ' ROWS');
       ROLLBACK;
     END;
/
-- 输出结果，最后一行表示test_cur表中执行该命令后，受到影响的行数为2。
ISOPEN: FALSE
ISFOUND: TRUE
ISNOTFOUND: FALSE
TOTAL UPDATE:2 ROWS

2.2 显式游标

2.2.1 定义

显式游标是由用户构建并管理的游标。使用显式游标，必须经过声明，为其命名并将其与查询相关联。

2.2.2 语法格式

显式游标的语法格式如下所示：

(SelectStmt ::= , select_no_parens ::= , parallel_opt ::= , opt_bulk ::= , opt_into_list ::= , opt_limit ::= )

• 子句与参数说明

子句	参数/关键字	说明
cursor_def_stmt	cursor_args SelectStmt	为显式游标指定参数，可以在块语句或 prepare 语句中进行参数传递。 指游标可以在 SELECT 语句上进行结果收集。
cursor_stmt	cursor_args SelectStmt	此语法定义的游标可在PL/SQL外使用，参数释义同上
open_cursor_stmt	expr_list n_expr opt_using	表示传递的参数链表，当没有参数时，该子句可缺省。 该显式游标也可以传递表达式作为参数。 将指定参数传递给带参游标
fetch_stmt	opt_bulk opt_into_list opt_limit integer	允许一次性加载查询结果集到变量中，提高大规模查询下的性能。 允许将结果集赋值给某些变量。 可以按照返回指定个数的结果。 用于指定从游标中返回的结果集个数。
close_cursor_stmt	cursor_name	关闭的游标名

显式游标的使用经过以下步骤：

• 定义游标(cursor_stmt/cursor_def_stmt)
• 打开游标(open_cursor_stmt)
• 提取游标记录(fetch_stmt)
• 关闭游标(close_cursor_stmt)

注意

cursor_def_stmt通常结合PL\SQL语句使用，在一整个块语句中完成定义、操作、关闭；
cursor_stmt可以单独被声明，其定义、操作、关闭可使用多条命令完成。

2.2.3 使用示例

示例1： 使用 CURSOR IS 定义了一个带有参数 inid 的游标 cur，该游标将返回 id 大于 inid 的所有记录并返回总行数

-- 创建表并插入数据
SQL> CREATE TABLE test_cur(id INT,name VARCHAR(20));

SQL> INSERT INTO test_cur VALUES(1,'TEST1')(2,'TEST2')(3,'TEST3');

-- 定义并使用游标
SQL> DECLARE
     CURSOR cur(inid INT) IS
       SELECT * FROM test_cur WHERE id > inid;
     oid   INT;
     oname VARCHAR;
     BEGIN
     -- 打开游标
     IF NOT cur%ISOPEN THEN
       OPEN cur(1);
     END IF;
 
     FETCH cur INTO oid, oname;
     -- 循环获取结果
     WHILE cur%FOUND LOOP
       SEND_MSG('ID IS:' || oid || ' NAME IS:' || oname);
       FETCH cur INTO oid, oname;
     END LOOP;
     -- 打印信息
     SEND_MSG('TOTAL FETCH :' || cur%ROWCOUNT || ' ROWS');
     CLOSE cur;
     END;
     /
-- 输出结果
ID IS:2 NAME IS:TEST2
ID IS:3 NAME IS:TEST3
TOTAL FETCH :2 ROWS

示例2： DECLARE CURSOR FOR 方式定义游标

-- 定义游标
SQL> DECLARE cur(inid INT) CURSOR FOR SELECT * FROM test_cur WHERE id > inid;
    /

+----+------+
| ID | NAME |
+----+------+
+----+------+

-- 打开游标
SQL> OPEN cur(1);    

-- 获取数据
SQL> FETCH cur;

+----+-------+
| ID | NAME  |
+----+-------+
| 2  | TEST2 |
| 3  | TEST3 |
+----+-------+

-- 关闭游标
SQL> CLOSE cur;

示例3： BULK COLLECT 批量返回结果

SQL> DECLARE
     CURSOR cur IS
      SELECT * FROM test_cur;
     TYPE trow IS TABLE OF cur%ROWTYPE;
     otab trow;
     BEGIN
       OPEN cur;
     -- 使用bulk子句批量获取
       FETCH cur BULK COLLECT INTO otab;
       CLOSE cur;
       FOR i IN 1 .. otab.COUNT LOOP
         SEND_MSG(otab(i).id || '|' || otab(i).name);
       END LOOP;
     END;
      /

-- 输出结果
1|TEST1
2|TEST2
3|TEST3

示例4： 与PREPARE语句配合使用

-- 创建表并插入数据
SQL> CREATE TABLE test(a INT);

SQL> INSERT INTO test VALUES(1)(2)(3);

SQL> PREPARE pre1 AS SELECT * FROM test;

+---+
| A |
+---+
+---+

-- 声明一个显式游标作用于pre1
SQL> DECLARE cur CURSOR FOR pre1;
     /

+---+
| A |
+---+
+---+

-- 打开游标并获取游标数据
SQL> OPEN cur;

SQL> FETCH cur;

+---+
| A |
+---+
| 1 |
| 2 |
| 3 |
+---+

SQL> CLOSE cur;

2.3 游标变量

2.3.1 定义

• 定义方式与普通变量一致，使用方式类似于显式游标；
• 可在运行时动态绑定任意查询；
• 常用于存储过程/函数的参数，便于调用者继续提取结果；

2.3.2 语法格式

2.3.3 使用示例

• 示例1： PL/SQL 语句中使用游标变量

-- 创建表并插入数据
SQL> CREATE TABLE test_cur(id INT,name VARCHAR(20));

SQL> INSERT INTO test_cur VALUES(1,'TEST1')(2,'TEST2')(3,'TEST3');

SQL> DECLARE
     i INT;
     v_chr VARCHAR;
     -- (1) 游标变量声明
     cur_sysref SYS_REFCURSOR;
     BEGIN
       -- (2) 游标变量打开
       OPEN cur_sysref FOR SELECT name FROM test_cur;
       i := 1;
       LOOP
         FETCH cur_sysref INTO v_chr;
         EXIT WHEN cur_sysref%NOTFOUND;
         SEND_MSG('Row ' || i || ' is: ' || v_chr);
         i := i + 1;
       END LOOP;
       -- 检查cur_sysref的属性
       SEND_MSG(to_char(cur_sysref%ISOPEN));
       SEND_MSG(to_char(cur_sysref%FOUND));
       SEND_MSG(to_char(cur_sysref%NOTFOUND));
       SEND_MSG(to_char(cur_sysref%ROWCOUNT));
       -- (3) 游标变量关闭
       CLOSE cur_sysref;
     END;

-- 输出结果
Row 1 is: TEST1
Row 2 is: TEST2
Row 3 is: TEST3
TRUE
FALSE
TRUE
3

• 示例2： 存储过程，使用游标变量作为参数

SQL> CREATE PROCEDURE pro_cur(cur_sysref SYS_REFCURSOR, test_cur VARCHAR) as
     DECLARE
        i INT;
        v_chr VARCHAR;
     BEGIN
         -- (2) 游标变量打开
        OPEN cur_sysref FOR SELECT name FROM test_cur;
        i := 1;
        LOOP
            FETCH cur_sysref INTO v_chr;
            EXIT WHEN cur_sysref%NOTFOUND;
            SEND_MSG('Row ' || i || ' is: ' || v_chr);
            i := i + 1;
        END LOOP;
        -- 检查cur_sysref的属性
        SEND_MSG(to_char(cur_sysref%ISOPEN));
        SEND_MSG(to_char(cur_sysref%FOUND));
        SEND_MSG(to_char(cur_sysref%NOTFOUND));
        SEND_MSG(to_char(cur_sysref%ROWCOUNT));
        -- (3) 游标变量关闭
        CLOSE cur_sysref;
     END;
     /

SQL> DECLARE
        cur_sysref REF CURSOR;
        test_cur VARCHAR;
     BEGIN
        test_cur := 'test_cur';
        EXEC pro_cur(cur_sysref, test_cur);
     END;
     /

-- 输出结果
Row 1 is: TEST1
Row 2 is: TEST2
Row 3 is: TEST3
TRUE
FALSE
TRUE
3

三、游标使用的注意事项

1. 及时关闭

• 显式游标和游标变量占用会话资源，使用完必须 CLOSE。

2. 限制批量大小

• 在大数据集场景使用 BULK COLLECT 时，建议搭配 LIMIT 分批提取，避免一次性占用过多内存。

3. 避免游标泄漏

• 使用 CURSOR … IS 定义时，尽量把所有操作放在同一 PL/SQL 块，便于维护。