Oracle cursor-PREPARE

This method is similar to method 2. It only uses PREPARE statements and cursors, so that dynamic SQL statements can process query statements that return multiple rows. In fact, if the dynamic SQL statement to be processed is a query statement that returns multi-row tuples, you can only use method 3 or method 4.

This method allows the application to accept or construct a dynamic query, and then process the query with the PREPARE command and the five cursor commands DECLARE, OPEN, FETCH, and CLOSE.

The number of query columns, the number of placeholders for the input primary variables, and the type of the input primary variables must be provided at compilation, and the database object name, such as the table name and column name, it can be determined during running. The clause of the SELECT statement can be provided at runtime. The format of method 3 is as follows:

Prepare from <command identifier> FROM <parameter identifier>;

DECLARE <CURSOR Name> cursor for <command identifier>;

OPEN <cursor Name> [USING <parameter identifier>];

FETCH <cursor Name> INTO <parameter identifier>;

CLOSE <cursor Name>;

PREPARE prepares a <command identifier> to represent the query statement to be executed, and then defines a cursor for the <command identifier>. This action is not only named after the cursor, in addition, the <cursor Name> is associated with the corresponding <command identifier>. Next, you can open the cursor like a static SQL statement. The difference is that when you open the cursor, you can use the USING clause to enter the input primary variables required for the placeholder input primary variables when preparing the command, if the number of input variables does not match the number of placeholders, the system will give an error message indicating that the number of parameters does not match and set the corresponding error code. If the number and type of the input primary variable are correct, the cursor can be opened correctly. During the opening process, the system executes the prepared SQL statement and obtains the result activity set. After the cursor is opened, you can perform the FETCH operation on the cursor like the static SQL statement to obtain the result value in the activity set. If the number and type of the output primary variables in the INTO clause do not match the tuples in the active set, the system will give the corresponding error information and set the error code.

The following is an example of using PREPARE and cursor. The source code is as follows:

Example 8-2
/*************************************** *****************
* Esql * C instances using PREPARE and cursor *
**************************************** ****************/
# Include <stdio. h>
# Include <stdlib. h>
Void error ()
{
/* Do not handle errors to prevent endless loops */
Exec SQL WHENEVER SQLERROR CONTINUE;
/* Output error message */
Sqlprint ();
Exec SQL ROLLBACK WORK;
Exit (1 );
}
Int main ()
{
/* Declare the host array variable */
Exec SQL BEGIN DECLARE SECTION;
Char command [80];
Char hnum [4];
Char hname [21];
Exec SQL END DECLARE SECTION;
 
/* Handle errors */
Exec SQL WHENEVER SQLERROR do error ();
Exec SQL WHENEVER NOT FOUND goto not_found;
 
/* Obtain Dynamic commands */
Sprintf (command, "SELECT empname FROM employee
WHERE empnum = ?");
Printf ("enter the employee ID to be named: \ n ");
Scanf ("% s", hnum );
 
/* Connect to Shentong database */
Exec SQL CONNECT TO sql92test @ localhost USER
Oscar USING zjuhxy;
/* Prepare */
Exec SQL PREPARE prep_emp FROM: command;
/* Declare a cursor related to the dynamic statement */
Exec SQL DECLARE cur_emp CURSOR FOR prep_emp;
/* PASS Parameters to dynamic statements through the OPEN operation */
Exec SQL OPEN cur_emp USING: hnum;
Exec SQL FETCH cur_emp INTO: hname;
/* The empname can be output here */
Printf ("Employee % s name: % s. \ n", hnum, hname );
/* The user does not call the close cursor statement, but the system will automatically close the cursor at the end of the transaction */
Exec SQL COMMIT WORK;
Exec SQL DISCONNECT;
Exit (0 );
Not_found:
Printf ("Employee % s does not exist. \ n", hnum );
Exec SQL ROLLBACK WORK;
Exec SQL DISCONNECT;
Exit (0 );
}