ABAP Open SQL 分页查询

分页查询是一个常见需求,特别是在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:

ABAP Open SQL 分页查询

运行程序,如图,我们得到了ID为4~13的条目:

ABAP Open SQL 分页查询

按照这个思路,只要每次查询不同区间的条目,就可以实现分页查询了:

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

    cl_demo_output=>next_section( |{ l_index + 1 }| ).

    SELECT * FROM sflight ORDER BY carrid, connid, fldate
      INTO TABLE @lt_sflight
      UP TO @i_rows ROWS OFFSET @l_index.

    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 + 1 }| ).
      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,回车运行:

ABAP Open SQL 分页查询

可以发现结果已经按键排序,分成了每10条数据一组:

ABAP Open SQL 分页查询

相关推荐