TDSQL PostgreSQL版
有奖捉虫：办公协同&微信生态&物联网文档专题 HOT
文档中心 > TDSQL PostgreSQL版 > 开发指南 > 数据库基础开发 > 游标的使用

游标的使用

最近更新时间：2022-09-02 16:54:15

我的收藏

本页目录：

定义一个游标

postgres=# begin;
BEGIN
postgres=#  DECLARE tdsql_pg_cur SCROLL CURSOR FOR SELECT * from tdsql_pg ORDER BY id;        
DECLARE CURSOR

游标需要放在一个事务中使用。

提取下一行数据

postgres=# DECLARE tdsql_pg_cur SCROLL CURSOR FOR SELECT * from tdsql_pg ORDER BY id; 
DECLARE CURSOR
postgres=# FETCH NEXT from tdsql_pg_cur ;        
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
(1 row)

postgres=# FETCH NEXT from tdsql_pg_cur ;
 id | nickname  
----+-----------
 2 | tdsql_pg好
(1 row)

提取前一行数据

postgres=# FETCH PRIOR from tdsql_pg_cur ;
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
(1 row)

postgres=# FETCH PRIOR from tdsql_pg_cur ;
 id | nickname 
----+----------
(0 rows)

提取最后一行

postgres=# FETCH LAST from tdsql_pg_cur ;     
 id |  nickname  
----+------------
 5 | tdsql_pg swap
(1 row)

提取第一行

postgres=# FETCH FIRST from tdsql_pg_cur ;
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
(1 row)

提取该查询的第 x 行

postgres=# FETCH ABSOLUTE 2 from tdsql_pg_cur ;         
 id | nickname  
----+-----------
 2 | tdsql_pg好
(1 row)

postgres=# FETCH ABSOLUTE -1 from tdsql_pg_cur ; 
 id |  nickname  
----+------------
 5 | tdsql_pg swap
(1 row)

postgres=# FETCH ABSOLUTE -2 from tdsql_pg_cur ; 
 id |  nickname   
----+---------------
 4 | tdsql_pg default
(1 row)
x 为负数时则从尾部向上提。

提取当前位置后的第 x 行

postgres=#  FETCH ABSOLUTE 1 from tdsql_pg_cur ; 
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
(1 row)

postgres=# FETCH RELATIVE 2 from tdsql_pg_cur ;
 id | nickname  
----+-----------
 3 | tdsql_pg好
(1 row)

postgres=# FETCH RELATIVE 2 from tdsql_pg_cur ;
 id |  nickname  
----+------------
 5 | tdsql_pg swap
(1 row)

每提取一次数据，游标的位置都是会前行。

向前提取 x 行数据

postgres=# FETCH FORWARD 2 from tdsql_pg_cur ;
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
 2 | tdsql_pg好
(2 rows)

postgres=# FETCH FORWARD 2 from tdsql_pg_cur ; 
 id |  nickname   
----+---------------
 3 | tdsql_pg好
 4 | tdsql_pg default
(2 rows)

向前提取剩下的所有数据

postgres=# FETCH FORWARD 2 from tdsql_pg_cur ;   
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
 2 | tdsql_pg好
(2 rows)

postgres=# FETCH FORWARD all from tdsql_pg_cur ;                     
 id |  nickname   
----+---------------
 3 | tdsql_pg好
 4 | tdsql_pg default
 5 | tdsql_pg swap
(3 rows)

向后提取 x 行数据

postgres=# FETCH BACKWARD 2 from tdsql_pg_cur ;
 id |  nickname   
----+---------------
 5 | tdsql_pg swap
 4 | tdsql_pg default
(2 rows)

向后提取剩下的所有数据

postgres=# FETCH BACKWARD all from tdsql_pg_cur ; 
 id |  nickname  
----+-------------
 3 | tdsql_pg好
 2 | tdsql_pg好
 1 | hello tdsql_pg
(3 rows)

中国站