Using the FOR Clause in Pro*C (Oracle)

Using the FOR Clause

You can use the optional embedded SQL FOR clause to set the number of array elements processed by any of the following SQL statements:

DELETE
EXECUTE
FETCH
INSERT
OPEN
UPDATE

The FOR clause is especially useful in UPDATE, INSERT, and DELETE statements. With these statements you might not want to use the entire array. The FOR clause lets you limit the elements used to just the number you need, as the following example shows:

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.


Restrictions
Two restrictions keep FOR clause semantics clear. You cannot use the FOR clause in a SELECT statement or with the CURRENT OF clause.

In a SELECT Statement
If you use the FOR clause in a SELECT statement, you get the following 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:


http://www.lsbu.ac.uk/oracle/oracle7/api/doc/PC_22/ch10.htm