pro*c中游标取数时遇到的几个问题,请指教

ohwww

正在看一个程序,我大体说明如下.

在头文件中定义local_len和结构体local_str:
#define local_len 450000

struct local_str {
        long   len;                  /*此结构体中实际保存的记录数*/
        long   pay_no[local_len+1];
        long   user_no[local_len+1];
        };
struct local_str *p_local;

程序中给结构体分配内存后,想通过游标从数据库中把数取到结构体中的pay_no数组和user_no数组里.

程序中游标对应的sql语句是这样的:
select  pay_no,user_no from tab1 where ...;

游标经过 EXEC SQL PREPARE s FROM :sqls;
             EXEC SQL DECLARE v2_cur CURSOR FOR s;
            EXEC SQL OPEN v2_cur;
之后,就是fetch数据了,fetch语句是这样写的:
EXEC SQL for :local_len FETCH v2_cur INTO  
:p_local->;pay_no,:p_local->;user_no;

我不明白的问题是:
1) fetch语句中的for :local_len 是什么意思,我没有查到有这种写法的相应资料;
2)通过fetch语句只能取到56785条记录,可是实际运行相应的sql语句却能查到425712条记录,我看了一下sqlca.sqlerrd[2]中的值也是56785,这是为什么??

ohwww

自问自答吧
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 does not exceed the smallest array dimension. Internally, the value is treated as an unsigned quantity. An attempt to pass a negative value through the use of a signed host variable will result in unpredictable behavior.

FOR Clause 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 an error message.

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 ...

With the CURRENT OF Clause
You can use the CURRENT OF clause in an UPDATE or DELETE statement to refer to the latest row returned by a FETCH statement, as the following example shows:

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;