ABAP AMDP 示例

AMDP 是HANA开发中的一种优化模式 

按SAP的官方建议，在可以使用Open SQL实现需要的功能或优化目标的时候，不建议使用AMDP。而在需要使用Open SQL不支持的特性，或者是大量处理流和分析导致了数据库和应用服务器之间有重复的大量数据传输的情况下，则应当使用AMDP。

下面我举例几种AMDP的实现方法，并且比较一下效率

示例代码如下

"! <p class="shorttext synchronized" lang="en">demo for select for vbap</p>
CLASS zcl_amdp_vbap DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.

    INTERFACES if_amdp_marker_hdb .
    TYPES: BEGIN OF ty_vbap,
             mandt TYPE s_mandt,
             vbeln TYPE vbeln_va,
             posnr TYPE posnr_va,
             matnr TYPE matnr,
             werks TYPE werks_d,
             lgort TYPE lgort_d,
           END OF ty_vbap.
    TYPES: tt_vbap TYPE STANDARD TABLE OF ty_vbap WITH EMPTY KEY.
    DATA: gt_vbap TYPE STANDARD TABLE OF ty_vbap.

    " 常规方法：Open SQL "
    CLASS-METHODS get_vbap_by_opensql
      IMPORTING
        VALUE(p_clnt)  TYPE s_mandt
        VALUE(p_vbeln) TYPE vbeln
        VALUE(p_posnr) TYPE posnr
        VALUE(p_matnr) TYPE matnr
      EXPORTING
        VALUE(et_vbap) TYPE tt_vbap.

    "AMDP方式1： AMDP PROCEDURE实现，直接写SQLScript从HDB获取数据 "
    " 也可直接调用已创建的数据库对象，如存储过程、视图、函数等 "
    " 该方法可以直接被ABAP程序调用，必须设置为PUBLIC "
    CLASS-METHODS get_vbap
      IMPORTING
        VALUE(p_clnt)  TYPE s_mandt
        VALUE(p_vbeln) TYPE vbeln
        VALUE(p_posnr) TYPE posnr
        VALUE(p_matnr) TYPE matnr
      EXPORTING
        VALUE(et_vbap) TYPE tt_vbap.


    "AMDP方式2-1-1： AMDP function实现，直接写SQLScript从HDB获取数据 "
    " 该方法不能直接被ABAP程序调用，只能在AMDP PROCEDURE实现中调用，建议可设置为 PRIVATE "
    CLASS-METHODS get_vbap_func
      IMPORTING
        VALUE(p_clnt)  TYPE s_mandt
*        VALUE(p_vbeln) TYPE vbeln
*        VALUE(p_posnr) TYPE posnr
*        VALUE(p_matnr) TYPE matnr
      RETURNING
        VALUE(et_vbap) TYPE tt_vbap.

    "AMDP方式2-1-2： AMDP procedure实现，调用上述的 AMDP function实现 "
    CLASS-METHODS get_vbap_by_func
      IMPORTING
        VALUE(p_clnt)  TYPE s_mandt
*        VALUE(p_vbeln) TYPE vbeln
*        VALUE(p_posnr) TYPE posnr
*        VALUE(p_matnr) TYPE matnr
      EXPORTING
        VALUE(et_vbap) TYPE tt_vbap.

    "AMDP方式2-2-1： AMDP procedure实现，为 TABLE FUNCTION CDS编写具体实现逻辑 "
    " 该方法无法直接被ABAP程序调用，但是对应的CDS可以被ABAP调用。 "
    " 此处对应的CDS名为：YCDS_INVENTORY，需要另外单独定义 "
    " 定义为 TABLE FUNCTION CDS 时，方法必须为 PUBLIC CLASS-METHODS "
    CLASS-METHODS get_vbap_for_cds FOR TABLE FUNCTION zcds_admp_demo_vbap.

ENDCLASS.



CLASS zcl_amdp_vbap IMPLEMENTATION.

  METHOD get_vbap_by_opensql.
    SELECT mandt,
           vbeln,
           posnr,
           matnr,
           werks,
           lgort
    FROM vbap
    WHERE ( vbeln = @p_vbeln OR @p_vbeln  = '' )
       OR ( posnr = @p_posnr OR @p_posnr = '' )
       OR ( matnr = @p_matnr OR @p_matnr = '' )
    INTO CORRESPONDING FIELDS OF TABLE @et_vbap.
  ENDMETHOD.

  METHOD get_vbap
    BY DATABASE PROCEDURE FOR HDB
         LANGUAGE SQLSCRIPT
         OPTIONS READ-ONLY
         USING vbap.
    et_vbap =
      select
         mandt,
         vbeln,
         posnr,
         matnr,
         werks,
         lgort
      FROM vbap
      WHERE mandt = :p_clnt;
  ENDMETHOD.

  METHOD get_vbap_func
      BY DATABASE FUNCTION FOR HDB
         LANGUAGE SQLSCRIPT
         OPTIONS READ-ONLY
         USING vbap.
    RETURN
    SELECT
      mandt,
      vbeln,
      posnr,
      matnr,
      werks,
      lgort
   FROM vbap
   WHERE mandt = :p_clnt;
  ENDMETHOD.

  METHOD get_vbap_by_func
    BY DATABASE PROCEDURE FOR HDB
         LANGUAGE SQLSCRIPT
         OPTIONS READ-ONLY
         USING zcl_amdp_vbap=>get_vbap_func.
    et_vbap =
    select *
     from "ZCL_AMDP_VBAP=>GET_VBAP_FUNC"
     (p_clnt => :p_clnt);
  ENDMETHOD.

  METHOD GET_VBAP_FOR_CDS
       BY DATABASE FUNCTION FOR HDB
       LANGUAGE SQLSCRIPT
       OPTIONS READ-ONLY
       USING vbap.
    RETURN
    SELECT
      mandt,
      vbeln,
      posnr,
      matnr,
      werks,
      lgort
   FROM vbap
   WHERE mandt = :p_clnt;
  ENDMETHOD.


ENDCLASS.

 ZCDS_ADMP_DEMO_VBAP  table function

@EndUserText.label: 'ZCDS_ADMP_DEMO_VBAP'
define table function ZCDS_ADMP_DEMO_VBAP
with parameters     @Environment.systemField: #CLIENT
    p_clnt  : abap.clnt
returns {
  mandt     : abap.clnt;
  vbeln     : vbeln;
  posnr     : posnr;
  matnr     : matnr;
  werks     : werks_d;
  lgort     : lgort_d;
  
}
implemented by method zcl_amdp_vbap=>get_vbap_for_cds;

ZCDS_VIEW_VBAP CDS VIEW

@AbapCatalog.sqlViewName: 'ZSD0002'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ZCDS_VIEW_VBAP'
define view ZCDS_VIEW_VBAP as select from vbap
{
  vbap.mandt,
  vbap.vbeln,
  vbap.posnr,
  vbap.matnr,
  vbap.werks,
  vbap.lgort
  
}

程序执行代码：

*&---------------------------------------------------------------------*
*& Report z_test_amdp_vbap
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT z_test_amdp_vbap.
TYPES: BEGIN OF ty_vbap,
         mandt TYPE s_mandt,
         vbeln TYPE vbeln_va,
         posnr TYPE posnr_va,
         matnr TYPE matnr,
         werks TYPE werks_d,
         lgort TYPE lgort_d,
       END OF ty_vbap.
TYPES: tt_vbap TYPE STANDARD TABLE OF ty_vbap WITH EMPTY KEY.
DATA: gt_vbap TYPE STANDARD TABLE OF ty_vbap WITH EMPTY KEY .
DATA: p_vbeln TYPE vbeln.
DATA: p_posnr TYPE posnr.
DATA: p_matnr TYPE matnr.
DATA: lv_begin TYPE tzonref-tstampl.
DATA: lv_end   TYPE tzonref-tstampl.
DATA: lv_open  TYPE char100.
DATA: lv_abap  TYPE char100.
DATA: lv_proc  TYPE char100.
DATA: lv_func  TYPE char100.
DATA: lv_cds  TYPE char100.
DATA: lv_cds2  TYPE char100.

PARAMETERS: p_open TYPE char01 AS CHECKBOX DEFAULT 'x'.
PARAMETERS: p_abap TYPE char01 AS CHECKBOX DEFAULT 'x'.
PARAMETERS: p_proc TYPE char01 AS CHECKBOX DEFAULT 'x'.
PARAMETERS: p_func TYPE char01 AS CHECKBOX DEFAULT 'x'.
PARAMETERS: p_cds TYPE char01 AS CHECKBOX DEFAULT 'x'.
PARAMETERS: p_cds2 TYPE char01 AS CHECKBOX DEFAULT 'x'.




START-OF-SELECTION.

  IF p_open IS NOT INITIAL .
    GET TIME STAMP FIELD lv_begin.
    CALL METHOD zcl_amdp_vbap=>get_vbap_by_opensql
      EXPORTING
        p_clnt  = sy-mandt
        p_vbeln = p_vbeln
        p_posnr = p_posnr
        p_matnr = p_matnr
      IMPORTING
        et_vbap = gt_vbap.
    GET TIME STAMP FIELD lv_end.

    lv_open = ( lv_end - lv_begin ) * 100000.
  ENDIF.

  IF p_abap IS NOT INITIAL .
    CLEAR:gt_vbap,lv_begin,lv_end.
    GET TIME STAMP FIELD lv_begin.
    SELECT mandt,
           vbeln,
           posnr,
           matnr,
           werks,
           lgort
    FROM vbap
    WHERE ( vbeln = @p_vbeln OR @p_vbeln  = '' )
       OR ( posnr = @p_posnr OR @p_posnr = '' )
       OR ( matnr = @p_matnr OR @p_matnr = '' )
    INTO CORRESPONDING FIELDS OF TABLE @gt_vbap.
    GET TIME STAMP FIELD lv_end.

    lv_abap = ( lv_end - lv_begin ) * 100000.
  ENDIF.

  IF p_proc IS NOT INITIAL .
    CLEAR:gt_vbap,lv_begin,lv_end.
    GET TIME STAMP FIELD lv_begin.
    CALL METHOD zcl_amdp_vbap=>get_vbap
      EXPORTING
        p_clnt  = sy-mandt
        p_vbeln = p_vbeln
        p_posnr = p_posnr
        p_matnr = p_matnr
      IMPORTING
        et_vbap = gt_vbap.
    GET TIME STAMP FIELD lv_end.

    lv_proc = ( lv_end - lv_begin ) * 100000.
  ENDIF.

  IF p_func IS NOT INITIAL .
    CLEAR:gt_vbap,lv_begin,lv_end.
    GET TIME STAMP FIELD lv_begin.
   CALL METHOD zcl_amdp_vbap=>get_vbap_by_func
     EXPORTING
       p_clnt  = sy-mandt
      IMPORTING
        et_vbap = gt_vbap.
       .

    GET TIME STAMP FIELD lv_end.

    lv_func = ( lv_end - lv_begin ) * 100000.
  ENDIF.

  IF p_cds IS NOT INITIAL .
    CLEAR:gt_vbap,lv_begin,lv_end.
    GET TIME STAMP FIELD lv_begin.


    SELECT * FROM ZCDS_ADMP_DEMO_VBAP
    INTO CORRESPONDING FIELDS OF TABLE @gt_vbap.


    GET TIME STAMP FIELD lv_end.

    lv_cds = ( lv_end - lv_begin ) * 100000.
  ENDIF.

IF p_cds2 IS NOT INITIAL .
    CLEAR:gt_vbap,lv_begin,lv_end.
    GET TIME STAMP FIELD lv_begin.


    SELECT * FROM ZSD0002
    INTO CORRESPONDING FIELDS OF TABLE @gt_vbap.


    GET TIME STAMP FIELD lv_end.

    lv_cds2 = ( lv_end - lv_begin ) * 100000.
ENDIF.


  WRITE 'open sql 执行时间：' &&  lv_open.
  WRITE '应用层 SQL 执行时间：' &&  lv_abap.
  WRITE 'amdp procedure 执行时间：' &&  lv_proc.
  WRITE 'amdp func 执行时间：' &&  lv_func.
  WRITE 'table function cds 执行时间：' &&  lv_cds.
  WRITE 'cds 执行时间：' &&  lv_cds2.

   执行结果如下：

这里我们用的VBAP中总共的条目是3,825

从执行结果上来看，可以看出CDS view 是 效率是优于 amdp的，当然可能也是因为我只是做的普通的取数，没有复杂的计算啥的。有空了，大家可以做深入的研究