Difference between OPEN CURSOR and FETCH CURSOR

Introduction

In previous article we have discussed the difference between Cursor and Select Statements. In this article we will focus upon the differences between Open Cursor and Fetch Cursor. Both Open Cursor and Fetch statements are part of SQL queries. Interestingly, both statements are dependent upon each other and are used to extract data or perform operations on SAP tables.

OPEN CURSOR

A cursor in SAP is like a pointer in other coding languages. It points towards the result of specific data selection. Unlike SQL query like SELECT, cursor doesn’t store any data in a variable, rather it points data with the help of a variable. To use cursor, we need to open it up, fetch required data and the close the cursor after its use. SAP allows 17 Open cursors at a time.

OPEN CURSOR is a SQL statement that is used to open a database cursor and hold the pointer/cursor in a local variable.

Syntax:

OPEN CURSOR [WITH HOLD] dbcur FOR
  SELECT { select_clause
           FROM source }
        |{ FROM source
           FIELDS select_clause }
           [[FOR ALL ENTRIES IN itab] WHERE sql_cond]
           [GROUP BY group] [HAVING group_cond]
           [UNION [ALL|DISTINCT] select]
       [ORDER BY sort_key]
           [additional_options].

 

Here the local variable dbcur is holding the cursor value. Select statement tells the cursor, what to hold.

FETCH CURSOR

Once we open a cursor, then we need to use the same with the help of another SQL query i.e. FETCH. A FETCH query extracts the requested row from the result set of the previously opened cursor (dbcur) and assigns them to a local variable.

Syntax:

FETCH NEXT CURSOR dbcur INTO|APPENDING ....

Difference between OPEN CURSOR and FETCH CURSOR

OPEN CURSOR FETCH CURSOR
OPEN CURSOR does not store the data in any variable during runtime. A FETCH statement stores the data of a cursor in a local variable during runtime.
OPEN CURSOR points to a data of a table. A FETCH statement actually stores data of a table in local variable.
OPEN CURSOR is the very first step of a CURSOR pattern. FETCH statements are second and most important step of CURSOR pattern.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.