分頁查詢是一個常見需求,特别是在web相關的開發當中。
讓人意外的是,google搜尋abap paging query,查到的結果似乎都指出需要使用native SQL來實作相關功能;使用百度搜尋 abap分頁查詢,不僅查不到解決方案,甚至可以看到有人提出分頁查詢非常影響效率,不應使用。我想這是沒有道理的。經過對文檔的查詢,我發現Open SQL擁有這方面的能力。
本文連結:http://www.cnblogs.com/hhelibeb/p/8991141.html
原創内容,轉載請注明。
OFFSET 關鍵字實作
從ABAP 7.51開始,Open SQL中引入了關鍵字OFFSET,可以指定查詢的開始位置。以下這是官方文檔中的一個小例子,通過UP TO n ROWS指定一次查詢的條目數,通過OFFSET指定開始行:
SELECT - UP TO, OFFSET:
REPORT demo_select_up_to_offset.
CLASS demo DEFINITION.
PUBLIC SECTION.
CLASS-METHODS
main.
PRIVATE SECTION.
CLASS-DATA:
o TYPE int8,
n TYPE int8.
CLASS-METHODS
setup.
ENDCLASS.
CLASS demo IMPLEMENTATION.
METHOD main.
setup( ).
SELECT FROM demo_expressions
FIELDS id, num1 AS number, numlong1 AS result
ORDER BY id, num1
INTO TABLE @DATA(itab)
OFFSET @o
UP TO @n ROWS.
cl_demo_output=>display( itab ).
DELETE FROM demo_expressions.
ENDMETHOD.
METHOD setup.
cl_demo_input=>new(
)->add_field( CHANGING field = o
)->request( CHANGING field = n ).
IF NOT ( ( o BETWEEN 0 AND 2147483646 ) AND
( n BETWEEN 0 AND 2147483646 ) ).
cl_demo_output=>display(
`Input not in allowed interval!` ).
LEAVE PROGRAM.
ENDIF.
DELETE FROM demo_expressions.
DO strlen( sy-abcde ) TIMES.
INSERT demo_expressions FROM @(
VALUE #( id = substring( val = sy-abcde
off = sy-index - 1
len = 1 )
num1 = sy-index
numlong1 = ipow( base = 2 exp = sy-index ) ) ).
ENDDO.
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
demo=>main( ).
運作這段程式,會出現一個彈窗,其中兩個輸入框,分别是查詢的開始位置和結果數量,讓我們分别輸入3,10:
運作程式,如圖,我們得到了ID為4~13的條目:
按照這個思路,隻要每次查詢不同區間的條目,就可以實作分頁查詢了:
SELECT * FROM sflight ORDER BY carrid, connid, fldate
INTO TABLE @lt_sflight
UP TO 10 ROWS OFFSET 0.
SELECT * FROM sflight ORDER BY carrid, connid, fldate
INTO TABLE @lt_sflight
UP TO 10 ROWS OFFSET 11.
………………
CONCAT 函數實作
如果可以将把資料庫表中的内容按鍵排序,那麼,首先擷取鍵最小的10行,擷取到結果集中鍵的值最大的條目,再繼續查詢鍵比該條目更大的10行...依次進行下去,也可以實作分頁。
(Open SQL中的CONCAT函數在ABAP 7.50和更高版本可用)
REPORT ztest_paging.
CLASS lcl_paging DEFINITION.
PUBLIC SECTION.
TYPES: ty_sflight_t TYPE STANDARD TABLE OF sflight.
METHODS: main.
PRIVATE SECTION.
CLASS-DATA: rows TYPE int8.
METHODS: setup,
get_max_key
IMPORTING
it_sflight TYPE ty_sflight_t
RETURNING
VALUE(r_result) TYPE string,
get_result IMPORTING i_rows TYPE int8.
ENDCLASS.
CLASS lcl_paging IMPLEMENTATION.
METHOD setup.
cl_demo_input=>new(
)->request( CHANGING field = rows ).
IF NOT ( rows BETWEEN 0 AND 2147483646 ).
cl_demo_output=>display(
`Input not in allowed interval!` ).
LEAVE PROGRAM.
ENDIF.
ENDMETHOD.
METHOD get_result.
DATA: lt_sflight TYPE ty_sflight_t.
DATA: l_index TYPE i VALUE 1.
cl_demo_output=>next_section( |{ l_index }| ).
SELECT * FROM sflight ORDER BY carrid, connid, fldate
INTO TABLE @lt_sflight
UP TO @i_rows ROWS.
cl_demo_output=>write( lt_sflight ).
DATA(l_key) = get_max_key( lt_sflight ).
DO 50 TIMES.
l_index = l_index + 1.
SELECT * FROM sflight
WHERE concat( concat( carrid, connid ), fldate ) > @l_key
ORDER BY carrid, connid, fldate
INTO TABLE @lt_sflight
UP TO @i_rows ROWS .
IF sy-subrc <> 0 .
EXIT.
ENDIF.
cl_demo_output=>next_section( |{ l_index }| ).
cl_demo_output=>write( lt_sflight ).
l_key = get_max_key( lt_sflight ).
ENDDO.
cl_demo_output=>display( ).
ENDMETHOD.
METHOD get_max_key.
DATA(ls_last_row) = VALUE #( it_sflight[ lines( it_sflight ) ] OPTIONAL ).
r_result = |{ ls_last_row-carrid }{ ls_last_row-connid }{ ls_last_row-fldate }|.
ENDMETHOD.
METHOD main.
setup( ).
get_result( rows ).
ENDMETHOD.
ENDCLASS.
INITIALIZATION.
NEW lcl_paging( )->main( ).
運作程式,可以看到輸入框,輸入每頁行數為10,回車運作:
可以發現結果已經按鍵排序,分成了每10條資料一組:
更多參考:SAP UI 搜尋分頁技術