Difference between revisions of "FETCH"
From Recital Documentation Wiki
Yvonnemilne (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
| (5 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
==Purpose== | ==Purpose== | ||
Fetches rows returned by the SELECT statement defined in a CURSOR | Fetches rows returned by the SELECT statement defined in a CURSOR | ||
| Line 20: | Line 13: | ||
==See Also== | ==See Also== | ||
| − | [[DECLARE CURSOR]], [[DROP CURSOR]], [[OPEN]], [[SQL SELECT|SELECT]] | + | [[SQL CLOSE|CLOSE]], [[DECLARE CURSOR]], [[DROP CURSOR]], [[OPEN]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]] |
| Line 49: | Line 42: | ||
|INTO data variable||Specifies a list of data variables to receive the extracted row values. The list may contain fewer variables than there are table columns, but may not contain more variables than there are table columns. | |INTO data variable||Specifies a list of data variables to receive the extracted row values. The list may contain fewer variables than there are table columns, but may not contain more variables than there are table columns. | ||
|- | |- | ||
| − | |INTO XML | + | |INTO XML <xml filename>||Specify the name of an XML file that will be created and populated with all the rows from the open cursor. |
| − | <xml filename>||Specify the name of an XML file that will be created and populated with all the rows from the open cursor. | + | |
|- | |- | ||
|} | |} | ||
| Line 58: | Line 50: | ||
<code lang="recital"> | <code lang="recital"> | ||
// Declare the cursor to retrieve records from the accounts table | // Declare the cursor to retrieve records from the accounts table | ||
| − | + | DECLARE accounts; | |
| − | DECLARE accounts CURSOR FOR | + | CURSOR FOR; |
| − | SELECT name, address, ord_value, balance | + | SELECT name, address, ord_value, balance; |
| − | FROM accounts | + | FROM accounts; |
| − | ORDER BY name | + | ORDER BY name |
// Open the cursor and establish a temporary set of records | // Open the cursor and establish a temporary set of records | ||
| − | + | OPEN accounts | |
| − | OPEN accounts | + | |
// Retrieve each row from open cursor | // Retrieve each row from open cursor | ||
DO WHILE sqlcode = 0 | DO WHILE sqlcode = 0 | ||
| − | + | FETCH NEXT accounts; | |
| − | FETCH NEXT accounts | + | INTO m_name, m_address, m_ord_value, m_balance |
| − | INTO m_name, m_address, m_ord_value, m_balance | + | |
ENDDO | ENDDO | ||
// Close the cursor | // Close the cursor | ||
| − | + | CLOSE accounts | |
| − | CLOSE accounts | + | |
// Free up any resources used for the cursor | // Free up any resources used for the cursor | ||
| − | + | DROP CURSOR accounts | |
| − | DROP CURSOR accounts | + | |
</code> | </code> | ||
==Products== | ==Products== | ||
| − | Recital | + | Recital Server, Recital |
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:SQL]] | [[Category:SQL]] | ||
[[Category:Commands]] | [[Category:Commands]] | ||
Latest revision as of 14:43, 22 December 2009
Purpose
Fetches rows returned by the SELECT statement defined in a CURSOR
Syntax
FETCH
[NEXT | PREVIOUS | FIRST | LAST | ABSOLUTE <row position> | CURRENT | RELATIVE <row position>] <cursor>
INTO [ [, ...] | XML <xml filename>]
See Also
CLOSE, DECLARE CURSOR, DROP CURSOR, OPEN, SELECT, SET XMLFORMAT
Description
The FETCH command fetches rows returned by the SELECT statement defined in a CURSOR. It reads the values in the specified row from the open cursor and places them in the bound data variables. If the variables do not exist, they are created. If they do exist, they are updated. The cursor must have already been opened with the OPEN statement.
| Keywords | Description |
|---|---|
| NEXT | Retrieves the next row from the open cursor. This is the default if no direction is specified. |
| PREVIOUS | Retrieves the previous row from the open cursor. |
| FIRST | Retrieves the first row from the open cursor. |
| LAST | Retrieves the last row from the open cursor. |
| ABSOLUTE row position | Retrieves the absolute row position. Row positions start at 1 in the open cursor. |
| CURRENT | Retrieves the current row from the open cursor. |
| RELATIVE row position | Retrieves the row position relative to the current cursor position. The row position can be negative to move to previous rows, positive to move to next rows and zero to retrieve the current row. |
| cursor | The cursor that has been declared with the DECLARE CURSOR statement. |
| INTO data variable | Specifies a list of data variables to receive the extracted row values. The list may contain fewer variables than there are table columns, but may not contain more variables than there are table columns. |
| INTO XML <xml filename> | Specify the name of an XML file that will be created and populated with all the rows from the open cursor. |
Example
// Declare the cursor to retrieve records from the accounts table DECLARE accounts; CURSOR FOR; SELECT name, address, ord_value, balance; FROM accounts; ORDER BY name // Open the cursor and establish a temporary set of records OPEN accounts // Retrieve each row from open cursor DO WHILE sqlcode = 0 FETCH NEXT accounts; INTO m_name, m_address, m_ord_value, m_balance ENDDO // Close the cursor CLOSE accounts // Free up any resources used for the cursor DROP CURSOR accounts
Products
Recital Server, Recital
