Microsoft
SQL Server Cursor statements
In embedded SQL, statements that retrieve data can involve cursors.
These statements are:
-
DECLARE cursor_name CURSOR FOR …
-
OPEN cursor_name
-
FETCH cursor_name INTO …
-
CLOSE cursor_name
Note UPDATE … WHERE CURRENT OF cursor_name and DELETE … WHERE
CURRENT OF cursor_name are not supported in SQL Server.
Retrieval
Retrieval using cursors is conceptually similar to retrieval in the
singleton SELECT. The main difference is that since there can be multiple
rows in a result set, you control when the next row is fetched into the
PowerScript data variables.
If you expect only a single row to exist in the employee table with
the specified emp_id, use the singleton SELECT. In a singleton SELECT, you
specify the SELECT statement and destination variables in one concise SQL
statement:
|
1 2 3 |
SELECT emp_name, emp_salary INTO :emp_name_var, :emp_salary_var FROM employee WHERE emp_id = :emp_id_var; |
However, when a SELECT may return multiple rows, you must:
-
Declare a cursor.
-
Open it (which conceptually executes the SELECT).
-
Fetch rows as needed.
-
Close the cursor.
Declaring and opening a
cursor
Declaring a cursor is tightly coupled with the OPEN statement. The
DECLARE specifies the SELECT statement to be executed, and the OPEN
actually executes it.
Declaring a cursor is similar to declaring a variable; a cursor is a
nonexecutable statement just like a variable declaration. The first step
in declaring a cursor is to define how the result set looks. To do this,
you need a SELECT statement. Since you must refer to the result set in
subsequent SQL statements, you must associate the result set with a
logical name.
Scrolling and locking
Use the CursorScroll and CursorLock DBParm parameters to specify the
scrolling and locking options.
Example
Assume the SingleLineEdit sle_1 contains the state code for the
retrieval:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// Declare cursor emp_curs for employee table // retrieval. DECLARE emp_curs CURSOR FOR SELECT emp_id, emp_name FROM EMPLOYEE WHERE emp_state = :sle_1.text; // Declare local variables for retrieval. string emp_id_var string emp_name_var // Execute the SELECT statement with // the current value of sle_1.text. OPEN emp_curs; // At this point, if there are no errors, // the cursor is available for further // processing. |
See also