24. Januar 2022
With AMDP procedures, ABAP can easily and elegantly outsource more complex requirements in the SAP HANA database. An excerpt from the 2nd edition of my book "SQLScript for SAP HANA", published by Rheinwerkverlag, SAP Press.
From the perspective of an ABAP developer, an AMDP procedure is an instance method of an ABAP class implemented in the SQLScript programming language. This trick solves several problems:
- The SQL procedure is called as a method call of an ABAP class. Thus, the procedure call is perfectly integrated into ABAP.
- The database procedure is transported via the ABAP class. This means that there are not several transport mechanisms involved that have to be kept synchronized.
- It is not necessary for the developer to have direct access to the SAP HANA database with the appropriate authorizations while devoloping. It is sufficient to work with the ADTs with the usual ABAP developer authorizations.
In the following sections, the terms AMDP and AMDP procedure are used interchangeably. If it is not explicitly mentioned, the information does not refer to AMDP functions.
Creating AMDP procedures
An AMDP method can only be created in a global class that implements the
IF_AMDP_MARKER_HDB marker interface. The interface itself has no methods, it is only used to mark the AMDP classes.
An AMDP class can include both normal ABAP methods and AMDP methods. Whether a method is implemented as AMDP is not specified in the declaration of the method. The following listing shows an example.
CLASS zcl_amdp_demo DEFINITION PUBLIC CREATE PUBLIC. PUBLIC SECTION. INTERFACES if_amdp_marker_hdb. TYPES gty_tt_countries TYPE TABLE OF t005t. METHODS get_countries IMPORTING VALUE(iv_langu) TYPE langu CHANGING VALUE(ct_country) TYPE gty_tt_countries VALUE(cv_subrc) TYPE sy-subrc. ENDCLASS. CLASS zcl_amdp_demo IMPLEMENTATION. METHOD get_countries BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING t005t. ct_country = select * FROM t005t WHERE spras = :iv_langu; SELECT CASE WHEN COUNT(*) > 0 THEN 0 ELSE 4 END AS subrc INTO cv_subrc FROM :ct_country; ENDMETHOD. ENDCLASS.
Example of a simple AMDP method
Method signature restrictions
An SQLScript procedure is later generated from the source code of an AMDP method. However, these database procedures are more restrictive in terms of parameters than is the case with ABAP methods. Accordingly, these restrictions must of course also apply to AMDP methods;
- First of all, all parameters must be fully typed. Generic data types such as
REF TO DATAare not allowed.
- Since SQLScript does not know any structures, only scalar data types and table types may be used as parameters. The table types in turn may only use scalar data types in their row structure.
- All parameters must be passed as value parameters (call by value); the use of reference parameters (call by reference) is not possible. This is obvious considering the fact that the application server runs on a different system than the database. Accordingly, there is no common memory area referencing both systems.
CHANGING-parameters are allowed in AMDP procedures. It is not possible to use
- Only AMDP exception classes can be declared in the method signature. These are the sub-classes of
CX_AMDP_ERROR. When declared, the AMDP method caller can handle these exceptions. However, if they are not declared, these errors will result in a dump.
Implementation of an AMDP procedure
The language in which AMDP procedures are developed is SQLScript. This is communicated to the system by the addition
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
If an AMDP implementation accesses data in read-only mode, the addition
OPTIONS READ-ONLY can optionally be specified.
The optional addition
USING can be used to tell an AMDP implementation to use the corresponding tables, views, AMDP functions or AMDP procedures from the standard database schema of the NetWeaver system. This allows you to use this name without always explicitly specifying the respective database schema in the SQLScript source code. After the keyword USING, the enumaration of the objects is showing up only separated by whitespace (blank). It should be noted that this is still ABAP code. If, for example, you specify a table generated by BW from the namespace /BIC/ or /BI0/, this is done without quotation marks, e.g.:
However, when you access this table using SQLScript code, you must use the special notation because the slash is not allowed in the simple notation:
SELECT ... FROM "/BI0/PPLANT".
USING for AMDP objects
The use of AMDP procedures and functions is declared in the USING clause using the name of the associated ABAP method with the notation
KLASSE=>METHODE. Listing 1.2 shows an example of the keywords in the
METHOD clause of the method implementation. Everything after the dot in line 5 up to the keyword
ENDMETHOD is interpreted as SQLScript.
METHOD get_countries BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING t005t. <SQLScript-Code> ENDMETHOD.
Key words used in the implementation of an AMDP method
Note: A general concept for a specific database
Basically, all statements and declarations related to AMDP are designed in such a way that AMDPs can be implemented on different database systems and in different languages. Accordingly, when implementing AMDP methods, there are also the mandatory
FOR <Datenbanksystem> and
However, until today (release SAP NetWeaver 7.51), implementations are only possible for the SAP HANA database using the SQLScript language. The L language may only be used internally in SAP.
Looking at other database systems under SAP NetWeaver, we can see that each of the comes along with their own programming languages, such as Oracle's PL/SQL language. However, these cannot be used to implement AMDPs.