Using Cursor in ABAP
A cursor is a database object which is used to manipulate data in a set of row by row. We can say that a cursor is a set of rows with a pointer and this pointer actually points to the current row. Since cursor works row by row, it actually kills the performance. So it is better to go with another way with the help ABAP logic. In ABAP we use cursor with the following four processes.
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.
REPORT zsr_test NO STANDARD PAGE HEADING.
TABLES spfli.
DATA: wa_spfli TYPE spfli.
"Declare cursor
data: cr_spfli TYPE cursor.
PARAMETERS p_from TYPE spfli-countryfr.
"Open Cursor
OPEN CURSOR cr_spfli FOR SELECT *
FROM spfli WHERE countryfr = p_from.
IF sy-subrc = 0.
WRITE: / 'Airline',
10 'Flight Number',
30 'Country From',
45 'City From',
66 'Departure airport',
86 'Country To',
100 'City To',
121 'Destination airport',
142 'Departure time',
160 'Arrival time',
175 'Distance'.
ULINE.
SKIP.
ENDIF.
DO.
"Fetch Next Cursor
FETCH NEXT CURSOR cr_spfli
INTO wa_spfli.
IF sy-subrc = 0.
CHECK wa_spfli-countryfr = p_from.
WRITE: /3 wa_spfli-carrid,
10 wa_spfli-connid,
30 wa_spfli-countryfr,
45 wa_spfli-cityfrom,
66 wa_spfli-airpfrom,
86 wa_spfli-countryto,
100 wa_spfli-cityto,
121 wa_spfli-airpto,
142 wa_spfli-deptime,
160 wa_spfli-arrtime,
175 wa_spfli-distance.
ELSE.
EXIT.
ENDIF.
ENDDO.
"Close Cursor
CLOSE CURSOR cr_spfli.
TABLES spfli.
DATA: wa_spfli TYPE spfli.
"Declare cursor
data: cr_spfli TYPE cursor.
PARAMETERS p_from TYPE spfli-countryfr.
"Open Cursor
OPEN CURSOR cr_spfli FOR SELECT *
FROM spfli WHERE countryfr = p_from.
IF sy-subrc = 0.
WRITE: / 'Airline',
10 'Flight Number',
30 'Country From',
45 'City From',
66 'Departure airport',
86 'Country To',
100 'City To',
121 'Destination airport',
142 'Departure time',
160 'Arrival time',
175 'Distance'.
ULINE.
SKIP.
ENDIF.
DO.
"Fetch Next Cursor
FETCH NEXT CURSOR cr_spfli
INTO wa_spfli.
IF sy-subrc = 0.
CHECK wa_spfli-countryfr = p_from.
WRITE: /3 wa_spfli-carrid,
10 wa_spfli-connid,
30 wa_spfli-countryfr,
45 wa_spfli-cityfrom,
66 wa_spfli-airpfrom,
86 wa_spfli-countryto,
100 wa_spfli-cityto,
121 wa_spfli-airpto,
142 wa_spfli-deptime,
160 wa_spfli-arrtime,
175 wa_spfli-distance.
ELSE.
EXIT.
ENDIF.
ENDDO.
"Close Cursor
CLOSE CURSOR cr_spfli.
The output is as follows.
This program can be done in another way as follows.
REPORT zsr_test NO STANDARD PAGE HEADING.
TABLES spfli.
DATA: wa_spfli TYPE spfli,
it_spfli TYPE TABLE OF spfli.
"Declare Cursor
DATA: cr_spfli TYPE cursor.
PARAMETERS p_from TYPE spfli-countryfr.
"Open Cursor
OPEN CURSOR cr_spfli FOR SELECT *
FROM spfli WHERE countryfr = p_from.
"Fetch Cursor
FETCH NEXT CURSOR cr_spfli
INTO TABLE it_spfli.
IF sy-subrc = 0.
WRITE: / 'Airline',
10 'Flight Number',
30 'Country From',
45 'City From',
66 'Departure airport',
86 'Country To',
100 'City To',
121 'Destination airport',
142 'Departure time',
160 'Arrival time',
175 'Distance'.
ULINE.
SKIP.
LOOP AT it_spfli INTO wa_spfli.
WRITE: /3 wa_spfli-carrid,
10 wa_spfli-connid,
30 wa_spfli-countryfr,
45 wa_spfli-cityfrom,
66 wa_spfli-airpfrom,
86 wa_spfli-countryto,
100 wa_spfli-cityto,
121 wa_spfli-airpto,
142 wa_spfli-deptime,
160 wa_spfli-arrtime,
175 wa_spfli-distance.
ENDLOOP.
ENDIF.
"Close Cursor
CLOSE CURSOR cr_spfli.
TABLES spfli.
DATA: wa_spfli TYPE spfli,
it_spfli TYPE TABLE OF spfli.
"Declare Cursor
DATA: cr_spfli TYPE cursor.
PARAMETERS p_from TYPE spfli-countryfr.
"Open Cursor
OPEN CURSOR cr_spfli FOR SELECT *
FROM spfli WHERE countryfr = p_from.
"Fetch Cursor
FETCH NEXT CURSOR cr_spfli
INTO TABLE it_spfli.
IF sy-subrc = 0.
WRITE: / 'Airline',
10 'Flight Number',
30 'Country From',
45 'City From',
66 'Departure airport',
86 'Country To',
100 'City To',
121 'Destination airport',
142 'Departure time',
160 'Arrival time',
175 'Distance'.
ULINE.
SKIP.
LOOP AT it_spfli INTO wa_spfli.
WRITE: /3 wa_spfli-carrid,
10 wa_spfli-connid,
30 wa_spfli-countryfr,
45 wa_spfli-cityfrom,
66 wa_spfli-airpfrom,
86 wa_spfli-countryto,
100 wa_spfli-cityto,
121 wa_spfli-airpto,
142 wa_spfli-deptime,
160 wa_spfli-arrtime,
175 wa_spfli-distance.
ENDLOOP.
ENDIF.
"Close Cursor
CLOSE CURSOR cr_spfli.
The output is similar as before.