Introduction:

In RPGLE (Report Program Generator Language), a cursor is a database feature that allows you to work with a set of rows from a result set one at a time. Cursors are particularly useful when you need to interact with data stored in a relational database, such as IBM Db2 on the AS400 platform. Cursors offer precise control over retrieving, updating, or deleting rows from database tables.

A cursor contains information on the statement executed and the rows of data accessed by it. The set of rows the cursor holds is called the active set.

Basically, there are four types of cursors in RPGLE-

• Sequential/Serial Cursor
• Scrollable Cursor
• Sensitive Cursor
• Insensitive Cursor

Now before directly jump over the types of cursors, let us understand what major operations we generally do for retrieval of data from as400 data base using cursor

• Declare Cursor
• Open Cursor
• Fetch Operation
• Close Cursor

• Declare CursorTo declare the cursor, you just need to write the statement in below format-

  Declare + Your Cursor Name + Cursor For + Your SQL Statement

  Example in fix format:

  

  Example in free format:

  

• Open CursorAfter declaring the cursor, you can open it using the ‘OPEN’ statement as explained below-

  Open + Your Cursor Name

  Example in fix format:

  

  Example in free format:

  

• Fetch OperationAfter opening the cursor, you can retrieve the data from it using the fetch statement. You can write the fetch statement as below-

  Fetch From + Cursor Name + Into + Variable names with colon and separated by commas.

  Example in fix format:

  

  Example in free format:

  

• Close CursorAfter fetching the data, you need to close the cursor using CLOSE keyword. You can write the statement as explained below-

  Close + Your Cursor Name

  

  Example in free format:

  

Sequential/Serial Cursor:

• In sequential cursor, we can fetch each row once after open cursor.
• In sequential cursor, we can fetch row only in forward direction that’s why it is also known as forward-only cursors.
• Once row is fetched, we can not move in any other direction within the data set.
• Sequential cursor is defined without scroll keyword.
• If we do not define the cursor type then by default it will be considered as sequential/serial cursor.

Fix Format Example of Serial Cursor

Free Format Example for Serial Cursor

Scrollable Cursor:

• Scrollable cursor is defied with keyword Scroll.
• Scrollable cursor provides us ability to navigate result set in both forward and backward direction.
• In scrollable cursor, we can move to any row within the result set.
• In scrollable cursor, we can fetch any row of result set multiple times.
• In scrollable cursor, row fetch from result set also depends on the keyword used with scroll.
  There are following keywords that can be used:
  • NEXT – It will fetch the next row within the data set with respect to current row.
  • PRIOR – It will fetch the previous row within the data set with respect to
    current row.
  • FIRST – It will FETCH the first row in the results set.
  • LAST – It will FETCH the last row in the results set.
  • CURRENT – It will re-FETCH the current row from the result set.
  • BEFORE – It will position the cursor before the first row of the results set.
  • AFTER – It will position the cursor after the last row of the results set.
  • RELATIVE n – It will fetch the nth with respect to current row within the data set.
    here n represent the integer value that can be positive or negative.
  • ABSOLUTE n – If n is 0 then cursor will be positioned before the first row of the result
    table. If n is positive then cursor will be positioned on the nth record of
    result table from TOP. If n is negative then cursor will be positioned
    on the nth record of the result table from BOTTOM.

Use of Next Keyword with Scroll Cursor in Fix Format:

Use of Next Keyword with Scroll Cursor in Free Format:

Use of Prior Keyword with Scroll Cursor in Free Format:

Use of Prior Keyword with Scroll Cursor in Free Format:

Use of First Keyword with Scroll Cursor in Fix Format:

Use of First Keyword with Scroll Cursor in Free Format:

Use of Last Keyword with Scroll Cursor in Fix Format:

Use of Last Keyword with Scroll Cursor in Free Format:

Use of Current Keyword with Scroll Cursor in Fix Format:

Use of Current Keyword with Scroll Cursor in Free Format:

Use of Before Keyword with Scroll Cursor in Fix Format

Use of Before Keyword with Scroll Cursor in Free Format

Use of After Keyword with Scroll Cursor in Fix Format

Use of After Keyword with Scroll Cursor in Free Format

Use of Relative Keyword with Scroll Cursor in Fix Format

Use of Relative Keyword with Scroll Cursor in Free Format

Use of Absolute Keyword with Scroll Cursor in Fix Format

Use of Absolute Keyword with Scroll Cursor in Free Format

Sensitive Cursor:

• Sensitive cursor has ability to detect changes made to the underlying data by the other processor or users while the cursor is active.
• This means that if another user or program modifies a row in the database table that your sensitive cursor is currently working with, the cursor can recognize that change.
• This helps you to keep your data up to date and avoid errors in a multi-user environment.

Fix Format Example

Free Format Example

Insensitive Cursor:

• Insensitive cursor doesn’t detect changes made to the underlying data by other processes or users while the cursor is active.
• This means insensitive cursor treats that data as static and doesn’t keep track of changes made by others.
• Insensitive cursors are helpful in situations where you don’t want to be affected by changes made by others or processes while you are working with specific data.
• They offer data consistency and can be more efficient than sensitive cursors in certain scenarios.

Free Format Example

Fix Format Example

Cursor in Dynamic/Embedded SQL

To use the cursor in dynamic SQL, we need to follow below steps-

• We need to store our SQL statement into a variable as string.
• Then we can prepare our SQL statement using ‘PREPARE’ keyword.
• Now we can declare the cursor for our prepared SQL statement.
• Now we can process our cursor as normal cursor.

Example of dynamic SQL without parameter marker:

Example of dynamic SQL with parameter markers: