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.
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
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"
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.
Step 4 - close the cursor
