ABAP CALL DB Procedure demo

最新推荐文章于 2023-11-17 11:13:35 发布

wycsnx

最新推荐文章于 2023-11-17 11:13:35 发布

阅读量1.1k

点赞数 1

分类专栏： S4HANA 文章标签： ABAP

本文链接：https://blog.csdn.net/wycsnx/article/details/76919091

版权

S4HANA 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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 classes
CLASS 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.