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。