Nonstop Database, HiRDB Version 9 UAP Development Guide
An SQL statement used to retrieve a table can be executed either statically or dynamically. The following table shows the classification of UAP retrieval methods using SQL statements.
Table 3-3 Classification of UAP retrieval methods using SQL statements
Retrieval method | SQL statement for specifying query | |
---|---|---|
Embedded UAP | Static SQL | Single-row SELECT statement |
Cursor declaration | ||
Dynamic SQL | Single-row SELECT statement | |
Dynamic SELECT statement |
The single-row SELECT statement extracts only a single-row of retrieval results from a table.
Because a cursor need not be used when the single-row SELECT statement is used, you can retrieve the table with only one SQL statement.
The single-row SELECT statement is effective when used in the cases listed below. You can also dynamically execute a single-row SELECT statement that is constructed during UAP execution.
Even when a single-row is retrieved, using a cursor results in better processing efficiency for updating or deleting the retrieved row. You should consider whether the single-row SELECT statement or the cursor will be used.
If retrieval results include multiple rows, the UAP cannot receive them all at once. A cursor is used to extract one row at a time. The flow from cursor declaration to retrieval completion is described as follows.
Use the dynamic SELECT statement to extract multiple retrieval results through dynamic SQL execution. To extract retrieval results with the dynamic SELECT statement, you must either declare a cursor in advance or allocate a cursor by using the ALLOCATE CURSOR statement. Once you declare or allocate a cursor, use the PREPARE statement to preprocess the SQL statements that are constructed during UAP execution. You can then perform the same operations as in normal retrieval using a cursor.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.