联 系 我 们
售前咨询
售后咨询
微信关注:星环科技服务号
更多联系方式 >
6.7.5.11 游标(Cursors)
更新时间:10/24/2023, 7:20:26 AM

在 PL/SQL 中,游标是指针,指向一段 DML 语句(INSERT/UPDATE/DELETE/MERGE)或者查询语句(SELECT)的上下文区域(context area)。

PL/SQL 中,游标分为显式游标(explicit cursor)隐式游标(implicit cursor)。在声明游标时可以选择带有或不带有参数。

显式游标

在声明游标时可以选择带有参数或不带有参数。

表 15. 显式游标的属性
游标的属性 返回值类型 意义

%ROWCOUNT

整型

获得 FETCH 语句返回的数据行数

%ROWTYPE

记录类型

游标返回结果的记录类型

%FOUND

布尔型

最近的 FETCH 语句返回一行数据则为 TRUE,否则为 FALSE

%NOTFOUND

布尔型

与 %FOUND 属性返回值相反

%ISOPEN

布尔型

游标已经打开时值为 TRUE,否则为 FALSE

使用步骤

显示游标可以在 SELECT 语句上创建,它使用的步骤为:

  1. 在声明部分声明游标:

    DECLARE CURSOR <cursor_name> IS <select_statement>;
    复制
    • <cursor_name>:定义游标 cursor 的名称

    • <select_statement>:游标的定义 select 语句。

  2. 在执行部分或异常处理部分打开游标:

    OPEN <cursor_name>;
    复制
  3. 取数据:

    FETCH <cursor_name>;
    复制
  4. 关闭游标:

    CLOSE <cursor_name>;
    复制

下面我们用一些例子来具体介绍显式游标的使用方法。

不带参数的显式游标
例 193. 在 LOOP 中声明一个显式游标,查询表 transactions 中的价格
!set plsqlUseSlash true
DECLARE
    -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。
    transactions_type transactions%ROWTYPE
    -- 定义一个显式游标 cur,用来查询表 transactions 中的全部信息。
    CURSOR cur IS SELECT * FROM transactions;
BEGIN
    -- 打开游标 cur。
    OPEN cur
    LOOP
        -- 把游标 cur 查询到的信息放进变量 transactions_type 中。
        FETCH cur INTO transactions_type
        -- 如果游标 cur 没有查询到信息,就退出。
        EXIT WHEN cur%NOTFOUND
        -- 输出变量 transactions_type 中的字段 price 的值。
        DBMS_OUTPUT.PUT_LINE(transactions_type.price)
    END LOOP;
    CLOSE cur
    EXCEPTION
    -- 声明一个异常情况名 OTHERS。
    WHEN OTHERS THEN
        -- 如果异常 OTHERS 发生,则返回的值。
        DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
        CLOSE cur
END;
/
复制

输出结果为:

+---------+
| output  |
+---------+
| 10.31   |
| 11.11   |
| 7.02    |
| 4.5     |
| 10.03   |
| 6.36    |
| 7.52    |
| 5.3     |
| 12.21   |
| 8.64    |
| 18.38   |
| 22.66   |
| 6.12    |
| 12.13   |
| 5.25    |
| 9.16    |
| 9.81    |
| 7.49    |
| 68.43   |
| 4.16    |
+---------+
复制
例 194. 在 FOR LOOP 中声明一个显式游标,查询表 transactions 中的账号,交易时间和价格
!set plsqlUseSlash true
DECLARE
    -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。
    transactions_type transactions%ROWTYPE
    -- 定义一个显式游标 cur,用来查询表 transactions 中的全部信息。
    CURSOR cur IS SELECT * FROM transactions;
BEGIN
    -- for..loop 循环语句,对于每一个在游标里的变量值。
    FOR transactions_type IN cur
    LOOP
    -- 输出变量 transactions_type 的字段名,账号,交易时间和价格的值。
    DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' ||transactions_type.trans_time || ' '  ||transactions_type.price)
    END LOOP;
END;
/
复制

输出结果为:

+-------------------------------+
|            output             |
+-------------------------------+
| 6670192 20140314145958 10.31  |
| 3912384 20140205140521 11.11  |
| 6513065 20140628133001 7.02   |
| 3912384 20140430111523 4.5    |
| 5224133 20140331115900 10.03  |
| 5224133 20140801110003 6.36   |
| 2755506 20140702113025 7.52   |
| 0700735 20140611102830 5.3    |
| 2394923 20141031135018 12.21  |
| 6670192 20141130113905 8.64   |
| 6513065 20140508094805 18.38  |
| 0700735 20140315111111 22.66  |
| 6513065 20140506133109 6.12   |
| 6513065 20140105100520 12.13  |
| 0700735 20140430143020 5.25   |
| 6600641 20140228140005 9.16   |
| 6513065 20140916105811 9.81   |
| 6513065 20141225133500 7.49   |
| 3912384 20140328102400 68.43  |
| 3912384 20140214141519 4.16   |
+-------------------------------+
复制
例 195. 定义一个 cursor%rowtype 类型的变量来查询 transactions 表中的交易号、交易时间和所对应的股票价格
!set plsqlUseSlash true
DECLARE
   -- 声明一个名为 transactions_cursor 的游标
   CURSOR transactions_cursor IS SELECT * FROM transactions;
   -- 声明一个 transactions_cursor%rowtype 类型的记录变量
   transactions_record transactions_cursor%ROWTYPE
BEGIN
   OPEN transactions_cursor
   LOOP
       FETCH transactions_cursor INTO transactions_record
       EXIT WHEN transactions_cursor%NOTFOUND;
       dbms_output.put_line(transactions_record.acc_num || ' ' ||transactions_record.trans_time || ' '  ||transactions_record.price);
      END LOOP;
   CLOSE transactions_cursor;
END;
/
复制

查询结果如下,结果和上例一致:

+-------------------------------+
|            output             |
+-------------------------------+
| 6670192 20140314145958 10.31  |
| 3912384 20140205140521 11.11  |
| 6513065 20140628133001 7.02   |
| 3912384 20140430111523 4.5    |
| 5224133 20140331115900 10.03  |
| 5224133 20140801110003 6.36   |
| 2755506 20140702113025 7.52   |
| 0700735 20140611102830 5.3    |
| 2394923 20141031135018 12.21  |
| 6670192 20141130113905 8.64   |
| 6513065 20140508094805 18.38  |
| 0700735 20140315111111 22.66  |
| 6513065 20140506133109 6.12   |
| 6513065 20140105100520 12.13  |
| 0700735 20140430143020 5.25   |
| 6600641 20140228140005 9.16   |
| 6513065 20140916105811 9.81   |
| 6513065 20141225133500 7.49   |
| 3912384 20140328102400 68.43  |
| 3912384 20140214141519 4.16   |
+-------------------------------+
复制
带参数的显式游标
例 196. 在 LOOP 中定义一个带有参数的游标,查询表 transactions 中账号为 6513065 的交易时间。
!set plsqlUseSlash true
DECLARE
    -- 定义一个带有参数的游标 cur 去查询表 transactions 中账号和参数的值相等的全部信息,其中参数名为 tacc_num,类型为 string。
    CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE acc_num=tacc_num;
    -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。
    transactions_type transactions%rowtype
BEGIN
    -- 打开游标 cur,并赋参数值为 6513065。
    OPEN cur('6513065')
    LOOP
        -- 把游标 cur 查询到的信息放进变量 transactions_type 中。
        FETCH cur INTO transactions_type
        EXIT WHEN cur%NOTFOUND
        -- 输出变量 transactions_type 中的交易时间。
        DBMS_OUTPUT.PUT_LINE(transactions_type.trans_time)
    END LOOP;
    CLOSE cur
    -- 如果有情况名为 OTHERS 的异常发生,则返回 Something unexpected happened!! 的信息。
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
        CLOSE cur
END;
/
复制

输出结果为:

+-----------------+
|     output      |
+-----------------+
| 20140628133001  |
| 20140508094805  |
| 20140506133109  |
| 20140105100520  |
| 20140916105811  |
| 20141225133500  |
+-----------------+
复制
例 197. 在 FOR LOOP 中定义一个游标,查询表 transactions 中账号为 6513065 的交易时间和价格
!set plsqlUseSlash true
DECLARE
    -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。
    transactions_type transactions%ROWTYPE
    -- 定义一个带有参数的游标 cur 去查询表 transactions 中账号和参数的值相等的全部信息,其中参数名为 tacc_num,类型为 string。
    CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE acc_num=tacc_num;
BEGIN
    -- for..loop 循环,对于每一个在游标 cur('6513065') 中的变量 transactions_type 的值。
    FOR transactions_type IN cur('6513065')
    LOOP
        -- 输出表 transactions 中账号为 6513065 的账号,交易时间和价格。
        DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' || ' ' || transactions_type.trans_time  ||transactions_type.price)
    END LOOP;
END;
/
复制

返回结果如下:

+-------------------------------+
|            output             |
+-------------------------------+
| 6513065  201406281330017.02   |
| 6513065  2014050809480518.38  |
| 6513065  201405061331096.12   |
| 6513065  2014010510052012.13  |
| 6513065  201409161058119.81   |
| 6513065  201412251335007.49   |
+-------------------------------+
复制
例 198. 声明一个显式游标,通过 cursor%rowtype 定义变量,查询表 transactions 中账号为 6513065 的交易时间和价格
!set plsqlUseSlash true
DECLARE
    -- 定义一个显式游标 cur,用来查询表 transactions 中的账号、交易时间以及价格信息。
    CURSOR cur(tacc_num string) IS SELECT acc_num, trans_time, price FROM transactions WHERE acc_num = tacc_num;
    -- 定义一个字段名和类型与表 transactions.acc_num、transactions.trans_time、transactions.price 均相同的记录变量 transactions_type。
    transactions_type cur%ROWTYPE;
BEGIN
    -- Open Cursor,并传入参数值 6513065。
    OPEN cur('6513065')
    LOOP
        -- 将 cur 的结果放入 transactions_type。
        FETCH cur INTO transactions_type
        EXIT WHEN cur%NOTFOUND
        -- 输出变量 transactions_type 的字段名:账户、交易时间和价格。
        DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' || transactions_type.trans_time || ' ' || transactions_type.price)
    END LOOP;
    CLOSE cur
    EXCEPTION
    WHEN OTHERS THEN
        -- 出现异常时的显示信息。
        DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
        CLOSE cur
END;
/
复制

输出结果上例一致:

+-------------------------------+
|            output             |
+-------------------------------+
| 6513065 20140628133001 7.02   |
| 6513065 20140508094805 18.38  |
| 6513065 20140506133109 6.12   |
| 6513065 20140105100520 12.13  |
| 6513065 20140916105811 9.81   |
| 6513065 20141225133500 7.49   |
+-------------------------------+
复制
隐式游标

隐式游标是没有明确的声明语句的游标类型。所有的 DML 操作都被 ArgoDB 内部解析为一个游标名为 SQL 的隐式游标。

表 16. 隐式游标的属性
游标的属性 返回值类型 意义

SQL%ROWCOUNT

整型

代表 DML 语句成功执行的数据行数

SQL%FOUND

布尔型

值为 TRUE 代表插入、删除、更新或单行查询操作成功

SQL%NOTFOUND

布尔型

与 SQL%FOUND 属性返回值相反

SQL%ISOPEN

布尔型

永远为 FALSE

说明

  • 对于 SELECT INTO 语句,如果执行成功,SQL%ROWCOUNT 的值为 1,如果没有成功,SQL%ROWCOUNT 的值为 0,同时产生一个异常 NO_DATA_FOUND。

  • 在执行 DML 语句后,SQL%FOUND 的属性值将为 TRUE。

例 199. 指向 DML 语句的隐式游标,使用 update 语句对表 zara 进行更新
!set plsqlUseSlash true
BEGIN
    UPDATE zara SET name='grace' WHERE age=20
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('name is updated')
    ELSE
        DBMS_OUTPUT.PUT_LINE('name is not updated')
    END IF
END;
/
复制

输出结果为:

+----------------------+
|        output        |
+----------------------+
| name is not updated  |
+----------------------+
复制
游标变量
  • 游标变量指向指定查询结果集当前行,像游标一样。

  • 但是相对游标,游标变量更加灵活因为其声明并不绑定指定查询。

  • 要定义一个游标变量,您需要先声明一个游标类型 REF CURSOR,然后定义这个类型的游标变量。

游标变量可分为两类,强类型和弱类型的:

  • 如果在声明游标类型 REF CURSOR 的时候指定了返回类型,那么 REF CURSOR 及其类型的游标变量被称为强类型

  • 如果在声明游标类型 REF CURSOR 的时候不指定返回类型,那么 REF CURSOR 及其类型的游标变量被称为弱类型

使用步骤
  1. 声明游标类型:

    DECLARE TYPE <type_name> IS REF CURSOR;
    复制
    • <type_name>:游标类型。

  2. 声明该类型的游标变量:

    cursor_name <type_name>;
    复制
  3. 在执行部分或异常处理部分打开:

    OPEN <cursor_name> FOR <sql_statement>;
    复制
    • <cursor_name>:游标名称。

    • <sql_statement>:定义游标的 select 语句。

  4. 取数据:

    FETCH <cursor_name>;
    复制
  5. 关闭游标:

    CLOSE <cursor_name>;
    复制
强类型游标变量
例 200. 查询表 transactions 中价格为 12.13 的交易号和交易时间
!set plsqlUseSlash true
DECLARE
    -- 声明一个名为 cur_transaction 的游标类型,指定返回值的类型为 transactions%rowtype。
    TYPE cur_transaction IS REF CURSOR RETURN transactions%ROWTYPE
    -- 定义一个名为 sqlcur 的游标变量。
    sqlcur cur_transaction
    -- 依次定义两个类型为字符串的变量 v_trans_id,v_trans_time。
    v_trans_id STRING
    v_trans_time STRING
BEGIN
    -- 打开游标 sqlcur,去查询表 transactions中价格为 12.13 的交易号和交易时间。
    OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13;
    LOOP
        -- 将游标查询的结果放进变量 v_trans_id,v_trans_time。
        FETCH sqlcur INTO v_trans_id,v_trans_time
        EXIT WHEN sqlcur%NOTFOUND
        -- 输出变量 v_trans_id,v_trans_time 的值。
        DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time)
    END LOOP;
    CLOSE sqlcur
END;
/
复制

输出结果为:

+---------------------------+
|          output           |
+---------------------------+
| 943197522 20140105100520  |
+---------------------------+
复制
弱类型游标变量
例 201. 查询表 transactions 中价格为 12.13 的交易号和交易时间
!set plsqlUseSlash true
DECLARE
    -- 声明一个名为 cur_transaction 的游标类型,没有定义返回值的类型。
    TYPE cur_transaction IS REF CURSOR
    -- 定义一个名为 sqlcur 的 cur_transaction 游标变量。
    sqlcur cur_transaction
    -- 依次定义两个类型为字符串的变量 v_trans_id,v_trans_time。
    v_trans_id STRING
    v_trans_time STRING
BEGIN
    -- 打开游标 sqlcur,去查询表 transactions 中价格为 12.13 的交易号和交易时间。
    OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13;
    LOOP
        -- 将游标查询的结果放进变量 v_trans_id,v_trans_time。
        FETCH sqlcur INTO v_trans_id,v_trans_time
        EXIT WHEN sqlcur%NOTFOUND
        -- 输出变量 v_trans_id,v_trans_time 的值。
        DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time)
    END LOOP;
    CLOSE sqlcur
END;
/
复制

输出结果为:

+---------------------------+
|          output           |
+---------------------------+
| 943197522 20140105100520  |
+---------------------------+
复制
游标变量作为参数传递
例 202. 将游标变量作为参数传递
!set plsqlUseSlash true
CREATE OR REPLACE PACKAGE pkg_a IS
    -- 在包 pkg_a 中定义一个游标类型 empcurtyp,和表 user_info 的行类型相同。
    TYPE empcurtyp IS REF CURSOR RETURN user_info%ROWTYPE
END pkg_a;
/

-- 定义一个过程 process_emp_cv,这个过程会一行行读取 user_info 中的记录的 name。
CREATE OR REPLACE PROCEDURE process_emp_cv(emp_cv in pkg_a.empcurtyp)
IS
    user_p user_info%ROWTYPE
BEGIN
    LOOP
        FETCH emp_cv INTO user_p
        EXIT WHEN emp_cv%NOTFOUND
        DBMS_OUTPUT.PUT_LINE('Name = ' || user_p.name)
    END LOOP;
END;
/
DECLARE
    emp pkg_a.empcurtyp
BEGIN
    -- 声明游标变量 emp,类型为在包 pkg_a 中定义的游标类型。
    OPEN emp FOR SELECT * FROM user_info;
    -- 将游标变量 emp 座位参数传给过程 process_emp_cv。
    process_emp_cv(emp)
    CLOSE emp
END;
/
复制

输出结果为:

+-------------+
|   output    |
+-------------+
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
| Name = **  |
+-------------+
复制