ABAP CALL DB Procedure demo

CLASS zcl_demo_procedure01 DEFINITION  PUBLIC  FINAL  CREATE PUBLIC .  PUBLIC SECTION.    INTERFACES if_amdp_marker_hdb.    TYPES:      BEGIN OF ty_matnr,        matnr TYPE matnr,      END OF ty_matnr .    TYPES:      BEGIN OF ty_material,        matnr TYPE matnr,        maktx TYPE maktx,      END OF   ty_material .    TYPES:      tt_material TYPE STANDARD TABLE OF ty_material .    TYPES:      tt_matnr    TYPE STANDARD TABLE OF ty_matnr .    METHODS constructor      IMPORTING        VALUE(isnew) TYPE flag .    METHODS query      IMPORTING        VALUE(s_matnr)   TYPE shp_matnr_range_t OPTIONAL      EXPORTING        VALUE(it_result) TYPE tt_material .    METHODS admp      IMPORTING        VALUE(s_matnr)   TYPE shp_matnr_range_t OPTIONAL      EXPORTING        VALUE(it_result) TYPE tt_material .    METHODS cdbp      IMPORTING        VALUE(s_matnr)   TYPE shp_matnr_range_t OPTIONAL      EXPORTING        VALUE(it_result) TYPE tt_material .  PROTECTED SECTION.  PRIVATE SECTION.    DATA r_matnr TYPE tt_matnr .    DATA client TYPE mandt .    DATA api TYPE REF TO if_dbproc_proxy_public_api .    METHODS get_data      IMPORTING        VALUE(r_matnr)   TYPE tt_matnr        VALUE(clt)       TYPE mandt      EXPORTING        VALUE(it_result) TYPE tt_material .    METHODS setup .    METHODS setup_proxy .    METHODS admp_meth      IMPORTING        VALUE(r_matnr)   TYPE tt_matnr        VALUE(clt)       TYPE syst_mandt      EXPORTING        VALUE(it_result) TYPE tt_material .    METHODS cdbp_meth      IMPORTING        VALUE(r_matnr)   TYPE tt_matnr        VALUE(clt)       TYPE syst_mandt      EXPORTING        VALUE(it_result) TYPE tt_material .ENDCLASS.CLASS zcl_demo_procedure01 IMPLEMENTATION.  METHOD admp.  "ABAP manage db procedure    IF s_matnr[] IS NOT INITIAL.      SELECT matnr FROM mara INTO TABLE r_matnr WHERE matnr IN s_matnr.      SORT r_matnr BY matnr.    ENDIF.    "call db procedure    me->admp_meth( EXPORTING r_matnr = r_matnr clt = client IMPORTING it_result = it_result ).  ENDMETHOD.  METHOD admp_meth BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.    --"db_schema"."[package.]procedure_name"    call "_SYS_BIC"."ZDBP_DEMO01"(        r_matnr   => :r_matnr,        clt       => :clt,        it_result => :it_result );  ENDMETHOD.  METHOD cdbp.  "Call database procedure    IF s_matnr[] IS NOT INITIAL.      SELECT matnr FROM mara INTO TABLE r_matnr WHERE matnr IN s_matnr.      SORT r_matnr BY matnr.    ENDIF.    "call    me->cdbp_meth( EXPORTING r_matnr = r_matnr clt = client IMPORTING it_result = it_result ).  ENDMETHOD.  METHOD cdbp_meth.    "proxy    "--Specified directly and statically as proxy.    "(proxy_name)    CALL DATABASE PROCEDURE ('ZDBP_DEMO01_PROXY')      EXPORTING r_matnr     = r_matnr                clt         = client      IMPORTING it_result   = it_result.  ENDMETHOD.  METHOD constructor.    client = sy-mandt.    CHECK isnew = 'X'.    "create procedure    me->setup( ).  ENDMETHOD.  METHOD get_data BY DATABASE PROCEDURE                             FOR HDB LANGUAGE SQLSCRIPT USING mara makt.    --sql    IT_RESULT = SELECT A.MATNR,T.MAKTX                      FROM MARA AS A LEFT JOIN MAKT AS T ON A.MANDT = T.MANDT AND A.MATNR = T.MATNR AND T.SPRAS = '1'                      WHERE A.MANDT = :CLT AND A.MATNR in ( SELECT MATNR FROM :R_MATNR );  ENDMETHOD.  METHOD query.    IF s_matnr[] IS NOT INITIAL.      SELECT matnr FROM mara INTO TABLE r_matnr WHERE matnr IN s_matnr.      SORT r_matnr BY matnr.    ENDIF.    "execute sql    me->get_data( EXPORTING r_matnr = r_matnr clt = client IMPORTING it_result = it_result ).  ENDMETHOD.  METHOD setup.    "Create a table type and a database procedure on HANA    "Normally, those would be available statically    DATA(sql) = NEW cl_sql_statement( ).    DATA str TYPE string.    TRY.        sql->execute_ddl(`drop procedure "_SYS_BIC"."ZDBP_DEMO01"` ).      CATCH cx_sql_exception ##no_handler.    ENDTRY.    TRY.        sql->execute_ddl('drop type "_SYS_BIC"."ZDBP_MATNR_RANGE"' ).      CATCH cx_sql_exception ##no_handler.    ENDTRY.    TRY.        sql->execute_ddl('drop type "_SYS_BIC"."ZDBP_DEMO01_RESULT"' ).      CATCH cx_sql_exception ##no_handler.    ENDTRY.    TRY.        sql->execute_ddl(          | create type "_SYS_BIC"."ZDBP_DEMO01_RESULT" as table  | &&          | ( MATNR  NVARCHAR(40),                     | &&          |   MAKTX  NVARCHAR(40) )                    | ).        str = | create type "_SYS_BIC"."ZDBP_MATNR_RANGE" as table  |  &&              | ( MATNR  NVARCHAR(40) )                  | .        sql->execute_ddl( str ).        str =          | create procedure "_SYS_BIC"."ZDBP_DEMO01"                           | &&          |  ( in R_MATNR ZDBP_MATNR_RANGE,                                     | &&          |    in CLT NVARCHAR(3),                                              | &&          |    out IT_RESULT ZDBP_DEMO01_RESULT ) language sqlscript as         | &&          |      begin                                                          | &&          |        IT_RESULT =                                                  | &&          |           SELECT A.MATNR,T.MAKTX                                    | &&          |           FROM MARA AS A LEFT JOIN MAKT AS T                        | &&          |                        ON A.MANDT = T.MANDT                         | &&          |                       AND A.MATNR = T.MATNR AND T.SPRAS = '1'       | &&          |           WHERE A.MANDT = :CLT                                      | &&          |             AND A.MATNR IN ( SELECT MATNR FROM :R_MATNR );          | &&          |      end                                                            |.        sql->execute_ddl( str ).      CATCH cx_sql_exception INTO DATA(err).        MESSAGE i001(00) WITH err->if_message~get_text(  ) DISPLAY LIKE 'E'.        LEAVE PROGRAM.    ENDTRY.    me->setup_proxy(  ).  ENDMETHOD.  METHOD setup_proxy.    "Create a database procedure proxy and its interface    "Normally, those would be prepared in a framework or in ADT    DATA db_schema TYPE if_dbproc_proxy_basic_types=>ty_db_name.    "CALL FUNCTION 'DB_DBSCHEMA_CURRENT'    "  IMPORTING    "    dbschema = db_schema.    db_schema = '_SYS_BIC'.    "parameters override    DATA(params) =         VALUE if_dbproc_proxy_basic_types=>ty_param_override_t(         ( db_name   = 'R_MATNR'           abap_name = 'R_MATNR'           descr     = cl_abap_typedescr=>describe_by_name(                       'ZDBP_DEMO02' ) ) ).    "create proxy    TRY.        api = cl_dbproc_proxy_factory=>get_proxy_public_api(         if_proxy_name = `ZDBP_DEMO01_PROXY` ).        api->delete( ).        api = cl_dbproc_proxy_factory=>get_proxy_public_api(          if_proxy_name = `ZDBP_DEMO01_PROXY` ).        api->create_proxy(          EXPORTING            if_proc_schema    = db_schema            it_param_override = params            if_proc_name      = 'ZDBP_DEMO01' ).        CALL FUNCTION 'DB_COMMIT'.      CATCH cx_dbproc_proxy INTO DATA(err).        MESSAGE i001(00) WITH err->if_message~get_text(  ).        LEAVE PROGRAM.    ENDTRY.  ENDMETHOD.ENDCLASS.

测试类

*"* use this source file for your ABAP unit test classesCLASS test_demo DEFINITION FOR TESTING DURATION MEDIUM  RISK LEVEL HARMLESS FINAL.  PUBLIC SECTION.  PRIVATE SECTION.    METHODS test FOR TESTING.ENDCLASS.CLASS test_demo IMPLEMENTATION.  METHOD test.    IF cl_db_sys=>is_in_memory_db = abap_false.      RETURN.    ENDIF.    DATA s_matnr TYPE RANGE OF mara-matnr.    APPEND VALUE #( sign = 'I' option = 'CP' low = '*') TO s_matnr.    DATA(lr_test) = NEW zcl_demo_procedure01('').    lr_test->query( EXPORTING s_matnr  = s_matnr[] IMPORTING it_result = DATA(osql_result) ).    lr_test->cdbp( EXPORTING s_matnr   = s_matnr[] IMPORTING it_result = DATA(cdbp_result) ).    lr_test->admp( EXPORTING s_matnr   = s_matnr[] IMPORTING it_result = DATA(admp_result) ).    IF NOT (       osql_result = cdbp_result AND       osql_result = admp_result ).      cl_aunit_assert=>fail(          msg = 'Wrong results'         level = cl_aunit_assert=>tolerable ).    ENDIF.  ENDMETHOD.ENDCLASS.