As an example of a retrieval from a single table, Figure 2-5 shows a case in which a SELECT statement is used to retrieve from a stock table those rows containing SKIRT as the product name.

Figure 2-5 Retrieval from a single table

The table retrieval results are expressed as a table and passed to the UAP that requested the processing.

A cursor is then used by the UAP to reference the retrieval results table. Because a cursor can point to a specific row in the retrieval results table, the UAP is able to read the contents of the row being indicated by the cursor and process that row's contents.

Figure 2-6 shows the sequence in which a UAP processes data from a retrieval results table.

Figure 2-6 UAP data processing sequence for a retrieval results table

The processing steps shown in Figure 2-6 are explained as follows.

1. Cursor definition
   To use a cursor, a cursor name, the name of the table to be retrieved using the cursor, and retrieval conditions are defined. For the example in Figure 2-5, the following definitions define the cursor name as CUR1 and specify that SKIRT only is to be retrieved from the stock table:
   

   DECLARE CUR1 CURSOR FOR
   SELECT PNAME,COLOR,PRICE FROM STOCK
     WHERE PNAME=N'SKIRT'

2. Cursor opening
   When a cursor is opened, retrieval results can be extracted in accordance with the defined conditions. The retrieval results are stored in a table format in the system and remain valid until the cursor is closed.
   The following specification opens the cursor:
   

   OPEN CUR1

   As soon as a cursor is opened, it is positioned at the first column above the first row of the retrieval results table. Figure 2-7 shows a cursor immediately after it has been opened; in this example, the cursor name is CUR1 and SKIRT is the retrieval condition for the stock table.
   

   Figure 2-7 Cursor position immediately following cursor opening

   

3. Data extraction
   The FETCH statement advances the cursor by one row (to the next row). The contents of that row are then stored in a specified area in the UAP.
   The example in Figure 2-8 shows the cursor immediately after it has been opened, and shows how the retrieved contents are stored in the UAP.
   

   Figure 2-8 Example of extracting retrieved contents and storing them in the UAP

   

4. Data output
   The data stored in the area in the UAP is output as necessary.
   
5. Cursor closure
   When processing of retrieved data by the UAP has been completed, close the cursor.
   Once the cursor is closed, the retrieval results table stored in the system is deleted. The following specification closes the cursor:
   

   CLOSE CUR1