Tuesday, April 26, 2016

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-OPTIONSs_date  FOR sy-datum,
                  s_bwart FOR mseg-bwart NO INTERVALS.

CLASS cl_test DEFINITION.
  PUBLIC SECTION.
    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.

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

      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.
        CLEARlw_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 METHODobj_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.


5 comments:

Rohini Kumar said...

hey you explain very bell hope you explain some new topic like webdynpro and workflow also

Anonymous said...

SAP Success Factors Real Time Hands on Training in Chennai...

Don't always Depend on Training Institute Alone and so please aware of Best Trainers too..

http://thecreatingexperts.com/sap-successfactors-training-in-chennai/

If You need a Best Trainer over SAP Success Factors Means??? Please ready for an DEMO From the Trainer MR.Karthick
CONTACT:8122241286

Both Classroom/Online Training is Available!!!!!!

kavi kalyan said...

Best SAP Success Factor Training in Chennai

http://thecreatingexperts.com/sap-training-in-chennai/
http://thecreatingexperts.com/sap-successfactors-training-in-chennai/
http://thecreatingexperts.com/sap-mm-training-in-chennai/
http://thecreatingexperts.com/sap-fico-training-in-chennai/

Anonymous said...

Informative Blog...For a long time I was craving for a career growth in programming and then I came to know that THE CREATING EXPERTS is the one who provide training with hands on training and real time scenarios

http://thecreatingexperts.com/sap-abap-training-in-chennai/

contact 8122241286

Anonymous said...

Hi Frndz,

My Name is KUmar , I am providing realtime Training on SAP ABAP with including FS and TS and ticketing tools , how u vil do inside of company.

so most of the people dnt know these real time training with low cost in in offline in hyderabad only and online also for limited peoples.
mail me at s.b.kumar123@gmail.com