DECLARE <cursorName> CURSOR FOR
You can define a static cursor in SQL Script by providing a unique name for it and specifying the query expression associated with the cursor.
Syntax
DECLARE <cursorName> CURSOR FOR <queryExpression>
Remarks
• The name resolution works like a standalone SELECT statement.
• Variables cannot be used in the query expression.
• Bind variables (such as '?') cannot be used.
• Declaring a static cursor is logically equivalent to preparing a statement in JDBC.
• A cursor declared in this way is like a constant: its value cannot be changed.
Examples
PROCEDURE p (OUT p_name VARCHAR)
BEGIN
DECLARE c CURSOR FOR SELECT name FROM /shared/T;
OPEN c;
FETCH c INTO p_name;
CLOSE c;
END
The procedure below returns the first name.
PROCEDURE p (OUT p_name VARCHAR)
BEGIN
DECLARE c CURSOR FOR SELECT name FROM /shared/T;
OPEN c;
FETCH c INTO p_name;
CLOSE c;
...
--Reopen cursor
OPEN c;
FETCH c INTO p_name;
CLOSE c;
END
The procedure below manipulates two cursors, c and d.
PROCEDURE p
BEGIN
DECLARE c CURSOR (name VARCHAR);
DECLARE d CURSOR FOR SELECT name FROM /shared/T;
--Open a new cursor in cursor variable c
OPEN c FOR SELECT name FROM /shared/T;
Assign the cursor referred to by d to c
The original cursor referred to by c is no longer accessible
SET c = d;
--c and d cursor variables now refer to the same cursor
--Use either one to open the cursor
OPEN d; -- or OPEN c
--c.ISOPEN is true
The procedure below returns an opened static cursor.
PROCEDURE p (OUT p_cursor CURSOR (name VARCHAR))
BEGIN
DECLARE c CURSOR FOR SELECT name FROM /shared/T;
SET p_cursor = c;
OPEN p_cursor;
END
--Returns an opened static cursor
PROCEDURE p (OUT p_cursor CURSOR (name VARCHAR))
BEGIN
OPEN p_cursor FOR SELECT name FROM /shared/T;
END
PROCEDURE p (OUT p_id INTEGER, OUT p_name VARCHAR)
BEGIN
DECLARE c CURSOR FOR SELECT id, name FROM /shared/T;
DECLARE r ROW (id INTEGER, name VARCHAR);
OPEN c;
FETCH INTO c;
CLOSE c;
SET p_id = r.id;
SET p_name = r.name;
END
PROCEDURE p ( )
BEGIN
DECLARE TYPE r_type ROW (id INTEGER, name VARCHAR);
DECLARE c CURSOR r_type;
DECLARE r r_type;
OPEN c FOR SELECT id, name FROM /shared/T;
FETCH INTO c;
CLOSE c;
END