Created by Wang, Jerry, last modified on Oct 12, 2016
Go to start of metadata
OPEN CURSOR: After the OPEN CURSOR statement, the database cursor is positioned in front of the first line of the result set.
FETCH: This statement extracts the requested rows (using the addition INTO or APPENDING) from the results set of the database cursor from the current cursor position and assigns these rows to the data objects specified in the results set. If an internal table is specified after INTO or APPENDING, then either all rows are extracted, or as many as specified in the addition PACKAGE SIZE. The statement FETCH moves the position of the database cursor by the amount of extracted lines to the next line to be extracted.
I have written some small test report to verify above explanation.
我写了一个很简单的report 验证：
Source code:
OPEN CURSOR lv_cursor FOR
SELECT product_guid
FROM comm_product.
FETCH NEXT CURSOR lv_cursor
INTO TABLE lt_selection
PACKAGE SIZE size.
Size = 1: when test with size = 1, I observe the number of scanned records are 1447:
Below screenshot is got from tcode ST05.
Size = 100:
第二次执行，PREPARE和OPEN直接变成REOPEN，但是recs仍然是1447.
Execute reports for the second time, the number of records remains the same: 1447
Recs F1的说明是：
Press F1 on Recs to get help:
这个1447是怎么来的呢？因为我OPEN CURSOR时候没有指定任何条件，所以在OPEN CURSOR时，DB把整个product table的所有entry视为一个result set，然后只返回指定package size的条数。
所以ST05里面看到的这个Recs是指满足OPEN CURSOR 指定条件的record的个数，并不是最后返回给ABAP的record的个数。
Why we get 1447? Simply because of the fact that I perform OPEN CURSOR but without any where condition specified. As a result, when DB server executes OPEN CURSOR, the whole entries in table COMM_PRODUCT is treated as result set and touched, and finally the given number of records specified by ABAP keyword PACKAGE SIZE are returned.
In my test system, the total number of records in COMM_PRODUCT is just 1447:
After I create three more products,the total number becomes 1447 + 3 = 1450.
然后我再生成3个新的product，COMM_PRODUCT里面就有1450条entry。
Execute the test report once again, 1450 observed in trace.
重复执行测试report。ST05证明我们的结论是正确的。
再做一个验证：table里面有prefix为JERRY06152012开头的3条记录：
Do another verification, in table I have three records with JERRY06152012 as prefix.
I change the test report a little bit: ( add WHERE condition ）
FROM comm_product
WHERE
product_id LIKE 'JERRY06152012%'.
第一次执行size = 1
Firstly execute report with size = 1
Recs变成3了，因为匹配OPEN CURSOR条件的确实只有3条记录
Now only three records are touched, since in DB it is really the fact that only three records meet the where condition.
Execute with size = 100, the same result.
Size = 100, ST05结果和size = 1完全一致，都是3.
Conclusion( related to CRM WebUI )
In the past there is misunderstanding that the Max hit number in WebUI can control how many records are being scanned in DB.
Through the verification above, this understanding is wrong.
所以max hit不能控制每次OPEN CURSOR去DB 查找record的条数，这个条数是由OPEN CURSOR后面跟的WHERE CONDITION决定的。Max hit只能控制OPEN CURSOR的WHERE CONDITION 所
决定出的result set里，到底有多少条返回给ABAP。
Max Hit can NOT limit the number of records scanned by DB triggered via OPEN CURSOR keyword. Instead, this number ( observed in ST05 ) is only determined by WHERE CONDITION specified after OPEN CURSOR. The Max hit value can only specify how many records will be returned based on the result set determined by WHERE CONDITION from DB server to ABAP layer via DB interface.
There is another variant in SELECT keyword: UP TO XX ROWS.
SELECT product_guid INTO CORRESPONDING FIELDS OF TABLE lt_line FROM comm_product UP TO num ROWS.
Num = 1
Num = 143
说明SELECT UP TO xx ROWS是可以控制Processed record number的。
It proves compared with PACKAGE SIZE, the SELECT UP TO XX ROWS can control how many records in DB are scanned.
Unfortunately, we should NEVER simply replace all OPEN CURSOR & FETCH with SELECT UP TO XXX ROWS in our application code. The reason is simple: the latter could never be executed within a loop - it is completely a different logic, there is no cursor maintained internally in latter, thus the position of current record being processed can never be remembered.
但SELECT UP TO xx ROWS不能像CURSOR那样能够在WHILE循环里面反复执行，没有一个cursor的机制记住当前正在操作的record在result set里的position。