Data retrieval is the process of selecting the rows from a table that satisfy conditions that are specified with respect to a column. The data retrieval methods and an example of specifying SQL statements are shown as follows.

Data retrieval methods

The SELECT statement is used to retrieve data. The following three methods are available for data retrieval:

• Retrieval using a cursor
• Retrieval of multiple tables (specify the FROM clause in the SELECT statement)
• Retrieval by row (specify ROW in the selection expression of the SELECT statement)

Data retrieval SQL specification example

An example of retrieval using a cursor is explained as follows.

Example

1. Define cursor.

In this example, a cursor named CUR1 is used to retrieve from the stock table (STOCK) the product names (PNAME), colors (COLOR), and prices (PRICE) of products whose product name (PNAME) is skirt:

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

2. Open cursor.

Cursor CUR1 is opened:

 
OPEN CUR1

3. Extract data.

While cursor CUR1 is open, it is advanced by one row and the contents of that row are stored in the UAP in specified areas (:XPNAME, :XCOLOR, :XPRICE):

 
FETCH CUR1 INTO
  :XPNAME,
  :XCOLOR,
  :XPRICE

4. Close cursor.

Cursor CUR1 is closed:

 
CLOSE CUR1
 

By specifying LIMIT following the ORDER BY clause, you can retrieve search results from the first n rows. Specifying LIMIT may also improve SQL search performance. For details about retrieving search results from the first n rows, see the HiRDB Version 8 UAP Development Guide.