ABAP AMDP: Database Procedures für SAP HANA

CLASS zcl_amdp_example DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_result,
             field1 TYPE string,
             field2 TYPE i,
           END OF ty_result,
           ty_results TYPE STANDARD TABLE OF ty_result WITH EMPTY KEY.

    CLASS-METHODS: get_data
      IMPORTING VALUE(iv_param) TYPE string
      EXPORTING VALUE(et_result) TYPE ty_results.
ENDCLASS.

CLASS zcl_amdp_example IMPLEMENTATION.
  METHOD get_data BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ztable.

    et_result = SELECT field1, field2
                FROM ztable
                WHERE field1 = :iv_param;
  ENDMETHOD.
ENDCLASS.

CLASS zcl_customer_amdp DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_customer,
             kunnr TYPE kunnr,
             name1 TYPE name1_gp,
             ort01 TYPE ort01_gp,
             land1 TYPE land1_gp,
           END OF ty_customer,
           ty_customers TYPE STANDARD TABLE OF ty_customer WITH EMPTY KEY.

    CLASS-METHODS: get_customers_by_country
      IMPORTING VALUE(iv_country) TYPE land1_gp
      EXPORTING VALUE(et_customers) TYPE ty_customers.
ENDCLASS.

CLASS zcl_customer_amdp IMPLEMENTATION.
  METHOD get_customers_by_country BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING kna1.

    et_customers = SELECT kunnr, name1, ort01, land1
                   FROM kna1
                   WHERE land1 = :iv_country;
  ENDMETHOD.
ENDCLASS.


" Aufruf
DATA: lt_customers TYPE zcl_customer_amdp=>ty_customers.

zcl_customer_amdp=>get_customers_by_country(
  EXPORTING iv_country = 'DE'
  IMPORTING et_customers = lt_customers
).

LOOP AT lt_customers INTO DATA(ls_cust).
  WRITE: / ls_cust-kunnr, ls_cust-name1, ls_cust-ort01.
ENDLOOP.

CLASS zcl_order_amdp DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_order_detail,
             vbeln     TYPE vbeln_va,
             posnr     TYPE posnr_va,
             matnr     TYPE matnr,
             kwmeng    TYPE kwmeng,
             netwr     TYPE netwr,
             kunnr     TYPE kunnr,
             name1     TYPE name1_gp,
           END OF ty_order_detail,
           ty_order_details TYPE STANDARD TABLE OF ty_order_detail WITH EMPTY KEY.

    CLASS-METHODS: get_order_details
      IMPORTING VALUE(iv_vbeln) TYPE vbeln_va
      EXPORTING VALUE(et_details) TYPE ty_order_details.
ENDCLASS.

CLASS zcl_order_amdp IMPLEMENTATION.
  METHOD get_order_details BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak vbap kna1.

    et_details = SELECT p.vbeln,
                        p.posnr,
                        p.matnr,
                        p.kwmeng,
                        p.netwr,
                        h.kunnr,
                        c.name1
                 FROM vbap AS p
                 INNER JOIN vbak AS h ON p.vbeln = h.vbeln
                 INNER JOIN kna1 AS c ON h.kunnr = c.kunnr
                 WHERE p.vbeln = :iv_vbeln;
  ENDMETHOD.
ENDCLASS.

CLASS zcl_sales_amdp DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_sales_summary,
             kunnr       TYPE kunnr,
             name1       TYPE name1_gp,
             order_count TYPE i,
             total_value TYPE netwr,
           END OF ty_sales_summary,
           ty_sales_summaries TYPE STANDARD TABLE OF ty_sales_summary WITH EMPTY KEY.

    CLASS-METHODS: get_sales_summary
      IMPORTING VALUE(iv_year) TYPE gjahr
      EXPORTING VALUE(et_summary) TYPE ty_sales_summaries.
ENDCLASS.

CLASS zcl_sales_amdp IMPLEMENTATION.
  METHOD get_sales_summary BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak kna1.

    -- Lokale Variable für Datumsbereich
    DECLARE lv_from_date DATE;
    DECLARE lv_to_date DATE;

    lv_from_date = CONCAT(:iv_year, '0101');
    lv_to_date = CONCAT(:iv_year, '1231');

    -- Aggregierte Abfrage
    et_summary = SELECT k.kunnr,
                        k.name1,
                        COUNT(*) AS order_count,
                        SUM(v.netwr) AS total_value
                 FROM vbak AS v
                 INNER JOIN kna1 AS k ON v.kunnr = k.kunnr
                 WHERE v.erdat BETWEEN :lv_from_date AND :lv_to_date
                 GROUP BY k.kunnr, k.name1
                 ORDER BY total_value DESC;
  ENDMETHOD.
ENDCLASS.

METHOD calculate_discount BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zcustomers.

  et_result = SELECT kunnr,
                     name1,
                     total_sales,
                     CASE
                       WHEN total_sales >= 100000 THEN 15
                       WHEN total_sales >= 50000 THEN 10
                       WHEN total_sales >= 10000 THEN 5
                       ELSE 0
                     END AS discount_percent
              FROM zcustomers;
ENDMETHOD.

METHOD process_data BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING ztable.

  DECLARE lv_counter INTEGER;
  DECLARE lv_max INTEGER;

  -- Daten laden
  lt_data = SELECT * FROM ztable;

  -- Zähler initialisieren
  lv_counter = 1;
  SELECT COUNT(*) INTO lv_max FROM :lt_data;

  -- Schleife
  WHILE lv_counter <= lv_max DO
    -- Verarbeitung
    lv_counter = lv_counter + 1;
  END WHILE;

  et_result = SELECT * FROM :lt_data;
ENDMETHOD.

METHOD complex_calculation BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING vbak vbap.

  -- Erste temporäre Tabelle
  lt_headers = SELECT vbeln, kunnr, erdat, netwr
               FROM vbak
               WHERE erdat >= ADD_DAYS(CURRENT_DATE, -365);

  -- Zweite temporäre Tabelle basierend auf erster
  lt_items = SELECT p.*
             FROM vbap AS p
             INNER JOIN :lt_headers AS h ON p.vbeln = h.vbeln;

  -- Aggregation
  lt_summary = SELECT h.kunnr,
                      COUNT(DISTINCT h.vbeln) AS order_count,
                      SUM(i.netwr) AS item_total
               FROM :lt_headers AS h
               INNER JOIN :lt_items AS i ON h.vbeln = i.vbeln
               GROUP BY h.kunnr;

  -- Ergebnis
  et_result = SELECT * FROM :lt_summary;
ENDMETHOD.

" CDS View Definition
@EndUserText.label: 'Sales by Customer (Table Function)'
define table function ZTF_SALES_BY_CUSTOMER
  with parameters
    @Environment.systemField: #SYSTEM_DATE
    p_date : abap.dats
  returns {
    key kunnr : abap.char(10);
    name1     : abap.char(35);
    total     : abap.dec(15,2);
  }
  implemented by method zcl_tf_sales=>get_sales;


" ABAP Implementierung
CLASS zcl_tf_sales DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    CLASS-METHODS: get_sales
      FOR TABLE FUNCTION ztf_sales_by_customer.
ENDCLASS.

CLASS zcl_tf_sales IMPLEMENTATION.
  METHOD get_sales BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak kna1.

    RETURN SELECT k.kunnr,
                  k.name1,
                  SUM(v.netwr) AS total
           FROM vbak AS v
           INNER JOIN kna1 AS k ON v.kunnr = k.kunnr
           WHERE v.erdat <= :p_date
           GROUP BY k.kunnr, k.name1;
  ENDMETHOD.
ENDCLASS.


" Verwendung in ABAP
SELECT * FROM ztf_sales_by_customer( p_date = @sy-datum )
  INTO TABLE @DATA(lt_sales).

METHOD process_with_error BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING ztable.

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Fehlerbehandlung
    et_errors = SELECT ::SQL_ERROR_CODE AS error_code,
                       ::SQL_ERROR_MESSAGE AS error_message
                FROM DUMMY;
  END;

  -- Normale Verarbeitung
  et_result = SELECT * FROM ztable;
ENDMETHOD.

CLASS zcl_amdp_chain DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    CLASS-METHODS: helper_method
      IMPORTING VALUE(iv_input) TYPE string
      EXPORTING VALUE(et_result) TYPE ty_results.

    CLASS-METHODS: main_method
      EXPORTING VALUE(et_final) TYPE ty_finals.
ENDCLASS.

CLASS zcl_amdp_chain IMPLEMENTATION.
  METHOD helper_method BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ztable.

    et_result = SELECT * FROM ztable WHERE field = :iv_input;
  ENDMETHOD.

  METHOD main_method BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING zcl_amdp_chain=>helper_method.

    -- Andere AMDP aufrufen
    CALL "ZCL_AMDP_CHAIN=>HELPER_METHOD"(
      iv_input => 'VALUE',
      et_result => lt_temp
    );

    et_final = SELECT * FROM :lt_temp;
  ENDMETHOD.
ENDCLASS.

" === Open SQL (Standard) ===
" Daten werden vom DB-Server zum App-Server übertragen
SELECT * FROM vbak INTO TABLE @DATA(lt_orders).

LOOP AT lt_orders INTO DATA(ls_order).
  " Verarbeitung auf App-Server
  IF ls_order-netwr > 10000.
    " ...
  ENDIF.
ENDLOOP.


" === AMDP (optimiert) ===
" Verarbeitung direkt auf der Datenbank
zcl_order_amdp=>get_high_value_orders(
  EXPORTING iv_min_value = 10000
  IMPORTING et_orders = lt_high_value_orders
).
" Nur relevante Daten werden übertragen

METHOD get_ranked_sales BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING vbak.

  et_result = SELECT kunnr,
                     vbeln,
                     netwr,
                     ROW_NUMBER() OVER (
                       PARTITION BY kunnr
                       ORDER BY netwr DESC
                     ) AS rank_per_customer,
                     SUM(netwr) OVER (
                       PARTITION BY kunnr
                     ) AS total_per_customer
              FROM vbak;
ENDMETHOD.

METHOD resolve_hierarchy BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zorg_units.

  -- Rekursive Hierarchie-Auflösung mit HANA
  et_hierarchy = SELECT node_id,
                        parent_id,
                        name,
                        HIERARCHY_LEVEL AS level
                 FROM HIERARCHY (
                   SOURCE ( SELECT * FROM zorg_units )
                   START WHERE parent_id IS NULL
                   NO ORPHANS
                 );
ENDMETHOD.

-- Variablen
DECLARE lv_var INTEGER;
DECLARE lv_text NVARCHAR(100);

-- Zuweisung
lv_var = 42;
lv_text = 'Hello';

-- Tabellenvariable
lt_data = SELECT * FROM table;

-- IF-Bedingung
IF :lv_var > 10 THEN
  -- ...
ELSEIF :lv_var > 5 THEN
  -- ...
ELSE
  -- ...
END IF;

-- WHILE-Schleife
WHILE :lv_counter < 100 DO
  lv_counter = lv_counter + 1;
END WHILE;

-- FOR-Schleife
FOR i IN 1..10 DO
  -- ...
END FOR;

-- Cursor
FOR row AS cursor_name DO
  -- row.field
END FOR;