SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-30
PREPARE
one call to the procedure where it is declared, you can still use the cursor in
subsequent calls without opening the cursor again.
This example shows the OPEN statement in a typical sequence of statements that use
a cursor.
EXEC SQL
DECLARE check_emp CURSOR FOR ! Declare the cursor.
SELECT deptnum
FROM employee
WHERE deptnum = :deptnum^del
FOR UPDATE OF deptnum ;
...
! get a value for :deptnum^del.
EXEC SQL
OPEN check_emp ; ! Open the cursor.
! FETCH rows until the NOT FOUND condition is met.
WHILE SQLCODE >= 100 DO
BEGIN
EXEC SQL
FETCH check_emp ! FETCH a value.
INTO :deptnum ;
! Delete a row that matches the criteria.
...
END
EXEC SQL
CLOSE check_emp ; ! Close the cursor.
PREPARE
The PREPARE statement dynamically compiles an SQL statement. PREPARE
associates a statement name or host variable name with an SQL statement, which
resides as a string literal in the host variable.
If the PREPARE statement completes without an error, you can execute the statement
by specifying the statement or host variable name in an EXECUTE (or EXECUTE
IMMEDIATE) statement. After a successful completion, the SQL statement is called a
prepared statement. If the PREPARE statement fails, an attempt to execute the
statement fails.
For a prepared SELECT statement, a program defines a cursor with a dynamic
DECLARE CURSOR statement, and then uses dynamic OPEN, FETCH, and CLOSE
statements to retrieve rows.