Internal Table and Flow Control in AMDP

[Nihal__Raj]

Introduction:  AMDP or ABAP Managed Database Procedure, is a SAP technology that allows database specific procedure (like those written in SQL Script for SAP HANA) to be managed and executed directly from ABAP code. The primary aim of AMDP is to optimize performance by pushing complex data operations to the database layer.

Important Points:

• While adding interface IF_AMDP_MARKER_HDB. Remember, interfaces can only be added in PUBLIC SECTION.
• Only Variables and Tables are allowed as parameters. We cannot use structures or nested tables.
• Generic types cannot be used for parameters. For example, Type Any cannot be used. Only elementary data types and table types with a structured row type can be used.
• The table type components must be elementary data types and it cannot have elements which are table types.
• Only pass by value can be used. Pass by reference is not permitted. Using VALUE keyword for all parameters is required.
• RETURNING parameters are not allowed. We can use EXPORTING or CHANGING to receive the values.
• Only input parameters can be flagged as optional with a DEFAULT value (literals/constants)
• Every AMDP method will have below addition. READ-ONLY is only the optional addition and is used for methods that only read the data.
  o BY DATABASE PROCEDURE
  o FOR HDB
  o LANGUAGE SQLSCRIPT
  o OPTIONS READ-ONLY
  o USING table/view names
• It is also mandatory to specify all the database objects and other AMDP methods that are used within the SQLSCRIPT code.
• No ABAP statements can be written in the method code.
• AMDP methods do not have any implicit enhancement options.

Internal Table and Flow Control in AMDP

In AMDP (ABAP Managed Database Procedures), you can declare internal tables using SQL Script. These internal tables are used to store intermediate results and can be manipulated within your AMDP methods.

CLASS zrj_amdp_01 DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
  TYPES: BEGIN OF ty_spfli,
         mandt TYPE mandt,
         carrid TYPE s_carr_id,
         connid TYPE s_conn_id,
         countryfr TYPE land1,
         countryto TYPE land1,
         END OF ty_spfli.
   TYPES: tt_spfli TYPE TABLE of ty_spfli. 
  INTERFACES: if_amdp_marker_hdb.
  METHODS: get_spfli_details IMPORTING VALUE(iv_mandt) TYPE mandt
                             EXPORTING VALUE(et_spfli) TYPE tt_spfli.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zrj_amdp_01 IMPLEMENTATION.
METHOD get_spfli_details BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
         DECLARE lt_spfli TABLE ( mandt     "$ABAP.type( sy-mandt )",
                                  carrid    "$ABAP.type( S_CARR_ID )",
                                  connid    "$ABAP.type( S_CONN_ID )",
                                  countryfr "$ABAP.type( LAND1 )",
                                  countryto "$ABAP.type( LAND1 )" );
         lt_spfli.mandt[1] := '100';
         lt_spfli.carrid[1] := 'AA';
         lt_spfli.connid[1] := '1000';
         lt_spfli.countryfr[1] := 'DE';
         lt_spfli.countryto[1] := 'US';
         et_spfli = select * from :lt_spfli;
         
ENDMETHOD.
ENDCLASS.

Some Useful Internal Table with Operations:
RECORD_COUNT( ) Operator is used to get to know the no of lines in the internal table. So
it is likes LINES( ) / DESCRIBE table in ABAP.
SEARCH() can be applied on an internal table and it receives two arguments, the column
name and the value, if it finds then it returns the index of that record within the internal
table.
EXISTS() tells whether the record exists or not.
IS_EMPTY( ) can be used and we can pass the Internal table name as an argument to it and
then it checks the emptiness of the table , it is similar to IS INITIAL check in ABAP.
APPLY_FILTER( ) function can be used on internal table or DB tables to get all the items that
match the filter condition.
EXCEPT use is quite simple. It’s like a minus operation. The result of EXCEPT operation is the
record set that present in first and not present in the second.

Flow Control with IF and Loops
Using IF and ELSE
If syntax is similar to ABAP but uses additional THEN keyword.
Condition can have following

• EXISTS
• Comparison between variables
• IS [NOT] NULL
• IS_EMPTY
• IN

The conditions can be negated with NOT, combined with keywords like AND, OR and can be
nested.

CLASS zrj_amdp_02 IMPLEMENTATION.
METHOD if_statement BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING sflight.
  DECLARE lv_result INT;
  DECLARE lv_carrier CONSTANT NVARCHAR(2) := 'AA';
  DECLARE lv_carrier1 CONSTANT NVARCHAR(2) := 'BB';
***Example for EXISTS
  if EXISTS ( select carrid FROM sflight WHERE carrid = '1' )
          THEN lv_result = 1;
   else
               lv_result = 2;
   END if;

***comparision between variables
  if :lv_carrier = :lv_carrier1
           THEN lv_result = 1;
  end if;

*** IN
  if :lv_carrier1 IN( 'AA', 'AB', 'AZ' )
       THEN lv_result = 1;
       end if;

ENDMETHOD.
ENDCLASS.

For Loop

FOR <variable> IN [REVERSE] <initial_value>..<final_value>  
DO  <block> 
END FOR; 

The variable is assigned an initial value for the first iteration, incremented or, if you
specified REVERSE, decremented with every iteration till final value is reached. Variables
declared inside the FOR loop are not visible on the outside.

While Loop

WHILE <condition>  
DO <block>  
END WHILE; 

Condition block is same as IF statement. BREAK statement can be used to abort the loop.

METHOD loop_statement BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.

    DECLARE lv_sum INT := 0;
    DECLARE lv_index INT := 0;

***       for loop
      for lv_index IN 1..10

          DO
              DECLARE lv_tmp int := lv_sum;
              lv_tmp = lv_tmp + lv_index;
              lv_sum = lv_tmp;

           END for;

***        while loop
       lv_index = 0;
        WHILE lv_index <= 10
              do
                 DECLARE lv_tmp INT := lv_sum;
                 lv_tmp = lv_tmp + lv_index;
                 lv_sum = lv_tmp;
                 lv_index = lv_index + 1;
        END WHILE;

While Loop With Break :

***        while loop with break
        lv_index = 0;
        WHILE lv_index <= 1000
              DO
                  lv_index = lv_index + 1;
              if  lv_index = 10
              THEN BREAK ;

              END IF;
              END WHILE;

  ENDMETHOD.

ENDCLASS.

Conclusion: Internal tables in AMDP mirror ABAP's data handling but are managed using SQL Script constructs. Flow control allows procedural operations inside the database to optimize logic execution. Together, these enable high-performance, code-pushed data processing-minimizing data transfer between ABAP and database layer and making AMDP ideal for complex analytical logic.