SQL Cursor DB2
SQL Cursor Processing with DB2
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;
Step 2 - open the cursor
Step 3 - fetch the data into a host variable
Fetch CursorJamesCameron Into :CameronMovieName;
Host variable :CameronMovieName will equal "Aliens"
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
|Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.|
|Comment by Anonymous on 2011-02-09 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 on 2011-04-13 Rate this Comment|
I am working in Mainframe Technplogy. In Db2 i got one doubt.
|Comment by Larry on 2011-01-18 Rate this Comment|
Yes, the syntax for an Oracle cursor, whcih can be seen here is quite different. Same basic concept, though.
|Comment by krishna_sri98 on 2013-05-22 Rate this Comment|
Its different in syntax compared to Oracle
|Sign in to comment on SQL Cursor DB2.|