SQLScript mit AMDP (ABAP)

SQLscript kann per AMDP in ABAP-Klassen mit ADT oder mit SE80 (nicht empfohlen) codiert werden.

Siehe auch: AMDP_(ABAP)

CLASS ZMO_CL_AMDP_01 DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    " Interface notwendig, um in Klasse AMDP nutzen zu können
    INTERFACES: IF_AMDP_MARKER_HDB.

    TYPES:
      BEGIN OF TY_DATECALC_RES,
        KEY_FIELD     TYPE N LENGTH 10,
        FROM_AMOUNT   TYPE P LENGTH 10 DECIMALS 2,
        FROM_CURRENCY TYPE C LENGTH 10,
        FROM_DATE     TYPE DATS,
        TO_AMOUNT     TYPE P LENGTH 10 DECIMALS 2,
        TO_DATE       TYPE DATS,
        TEXT          TYPE C LENGTH 255,
      END OF TY_DATECALC_RES.

    TYPES:
      TT_DATECALC_RES TYPE STANDARD TABLE OF TY_DATECALC_RES WITH EMPTY KEY,
      TV_CURR         TYPE C LENGTH 5.

    CLASS-METHODS EXCEPTION_HANDLING_AMDP_HDB
      IMPORTING
                VALUE(IV_SQL_OBJECT_SCHEMA) TYPE ZTW_CL_AMDP_01=>TV_SQL_OBJECT_SCHEMA
                VALUE(IV_SQL_OBJECT_NAME)   TYPE ZTW_CL_AMDP_01=>TV_SQL_OBJECT_NAME
                VALUE(IV_SQL_ERROR_CODE)    TYPE INTEGER
                VALUE(IV_SQL_ERROR_MESSAGE) TYPE ZTW_CL_AMDP_01=>TV_SQL_ERROR_MESSAGE
      CHANGING
                VALUE(CT_MESG)              TYPE TREXT_PE_MSG
      RAISING   CX_AMDP_ERROR.

    "!Aufgabe 1: Inhalt der Tabelle ZTW_AMDP_DEMO_01 zurückgeben
    "! Import- und Export-Parameter werden als VALUE übergeben
    "! Export-Parameter einen statischen Datentyp / Tabellentyp haben
    CLASS-METHODS METHOD_01_READ
      EXPORTING
        VALUE(ET_AMDP) TYPE ZTW_TT_AMDP_DEMO_01
      RAISING
        CX_AMDP_ERROR.


    "!Aufgabe 2: Inhalt der Tabelle ZTW_AMDP_DEMO_01 zurückgeben;
    "! Jeden Satz umgerechnen nach EUR;
    "! Neues Feld DATE_FIELD_NEW: Datum in DATE_FIELD um 30 Tage erhöhen
    "! Stichtag Umrechnung: 1.1. des Jahres aus DATE_FIELD
    "! Umrechnungskurs INVB
    CLASS-METHODS
      METHOD_02_SQLSCRIPT
        EXPORTING
          VALUE(ET_AMDP) TYPE TT_DATECALC_RES
        RAISING
          CX_AMDP_ERROR.


    "!Aufgabe 3: Inhalt der Tabelle ZTW_AMDP_DEMO_01 zurückgeben;
    "! Jeden Satz umrechnen nach iv_curr;
    "! Neues Feld DATE_FIELD_NEW: Datum in DATE_FIELD um iv_days Tage erhöhen
    "! Stichtag Umrechnung: 1.1. des Jahres aus DATE_FIELD
    "! Umrechnungskurs INVB
    CLASS-METHODS
      METHOD_03_IMPORT
        IMPORTING
          VALUE(IV_CURR) TYPE TV_CURR
          VALUE(IV_DAYS) TYPE I
        EXPORTING
          VALUE(ET_AMDP) TYPE TT_DATECALC_RES
          VALUE(ET_MESG) TYPE TREXT_PE_MSG
        RAISING
          CX_AMDP_ERROR.


    "!Aufgabe 5: Erhoehung von iv_days um 5, dann Aufruf der
    "! DB Prozedur ZMO_CL_AMDP_01=>method03_sqlscript_import
    "! mit iv_curr und iv_days und Rueckgabe der Ergebnisse
    CLASS-METHODS
      METHOD_05_USING
        IMPORTING
          VALUE(IV_CURR) TYPE TV_CURR
          VALUE(IV_DAYS) TYPE I
        EXPORTING
          VALUE(ET_AMDP) TYPE TT_DATECALC_RES
          VALUE(ET_MESG) TYPE TREXT_PE_MSG
        RAISING
          CX_AMDP_ERROR.


    "!Aufgabe 6: Filtere in der Rückgabe der Methode die
    "! Tabelle ZTW_AMDP_DEMO_01 dynamisch, je nach Input der Methode
    CLASS-METHODS
      METHOD_06_FILTER
        IMPORTING
          VALUE(IV_FILTER) TYPE STRING
        EXPORTING
          VALUE(ET_AMDP) TYPE ztw_tt_amdp_demo_01
          VALUE(ET_MESG) TYPE TREXT_PE_MSG
        RAISING
          CX_AMDP_ERROR.

    CLASS-METHODS: START.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS ZMO_CL_AMDP_01 IMPLEMENTATION.
  METHOD EXCEPTION_HANDLING_AMDP_HDB  BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

    /*
    credits @Leon Struski, PROCON IT
    */

    declare exit handler FOR SQLEXCEPTION
    BEGIN
      CT_MESG = SELECT
      'ZTW_MSG'                                 as "MSGID",
      '000'                                     as "MSGNO",
      'E'                                       as "MSGTY",
      'SQL Exception occured. In'               as "MSGV1",
      'Error Code is:'                          as "MSGV2",
      CAST (::SQL_ERROR_CODE   as NVARCHAR(50)) as "MSGV3",
      ''                                        as "MSGV4",
      ''                                        as "CONTEXT"
      FROM DUMMY
      union all
      SELECT
      'ZTW_MSG'                                                                                 as "MSGID",
      '000'                                                                                               as "MSGNO",
      'E'                                                                                                 as "MSGTY",
      CAST ( SUBSTRING(::CURRENT_OBJECT_SCHEMA || '.' ||::CURRENT_OBJECT_NAME, 1, 50)   as NVARCHAR(50))  as "MSGV1",
      CAST ( SUBSTRING(::CURRENT_OBJECT_SCHEMA || '.' ||::CURRENT_OBJECT_NAME, 51, 50)  as NVARCHAR(50))  as "MSGV2",
      CAST ( SUBSTRING(::CURRENT_OBJECT_SCHEMA || '.' ||::CURRENT_OBJECT_NAME, 101, 50) as NVARCHAR(50))  as "MSGV3",
      CAST ( SUBSTRING(::CURRENT_OBJECT_SCHEMA || '.' ||::CURRENT_OBJECT_NAME, 152, 50) as NVARCHAR(50))  as "MSGV4",
      ''                                                                                                  as "CONTEXT"
      FROM DUMMY
      union all
      SELECT
      'ZTW_MSG'                                               as "MSGID",
      '000'                                                             as "MSGNO",
      'E'                                                               as "MSGTY",
      CAST( SUBSTRING(::SQL_ERROR_MESSAGE , 1, 50)    as NVARCHAR(50))  as "MSGV1",
      CAST( SUBSTRING(::SQL_ERROR_MESSAGE , 51, 50)   as NVARCHAR(50))  as "MSGV2",
      CAST( SUBSTRING(::SQL_ERROR_MESSAGE , 101, 50)  as NVARCHAR(50))  as "MSGV3",
      CAST( SUBSTRING(::SQL_ERROR_MESSAGE , 153, 50)  as NVARCHAR(50))  as "MSGV4",
      ''                                                                as "CONTEXT"
      FROM DUMMY
      union all select * from :CT_MESG;
    END;

    CT_MESG = SELECT
    'ZTW_MSG'                         as "MSGID",
    '000'                                       as "MSGNO",
    'E'                                         as "MSGTY",
    'SQL Exception occured.'                    as "MSGV1",
    'Error Code is:'                            as "MSGV2",
    CAST ( iv_SQL_ERROR_CODE   as NVARCHAR(50)) as "MSGV3",
    ''                                          as "MSGV4",
    ''                                          as "CONTEXT"
    FROM DUMMY
    union all
    SELECT
    'ZTW_MSG'                                                                           as "MSGID",
    '000'                                                                                         as "MSGNO",
    'E'                                                                                           as "MSGTY",
    CAST ( SUBSTRING(IV_SQL_OBJECT_SCHEMA || '.' ||IV_SQL_OBJECT_NAME, 1, 50)   as NVARCHAR(50))  as "MSGV1",
    CAST ( SUBSTRING(IV_SQL_OBJECT_SCHEMA || '.' ||IV_SQL_OBJECT_NAME, 51, 50)  as NVARCHAR(50))  as "MSGV2",
    CAST ( SUBSTRING(IV_SQL_OBJECT_SCHEMA || '.' ||IV_SQL_OBJECT_NAME, 102, 50) as NVARCHAR(50))  as "MSGV3",
    CAST ( SUBSTRING(IV_SQL_OBJECT_SCHEMA || '.' ||IV_SQL_OBJECT_NAME, 153, 50) as NVARCHAR(50))  as "MSGV4",
    ''                                                                                            as "CONTEXT"
    FROM DUMMY
    union all
    SELECT
    'ZTW_MSG'                                               as "MSGID",
    '000'                                                             as "MSGNO",
    'E'                                                               as "MSGTY",
    CAST( SUBSTRING(iv_SQL_ERROR_MESSAGE , 1, 50)   as NVARCHAR(50))  as "MSGV1",
    CAST( SUBSTRING(iv_SQL_ERROR_MESSAGE , 51, 50)  as NVARCHAR(50))  as "MSGV2",
    CAST( SUBSTRING(iv_SQL_ERROR_MESSAGE , 101, 50) as NVARCHAR(50))  as "MSGV3",
    CAST( SUBSTRING(iv_SQL_ERROR_MESSAGE , 153, 50) as NVARCHAR(50))  as "MSGV4",
    ''                                                                as "CONTEXT"
    FROM DUMMY
    union all select * from :CT_MESG;
  ENDMETHOD.



  METHOD METHOD_01_READ BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING ZTW_AMDP_DEMO_01.
    et_amdp =
      select * from ZTW_AMDP_DEMO_01;

  ENDMETHOD.

  METHOD METHOD_02_SQLSCRIPT BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING ZTW_AMDP_DEMO_01.
    et_amdp =
      select
            key_field,
            amount as from_amount,
            currency as from_currency,
            date_field as from_date,
            CONVERT_CURRENCY(
                amount              => amount,
                source_unit_column  => currency,
                schema              => ::CURRENT_OBJECT_SCHEMA,
                target_unit_column  => 'EUR',
                steps               => 'shift,convert,shift_back',
                reference_date      => TO_DATE('2018-01-01'),
                client              => '010',
                conversion_type     => 'INVB'
              ) AS to_amount,
            to_dats(add_days(date_field, 30)) as to_date,
            text
        from ZTW_AMDP_DEMO_01;
  ENDMETHOD.

  METHOD METHOD_03_IMPORT BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING ZTW_AMDP_DEMO_01 ZMO_CL_AMDP_01=>EXCEPTION_HANDLING_AMDP_HDB.
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        call "ZMO_CL_AMDP_01=>EXCEPTION_HANDLING_AMDP_HDB"(
        iv_sql_object_schema  => ::CURRENT_OBJECT_SCHEMA,
        iv_sql_OBJECT_NAME    => ::CURRENT_OBJECT_NAME,
        iv_SQL_ERROR_CODE     => ::SQL_ERROR_CODE,
        iv_SQL_ERROR_MESSAGE  => ::SQL_ERROR_MESSAGE,
        ct_mesg__IN__         => :et_mesg,
        ct_mesg               => :et_mesg );
      END;

    et_amdp =
      select
            key_field,
            amount as from_amount,
            currency as from_currency,
            date_field as from_date,
            CONVERT_CURRENCY(
                amount              => amount,
                source_unit_column  => currency,
                schema              => ::CURRENT_OBJECT_SCHEMA,
                target_unit_column  => :iv_curr,
                steps               => 'shift,convert,shift_back',
                reference_date      => TO_DATE('2018-01-01'),
                client              => '010',
                conversion_type     => 'INVB'
              ) AS to_amount,
            to_dats(add_days(date_field, :iv_days)) as to_date,
            text
        from ZTW_AMDP_DEMO_01;
  ENDMETHOD.

  METHOD METHOD_05_USING BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING ZMO_CL_AMDP_01=>METHOD_03_IMPORT.
    iv_days = iv_days + 5;

    call "ZMO_CL_AMDP_01=>METHOD_03_IMPORT" (
      iv_curr => :iv_curr,
      iv_days => :iv_days,
      et_amdp => :et_amdp,
      et_mesg => :et_mesg
    );
  ENDMETHOD.

  METHOD METHOD_06_FILTER BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING ZTW_AMDP_DEMO_01 ZMO_CL_AMDP_01=>EXCEPTION_HANDLING_AMDP_HDB.
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        call "ZMO_CL_AMDP_01=>EXCEPTION_HANDLING_AMDP_HDB"(
        iv_sql_object_schema  => ::CURRENT_OBJECT_SCHEMA,
        iv_sql_OBJECT_NAME    => ::CURRENT_OBJECT_NAME,
        iv_SQL_ERROR_CODE     => ::SQL_ERROR_CODE,
        iv_SQL_ERROR_MESSAGE  => ::SQL_ERROR_MESSAGE,
        ct_mesg__IN__         => :et_mesg,
        ct_mesg               => :et_mesg );
      END;

    et_amdp = APPLY_FILTER (ZTW_AMDP_DEMO_01,:iv_filter);

  ENDMETHOD.

  METHOD START.
    "zmo_cl_amdp_01=>
  ENDMETHOD.
ENDCLASS.