Using Cursor in ABAP

Cursor is a database object which actually points to a database table with selected fields. It has a life cycle: declare, open, fetch and close. In daily requirement we need to fetch data from cluster tables like BSEG, MSEG, RSEG etc. Since these are cluster tables, these hold a lot of data. So, if we use Select query to fetch from cluster table then system will take a lot of time. This is because Select query always reads directly to the target are. Due to this we use Cursor which actually points those selected area of database. Initially it does not fetch data, just points to those data. Then after when we use FETCH NEXT CURSOR, system actually fetches one by one records into internal table or field-symbol. This technique increases the query performance 100 to 1000 times. With the help of that we can create query to the cluster table and fetch data in a faster way.

Declare the Cursor:

·         The cursor is declared by the DATA statement with keyword CURSOR.

Open Cursor Statement:

·         Open cursor opens a database cursor for a specific selection, defined after FOR.

·         It links the cursor variable (cr_spfli) to the database cursor.

·         If the cursor variable is already opened then it cannot be reopened.

·         The statement takes the cursor position at the first row of the resulting set.

·         The select statement declared after FOR doesn’t enter any record into any table or work area.

·         Select single statement cannot be used here.

·         Only a limited number of database cursor can be open at the same time.

·         Open cursor actually initialize the cursor at the first position of database.

Fetch Next Cursor Statement:

·         It extracts the requested rows from the database.

·         We can enter the fetched data into a table or work area. The append work can also be done here.

·         It changes the position of the database cursor to the next line to be extracted.

·         System can fetch one or more data records by this statement.

·         Sy-subrc will be zero when the system fetches data.

·         When the cursor is at the last position of rows then the next cursor will cause sy-subrc = 4. Because no line will be extracted further.

Close Cursor Statement:

·         It closes the database cursor and initializes the cursor variable.

·         We should close all the open database cursor if they are no longer required.

·         Once the cursor is closed it no longer is accessed.

In the following example we have demonstrated a program where cursor has been used.

TABLES mseg.
DATA it_fcat TYPE slis_t_fieldcat_alv.

INITIALIZATION.
  SELECT-OPTIONS: s_date  FOR sy-datum,
                  s_bwart FOR mseg-bwart NO INTERVALS.

CLASS cl_test DEFINITION.
  PUBLIC SECTION.
    TYPES: BEGIN OF ts_mkpf,
             mblnr TYPE mkpf-mblnr,
             mjahr TYPE mkpf-mjahr,
             cpudt TYPE mkpf-cpudt,
           END OF ts_mkpf.
    DATA it_mkpf TYPE TABLE OF ts_mkpf.

    TYPES: BEGIN OF ts_mseg,
             mblnr TYPE mseg-mblnr,
             mjahr TYPE mseg-mjahr,
             zeile TYPE mseg-zeile,
             bwart TYPE mseg-bwart,
             matnr TYPE mseg-matnr,
             werks TYPE mseg-werks,
             lgort TYPE mseg-lgort,
             menge TYPE mseg-menge,
             meins TYPE mseg-meins,
             erfmg TYPE mseg-erfmg,
             aufnr TYPE mseg-aufnr,
             cpudt TYPE mkpf-cpudt,
           END OF ts_mseg.
    DATA: it_mseg   TYPE TABLE OF ts_mseg,
          wa_layout TYPE slis_layout_alv.

    METHODS:
      m_get_mkpf,
      m_get_mseg,
      m_field_catalog,
      m_alv_grid.
ENDCLASS.

CLASS cl_test IMPLEMENTATION.
  METHOD m_get_mkpf.
    SELECT mblnr mjahr cpudt
      FROM mkpf INTO TABLE it_mkpf
      WHERE cpudt IN s_date.
  ENDMETHOD.

  METHOD m_get_mseg.
    DATA: mseg_cursor TYPE cursor,
          lw_mseg     TYPE ts_mseg.

    IF it_mkpf IS NOT INITIAL.
      SORT it_mkpf BY mblnr mjahr.

      OPEN CURSOR WITH HOLD mseg_cursor FOR
      SELECT mblnr mjahr zeile bwart
             matnr werks lgort menge
             meins erfmg aufnr
        FROM mseg FOR ALL ENTRIES IN it_mkpf
        WHERE mblnr = it_mkpf-mblnr
          AND mjahr = it_mkpf-mjahr
          AND bwart IN s_bwart.

      DO.
        FETCH NEXT CURSOR mseg_cursor INTO
        (mseg-mblnr,
         mseg-mjahr,
         mseg-zeile,
         mseg-bwart,
         mseg-matnr,
         mseg-werks,
         mseg-lgort,
         mseg-menge,
         mseg-meins,
         mseg-erfmg,
         mseg-aufnr).

        IF sy-subrc <> 0.
          EXIT.
        ENDIF.

        lw_mseg-mblnr = mseg-mblnr.
        lw_mseg-mjahr = mseg-mjahr.
        lw_mseg-zeile = mseg-zeile.
        lw_mseg-bwart = mseg-bwart.
        lw_mseg-matnr = mseg-matnr.
        lw_mseg-werks = mseg-werks.
        lw_mseg-lgort = mseg-lgort.
        lw_mseg-menge = mseg-menge.
        lw_mseg-meins = mseg-meins.
        lw_mseg-erfmg = mseg-erfmg.
        lw_mseg-aufnr = mseg-aufnr.

        READ TABLE it_mkpf ASSIGNING FIELD-SYMBOL(<lw_mkpf>)
        WITH KEY mblnr = mseg-mblnr
                 mjahr = mseg-mjahr BINARY SEARCH.
        IF sy-subrc = 0.
          lw_mseg-cpudt = <lw_mkpf>-cpudt.
        ENDIF.

        APPEND lw_mseg TO it_mseg.
        CLEAR: lw_mseg.
      ENDDO.
      CLOSE CURSOR mseg_cursor.
    ENDIF.
  ENDMETHOD.

  METHOD m_field_catalog.
    wa_layout-colwidth_optimize = 'X'.
    wa_layout-zebra             = 'X'.

    PERFORM field_catalog USING:
          'MBLNR'  'Mat.Doc.'        'X',
          'MJAHR'  'Year'            'X',
          'BWART'  'Movement'        '',
          'MATNR'  'Material'        'X',
          'WERKS'  'Plant'           '',
          'LGORT'  'Storage'         '',
          'MENGE'  'Quantity'        'X',
          'MEINS'  'UoM'             '',
          'AUFNR'  'Order'           'X'.
  ENDMETHOD.

  METHOD m_alv_grid.
    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
      EXPORTING
        i_callback_program = sy-repid
        is_layout          = wa_layout
        it_fieldcat        = it_fcat
        i_default          = 'X'
        i_save             = 'A'
      TABLES
        t_outtab           = it_mseg
      EXCEPTIONS
        program_error      = 1
        OTHERS             = 2.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  DATA obj_test TYPE REF TO cl_test.
  CREATE OBJECT obj_test.
  CALL METHOD: obj_test->m_get_mkpf,
               obj_test->m_get_mseg,
               obj_test->m_field_catalog,
               obj_test->m_alv_grid.

*&---------------------------------------------------------------------*
*& Form FIELD_CATALOG
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
FORM field_catalog  USING p_fname   TYPE slis_fieldname
                          p_text    TYPE dd03p-scrtext_l
                          p_no_zero TYPE c.

  DATA lw_fcat TYPE slis_fieldcat_alv.
  lw_fcat-fieldname = p_fname.
  lw_fcat-seltext_l = p_text.
  lw_fcat-no_zero   = p_no_zero.
  APPEND lw_fcat TO it_fcat.
  CLEAR  lw_fcat.
ENDFORM.

The output is as follows.