Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

OPEN CURSOR and FETCH

Former Member
0 Kudos

Hi,

Can some expert please provide any good reasons for using OPEN CURSOR and FETCH to access data ? Is it an old technique ? still in use ? Is there any real performance gain in using this CURSOR technique in comparison with direct selection from database tables ?

Thanks,

Ruong

7 REPLIES 7

Former Member
0 Kudos

The OPEN CURSOR statement allows you to open several cursors at the same time in a table. Unlike with SELECT, you thus have several independent access paths to this table.

Since you can open only a restricted number of cursors at the same time, you should close cursors that are no longer required with CLOSE CURSOR.

Since the OPEN statement does not support authorization checks, you must program these yourself.

Related

SELECT, FETCH and CLOSE.

for more detail..

http://help.sap.com/saphelp_nw04/helpdata/en/9f/db9b5e35c111d1829f0000e829fbfe/content.htm

former_member378318
Contributor
0 Kudos

OPEN CURSOR and FETCH is also needed if your select statement is to return more than one row.

Former Member

HI Ruong,

In the normal SELECT statement, the data from the selection is always read directly into the target area specified in the INTO clause during the SELECT statement. When you use a cursor to read data, you decouple the process from the SELECT statement. To do this, you must open a cursor for a SELECT statement. Afterwards, you can place the lines from the selection into a flat target area.

Opening and Closing Cursors

To open a cursor for a SELECT statement, use the following:

OPEN CURSOR [WITH HOLD] <c> FOR SELECT <result>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

You must first have declared the cursor <c> using the DATA statement and the special data type CURSOR. You can use all clauses of the SELECT statement apart from the INTO clause. Furthermore, you can only formulate the SELECT clause so that the selection consists of more than one line. This means that you may not use the SINGLE addition, and that the column selection may not contain only aggregate expressions.

An open cursor points to an internal handler, similarly to a reference variable pointing to an object. You can reassign cursors so that more than one points to the same handler. In a MOVE statement, the target cursor adopts all of the attributes of the source cursor, namely its position, and all of the clauses in the OPEN CURSOR statement.

You can also open more than one cursor in parallel for a single database table. If a cursor is already open, you cannot reopen it. To close a cursor explicitly, use the following statement:

CLOSE CURSOR <c>.

You should use this statement to close all cursors that you no longer require, since only a limited number of cursors may be open simultaneously. With one exception, a database LUW is concluded when you close a cursor either explicitly or implicitly. The WITH HOLD addition in the OPEN CURSOR statement allows you to prevent a cursor from being closed when a database commit occurs in Native SQL.

Reading Data

An open cursor is linked to a multiple-line selection in the database table. To read the data into a target area in the ABAP program, use the following:

FETCH NEXT CURSOR <c> INTO <target>.

This writes one line of the selection into the target area <target>, and the cursor moves one line further in the selection set. The fetch statement decouples the INTO clause from the other clauses in the SELECT statement. All the INTO clauses of the SELECT statement can be used. The statement reads the lines that are needed to fill the target area of the INTO clause and moves the cursor to the next line to be read.

SY-SUBRC is set to 0 until all the lines of the selection have been read; otherwise it is 4. After a FETCH statement, system field SY-DBCNT contains the number of all the lines read so far for the corresponding cursor.

REPORT demo_select_cursor_1.

DATA: c1 TYPE cursor,

c2 TYPE cursor.

DATA: wa1 TYPE spfli,

wa2 TYPE spfli.

DATA: flag1(1) TYPE c,

flag2(1) TYPE c.

OPEN CURSOR: c1 FOR SELECT carrid connid

FROM spfli

WHERE carrid = 'LH',

c2 FOR SELECT carrid connid cityfrom cityto

FROM spfli

WHERE carrid = 'AZ'.

DO.

IF flag1 NE 'X'.

FETCH NEXT CURSOR c1 INTO CORRESPONDING FIELDS OF wa1.

IF sy-subrc <> 0.

CLOSE CURSOR c1.

flag1 = 'X'.

ELSE.

WRITE: / wa1-carrid, wa1-connid.

ENDIF.

ENDIF.

IF flag2 NE 'X'.

FETCH NEXT CURSOR c2 INTO CORRESPONDING FIELDS OF wa2.

IF sy-subrc <> 0.

CLOSE CURSOR c2.

flag2 = 'X'.

ELSE.

WRITE: / wa2-carrid, wa2-connid,

wa2-cityfrom, wa2-cityto.

ENDIF.

ENDIF.

IF flag1 = 'X' AND flag2 = 'X'.

EXIT.

ENDIF.

ENDDO.

eg:

FETCH NEXT CURSOR c ... INTO wa.

FETCH NEXT CURSOR c ... INTO TABLE itab.

FETCH NEXT CURSOR c ... APPENDING TABLE itab.

Regards,

Laxmi.

Former Member
0 Kudos

Hi,

Sorry if I have wasted your time, I already know how to code these statements, I just wanted to know from the experts whether there are any real advantages / improvements in program run-time performance if I use these statements to access data as opposed to the normal direct data selection ?

Thanks,

Ruong

0 Kudos

I believe the OPEN CURSOR might have a slight advantage in terms of performance, as its a native SQL command instead of the Open SQL command SELECT. However, it comes with its own disadvantages - debugging is not possible and errors will be difficult to analyze and fix.

SAP recommends the use of Open SQL where possible, usually native SQL is used only in cases where the DB hit is very large.

Hope this helps.

Sudha

0 Kudos

Ruong,

The Open/Fetch should be used when the SELECT is to return a large amount of data, which you need to process.

It is much efficient as compared to using nested SELECT statements.

Regards

Anurag

Bottom line is you should not be using OPEN CURSOR/FETCH if you can use an ABAP select statement. If on the other hand you are fetching data from a remote database that does not support the ABAP interface then you have no choice but to use OPEN CURSOR/FETCH.

I can't see why you would use OPEN CURSOR/FETCH in place of ABAP SELECT/SELECT SINGLE/SELECT INTO TABLE, etc.