Using a Cursor to Read Data (SAP Library - ABAP Programming (BC-ABA))

Using a Cursor to Read Data

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 DATAstatement and the special data type CURSOR. You can use all clauses of the SELECTstatement 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 MOVEstatement, 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 HOLDaddition 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 FETCHstatement decouples the INTO clause from the other clauses in the SELECT statement. You can use all INTO clauses of the SELECTstatement. The statement reads the required lines to fill the target area of the INTO clause and moves the cursor to the next line.

If not all lines in the selection have been read, sy-subrc is set to 0; otherwise, it is set to 4. After a FETCH statement, the system field sy-dbcntcontains the number of lines read for the corresponding cursor.

Example

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.

The output looks something like this:

This graphic is explained in the accompanying text

The database table SPFLI is read using two cursors, each with different conditions.. The selected lines are read alternately in a DO loop.

Example

REPORT demo_select_cursor_2.

DATA c TYPE cursor.

DATA wa TYPE sbook.

OPEN CURSOR c FOR SELECT     carrid connid fldate bookid smoker
                    FROM     sbook
                    ORDER BY carrid connid fldate smoker bookid.

FETCH NEXT CURSOR c INTO CORRESPONDING FIELDS OF wa.

WHILE sy-subrc = 0.
  IF wa-smoker = ' '.
    PERFORM nonsmoker USING c.
  ELSEIF wa-smoker = 'X'.
    PERFORM smoker USING c.
    SKIP.
  ELSE.
    EXIT.
  ENDIF.
ENDWHILE.

FORM nonsmoker USING n_cur TYPE cursor.
  WHILE wa-smoker = ' ' AND sy-subrc = 0.
    FORMAT COLOR = 5.
    WRITE: / wa-carrid, wa-connid, wa-fldate, wa-bookid.
    FETCH NEXT CURSOR n_cur INTO CORRESPONDING FIELDS OF wa.
  ENDWHILE.
ENDFORM.

FORM smoker USING s_cur TYPE cursor.
  WHILE wa-smoker = 'X' AND sy-subrc = 0.
    FORMAT COLOR = 6.
    WRITE: / wa-carrid, wa-connid, wa-fldate, wa-bookid.
    FETCH NEXT CURSOR s_cur INTO CORRESPONDING FIELDS OF wa.
  ENDWHILE.
ENDFORM.

The following is an extract from the list display:

This graphic is explained in the accompanying text

The program opens a cursor for the database table SBOOK. After the first FETCH statement, a subroutine is called, which is dependent on the contents of the SMOKER column. The cursor is passed to an interface parameter in the subroutine. The subroutines read further lines until the contents of the SMOKER column change. The subroutines perform different tasks using the lines read by the cursor.

Example

REPORT demo_select_cursor_3.

DATA: wa_spfli   TYPE spfli,
      wa_sflight TYPE sflight,
      wa_sflight_back TYPE sflight.

DATA: c1 TYPE cursor,
c2 TYPE cursor.

OPEN CURSOR c1 FOR     SELECT *
                 FROM  spfli
                 ORDER BY PRIMARY KEY.

OPEN CURSOR c2 FOR     SELECT *
                 FROM  sflight
                 ORDER BY PRIMARY KEY.

DO.
  FETCH NEXT CURSOR c1 INTO wa_spfli.
  IF sy-subrc NE 0.
    EXIT.
  ENDIF.
  WRITE: / wa_spfli-carrid, wa_spfli-connid.
  DO.
    IF NOT wa_sflight_back IS INITIAL.
      wa_sflight = wa_sflight_back.
      CLEAR wa_sflight_back.
    ELSE.
      FETCH NEXT CURSOR c2 INTO wa_sflight.
      IF  sy-subrc <> 0.
        EXIT.
      ELSEIF wa_sflight-carrid <> wa_spfli-carrid
          OR wa_sflight-connid <> wa_spfli-connid.
        wa_sflight_back = wa_sflight.
        EXIT.
      ENDIF.
    ENDIF.
    WRITE: / wa_sflight-carrid, wa_sflight-connid,
             wa_sflight-fldate.
  ENDDO.
ENDDO.

The output is as follows:

This graphic is explained in the accompanying text

The program opens a cursor for each of the table SPFLI and SFLIGHT. Since both tables are linked by a foreign key relationship, it is possible to program a nested loop by sorting the selection by its primary key, so that the data read in the inner loop depends on the data in the outer loop. This programming method is quicker than using nested SELECT statements, since the cursor for the inner loop does not continually have to be reopened. If there is a control level change in the inner loop, the read data is buffered until the next loop pass, since the cursor cannot be reset.