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.

  SELECT-OPTIONSs_date  FOR sy-datum,
                  s_bwart FOR mseg-bwart NO INTERVALS.

    TYPESBEGIN 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.

    TYPESBEGIN 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.
    DATAit_mseg   TYPE TABLE OF ts_mseg,
          wa_layout TYPE slis_layout_alv.


  METHOD m_get_mkpf.
    SELECT mblnr mjahr cpudt
      FROM mkpf INTO TABLE it_mkpf
      WHERE cpudt IN s_date.

  METHOD m_get_mseg.
    DATAmseg_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.

        FETCH NEXT CURSOR mseg_cursor INTO

        IF sy-subrc <> 0.

        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.

        APPEND lw_mseg TO it_mseg.
      CLOSE CURSOR mseg_cursor.

  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'.

  METHOD m_alv_grid.
        i_callback_program sy-repid
        is_layout          wa_layout
        it_fieldcat        it_fcat
        i_default          'X'
        i_save             'A'
        t_outtab           it_mseg
        program_error      1
        OTHERS             2.

  DATA obj_test TYPE REF TO cl_test.
  CREATE OBJECT obj_test.
  CALL METHODobj_test->m_get_mkpf,

*& 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.

The output is as follows.