During the development of C language, it is necessary to operate the database. Generally, Oracle Pro*C/C++ precompiler is used to use Oracle database.

This article describes the Oracle Pro*C/C++ precompiler, its role in developing applications that manipulate Oracle data, and what it enables your applications to do. Specific such as use.

The cursor

When a query returns multiple rows, you can explicitly define a cursor

  • Processing beyond the first row returned by the query
  • Tracks the rows currently being processed

Alternatively, you can use host arrays.

The cursor identifies the current row in the rowset returned by the query. This allows your program to process one line at a time. The following statements allow you to define and manipulate cursors:

  • DECLARE CURSOR
  • OPEN
  • FETCH
  • CLOSE

The CURSOR is first named and associated with the query using the DECLARE CURSOR statement.

The OPEN statement executes the query and identifies all rows that meet the search criteria of the query. These rows form a set called the set of activities of the cursor. Once the cursor is opened, it can be used to retrieve the rows returned by its associated query.

The rows of the active set are retrieved one by one (unless you are using a host array). You can use the FETCH statement to retrieve the current move in an activity set. The FETCH can be repeated until all rows are retrieved.

After fetching rows from the active set, you can use the CLOSE statement to disable the cursor, and the active set becomes undefined.

The following sections show how to use these cursor control statements in your application.

DECLARE CURSOR statement

You can define a CURSOR by naming it and associating it with a query using the DECLARE CURSOR statement, as shown in the following example:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
     SELECT ename, empno, sal 
     FROM emp 
     WHERE deptno = :dept_number;
Copy the code

The cursor name is an identifier used by the precompiler, not a host or program variable, and should not be defined in the declaration section. Therefore, cursor names cannot be passed from one precompiled unit to another. Cursor names cannot be hyphenated. They can be of any length, but only the first 31 characters make sense. For ANSI compatibility, use a cursor name of no more than 18 characters.

The pre-compiler option CLOSE_ON_COMMIT is provided for use on the command line or in configuration files. When CLOSE_ON_COMMIT=YES, any cursor not declared WITH the WITH HOLD clause is closed after COMMIT or ROLLBACK.

If the level specified by MODE is higher than CLOSE_ON_COMMIT, MODE takes precedence. The default values are MODE=ORACLE and CLOSE_ON_COMMIT=NO. If MODE=ANSI is specified, any cursor that does not use the WITH HOLD clause will be closed at COMMIT. The application will run more slowly because the cursor closes and reopens several times. Setting CLOSE_ON_COMMIT=NO when MODE=ANSI improves performance. Want to see how macro options like MODE affect micro options like CLOSE_ON_COMMIT

The SELECT statement associated with the cursor cannot contain the INTO clause. In contrast, the INTO clause and the output master variable list are part of the FETCH statement.

Because it is declarative, the DECLARE CURSOR statement must physically (not just logically) precede all other SQL statements that reference the CURSOR. That is, the cursor is not allowed to be referenced forward. In the following example, the OPEN statement is misplaced:

. EXEC SQL OPEN emp_cursor; * -- MISPLACED OPEN STATEMENT EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, empno, sal FROM emp WHERE ename = :emp_name;Copy the code

Cursor control statements (DECLARE, OPEN, FETCH, CLOSE) must all appear in the same precompiled unit. For example, you cannot declare A cursor in file A and then open it in file B.

Your host program can declare as many cursors as it wants. However, each DECLARE statement must be unique in a given file. That is, you cannot declare two cursors with the same name in a single precompiled unit, even across blocks or procedures, because the scope of the cursor is global in the file.

For users WITH MODE=ANSI or CLOSE_ON_COMMIT=YES, you can use the WITH HOLD clause in the DECLARE part to override the behavior defined by the two options. With these options set, the behavior will be to close all cursors when you issue a COMMIT. This can affect performance due to the overhead of re-opening the cursor to continue processing. Careful use of WITH HOLD can speed up programs that need to comply WITH ANSI precompiler standards in most respects.

If you will be using many cursors, you may need to specify the MAXOPENCURSORS option.

The OPEN statement

You can use the OPEN statement to execute the query and identify the active set. In the following example, you open a cursor named emp_CURSOR:

EXEC SQL OPEN emp_cursor;
Copy the code

OPEN zeroes out the row processing count saved by the third element of SQLERRD in the SQLCA. However, none of the rows are visible to the application at this point. This is handled by the FETCH statement.

OPEN positions the cursor before the first line of the active set. It also zeroes out the row processing count held by the third element of SQLERRD in the SQLCA. However, no rows are actually retrieved at this point. This will be done by the FETCH statement.

Once you open the cursor, the input pivot variables for the query are not re-examined before you reopen the cursor. Therefore, the set of activities does not change. To change the active set, you must reopen the cursor.

In general, you should close the cursor before opening it again. However, if MODE=ORACLE (the default) is specified, the CLOSE cursor is not required before the cursor is reopened. This can improve performance.

The amount of work done by the OPEN depends on the values of three precompiler options: HOLD_CURSOR, RELEASE_CURSOR and MAXOPENCURSORS.

FETCH statement

You can use the FETCH statement to retrieve rows from the active set and specify the output master variable that will contain the results. Recall that the SELECT statement associated with a cursor cannot contain the INTO clause. In contrast, the INTO clause and the output master variable list are part of the FETCH statement. In the following example, you FETCH INTO three host variables:

EXEC SQL FETCH emp_cursor 
INTO :emp_name, :emp_number, :salary;
Copy the code

Cursors must be declared and opened in advance. The first time a FETCH is performed, the cursor moves from before the first row in the active set to the first row. This row becomes the current row. Each subsequent FETCH changes the current row by advancing the cursor to the next row in the active set. The cursor can only move forward in the activity set. To return to a row that has been fetched, you must reopen the cursor and start from the first row in the active set.

If you want to change the active set, you must assign new values to the input pivot variables in the query associated with the cursor, and then reopen the cursor. When MODE=ANSI, you must close the cursor before restarting it.

As shown in the following example, you can FETCH from the same cursor using different sets of output pivot variables. However, the corresponding pivot variable in the INTO clause of each FETCH statement must have the same data type.

EXEC SQL DECLARE emp_cursor CURSOR FOR 
SELECT ename, Sal FROM emp WHERE deptno = 20; . EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND GOTO ... For (;;) { EXEC SQL FETCH emp_cursor INTO :emp_name1, :salary1; EXEC SQL FETCH emp_cursor INTO :emp_name2, :salary2; EXEC SQL FETCH emp_cursor INTO :emp_name3, :salary3; . }Copy the code

If the active set is empty or contains no more rows, FETCH returns the “data not found” error code to the SQLcode in the SQLCA, or to the SQLCode or SQLSTATE state variable. The state of the output pivot variable is uncertain. (In a typical program, the WHENEVER NOT FOUND statement detects this error.) To reuse a cursor, you must reopen it.

It is an error to FETCH a cursor if:

  • Before you open the cursor
  • After the “data not found” condition
  • After the closing

The CLOSE statement

After fetching rows from the active set, you close the cursor to release resources, such as storage, that you obtained by opening the cursor. When the cursor is closed, the parse lock is released. Which resources are released depends on how you specify the HOLD_CURSOR and RELEASE_CURSOR options. In the following example, you close the cursor named emp_CURSOR:

EXEC SQL CLOSE emp_cursor; 
Copy the code

You cannot FETCH from a closed cursor because its active set becomes undefined. If necessary, you can reopen the cursor (for example, with the new value of the input pivot variable).

When MODE=ORACLE, issuing a COMMIT or ROLLBACK closes the cursor referenced in the CURRENT OF clause. Other cursors are not affected by COMMIT or ROLLBACK and, if opened, remain open. However, when MODE=ANSI, issuing COMMIT or ROLLBACK closes all explicit cursors.