Scrollable Cursor Support for Reverse Retrieval

Availability

This feature is available since MogDB 5.0.6.

Introduction

A cursor refers to a handle or pointer to a context area. With the help of a cursor, stored procedures can control the changes in the context area. This feature supports specifying SCROLL when declaring a cursor, making it possible to retrieve data rows in reverse order (i.e., backward retrieval). Depending on the complexity of the execution plan of the query statement, specifying SCROLL may result in performance loss in query execution time. The relevant syntax is as follows:

DECLARE cursor_name [ BINARY ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query;

Constraints

• This feature is applicable to both A and PG compatibility modes.
• It does not support for update/share.
• It does not support the refcursor of the cursor.

Example

-- Create table and insert data
drop table if exists t_scroll_cursor_0016 cascade; 

create table t_scroll_cursor_0016(c1 int constraint i_scroll_cursor_0016 primary key,c2 text);

insert into t_scroll_cursor_0016 values(generate_series(1,100000),'t_scroll_cursor_0016' || generate_series(1,100000));

-- Declare cursor and fetch data
BEGIN;
DECLARE c_scroll_cursor_0016 SCROLL CURSOR FOR SELECT * FROM t_scroll_cursor_0016 where c1 between 1000 and 1200 ORDER BY c1;
FETCH 2 FROM c_scroll_cursor_0016;
FETCH BACKWARD 1 FROM c_scroll_cursor_0016; 
FETCH 100 FROM c_scroll_cursor_0016;
END;

Related Pages

DECLARE, CURSOR, Cursors