EXEC SQL - OPEN, CLOSE, FETCH

In native SQL, similar statements for reading data using a database cursor as in Open SQL can be specified.

Syntax

EXEC SQL.
  OPEN dbcur FOR SELECT ...
ENDEXEC.

Effect

Opens a database cursor dbcur. For dbcur, a flat character-type host variable can be specifried.

Syntax

EXEC SQL.
  FETCH NEXT dbcur INTO ...
ENDEXEC.

Effect

Reads data using an open database cursor dbcur.

Syntax

EXEC SQL.
  CLOSE dbcur
ENDEXEC.

Effect

Closes an opened database cursor dbcur.

If no row can be read using FETCH, sy-subrc is set to 4 by ENDEXEC. The system field sy-dbcnt is set, after a FETCH statement, to the number of rows read up to that point using the respective cursor.

Example

Reading several rows from the database table SPFLI using cursor handling and host variables in native SQL. If rows were found, sy-subrc is set to 0 and sy-dbcnt is increased by one for each row read.

PARAMETERS p_carrid TYPE spfli-carrid.

DATA:  connid   TYPE spfli-connid,
       cityfrom TYPE spfli-cityfrom,
       cityto   TYPE spfli-cityto.

EXEC SQL.
  OPEN dbcur FOR
    SELECT connid, cityfrom, cityto
           FROM spfli
           WHERE carrid = :p_carrid
ENDEXEC.

DO.
  EXEC SQL.
    FETCH NEXT dbcur INTO :connid, :cityfrom, :cityto
  ENDEXEC.
  IF sy-subrc <> 0.
    EXIT.
  ELSE.
    ...
  ENDIF.
ENDDO.

EXEC SQL.
  CLOSE dbcur
ENDEXEC.