Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

open cursor???

deepak_kumar11
Participant
0 Kudos

Hi all ,

Wat is the use of statement open cursor & how it should be used.

Deepak

1 ACCEPTED SOLUTION

0 Kudos

OPEN CURSOR

OPEN CURSOR [WITH HOLD] dbcur FOR

SELECT result

FROM source

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

[GROUP BY group] [HAVING group_cond]

[ORDER BY sort_key].

Addition: ... WITH HOLD

This statement opens a database cursor for the selection defined after FOR, and links a cursor variable dbcur with this database cursor. For dbcur, a declared variable with the specific predefined data type cursor must be entered. A database cursor dbcur that has already been opened cannot be opened again. A line of the resulting set is always assigned to an opened database cursor as a cursor position. After the OPEN CURSOR statement, the database cursor is positioned in front of the first line of the resulting set.

After FOR, the syntax of a SELECT statement can be entered, which contains all the additions of the normal SELECT statement, except for INTO and APPENDING. In the addition result, the addition SINGLE can also not be used after SELECT.

Only a limited number of database cursors can be open at the same time. An open database cursor can be closed using the statement CLOSE CURSOR . In addition, an open database cursor is closed for a database commit or a database rollback.

If a cursor variable dbcur of an open database cursor is assigned to another cursor variable, the latter is linked to the same database cursor at the same position. A cursor variable of an open database cursor can also be transferred to procedures that have been called externally, to enable the database cursor to be accessed from there.

It is not recommended to assign cursor variables to each other, but rather to set them exclusively using the statements OPEN CURSOR and CLOSE CURSOR.

Addition ... WITH HOLD

:

If the addition WITH HOLD is specified, the database cursor is not closed in an explicitly triggered database commit or database rollback, for example Native SQL. The addition WITH HOLD cannot be specified if the cursor is to be opened for a secondary database connection.

FETCH

FETCH NEXT CURSOR dbcur {INTO|APPENDING} target.

This statement extracts the requested rows (by use of the addition INTO resp. APPENDING) from the resulting set of the database cursor (which is linked to the cursor variable dbcur) from the current cursor-position and assigns these rows to the data objects specified in the resulting set.

The cursor-variable dbcur has to be a variable declared by the special pre-defined data type cursor, which was opened with the statement OPEN CURSOR, or which had an opened cursor assigned to.

Syntax and meaning of the addition INTO resp. APPENDING target are completely synonymous to the additions of the SELECT-statement. If you specify non-table-type data objects after INTO, then one line is extracted. If an internal table is specified after INTO resp. APPENDING, then, either all lines get extracted ,or as many as specified in the addition PACKAGE SIZE.

The statement FETCH moves the position of the database cursor (which is linked to dbcur) by the amount of extracted lines to the next line to be extracted. If you extracted the last line of the resulting set in a FETCH-statement, then every following FETCH-Statement, in which dbcur is linked to the same database-cursor, sets sy-subrc to 4, without influencing the data objects specified after INTO resp. APPENDING.

System Fields

The statement FETCH sets the values of the system fields sy-subrc and sy-dbcnt.

sy-subrc description

0 At least one line was extracted from the resulting set.

4 No line was extracted.

The statement FETCH sets sy-dbcnt after every line extraction to the amount of the lines that have been extracted so far from the resulting set in concern. If no line can be extracted, then sy-dbcnt is set to 0.

Subsequent FETCH-statements, which access the same resulting set, can have different additions INTO res. APPENDING: The specification of work areas can be combined with the specification of internal tables. In doing so, the addition CORRESPONDING FIELDS is either not listed at all in all the FETCH-statements, or has to be listed in every statement. Moreover, the data types of all involved work areas wa resp. the line types of the internal tables itab have to be identical. The specification of a bracketed list of data objects after INTO can not be combined with the specification of work areas or internal tables, but every involved FETCH-statement has to contain such a list.

Example

Reading of data of the database table SPFLI in pakets of varying size with the help of two parallel cursors. The paket size is determined by the first cursor through the aggregation functioncount( * ) and used via the second cursor when accessing. The variable control of the addition PACKAGE SIZE is not possible within a single SELECT-statement.

DATA: BEGIN OF count_line,

carrid TYPE spfli-carrid,

count TYPE i,

END OF count_line,

spfli_tab TYPE TABLE OF spfli.

DATA: dbcur1 TYPE cursor,

dbcur2 TYPE cursor.

OPEN CURSOR dbcur1 FOR

SELECT carrid count(*) AS count

FROM spfli

GROUP BY carrid

ORDER BY carrid.

OPEN CURSOR dbcur2 FOR

SELECT *

FROM spfli

ORDER BY carrid.

DO.

FETCH NEXT CURSOR dbcur1 INTO count_line.

IF sy-subrc <> 0.

EXIT.

ENDIF.

FETCH NEXT CURSOR dbcur2

INTO TABLE spfli_tab PACKAGE SIZE count_line-count.

ENDDO.

CLOSE CURSOR: dbcur1,

dbcur2.

... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

If the result set consists of multiple lines, an internal table itab of any table type can be specified after INTO or APPENDING. The row type of the internal table must meet the prerequisites.

The result set is inserted into the internal table itab line-by-line; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized before the first line is inserted. Previous lines remain intact if APPENDING is used.

Before any assignment of a line of the result set, an initial row of the internal table itab is created and the line of the result set is assigned to this row. When assigning a line of the result set to a row of the internal table with or without CORRESPONDING FIELDS, the same rules apply as when assigning to an individual work area wa (see above).

If the PACKAGE SIZE addition is not used, all lines of the result set are inserted in the internal table itab and the ENDSELECT statement must not be specified after SELECT.

If you specify the PACKAGE SIZE addition, all lines of the result set are processed in a loop, which must be closed with ENDSELECT. The lines are inserted in the internal table itab in packages of n lines. n must be a type i data object that contains the number of lines. If the value of n is smaller than 0, an exception that cannot be handled occurs. If n is equal to 0, all lines of the result set are inserted in the internal table itab.

If INTO is used, the internal table is initialized before each loop pass and, in the SELECT loop, it only contains the lines of thecurrent package. If APPENDING is used, a further package is added to the existing rows of the internal table in each SELECT loop.

After ENDSELECT, the contents of itab is not defined if INTO is used - that is, the table can either contain the lines of the last package or it can be initial. If APPENDING is used, the content of itab retains the state of the last loop pass.

In the case of an internal table with a unique key, an exception that cannot be handled occurs if an attempt is made to create a duplicate entry

Example

In this example, all columns of the result set are read into an internal table whose row type is a nested structure with the same construction as the result set. Note that in practice, the column carrid exists twice in the result set with the same content and, after the assignment, this content is stored redundantly in the columns struc1-carrid and struc2-carrid of the internal table.

DATA: BEGIN OF wa,

struc1 TYPE scarr,

struc2 TYPE spfli,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY table_line.

SELECT *

FROM scarr

INNER JOIN spfli ON scarrcarrid = spflicarrid

INTO TABLE itab.

LOOP AT itab INTO wa.

WRITE: / wa-struc1-carrid,

wa-struc1-carrname,

wa-struc2-connid.

ENDLOOP.

REWARD IF USEFULL

5 REPLIES 5

Former Member
0 Kudos

HI

OPEN CURSOR

OPEN CURSOR [WITH HOLD] dbcur FOR

SELECT result

FROM source

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

[GROUP BY group] [HAVING group_cond]

[ORDER BY sort_key].

Addition: ... WITH HOLD

This statement opens a database cursor for the selection defined after FOR, and links a cursor variable dbcur with this database cursor. For dbcur, a declared variable with the specific predefined data type cursor must be entered. A database cursor dbcur that has already been opened cannot be opened again. A line of the resulting set is always assigned to an opened database cursor as a cursor position. After the OPEN CURSOR statement, the database cursor is positioned in front of the first line of the resulting set.

After FOR, the syntax of a SELECT statement can be entered, which contains all the additions of the normal SELECT statement, except for INTO and APPENDING. In the addition result, the addition SINGLE can also not be used after SELECT.

Only a limited number of database cursors can be open at the same time. An open database cursor can be closed using the statement CLOSE CURSOR . In addition, an open database cursor is closed for a database commit or a database rollback.

If a cursor variable dbcur of an open database cursor is assigned to another cursor variable, the latter is linked to the same database cursor at the same position. A cursor variable of an open database cursor can also be transferred to procedures that have been called externally, to enable the database cursor to be accessed from there.

It is not recommended to assign cursor variables to each other, but rather to set them exclusively using the statements OPEN CURSOR and CLOSE CURSOR.

Addition ... WITH HOLD

:

If the addition WITH HOLD is specified, the database cursor is not closed in an explicitly triggered database commit or database rollback, for example Native SQL. The addition WITH HOLD cannot be specified if the cursor is to be opened for a secondary database connection.

*****************************************************

Using a Cursor to Read Data

In the normal SELECT statement, the data from the selection is always read directly into the target area specified in the INTO clause during the SELECT statement. When you use a cursor to read data, you decouple the process from the SELECT statement. To do this, you must open a cursor for a SELECT statement. Afterwards, you can place the lines from the selection into a flat target area.

Opening and Closing Cursors

To open a cursor for a SELECT statement, use the following:

OPEN CURSOR [WITH HOLD] <c> FOR SELECT <result>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

You must first have declared the cursor <c> using the DATA statement and the special data type CURSOR. You can use all clauses of the SELECT statement apart from the INTO clause. Furthermore, you can only formulate the SELECT clause so that the selection consists of more than one line. This means that you may not use the SINGLE addition, and that the column selection may not contain only aggregate expressions.

An open cursor points to an internal handler, similarly to a reference variable pointing to an object. You can reassign cursors so that more than one points to the same handler. In a MOVE statement, the target cursor adopts all of the attributes of the source cursor, namely its position, and all of the clauses in the OPEN CURSOR statement.

You can also open more than one cursor in parallel for a single database table. If a cursor is already open, you cannot reopen it. To close a cursor explicitly, use the following statement:

CLOSE CURSOR <c>.

You should use this statement to close all cursors that you no longer require, since only a limited number of cursors may be open simultaneously. With one exception, a database LUW is concluded when you close a cursor either explicitly or implicitly. The WITH HOLD addition in the OPEN CURSOR statement allows you to prevent a cursor from being closed when a database commit occurs in Native SQL.

Reading Data

An open cursor is linked to a multiple-line selection in the database table. To read the data into a target area in the ABAP program, use the following:

FETCH NEXT CURSOR <c> INTO <target>.

This writes one line of the selection into the target area <target>, and the cursor moves one line further in the selection set. The fetch statement decouples the INTO clause from the other clauses in the SELECT statement. All the INTO clauses of the SELECT statement can be used. The statement reads the lines that are needed to fill the target area of the INTO clause and moves the cursor to the next line to be read.

SY-SUBRC is set to 0 until all the lines of the selection have been read; otherwise it is 4. After a FETCH statement, system field SY-DBCNT contains the number of all the lines read so far for the corresponding cursor.

REPORT demo_select_cursor_1.

DATA: c1 TYPE cursor,

c2 TYPE cursor.

DATA: wa1 TYPE spfli,

wa2 TYPE spfli.

DATA: flag1(1) TYPE c,

flag2(1) TYPE c.

OPEN CURSOR: c1 FOR SELECT carrid connid

FROM spfli

WHERE carrid = 'LH',

c2 FOR SELECT carrid connid cityfrom cityto

FROM spfli

WHERE carrid = 'AZ'.

DO.

IF flag1 NE 'X'.

FETCH NEXT CURSOR c1 INTO CORRESPONDING FIELDS OF wa1.

IF sy-subrc <> 0.

CLOSE CURSOR c1.

flag1 = 'X'.

ELSE.

WRITE: / wa1-carrid, wa1-connid.

ENDIF.

ENDIF.

IF flag2 NE 'X'.

FETCH NEXT CURSOR c2 INTO CORRESPONDING FIELDS OF wa2.

IF sy-subrc <> 0.

CLOSE CURSOR c2.

flag2 = 'X'.

ELSE.

WRITE: / wa2-carrid, wa2-connid,

wa2-cityfrom, wa2-cityto.

ENDIF.

ENDIF.

IF flag1 = 'X' AND flag2 = 'X'.

EXIT.

ENDIF.

ENDDO.

The output looks something like this:

The database table SPFLI is read using two cursors, each with different conditions.. The selected lines are read alternately in a DO loop.

REPORT demo_select_cursor_2.

DATA c TYPE cursor.

DATA wa TYPE sbook.

OPEN CURSOR c FOR SELECT carrid connid fldate bookid smoker

FROM sbook

ORDER BY carrid connid fldate smoker bookid.

FETCH NEXT CURSOR c INTO CORRESPONDING FIELDS OF wa.

WHILE sy-subrc = 0.

IF wa-smoker = ' '.

PERFORM nonsmoker USING c.

ELSEIF wa-smoker = 'X'.

PERFORM smoker USING c.

SKIP.

ELSE.

EXIT.

ENDIF.

ENDWHILE.

FORM nonsmoker USING n_cur TYPE cursor.

WHILE wa-smoker = ' ' AND sy-subrc = 0.

FORMAT COLOR = 5.

WRITE: / wa-carrid, wa-connid, wa-fldate, wa-bookid.

FETCH NEXT CURSOR n_cur INTO CORRESPONDING FIELDS OF wa.

ENDWHILE.

ENDFORM.

FORM smoker USING s_cur TYPE cursor.

WHILE wa-smoker = 'X' AND sy-subrc = 0.

FORMAT COLOR = 6.

WRITE: / wa-carrid, wa-connid, wa-fldate, wa-bookid.

FETCH NEXT CURSOR s_cur INTO CORRESPONDING FIELDS OF wa.

ENDWHILE.

ENDFORM.

The following is an extract from the list display:

The program opens a cursor for the database table SBOOK. After the first FETCH statement, a subroutine is called, which is dependent on the contents of the SMOKER column. The cursor is passed to an interface parameter in the subroutine. The subroutines read further lines until the contents of the SMOKER column change. The subroutines perform different tasks using the lines read by the cursor.

REPORT demo_select_cursor_3.

DATA: wa_spfli TYPE spfli,

wa_sflight TYPE sflight,

wa_sflight_back TYPE sflight.

DATA: c1 TYPE cursor,

c2 TYPE cursor.

OPEN CURSOR c1 FOR SELECT *

FROM spfli

ORDER BY PRIMARY KEY.

OPEN CURSOR c2 FOR SELECT *

FROM sflight

ORDER BY PRIMARY KEY.

DO.

FETCH NEXT CURSOR c1 INTO wa_spfli.

IF sy-subrc NE 0.

EXIT.

ENDIF.

WRITE: / wa_spfli-carrid, wa_spfli-connid.

DO.

IF NOT wa_sflight_back IS INITIAL.

wa_sflight = wa_sflight_back.

CLEAR wa_sflight_back.

ELSE.

FETCH NEXT CURSOR c2 INTO wa_sflight.

IF sy-subrc <> 0.

EXIT.

ELSEIF wa_sflight-carrid <> wa_spfli-carrid

OR wa_sflight-connid <> wa_spfli-connid.

wa_sflight_back = wa_sflight.

EXIT.

ENDIF.

ENDIF.

WRITE: / wa_sflight-carrid, wa_sflight-connid,

wa_sflight-fldate.

ENDDO.

ENDDO.

The output is as follows:

The program opens a cursor for each of the table SPFLI and SFLIGHT. Since both tables are linked by a foreign key relationship, it is possible to program a nested loop by sorting the selection by its primary key, so that the data read in the inner loop depends on the data in the outer loop. This programming method is quicker than using nested SELECT statements, since the cursor for the inner loop does not continually have to be reopened. If the group level in the inner loop is changed, the data that is read is stored temporarily until the next loop pass since it is not possible to reset the cursor.

0 Kudos

OPEN CURSOR

OPEN CURSOR [WITH HOLD] dbcur FOR

SELECT result

FROM source

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

[GROUP BY group] [HAVING group_cond]

[ORDER BY sort_key].

Addition: ... WITH HOLD

This statement opens a database cursor for the selection defined after FOR, and links a cursor variable dbcur with this database cursor. For dbcur, a declared variable with the specific predefined data type cursor must be entered. A database cursor dbcur that has already been opened cannot be opened again. A line of the resulting set is always assigned to an opened database cursor as a cursor position. After the OPEN CURSOR statement, the database cursor is positioned in front of the first line of the resulting set.

After FOR, the syntax of a SELECT statement can be entered, which contains all the additions of the normal SELECT statement, except for INTO and APPENDING. In the addition result, the addition SINGLE can also not be used after SELECT.

Only a limited number of database cursors can be open at the same time. An open database cursor can be closed using the statement CLOSE CURSOR . In addition, an open database cursor is closed for a database commit or a database rollback.

If a cursor variable dbcur of an open database cursor is assigned to another cursor variable, the latter is linked to the same database cursor at the same position. A cursor variable of an open database cursor can also be transferred to procedures that have been called externally, to enable the database cursor to be accessed from there.

It is not recommended to assign cursor variables to each other, but rather to set them exclusively using the statements OPEN CURSOR and CLOSE CURSOR.

Addition ... WITH HOLD

:

If the addition WITH HOLD is specified, the database cursor is not closed in an explicitly triggered database commit or database rollback, for example Native SQL. The addition WITH HOLD cannot be specified if the cursor is to be opened for a secondary database connection.

FETCH

FETCH NEXT CURSOR dbcur {INTO|APPENDING} target.

This statement extracts the requested rows (by use of the addition INTO resp. APPENDING) from the resulting set of the database cursor (which is linked to the cursor variable dbcur) from the current cursor-position and assigns these rows to the data objects specified in the resulting set.

The cursor-variable dbcur has to be a variable declared by the special pre-defined data type cursor, which was opened with the statement OPEN CURSOR, or which had an opened cursor assigned to.

Syntax and meaning of the addition INTO resp. APPENDING target are completely synonymous to the additions of the SELECT-statement. If you specify non-table-type data objects after INTO, then one line is extracted. If an internal table is specified after INTO resp. APPENDING, then, either all lines get extracted ,or as many as specified in the addition PACKAGE SIZE.

The statement FETCH moves the position of the database cursor (which is linked to dbcur) by the amount of extracted lines to the next line to be extracted. If you extracted the last line of the resulting set in a FETCH-statement, then every following FETCH-Statement, in which dbcur is linked to the same database-cursor, sets sy-subrc to 4, without influencing the data objects specified after INTO resp. APPENDING.

System Fields

The statement FETCH sets the values of the system fields sy-subrc and sy-dbcnt.

sy-subrc description

0 At least one line was extracted from the resulting set.

4 No line was extracted.

The statement FETCH sets sy-dbcnt after every line extraction to the amount of the lines that have been extracted so far from the resulting set in concern. If no line can be extracted, then sy-dbcnt is set to 0.

Subsequent FETCH-statements, which access the same resulting set, can have different additions INTO res. APPENDING: The specification of work areas can be combined with the specification of internal tables. In doing so, the addition CORRESPONDING FIELDS is either not listed at all in all the FETCH-statements, or has to be listed in every statement. Moreover, the data types of all involved work areas wa resp. the line types of the internal tables itab have to be identical. The specification of a bracketed list of data objects after INTO can not be combined with the specification of work areas or internal tables, but every involved FETCH-statement has to contain such a list.

Example

Reading of data of the database table SPFLI in pakets of varying size with the help of two parallel cursors. The paket size is determined by the first cursor through the aggregation functioncount( * ) and used via the second cursor when accessing. The variable control of the addition PACKAGE SIZE is not possible within a single SELECT-statement.

DATA: BEGIN OF count_line,

carrid TYPE spfli-carrid,

count TYPE i,

END OF count_line,

spfli_tab TYPE TABLE OF spfli.

DATA: dbcur1 TYPE cursor,

dbcur2 TYPE cursor.

OPEN CURSOR dbcur1 FOR

SELECT carrid count(*) AS count

FROM spfli

GROUP BY carrid

ORDER BY carrid.

OPEN CURSOR dbcur2 FOR

SELECT *

FROM spfli

ORDER BY carrid.

DO.

FETCH NEXT CURSOR dbcur1 INTO count_line.

IF sy-subrc <> 0.

EXIT.

ENDIF.

FETCH NEXT CURSOR dbcur2

INTO TABLE spfli_tab PACKAGE SIZE count_line-count.

ENDDO.

CLOSE CURSOR: dbcur1,

dbcur2.

... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

If the result set consists of multiple lines, an internal table itab of any table type can be specified after INTO or APPENDING. The row type of the internal table must meet the prerequisites.

The result set is inserted into the internal table itab line-by-line; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized before the first line is inserted. Previous lines remain intact if APPENDING is used.

Before any assignment of a line of the result set, an initial row of the internal table itab is created and the line of the result set is assigned to this row. When assigning a line of the result set to a row of the internal table with or without CORRESPONDING FIELDS, the same rules apply as when assigning to an individual work area wa (see above).

If the PACKAGE SIZE addition is not used, all lines of the result set are inserted in the internal table itab and the ENDSELECT statement must not be specified after SELECT.

If you specify the PACKAGE SIZE addition, all lines of the result set are processed in a loop, which must be closed with ENDSELECT. The lines are inserted in the internal table itab in packages of n lines. n must be a type i data object that contains the number of lines. If the value of n is smaller than 0, an exception that cannot be handled occurs. If n is equal to 0, all lines of the result set are inserted in the internal table itab.

If INTO is used, the internal table is initialized before each loop pass and, in the SELECT loop, it only contains the lines of thecurrent package. If APPENDING is used, a further package is added to the existing rows of the internal table in each SELECT loop.

After ENDSELECT, the contents of itab is not defined if INTO is used - that is, the table can either contain the lines of the last package or it can be initial. If APPENDING is used, the content of itab retains the state of the last loop pass.

In the case of an internal table with a unique key, an exception that cannot be handled occurs if an attempt is made to create a duplicate entry

Example

In this example, all columns of the result set are read into an internal table whose row type is a nested structure with the same construction as the result set. Note that in practice, the column carrid exists twice in the result set with the same content and, after the assignment, this content is stored redundantly in the columns struc1-carrid and struc2-carrid of the internal table.

DATA: BEGIN OF wa,

struc1 TYPE scarr,

struc2 TYPE spfli,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY table_line.

SELECT *

FROM scarr

INNER JOIN spfli ON scarrcarrid = spflicarrid

INTO TABLE itab.

LOOP AT itab INTO wa.

WRITE: / wa-struc1-carrid,

wa-struc1-carrname,

wa-struc2-connid.

ENDLOOP.

REWARD IF USEFULL

Former Member
0 Kudos

HI

<b>OPEN CURSOR</b>

OPEN CURSOR [WITH HOLD] dbcur FOR

SELECT result

FROM source

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

[GROUP BY group] [HAVING group_cond]

[ORDER BY sort_key].

<b>Addition: ... WITH HOLD</b>

This statement opens a database cursor for the selection defined after FOR, and links a cursor variable dbcur with this database cursor. For dbcur, a declared variable with the specific predefined data type cursor must be entered. A database cursor dbcur that has already been opened cannot be opened again. A line of the resulting set is always assigned to an opened database cursor as a cursor position. After the OPEN CURSOR statement, the database cursor is positioned in front of the first line of the resulting set.

After FOR, the syntax of a SELECT statement can be entered, which contains all the additions of the normal SELECT statement, except for INTO and APPENDING. In the addition result, the addition SINGLE can also not be used after SELECT.

Only a limited number of database cursors can be open at the same time. An open database cursor can be closed using the statement CLOSE CURSOR . In addition, an open database cursor is closed for a database commit or a database rollback.

If a cursor variable dbcur of an open database cursor is assigned to another cursor variable, the latter is linked to the same database cursor at the same position. A cursor variable of an open database cursor can also be transferred to procedures that have been called externally, to enable the database cursor to be accessed from there.

It is not recommended to assign cursor variables to each other, but rather to set them exclusively using the statements OPEN CURSOR and CLOSE CURSOR.

<b>Addition ... WITH HOLD</b>

:

If the addition WITH HOLD is specified, the database cursor is not closed in an explicitly triggered database commit or database rollback, for example Native SQL. The addition WITH HOLD cannot be specified if the cursor is to be opened for a secondary database connection.

<b>FETCH</b>

FETCH NEXT CURSOR dbcur {INTO|APPENDING} target.

This statement extracts the requested rows (by use of the addition INTO resp. APPENDING) from the resulting set of the database cursor (which is linked to the cursor variable dbcur) from the current cursor-position and assigns these rows to the data objects specified in the resulting set.

The cursor-variable dbcur has to be a variable declared by the special pre-defined data type cursor, which was opened with the statement OPEN CURSOR, or which had an opened cursor assigned to.

Syntax and meaning of the addition INTO resp. APPENDING target are completely synonymous to the additions of the SELECT-statement. If you specify non-table-type data objects after INTO, then one line is extracted. If an internal table is specified after INTO resp. APPENDING, then, either all lines get extracted ,or as many as specified in the addition PACKAGE SIZE.

The statement FETCH moves the position of the database cursor (which is linked to dbcur) by the amount of extracted lines to the next line to be extracted. If you extracted the last line of the resulting set in a FETCH-statement, then every following FETCH-Statement, in which dbcur is linked to the same database-cursor, sets sy-subrc to 4, without influencing the data objects specified after INTO resp. APPENDING.

<b>System Fields</b>

The statement FETCH sets the values of the system fields sy-subrc and sy-dbcnt.

sy-subrc description

0 At least one line was extracted from the resulting set.

4 No line was extracted.

The statement FETCH sets sy-dbcnt after every line extraction to the amount of the lines that have been extracted so far from the resulting set in concern. If no line can be extracted, then sy-dbcnt is set to 0.

Subsequent FETCH-statements, which access the same resulting set, can have different additions INTO res. APPENDING: The specification of work areas can be combined with the specification of internal tables. In doing so, the addition CORRESPONDING FIELDS is either not listed at all in all the FETCH-statements, or has to be listed in every statement. Moreover, the data types of all involved work areas wa resp. the line types of the internal tables itab have to be identical. The specification of a bracketed list of data objects after INTO can not be combined with the specification of work areas or internal tables, but every involved FETCH-statement has to contain such a list.

<b>Example</b>

Reading of data of the database table SPFLI in pakets of varying size with the help of two parallel cursors. The paket size is determined by the first cursor through the aggregation functioncount( * ) and used via the second cursor when accessing. The variable control of the addition PACKAGE SIZE is not possible within a single SELECT-statement.

DATA: BEGIN OF count_line,

carrid TYPE spfli-carrid,

count TYPE i,

END OF count_line,

spfli_tab TYPE TABLE OF spfli.

DATA: dbcur1 TYPE cursor,

dbcur2 TYPE cursor.

OPEN CURSOR dbcur1 FOR

SELECT carrid count(*) AS count

FROM spfli

GROUP BY carrid

ORDER BY carrid.

OPEN CURSOR dbcur2 FOR

SELECT *

FROM spfli

ORDER BY carrid.

DO.

FETCH NEXT CURSOR dbcur1 INTO count_line.

IF sy-subrc <> 0.

EXIT.

ENDIF.

FETCH NEXT CURSOR dbcur2

INTO TABLE spfli_tab PACKAGE SIZE count_line-count.

ENDDO.

<b>CLOSE CURSOR: dbcur1</b>,

dbcur2.

... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

If the result set consists of multiple lines, an internal table itab of any table type can be specified after INTO or APPENDING. The row type of the internal table must meet the prerequisites.

The result set is inserted into the internal table itab line-by-line; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized before the first line is inserted. Previous lines remain intact if APPENDING is used.

Before any assignment of a line of the result set, an initial row of the internal table itab is created and the line of the result set is assigned to this row. When assigning a line of the result set to a row of the internal table with or without CORRESPONDING FIELDS, the same rules apply as when assigning to an individual work area wa (see above).

If the PACKAGE SIZE addition is not used, all lines of the result set are inserted in the internal table itab and the ENDSELECT statement must not be specified after SELECT.

If you specify the PACKAGE SIZE addition, all lines of the result set are processed in a loop, which must be closed with ENDSELECT. The lines are inserted in the internal table itab in packages of n lines. n must be a type i data object that contains the number of lines. If the value of n is smaller than 0, an exception that cannot be handled occurs. If n is equal to 0, all lines of the result set are inserted in the internal table itab.

If INTO is used, the internal table is initialized before each loop pass and, in the SELECT loop, it only contains the lines of thecurrent package. If APPENDING is used, a further package is added to the existing rows of the internal table in each SELECT loop.

After ENDSELECT, the contents of itab is not defined if INTO is used - that is, the table can either contain the lines of the last package or it can be initial. If APPENDING is used, the content of itab retains the state of the last loop pass.

In the case of an internal table with a unique key, an exception that cannot be handled occurs if an attempt is made to create a duplicate entry

<b>Example</b>

In this example, all columns of the result set are read into an internal table whose row type is a nested structure with the same construction as the result set. Note that in practice, the column carrid exists twice in the result set with the same content and, after the assignment, this content is stored redundantly in the columns struc1-carrid and struc2-carrid of the internal table.

DATA: BEGIN OF wa,

struc1 TYPE scarr,

struc2 TYPE spfli,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY table_line.

SELECT *

FROM scarr

INNER JOIN spfli ON scarrcarrid = spflicarrid

INTO TABLE itab.

LOOP AT itab INTO wa.

WRITE: / wa-struc1-carrid,

wa-struc1-carrname,

wa-struc2-connid.

ENDLOOP.

<b>REWARD IF USEFULL</b>

Former Member
0 Kudos

Hi

I've read many mails in the forum about this subject and each developer seems to have his own point of view on it.

I've been working on ABAP for several years and I never used open cursor (never had to).

<b>But one of our consultant did it on a program which had performance and the result seem impressive :</b>


  select b~MATNR a~BLDAT b~BWART b~CHARG 
           INTO CORRESPONDING FIELDS OF TABLE WT_select 
           from  MSEG  as b 
           inner join  MKPF  as a 
             on  a~MBLNR = b~MBLNR 
             and  a~MJAHR = b~MJAHR 
           where   b~MBLNR  > wv_ZLAST_MBLNR 
              and   b~MJAHR IN S_MJAHR 
              and   b~BWART in S_BWART 
              and   b~WERKS  = P_WERKS 
              and   a~BLART  = c_wa. 
=> Response time +/- 30 minutes 

  OPEN CURSOR C1 FOR 
       select b~MATNR a~BLDAT b~BWART b~CHARG 
       from  MSEG  as b 
       inner join  MKPF  as a 
              on  a~MBLNR = b~MBLNR 
              and  a~MJAHR = b~MJAHR 
         where   b~MBLNR  > wv_ZLAST_MBLNR 
             and   b~MJAHR IN S_MJAHR 
             and   b~BWART in S_BWART 
             and   b~WERKS  = P_WERKS 
             and   a~BLART  = c_wa. 
     FETCH NEXT CURSOR C1 
            INTO CORRESPONDING FIELDS OF TABLE WT_select. 
     CLOSE CURSOR C1.

=> Response time +/- 1 minute

<b>Reward pts if found usefull :)</b>

Regards

Sathish

0 Kudos

HI SATISH,

SO YOU WANT TO SAY THAT OPEN CURSOR CAN BE USED FOR THOSE SELECTS STATEMENT WHICH ARE USED FOR SELECTING BIG SET OF DATA.

REGARDS,

DEEPAK