Cursor
Steps through the rows of a query one at a time, when there is more than one row.
Also written DECLARE CURSOR, FETCH
Why it matters. A query like "all accounts under 3000" returns many rows. A program can only handle one at a time.
See it
A cursor lets the program read many rows, one at a time.
Syntax
EXEC SQL DECLARE name CURSOR [WITH HOLD] FOR select-statement END-EXEC EXEC SQL OPEN name END-EXEC EXEC SQL FETCH name INTO :host-var [, :host-var ...] END-EXEC EXEC SQL CLOSE name END-EXEC
- DECLARE ... FOR
- Names the cursor and gives its query.
- WITH HOLD
- Keep the cursor open across a COMMIT.
- OPEN
- Run the query and position before the first row.
- FETCH ... INTO
- Move to the next row and copy its columns into host variables.
- CLOSE
- Let go of the cursor.
- FOR UPDATE OF
- Allow UPDATE or DELETE WHERE CURRENT OF on the row.
Try it DECLARE CURSOR
- 1
In the program CURSOR1, the cursor ACCT-CSR is declared in WORKING-STORAGE: accounts with a balance under 3000. Start it.
COBOL EXEC SQL OPEN ACCT-CSR END-EXEC - 2
Read one row. Put the account number in WS-ACCT and the balance in WS-BAL.
COBOL EXEC SQL FETCH ACCT-CSR INTO :WS-ACCT, :WS-BAL END-EXEC - 3
Run CURSOR1.
RUNCOBOL 'ZB0001.DEV.COBOL(CURSOR1)'
Remember
- A cursor reads the rows of a query one at a time.
- The order is DECLARE, OPEN, FETCH again and again, then CLOSE. A COMMIT closes the cursor unless it is WITH HOLD.
- SQLCODE 100 on a FETCH means there are no more rows.
The usual mistake
SELECT ... INTO :WS-ACCT for a query that returns many rows
SELECT INTO wants exactly one row. More than one gives SQLCODE -811.
Declare a cursor, then FETCH in a loop
On real z/OS
MF/OS runs cursors only inside COBOL EXEC SQL. In plain SQL run at the terminal or in a batch SQL step, DECLARE, OPEN, FETCH and CLOSE print a note instead.
Where can I practise Cursor?
Right here, at ZedBank. You take a junior job at a simulated bank and use Cursor in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- Embedded SQLSQL statements written directly inside a COBOL program, marked off so they can be prepared before compiling.
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- SQLCODEThe result code DB2 returns after every SQL statement, saying whether it worked and what happened if it did not.
- COBOLThe programming language most business logic on the mainframe is written in. Verbose by design, and still running the world's payments.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.