Programmer's Guide to the Pro*C/C++ Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Host variables in the INTO clause of a SELECT or FETCH statement are called output host variables because they hold column values output by Oracle. Oracle assigns the column values to corresponding output host variables in the INTO clause.
All other host variables in a SQL statement are called input host variables because your program inputs their values to Oracle. For example, you use input host variables in the VALUES clause of an INSERT statement and in the SET clause of an UPDATE statement. They are also used in the WHERE, HAVING, and FOR clauses. Input host variables can appear in a SQL statement wherever a value or expression is allowed.
Attention: In an ORDER BY clause, you can use a host variable, but it is treated as a constant or literal, and hence the contents of the host variable have no effect. For example, the SQL statement
EXEC SQL SELECT ename, empno INTO :name, :number FROM emp ORDER BY :ord;
appears to contain an input host variable, :ord. However, the host variable in this case is treated as a constant, and regardless of the value of :ord, no ordering is done.
You cannot use input host variables to supply SQL keywords or the names of database objects. Thus, you cannot use input host variables in data definition statements such as ALTER, CREATE, and DROP. In the following example, the DROP TABLE statement is invalid:
char table_name[30]; printf("Table name? "); gets(table_name); EXEC SQL DROP TABLE :table_name; -- host variable not allowed
If you need to change database object names at runtime, use dynamic SQL. See Chapter for more information about dynamic SQL.
Before Oracle executes a SQL statement containing input host variables, your program must assign values to them. An example follows:
int emp_number; char temp[20]; VARCHAR emp_name[20]; /* get values for input host variables */ printf("Employee number? "); gets(temp); emp_number = atoi(temp); printf("Employee name? "); gets(emp_name.arr); emp_name.len = strlen(emp_name.arr); EXEC SQL INSERT INTO EMP (EMPNO, ENAME) VALUES (:emp_number, :emp_name);
Notice that the input host variables in the VALUES clause of the INSERT statement are prefixed with colons.
You use indicator variables in the VALUES or SET clause to assign nulls to input host variables and in the INTO clause to detect nulls or truncated values in output host variables.
On Input The values your program can assign to an indicator variable have the following meanings:
-1
Oracle will assign a null to the column, ignoring the value of the host variable.
>=0
Oracle will assign the value of the host variable to the column.
On Output The values Oracle can assign to an indicator variable have the following meanings:
-1
The column value is null, so the value of the host variable is indeterminate.
0
Oracle assigned an intact column value to the host variable.
>0
Oracle assigned a truncated column value to the host variable. The integer returned by the indicator variable is the original length of the column value, and SQLCODE in SQLCA is set to zero.
-2
Oracle assigned a truncated column variable to the host variable, but the original column value could not be determined (a LONG column, for example).
Remember, an indicator variable must be defined in the Declare Section as a 2-byte integer and, in SQL statements, must be prefixed with a colon and must immediately follow its host variable.
set ind_comm = -1; EXEC SQL INSERT INTO emp (empno, comm) VALUES (:emp_number, :commission:ind_comm);
The indicator variable ind_comm specifies that a null is to be stored in the COMM column.
You can hardcode the null instead, as follows:
EXEC SQL INSERT INTO emp (empno, comm) VALUES (:emp_number, NULL);
While this is less flexible, it might be more readable. Typically, you insert nulls conditionally, as the next example shows:
printf("Enter employee number or 0 if not available: "); scanf("%d", &emp_number); if (emp_number == 0) ind_empnum = -1; else ind_empnum = 0; EXEC SQL INSERT INTO emp (empno, sal) VALUES (:emp_number:ind_empnum, :salary);
EXEC SQL SELECT ename, sal, comm INTO :emp_name, :salary, :commission:ind_comm FROM emp WHERE empno = :emp_number; if (ind_comm == -1) pay = salary; /* commission is null; ignore it */ else pay = salary + commission;
/* assume that commission is NULL */ EXEC SQL SELECT ename, sal, comm INTO :emp_name, :salary, :commission FROM emp WHERE empno = :emp_number;
SQLCODE in the SQLCA is set to zero indicating that Oracle executed the statement without detecting an error or exception.
However, when DBMS=V7 or DBMS=V6_CHAR, if you SELECT or FETCH nulls into a host variable not associated with an indicator variable, Oracle issues the following error message:
ORA-01405: fetched column value is NULL
For more information about the DBMS option, see page 7 - 9, "Using the Precompiler Options".
EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE :commission INDICATOR :ind_comm IS NULL ...
However, you cannot use a relational operator to compare nulls with each other or with other values. For example, the following SELECT statement fails if the COMM column contains one or more nulls:
EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE comm = :commission;
The next example shows how to compare values for equality when some of them might be nulls:
EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE (comm = :commission) OR ((comm IS NULL) AND (:commission INDICATOR :ind_comm IS NULL));
ORA-01406: fetched column value was truncated
However, when DBMS=V7 or V6_CHAR, a warning is generated instead of an error.
When executing a data manipulation statement such as INSERT, UPDATE, or DELETE, your only concern, besides setting the values of any input host variables, is whether the statement succeeds or fails. To find out, you simply check the SQLCA. (Executing any SQL statement sets the SQLCA variables.) You can check in the following two ways:
When executing a SELECT statement (query), however, you must also deal with the rows of data it returns. Queries can be classified as follows:
Note: Host arrays let you process "batches" of rows. For more information, see Chapter 10 "Using Host Arrays." This chapter assumes the use of scalar host variables.
The following embedded SQL statements let you query and manipulate Oracle data:
SELECT
Returns rows from one or more tables.
INSERT
Adds new rows to a table.
UPDATE
Modifies rows in a table.
DELETE
Removes unwanted rows from a table.
The following embedded SQL statements let you define and manipulate an explicit cursor:
DECLARE
Names the cursor and associates it with a query.
ALLOCATE
Allocates memory for a cursor variable.
OPEN
Executes the query and identifies the active set. (Cannot be used for cursor variables, which must be OPENed on the server.)
FETCH
Advances the cursor and retrieves each row in the active set, one by one.
CLOSE
Disables the cursor (the active set becomes undefined).
In the coming sections, first you learn how to code INSERT, UPDATE, DELETE, and single-row SELECT statements. Then, you progress to multirow SELECT statements. For a detailed discussion of each statement and its clauses, see the Oracle7 Server SQL Reference.
EXEC SQL SELECT ename, job, sal + 2000 INTO :emp_name, :job_title, :salary FROM emp WHERE empno = :emp_number;
The column names and expressions following the keyword SELECT make up the select list. The select list in our example contains three items. Under the conditions specified in the WHERE clause (and following clauses, if present), Oracle returns column values to the host variables in the INTO clause.
The number of items in the select list should equal the number of host variables in the INTO clause, so there is a place to store every returned value.
In the simplest case, when a query returns one row, its form is that shown in the last example. However, if a query can return more than one row, you must FETCH the rows using a cursor or SELECT them into a host-variable array. Cursors and the FETCH statement are discussed later in this chapter; array processing is discussed, "Using Host Arrays."
If a query is written to return only one row but might actually return several rows, the result of the SELECT is indeterminate. Whether this causes an error depends on how you specify the SELECT_ERROR option. The default value, YES, generates an error if more than one row is returned.
EXEC SQL INSERT INTO emp (empno, ename, sal, deptno) VALUES (:emp_number, :emp_name, :salary, :dept_number);
Each column you specify in the column list must belong to the table named in the INTO clause. The VALUES clause specifies the row of values to be inserted. The values can be those of constants, host variables, SQL expressions, SQL functions such as USER and SYSDATE, or user-defined PL/SQL functions.
The number of values in the VALUES clause must equal the number of names in the column list. However, you can omit the column list if the VALUES clause contains a value for each column in the table, in the order that they are defined in the table.
EXEC SQL INSERT INTO emp2 (empno, ename, sal, deptno) SELECT empno, ename, sal, deptno FROM emp WHERE job = :job_title;
Notice how the INSERT statement uses the subquery to obtain intermediate results.
EXEC SQL UPDATE emp SET sal = :salary, comm = :commission WHERE empno = :emp_number;
You can use the optional WHERE clause to specify the conditions under which rows are UPDATEd. See the section "Using the WHERE Clause" .
The SET clause lists the names of one or more columns for which you must provide values. You can use a subquery to provide the values, as the following example shows:
EXEC SQL UPDATE emp SET sal = (SELECT AVG(sal)*1.1 FROM emp WHERE deptno = 20) WHERE empno = :emp_number;
EXEC SQL DELETE FROM emp WHERE deptno = :dept_number;
You can use the optional WHERE clause to specify the condition under which rows are DELETEd.
If you omit the WHERE clause, all rows in the table or view are processed. If you omit the WHERE clause in an UPDATE or DELETE statement, Oracle sets sqlwarn[4] in the SQLCA to `W' to warn that all rows were processed.
A cursor identifies the current row in the set of rows returned by the query. This allows your program to process the rows one at a time. The following statements let you define and manipulate a cursor:
The OPEN statement executes the query and identifies all the rows that meet the query search condition. These rows form a set called the active set of the cursor. After OPENing the cursor, you can use it to retrieve the rows returned by its associated query.
Rows of the active set are retrieved one by one (unless you use host arrays). You use a FETCH statement to retrieve the current row in the active set. You can execute FETCH repeatedly until all rows have been retrieved.
When done FETCHing rows from the active set, you disable the cursor with a CLOSE statement, and the active set becomes undefined.
The following sections show you how to use these cursor control statements in your application program.
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, empno, sal FROM emp WHERE deptno = :dept_number;
The cursor name is an identifier used by the precompiler, not a host or program variable, and should not be defined in the Declare Section. Cursor names cannot be hyphenated. They can be any length, but only the first 31 characters are significant. For ANSI compatibility, use cursor names no longer than 18 characters.
The SELECT statement associated with the cursor cannot include an INTO clause. Rather, the INTO clause and list of output host variables are part of the FETCH statement.
Because it is declarative, the DECLARE statement must physically (not just logically) precede all other SQL statements referencing the cursor. That is, forward references to the cursor are not allowed. In the following example, the OPEN statement is misplaced:
... EXEC SQL OPEN emp_cursor; EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, empno, sal FROM emp WHERE ename = :emp_name;
The cursor control statements (DECLARE, OPEN, FETCH, CLOSE) must all occur within the same precompiled unit. For example, you cannot DECLARE a cursor in file A, then OPEN it in file B.
Your host program can DECLARE as many cursors as it needs. However, in a given file, every DECLARE statement must be unique. That is, you cannot DECLARE two cursors with the same name in one precompilation unit, even across blocks or procedures, because the scope of a cursor is global within a file.
If you will be using many cursors, you might want to specify the MAXOPENCURSORS option. For more information, see Chapter 7, "Running the Pro*C/C++ Precompiler," and Appendix C, "Performance Tuning."
EXEC SQL OPEN emp_cursor;
OPEN positions the cursor just before the first row of the active set. It also zeroes the rows-processed count kept by the third element of SQLERRD in the SQLCA. However, none of the rows is actually retrieved at this point. That will be done by the FETCH statement.
Once you OPEN a cursor, the query's input host variables are not re-examined until you reOPEN the cursor. Thus, the active set does not change. To change the active set, you must reOPEN the cursor.
Generally, you should CLOSE a cursor before reOPENing it. However, if you specify MODE=ORACLE (the default), you need not CLOSE a cursor before reOPENing it. This can increase performance; for details, see Appendix C, "Performance Tuning."
The amount of work done by OPEN depends on the values of three precompiler options: HOLD_CURSOR, RELEASE_CURSOR, and MAXOPENCURSORS. For more information, see the section "Using the Precompiler Options" .
EXEC SQL FETCH emp_cursor INTO :emp_name, :emp_number, :salary;
The cursor must have been previously DECLAREd and OPENed. The first time you execute FETCH, the cursor moves from before the first row in the active set to the first row. This row becomes the current row. Each subsequent execution of FETCH advances the cursor to the next row in the active set, changing the current row. The cursor can only move forward in the active set. To return to a row that has already been FETCHed, you must reOPEN the cursor, then begin again at the first row of the active set.
If you want to change the active set, you must assign new values to the input host variables in the query associated with the cursor, then reOPEN the cursor. When MODE=ANSI, you must CLOSE the cursor before reOPENing it.
As the next example shows, you can FETCH from the same cursor using different sets of output host variables. However, corresponding host variables in the INTO clause of each FETCH statement must have the same datatype.
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; ... }
If the active set is empty or contains no more rows, FETCH returns the "no data found" error code to sqlcode in the SQLCA, or to the SQLCODE or SQLSTATE status variables. The status of the output host variables is indeterminate. (In a typical program, the WHENEVER NOT FOUND statement detects this error.) To re-use the cursor, you must reOPEN it.
It is an error to FETCH on a cursor under the following conditions:
EXEC SQL CLOSE emp_cursor;
You cannot FETCH from a closed cursor because its active set becomes undefined. If necessary, you can reOPEN a cursor (with new values for the input host variables, for example).
When MODE=ORACLE, issuing a COMMIT or ROLLBACK closes cursors referenced in a CURRENT OF clause. Other cursors are unaffected by COMMIT or ROLLBACK and if open, remain open. However, when MODE=ANSI, issuing a COMMIT or ROLLBACK closes all explicit cursors. For more information about COMMIT and ROLLBACK, see Chapter 7, "Defining and Controlling Transactions." For more information about the CURRENT OF clause, see the next section.
SELECT /*+ ALL_ROWS (cost-based) */ empno, ename, sal, job INTO :emp_rec FROM emp WHERE deptno = :dept_number;
As shown in this statement, the comment can contain optimizer hints as well as other comments.
For more information about the cost-based optimizer, and optimizer hints, see the Oracle7 Server Application Developer's Guide.
The FOR UPDATE OF clause is optional when you DECLARE a cursor that is referenced in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause signals the precompiler to add a FOR UPDATE clause if necessary. For more information, see page 8 - 11, "Using the FOR UPDATE OF Clause".
In the following example, you use the CURRENT OF clause to refer to the latest row FETCHed from a cursor named emp_cursor:
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, sal FROM emp WHERE job = 'CLERK' FOR UPDATE OF sal; ... EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND GOTO ... for (;;) { EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; ... EXEC SQL UPDATE emp SET sal = :new_salary WHERE CURRENT OF emp_cursor; }
Also, you cannot use host arrays with the CURRENT OF clause. For an alternative, see page 10 - 13, "Mimicking CURRENT OF".
Furthermore, you cannot reference multiple tables in an associated FOR UPDATE OF clause, which means that you cannot do joins with the CURRENT OF clause.
Finally, you cannot use dynamic SQL with the CURRENT OF clause.
... /* define a cursor */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, job FROM emp WHERE empno = :emp_number FOR UPDATE OF job; /* open the cursor and identify the active set */ EXEC SQL OPEN emp_cursor; /* break if the last row was already fetched */ EXEC SQL WHENEVER NOT FOUND DO break; /* fetch and process data in a loop */ for (;;) { EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title; /* optional host-language statements that operate on the FETCHed data */ EXEC SQL UPDATE emp SET job = :new_job_title WHERE CURRENT OF emp_cursor; } ... /* disable the cursor */ EXEC SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; ...
All FETCHes except the final one return a row and, if no errors were detected during the FETCH, a success status code. The final FETCH fails and returns the "no data found" Oracle error code to sqlca.sqlcode. The cumulative number of rows actually FETCHed is found in sqlerrd[2] in the SQLCA.
#include <stdio.h> /* declare host variables */ char userid[12] = "SCOTT/TIGER"; char emp_name[10]; int emp_number; int dept_number; char temp[32]; void sql_error(); /* include the SQL Communications Area */ #include <sqlca.h> main() { emp_number = 7499; /* handle errors */ EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error"); /* connect to Oracle */ EXEC SQL CONNECT :userid; printf("Connected.\n"); /* declare a cursor */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename FROM emp WHERE deptno = :dept_number; printf("Department number? "); gets(temp); dept_number = atoi(temp); /* open the cursor and identify the active set */ EXEC SQL OPEN emp_cursor; printf("Employee Name\n"); printf("-------------\n");
/* fetch and process data in a loop exit when no more data */ EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH emp_cursor INTO :emp_name; printf("%s\n", emp_name); } EXEC SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; exit(0); } void sql_error(msg) char *msg; { char buf[500]; int buflen, msglen; EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; buflen = sizeof (buf); sqlglm(buf, &buflen, &msglen); printf("%s\n", msg); printf("%*.s\n", msglen, buf); exit(1); }
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |