. SQL . SQL Cursor DB2
SQL Cursor DB2
SQL Cursor Processing with DB2
Q: How can I retrieve data one row at a time with DB2?
To retrieve data with SQL one row at a time you need to use cursor processing.
Not all relational databases support this, but many do. Here I show this with DB2 and embedded SQL, which is SQL that is "embedded" in a program like COBOL, C, or Java.
Cursor processing is done in several steps:
1. Define the rows you want to retrieve. This is called declaring the cursor.
2. Open the cursor. This activates the cursor and loads the data. Note that defining the cursor doesn't load data, opening the cursor does.
3. Fetch the data into host variables.
4. Close the cursor.
Not all relational databases support this, but many do. Here I show this with DB2 and embedded SQL, which is SQL that is "embedded" in a program like COBOL, C, or Java.
Cursor processing is done in several steps:
1. Define the rows you want to retrieve. This is called declaring the cursor.
2. Open the cursor. This activates the cursor and loads the data. Note that defining the cursor doesn't load data, opening the cursor does.
3. Fetch the data into host variables.
4. Close the cursor.
Step 1 - declare the cursor
Declare CursorJamesCameron Cursor for
Select Film_Title
From Director_Film_Table,
Where Director_Last_Name equals "Cameron"
and Director_First_Name equals "James"
Order By Film_Title;
Note: That this example fetches only one column - Film_Title - but multiple columns can be fetched simultaneously.
Step 2 - open the cursor
Open CursorJamesCameron;
Note: Not much seems to happen in step 2. Quite a bit really does happen behind the scenes, as cursor CursorJamesCameron is loaded with data in step 2.
Step 3 - fetch the data into a host variable
Fetch CursorJamesCameron Into :CameronMovieName;
Result after the first fetch
Host variable :CameronMovieName will equal "Aliens"
Host variable :CameronMovieName will equal "Aliens"
The fetch will take the current sequential row and put it into the host variable. It will then set the next sequential row to the current sequential row.
The host variable must be able to accommodate the data that the cursor has defined. If more than one column is fetched each column must have a correlating host variable.
You can repeat doing the fetch until you finish reading each row. When all rows are read the host variable will be set to null or spaces. Usually a special host variable is set by the dbms to indicate that the cursor is empty. In DB2 the SQLCODE is set to +100.
The host variable must be able to accommodate the data that the cursor has defined. If more than one column is fetched each column must have a correlating host variable.
You can repeat doing the fetch until you finish reading each row. When all rows are read the host variable will be set to null or spaces. Usually a special host variable is set by the dbms to indicate that the cursor is empty. In DB2 the SQLCODE is set to +100.
Step 4 - close the cursor
Close CursorJamesCameron;
| Comments Comments are left by visitors to FluffyCat.com, are not endorsed by FluffyCat.com, and may or may not be accurate. |
| Comment by Anonymous Rate this Comment |
Not sure what 'CR' is - did you perhaps mean 'CS'? If so, these would correspond to 'Cursor Stability' and 'Uncommitted Read', respectively. These define what happens when you've opened a cursor (thus loading your table with data) and, while the cursor is live, another process then updates the source table your cursor was defined against. |
| Comment by archive Rate this Comment |
I am working in Mainframe Technplogy. In Db2 i got one doubt.
|
| Comment by Larry Rate this Comment |
Yes, the syntax for an Oracle cursor, whcih can be seen here is quite different. Same basic concept, though. |
| Sign In |
| to add your own comment |
