管理游标

最近更新时间:2019-12-03 17:12:33

定义一个游标

postgres=# begin;
BEGIN
postgres=#  DECLARE tbase_cur SCROLL CURSOR FOR SELECT * from tbase ORDER BY id;              
DECLARE CURSOR
说明:

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

提取下一行数据

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

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

提取前一行数据

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

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

提取最后一行

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

提取第一行

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

提取该查询的第x行

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

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

postgres=# FETCH ABSOLUTE -2 from tbase_cur ; 
 id |   nickname    
----+---------------
  4 | TBase default
(1 row)

x为负数时则从尾部向上提。

提取当前位置后的第x行

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

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

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

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

向前提取x行数据

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

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

向前提取剩下的所有数据

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

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

向后提取x行数据

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

向后提取剩下的所有数据

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