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 DEFINITIONPUBLICFINALCREATE 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_opensqlIMPORTINGVALUE(p_clnt)  TYPE s_mandtVALUE(p_vbeln) TYPE vbelnVALUE(p_posnr) TYPE posnrVALUE(p_matnr) TYPE matnrEXPORTINGVALUE(et_vbap) TYPE tt_vbap."AMDP方式1： AMDP PROCEDURE实现，直接写SQLScript从HDB获取数据 "" 也可直接调用已创建的数据库对象，如存储过程、视图、函数等 "" 该方法可以直接被ABAP程序调用，必须设置为PUBLIC "CLASS-METHODS get_vbapIMPORTINGVALUE(p_clnt)  TYPE s_mandtVALUE(p_vbeln) TYPE vbelnVALUE(p_posnr) TYPE posnrVALUE(p_matnr) TYPE matnrEXPORTINGVALUE(et_vbap) TYPE tt_vbap."AMDP方式2-1-1： AMDP function实现，直接写SQLScript从HDB获取数据 "" 该方法不能直接被ABAP程序调用，只能在AMDP PROCEDURE实现中调用，建议可设置为 PRIVATE "CLASS-METHODS get_vbap_funcIMPORTINGVALUE(p_clnt)  TYPE s_mandt
*        VALUE(p_vbeln) TYPE vbeln
*        VALUE(p_posnr) TYPE posnr
*        VALUE(p_matnr) TYPE matnrRETURNINGVALUE(et_vbap) TYPE tt_vbap."AMDP方式2-1-2： AMDP procedure实现，调用上述的 AMDP function实现 "CLASS-METHODS get_vbap_by_funcIMPORTINGVALUE(p_clnt)  TYPE s_mandt
*        VALUE(p_vbeln) TYPE vbeln
*        VALUE(p_posnr) TYPE posnr
*        VALUE(p_matnr) TYPE matnrEXPORTINGVALUE(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,lgortFROM vbapWHERE ( 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_vbapBY DATABASE PROCEDURE FOR HDBLANGUAGE SQLSCRIPTOPTIONS READ-ONLYUSING vbap.et_vbap =selectmandt,vbeln,posnr,matnr,werks,lgortFROM vbapWHERE mandt = :p_clnt;ENDMETHOD.METHOD get_vbap_funcBY DATABASE FUNCTION FOR HDBLANGUAGE SQLSCRIPTOPTIONS READ-ONLYUSING vbap.RETURNSELECTmandt,vbeln,posnr,matnr,werks,lgortFROM vbapWHERE mandt = :p_clnt;ENDMETHOD.METHOD get_vbap_by_funcBY DATABASE PROCEDURE FOR HDBLANGUAGE SQLSCRIPTOPTIONS READ-ONLYUSING 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_CDSBY DATABASE FUNCTION FOR HDBLANGUAGE SQLSCRIPTOPTIONS READ-ONLYUSING vbap.RETURNSELECTmandt,vbeln,posnr,matnr,werks,lgortFROM vbapWHERE 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: #CLIENTp_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_opensqlEXPORTINGp_clnt  = sy-mandtp_vbeln = p_vbelnp_posnr = p_posnrp_matnr = p_matnrIMPORTINGet_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,lgortFROM vbapWHERE ( 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_vbapEXPORTINGp_clnt  = sy-mandtp_vbeln = p_vbelnp_posnr = p_posnrp_matnr = p_matnrIMPORTINGet_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_funcEXPORTINGp_clnt  = sy-mandtIMPORTINGet_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_VBAPINTO 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 ZSD0002INTO 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的，当然可能也是因为我只是做的普通的取数，没有复杂的计算啥的。有空了，大家可以做深入的研究