Programmer's Guide to the Pro*C/C++ Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
When writing an application, you are usually faced with the problem of storing and manipulating large collections of data. Arrays simplify the task of naming and referencing the individual items in each collection.
Using arrays can boost the performance of your application. Arrays let you manipulate an entire collection of data items with a single SQL statement. Thus, Oracle communication overhead is reduced markedly, especially in a networked environment.
For example, suppose you want to insert information about 300 employees into the EMP table. Without arrays your program must do 300 individual INSERTs--one for each employee. With arrays, only one INSERT need be done.
char emp_name[50][10];
int emp_number[50];
float salary[50];
Except for character arrays (strings), host arrays that might be referenced in a SQL statement are limited to one dimension. So, the two-dimensional array declared in the following example is invalid:
int hi_lo_scores[25][25]; /* not allowed */
If you use multiple host arrays in a single SQL statement, their dimensions should be the same. Otherwise, an "array size mismatch" warning message is issued at precompile time. If you ignore this warning, the precompiler uses the smallest dimension for the SQL operation.
The embedded SQL syntax used for host arrays and simple host variables is nearly the same. One difference is the optional FOR clause, which lets you control array processing. Also, there are restrictions on mixing host arrays and simple host variables in a SQL statement.
The following sections illustrate the use of host arrays in data manipulation statements.
char emp_name[50][20];
int emp_number[50];
float salary[50];
EXEC SQL SELECT ENAME, EMPNO, SAL
INTO :emp_name, :emp_number, :salary
FROM EMP
WHERE SAL > 1000;
In this example, the SELECT statement returns up to 50 rows. If there are fewer than 50 eligible rows or you want to retrieve only 50 rows, this method will suffice. However, if there are more than 50 eligible rows, you cannot retrieve all of them this way. If you re-execute the SELECT statement, it just returns the first 50 rows again, even if more are eligible. You must either dimension a larger array or declare a cursor for use with the FETCH statement.
If a SELECT INTO statement returns more rows than the number of elements you dimensioned, Oracle issues the error message
ORA-02112: PCC: SELECT ...INTO returns too many rows
unless you specify SELECT_ERROR=NO. For more information about the SELECT_ERROR option, see the section "Using the Precompiler Options" .
Batch fetches within a loop let you retrieve a large number of rows with ease. Each FETCH returns the next batch of rows from the current active set. In the following example, you fetch in 20-row batches:
int emp_number[20];
float salary[20];
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, sal FROM emp;
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND do break;
for (;;)
{
EXEC SQL FETCH emp_cursor
INTO :emp_number, :salary;
/* process batch of rows */
...
}
See the demo program sample3.pc (page 3 - 30) for an additional example of batch fetching.
EXEC SQL OPEN cursor1;
EXEC SQL OPEN cursor2;
EXEC SQL FETCH cursor1 INTO :array_of_20;
/* now running total in sqlerrd[2] is 20 */
EXEC SQL FETCH cursor2 INTO :array_of_30;
/* now running total in sqlerrd[2] is 30, not 50 */
EXEC SQL FETCH cursor1 INTO :array_of_20;
/* now running total in sqlerrd[2] is 40 (20 + 20) */
EXEC SQL FETCH cursor2 INTO :array_of_30;
/* now running total in sqlerrd[2] is 60 (30 + 30) */
Also, you cannot mix simple host variables with host arrays in the INTO clause of a SELECT or FETCH statement. If any of the host variables is an array, all must be arrays.
Table 10 - 1 shows which uses of host arrays are valid in a SELECT INTO statement:
INTO Clause | WHERE Clause | Valid? |
array | array | no |
scalar | scalar | yes |
array | scalar | yes |
scalar | array | no |
When DBMS={V7 | V6_CHAR}, if you SELECT or FETCH a null column value into a host array not associated with an indicator array, Oracle stops processing, sets sqlerrd[2] to the number of rows processed, and issues the following error message:
ORA-01405: fetched column value is NULL
ORA-01406: fetched column value was truncated
In either case, you can check sqlerrd[2] for the number of rows processed before the truncation occurred. The rows-processed count includes the row that caused the truncation error.
When DBMS={V7 | V6_CHAR}, truncation is not considered an error, so Oracle continues processing.
Again, when doing array SELECTs and FETCHes, always use indicator arrays. That way, if Oracle assigns one or more truncated column values to an output host array, you can find the original lengths of the column values in the associated indicator array.
If some elements in the arrays are irrelevant, you can use the FOR clause to control the number of rows inserted. See the section "Using the FOR Clause" .
An example of inserting with host arrays follows:
char emp_name[50][20];
int emp_number[50];
float salary[50];
/* populate the host arrays */
...
EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)
VALUES (:emp_name, :emp_number, :salary);
The cumulative number of rows inserted can be found in the rows-processed count, sqlca.sqlerrd[2].
In the following example, the INSERT is done one row at a time. This is much less efficient than the previous example, since a call to the server must be made for each row inserted.
for (i = 0; i < array_dimension; i++)
EXEC SQL INSERT INTO emp (ename, empno, sal)
VALUES (:emp_name[i], :emp_number[i], :salary[i]);
Mixing simple host variables with host arrays in the VALUES clause of an INSERT statement is not allowed. If any of the host variables is an array, all must be arrays.
int emp_number[50];
float salary[50];
/* populate the host arrays */
EXEC SQL UPDATE emp SET sal = :salary
WHERE EMPNO = :emp_number;
The cumulative number of rows updated can be found in sqlerrd[2]. The number does not include rows processed by an update cascade.
If some elements in the arrays are irrelevant, you can use the embedded SQL FOR clause to limit the number of rows updated.
The last example showed a typical update using a unique key (EMP_NUMBER). Each array element qualified just one row for updating. In the following example, each array element qualifies multiple rows:
char job_title [10][20];
float commission[10];
...
EXEC SQL UPDATE emp SET comm = :commission
WHERE job = :job_title;
You cannot use host arrays with the CURRENT OF clause in an UPDATE statement. For an alternative, see the section "Mimicking CURRENT OF" .
Table 10 - 2 shows which uses of host arrays are valid in an UPDATE statement:
SET Clause | WHERE Clause | Valid? |
array | array | yes |
scalar | scalar | yes |
array | scalar | no |
scalar | array | no |
An example of deleting with host arrays follows:
...
int emp_number[50];
/* populate the host array */
...
EXEC SQL DELETE FROM emp
WHERE empno = :emp_number;
The cumulative number of rows deleted can be found in sqlerrd[2]. The number does not include rows processed by a delete cascade.
The last example showed a typical delete using a unique key (EMP_NUMBER). Each array element qualified just one row for deletion. In the following example, each array element qualifies multiple rows:
...
char job_title[10][20];
/* populate the host array */
...
EXEC SQL DELETE FROM emp
WHERE job = :job_title;
You cannot use host arrays with the CURRENT OF clause in a DELETE statement. For an alternative, see the section "Mimicking CURRENT OF" 10 - 13.
int emp_number[50];
int dept_number[50];
float commission[50];
short ind_comm[50]; /* indicator array */
/* populate the host arrays and
populate the indicator array; to insert a null into
the COMM column, assign -1 to the appropriate element in
the indicator array
*/
EXEC SQL INSERT INTO emp (empno, deptno, comm)
VALUES (:emp_number, :dept_number, :commission INDICATOR :ind_comm);
The indicator array dimension cannot be smaller than the host array dimension.
char emp_name[100][20];
float salary[100];
int rows_to_insert;
/* populate the host arrays */
rows_to_insert = 25; /* set FOR-clause variable */
EXEC SQL FOR :rows_to_insert /* will process only 25 rows */
INSERT INTO emp (ename, sal)
VALUES (:emp_name, :salary);
The FOR clause can use an integer host variable to count array elements, or an integer literal. A complex C expression that resolves to an integer cannot be used. For example, the following statement that uses an integer expression is illegal:
EXEC SQL FOR :rows_to_insert + 5 /* illegal */
INSERT INTO emp (ename, empno, sal)
VALUES (:emp_name, :emp_number, :salary);
The FOR clause variable specifies the number of array elements to be processed. Make sure the number is not larger than the smallest array dimension. Also, the number must be positive. If it is negative or zero, no rows are processed and Oracle issues an error message.
PCC-E-0056: FOR clause not allowed on SELECT statement at ...
The FOR clause is not allowed in SELECT statements because its meaning is unclear. Does it mean "execute this SELECT statement n times"? Or, does it mean "execute this SELECT statement once, but return n rows"? The problem in the former case is that each execution might return multiple rows. In the latter case, it is better to declare a cursor and use the FOR clause in a FETCH statement, as follows:
EXEC SQL FOR :limit FETCH emp_cursor INTO ...
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE empno = :emp_number;
...
EXEC SQL OPEN emp_cursor;
...
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
EXEC SQL UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
However, you cannot use the FOR clause with the CURRENT OF clause. The following statements are invalid because the only logical value of limit is 1 (you can only update or delete the current row once):
EXEC SQL FOR :limit UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
...
EXEC SQL FOR :limit DELETE FROM emp
WHERE CURRENT OF emp_cursor;
PCC-S-0055: Array <name> not allowed as bind variable at ...
For example, assuming the declarations
int mgr_number[50];
char job_title[50][20];
it would be ambiguous if the statement
EXEC SQL SELECT mgr INTO :mgr_number FROM emp
WHERE job = :job_title;
were treated like the imaginary statement
for (i = 0; i < 50; i++)
SELECT mgr INTO :mgr_number[i] FROM emp
WHERE job = :job_title[i];
because multiple rows might meet the WHERE-clause search condition, but only one output variable is available to receive data. Therefore, an error message is issued.
On the other hand, it would not be ambiguous if the statement
EXEC SQL UPDATE emp SET mgr = :mgr_number
WHERE empno IN (SELECT empno FROM emp
WHERE job = :job_title);
were treated like the imaginary statement
for (i = 0; i < 50; i++)
UPDATE emp SET mgr = :mgr_number[i]
WHERE empno IN (SELECT empno FROM emp
WHERE job = :job_title[i]);
because there is a mgr_number in the SET clause for each row matching job_title in the WHERE clause, even if each job_title matches multiple rows. All rows matching each job_title can be SET to the same mgr_number. Therefore, no error message is issued.
char emp_name[20][10];
char job_title[20][10];
char old_title[20][10];
char row_id[20][18];
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, job, rowid FROM emp;
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND do break;
for (;;)
{
EXEC SQL FETCH emp_cursor
INTO :emp_name, :job_title, :row_id;
...
EXEC SQL DELETE FROM emp
WHERE job = :old_title AND rowid = :row_id;
EXEC SQL COMMIT WORK;
}
However, the fetched rows are not locked because no FOR UPDATE OF clause is used. (You cannot use FOR UPDATE OF without CURRENT OF.) So, you might get inconsistent results if another user changes a row after you read it but before you delete it.
When using host arrays with FETCH, to find the number of rows returned by the most recent iteration, subtract the current value of sqlca.sqlerrd[2] from its previous value (stored in another variable). In the following example, you determine the number of rows returned by the most recent fetch:
int emp_number[100];
char emp_name[100][20];
int rows_to_fetch, rows_before, rows_this_time;
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, ename
FROM emp
WHERE deptno = 30;
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
/* initialize loop variables */
rows_to_fetch = 20; /* number of rows in each "batch" */
rows_before = 0; /* previous value of sqlerrd[2] */
rows_this_time = 20;
while (rows_this_time == rows_to_fetch)
{
EXEC SQL FOR :rows_to_fetch
FETCH emp_cursor
INTO :emp_number, :emp_name;
rows_this_time = sqlca.sqlerrd[2] - rows_before;
rows_before = sqlca.sqlerrd[2];
}
...
sqlca.sqlerrd[2] is also useful when an error occurs during an array operation. Processing stops at the row that caused the error, so sqlerrd[2] gives the number of rows processed successfully.
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |