游标

为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。

语法格式

  • 定义游标

1. CURSOR cursor_name 2. [ BINARY ] [ NO SCROLL ] [ { WITH | WITHOUT } HOLD ] 3. FOR query ; - 通过已经创建的游标检索数据

1. FETCH [ direction { FROM | IN } ] cursor_name;

其中direction子句为可选参数。

1. NEXT 2. | PRIOR 3. | FIRST 4. | LAST 5. | ABSOLUTE count 6. | RELATIVE count 7. | count 8. | ALL 9. | FORWARD 10. | FORWARD count 11. | FORWARD ALL 12. | BACKWARD 13. | BACKWARD count 14. | BACKWARD ALL - 在不检索数据的情况下重新定位一个游标

MOVE的作用类似于FETCH命令，但只是重定位游标而不返回行。

1. MOVE [ direction [ FROM | IN ] ] cursor_name;

其中direction子句为可选参数。

1. NEXT 2. | PRIOR 3. | FIRST 4. | LAST 5. | ABSOLUTE count 6. | RELATIVE count 7. | count 8. | ALL 9. | FORWARD 10. | FORWARD count 11. | FORWARD ALL 12. | BACKWARD 13. | BACKWARD count 14. | BACKWARD ALL - 关闭游标，释放和一个游标关联的所有资源

1. CLOSE { cursor_name | ALL } ;

参数说明

  • cursor_name

将要创建、关闭的游标名。 - BINARY

指明游标以二进制而不是文本格式返回数据。 - NO SCROLL

声明游标检索数据行的方式。

  • NO SCROLL：声明该游标不能用于以倒序的方式检索数据行。
  • 未声明：根据执行计划的不同，自动判断该游标是否可以用于以倒序的方式检索数据行。
  • WITH HOLD | WITHOUT HOLD

声明当创建游标的事务结束后，游标是否能继续使用。

  • WITH HOLD：声明该游标在创建它的事务结束后仍可继续使用。
  • WITHOUT HOLD：声明该游标在创建它的事务之外不能再继续使用，此游标将在事务结束时被自动关闭。
  • 如果不指定WITH HOLD或WITHOUT HOLD，默认行为是WITHOUT HOLD。
  • 跨节点事务不支持WITH HOLD（例如在多DBnode部署openGauss中所创建的含有DDL的事务属于跨节点事务）。
  • query

使用SELECT或VALUES子句指定游标返回的行。

取值范围：SELECT或VALUES子句。 - direction_clause

定义抓取数据的方向。

取值范围：

  • NEXT（缺省值）

    从当前关联位置开始，抓取下一行。 - PRIOR

    从当前关联位置开始，抓取上一行。 - FIRST

    抓取查询的第一行（和ABSOLUTE 1相同）。 - LAST

    抓取查询的最后一行（和ABSOLUTE -1相同）。 - ABSOLUTE count

    抓取查询中第count行。

    ABSOLUTE抓取不会比用相对位移移动到需要的数据行更快，因为下层的实现必须遍历所有中间的行。

    count取值范围：有符号的整数

    • count为正数，就从查询结果的第一行开始，抓取第count行。
    • count为负数，就从查询结果末尾抓取第abs(count)行。
    • count为0时，定位在第一行之前。
    • RELATIVE count

    从当前关联位置开始，抓取随后或前面的第count行。

    取值范围：有符号的整数

    • count为正数就抓取当前关联位置之后的第count行。
    • count为负数就抓取当前关联位置之前的第abs(count)行。
    • 如果当前行没有数据的话，RELATIVE 0返回空。
    • count

    抓取随后的count行（和FORWARD count一样）。 - ALL

    从当前关联位置开始，抓取所有剩余的行（和FORWARD ALL一样）。 - FORWARD

    抓取下一行（和NEXT一样）。 - FORWARD count

    从当前关联位置开始，抓取随后或前面的count行。 - FORWARD ALL

    从当前关联位置开始，抓取所有剩余行。 - BACKWARD

    从当前关联位置开始，抓取前面一行(和PRIOR一样) 。 - BACKWARD count

    从当前关联位置开始，抓取前面的count行（向后扫描）。

    取值范围：有符号的整数

    • count为正数就抓取当前关联位置之前的count行。
    • count为负数就抓取当前关联位置之后的abs（count）行。
    • 如果有数据的话，BACKWARD 0重新抓取当前行。
    • BACKWARD ALL

    从当前关联位置开始，抓取所有前面的行（向后扫描） 。 - { FROM | IN } cursor_name

使用关键字FROM或IN指定游标名称。

取值范围：已创建的游标的名称。 - ALL

关闭所有已打开的游标。

示例

假设存在表customer_t1 ，数据内容如下：

1. openGauss=# SELECT * FROM customer_t1;
2. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
3. ---------------+---------------+--------------+-------------+--------
4. 3769 |               | Grace        |             |
5. 3769 | hello         |              |             |
6. 6885 | maps          | Joes         |             |   2200
7. 4321 | tpcds         | Lily         |             |   3000
8. 9527 | world         | James        |             |   5000
9. (5 rows)
  • 用一个游标读取一个表。

``` 1. --开始一个事务。 2. openGauss=# START TRANSACTION; 3. START TRANSACTION

  1. --建立一个名为cursor1的游标。
  2. openGauss=# CURSOR cursor1 FOR SELECT * FROM customer_t1;

  3. DECLARE CURSOR

  4. --抓取前3行到游标cursor1里。

  5. openGauss=# FETCH FORWARD 3 FROM cursor1;
  6. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
  7. ---------------+---------------+--------------+-------------+--------
  8. 3769 | | Grace | |
  9. 3769 | hello | | |
  10. 6885 | maps | Joes | | 2200

  11. (3 rows)

  12. --关闭游标并提交事务。

  13. openGauss=# CLOSE cursor1;

  14. CLOSE CURSOR

  15. --结束一个事务。

  16. openGauss=# END;
  17. COMMIT ```
  18. 用一个游标读取VALUES子句中的内容。

``` 1. --开始一个事务。 2. openGauss=# START TRANSACTION; 3. START TRANSACTION

  1. --建立一个名为cursor2的游标。
  2. openGauss=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;

  3. DECLARE CURSOR

  4. --抓取前2行到游标cursor2里。

  5. openGauss=# FETCH FORWARD 2 FROM cursor2;
  6. column1 | column2
  7. ---------+---------
  8. 0 | 3
  9. 1 | 2

  10. (2 rows)

  11. --关闭游标并提交事务。

  12. openGauss=# CLOSE cursor2;

  13. CLOSE CURSOR

  14. --结束一个事务。

  15. openGauss=# END;
  16. COMMIT ```
  17. WITH HOLD游标的使用。

``` 1. --开启事务。 2. openGauss=# START TRANSACTION;

  1. --创建一个with hold游标。

  2. openGauss=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer_t1;

  3. --抓取头2行到游标cursor1里。

  4. openGauss=# FETCH FORWARD 2 FROM cursor1;
  5. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
  6. ---------------+---------------+--------------+-------------+--------
  7. 3769 | | Grace | |
  8. 3769 | hello | | |

  9. (2 rows)

  10. --结束事务。

  11. openGauss=# END;

  12. COMMIT

  13. --抓取下一行到游标cursor1里。

  14. openGauss=# FETCH FORWARD 1 FROM cursor1;
  15. c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
  16. ---------------+---------------+--------------+-------------+--------
  17. 6885 | maps | Joes | | 2200

  18. (1 row)

  19. --关闭游标。

  20. openGauss=# CLOSE cursor1;
  21. CLOSE CURSOR ```
  22. MOVE语句的使用。

``` —开始一个事务。 openGauss=# START TRANSACTION; START TRANSACTION

—定义一个名为cursor1的游标。 openGauss=# CURSOR cursor1 FOR SELECT * FROM customer_t1; DECLARE CURSOR

—忽略游标cursor1的前3行。 openGauss=# MOVE FORWARD 1 FROM cursor1; MOVE 1

—抓取游标cursor1的前2行。 openGauss=# FETCH 2 FROM cursor1; c_customer_sk | c_customer_id | c_first_name | c_last_name | amount ———————-+———————-+———————+——————-+————

1. 3769 | hello | | | 2. 6885 | maps | Joes | | 2200

(2 rows)

1. --关闭游标。
2. openGauss=# CLOSE cursor1;
3. CLOSE CURSOR

5. --结束一个事务。
6. openGauss=# END;
7. COMMIT
8. ```