12_AMDP.md

ABAP Managed Database Procedures (AMDP)

• ABAP Managed Database Procedures (AMDP)
  • Introduction
  • AMDP Classes
  • AMDP Methods
  • AMDP Procedures
  • AMDP Functions
    • CDS Table Functions
  • More Information
  • Executable Example

This cheat sheet gathers basic information on ABAP Managed Database Procedures (AMDP). Find more details here in the ABAP Keyword Documentation.

Introduction

• AMDP are a class-based framework for managing and calling

  • database procedures (which is a synonym for stored procedures, i. e. the procedures are stored in the database - the SAP HANA database in this case - and executed there)
  • database functions (which are SQLScript functions in the SAP HANA database)

  in AS ABAP.

• "ABAP managed" enters the picture in ABAP with the option of implementing special AMDP procedures as database procedures and AMDP functions as database functions.

• The implementations are programmed using a database-specific language. Currently, AMDP only supports database procedures and functions from the SAP HANA database. That is, SQLScript is the programming language of choice.

• AMDP procedures and functions are part of a dedicated AMDP class and declared and implemented as part of a method. The classes and methods have certain characteristics as outlined further down.

• The AMDP framework replicates the procedure or function to the database system, i. e. despite the fact that the programming happens in an AMDP class (which is an ABAP Repository object as other global classes, too), the (SQLScript) code is executed only on the (SAP HANA) database and not in AS ABAP, i. e. method calls are sent to the database procedure or function.

💡 Note

• The use of AMDP is not recommended if the same task can be achieved using ABAP SQL.
• AMDP classes can only be edited with the ABAP development tools for Eclipse (ADT).

⬆️ back to top

AMDP Classes

• As mentioned above, an AMDP class is an ABAP Repository object like other global classes.
• However, an AMDP class includes the specification of the interface IF_AMDP_MARKER_HDB for the SAP HANA database (indicated by HDB), which is currently the only possible database.
• An AMDP class can contain both (one or more) AMDP methods and non-AMDP methods.

Example for a declaration part of an AMDP class:

CLASS cl_some_amdp_class DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    "Specifying the interface is mandatory
    INTERFACES if_amdp_marker_hdb.
...
ENDCLASS.

⬆️ back to top

AMDP Methods

• Can be created as instance methods using METHODS or static methods using CLASS-METHODS in any visibility section.
• Cannot be identified as AMDP methods in the declaration part of the class since there are no specific additions to the methods. Exceptions: AMDP function implementations that implement any CDS table functions as shown further down and method declarations using AMDP OPTIONS that are not dealt with here.

AMDP method declarations in any visibility section like non-AMDP methods:

...
PUBLIC SECTION.

  METHODS some_amdp_meth
    ... "Here go the parameters

PRIVATE SECTION.

  CLASS-METHODS another_amdp_meth
    ... "Here go the parameters

...

⬆️ back to top

AMDP Procedures

Despite the fact that AMDP methods cannot be identified as such from the declaration part (apart from the exceptions mentioned above), the declaration part of AMDP procedures has special characteristics:

• Parameters must be passed by value using VALUE(...). Passing by reference is not allowed.
• Parameter types ...
  • must not be generic.
  • can only be elementary data types and table types with a structured row type (and this type can only contain elementary data types as components).
• Return values cannot be declared using RETURNING.
• Only input parameters can be flagged as optional parameters.

Example for an AMDP procedure's declaration part:

...
PUBLIC SECTION.

  "Table type with a structured row type
  TYPES tab_type TYPE STANDARD TABLE OF dbtab WITH EMPTY KEY.

  METHODS amdp_meth
    IMPORTING VALUE(num) TYPE i,
    EXPORTING VALUE(tab) TYPE tab_type.

...

In contrast to the declaration part, the implementation part of an AMDP method has multiple special additions for AMDP purposes following METHOD and the method name:

...
METHOD amdp_meth
  BY DATABASE PROCEDURE
  FOR HDB  
  LANGUAGE SQLSCRIPT  
  OPTIONS READ-ONLY
  USING db_object.   "see comments further down
                         
"Beginning of the SQLScript code (note that it is not ABAP code although it looks similar)

  ... "Here goes SQLScript code
      "Note that an AMDP method implementation must not be empty.

"End of the SQLScript code

ENDMETHOD.
...

"Comments:
"  BY DATABASE PROCEDURE   -> Flags the AMDP method as AMDP procedure
"  FOR HDB                 -> Definess the database system where the method is to be used;
"                             currently, only HDB (SAP HANA database) is possible
"  LANGUAGE SQLSCRIPT      -> Defines the programming language of the database system
"  OPTIONS READ-ONLY       -> Specifies database-specific options
"  USING db_object.        -> Optional addition; specifies database objects;
"                             can also be AMDP procedures and functions

Note:

• In the restricted ABAP language version scope, only reads are allowed. Hence, the addition OPTIONS READ-ONLY is mandatory. Furthermore, you must make sure that the database objects that are specified after USING are accessible.
• Generally, in the unrestricted ABAP language version scope (ABAP for Cloud Development), more syntax options are allowed for AMDP method declaration and implementation parts. Check the ABAP Keyword Documentation for more details as covered further down.

⬆️ back to top

AMDP Functions

Scalar and table functions can be managed as AMDP functions. Such AMDP table functions have, as the name implies, a tabular return value whereas AMDP scalar functions have a scalar or elementary return value (more information here). Regarding AMDP table functions, there are two types available:

• Functions that can only be accessed in other AMDP methods (i. e. other AMDP functions or procedures) and cannot be called directly in ABAP
• Functions that implement CDS table functions that can be accessed in ABAP SQL

Characteristics of method declaration parts of AMDP functions:

• Similar to AMDP procedures, the methods can be declared as static or instance methods in any visibility section.
• The method parameters must include a return value using RETURNING and having a tabular data type.
• Additionally, the parameters can include elementary and tabular input parameters.
• No class-based exceptions can be declared using RAISING.

Example for an AMDP function's declaration part:

...
PUBLIC SECTION.

  "Table type with a structured row type
  TYPES tab_type TYPE STANDARD TABLE OF dbtab WITH EMPTY KEY.

  METHODS amdp_func
    IMPORTING VALUE(num)       TYPE i,
              VALUE(some_elem) TYPE c LENGTH 3,
    RETURNING VALUE(tab)       TYPE tab_type.

...

The implementation part of an AMDP function is similar to the one of AMDP procedure as shown above. The difference is the use of BY DATABASE FUNCTION instead of BY DATABASE PROCEDURE:

...
METHOD amdp_func
  BY DATABASE FUNCTION    
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING db_object.

"Beginning of the SQLScript code (note that it is not ABAP code although it looks similar)

  ... "Here goes SQLScript code;
      "AMDP table function to be called by other AMDP methods only

"End of the SQLScript code

ENDMETHOD.

"Comment:
"  BY DATABASE FUNCTION    -> Flags the AMDP method as AMDP function
...

⬆️ back to top

CDS Table Functions

• Each CDS table function is linked with an AMDP function in which it is implemented using SQLScript.
• Can be used as data sources of ABAP SQL read statements.
• Characteristics for method declaration and implementation parts regarding AMDP functions for CDS table functions:
  • Method can only be declared as a static method in the public visibility section of an AMDP class using CLASS-METHODS.
  • For the declaration, there is a special form with the addition FOR TABLE FUNCTION.
  • The parameter interface is not specified. Instead, the input parameters are determined by the input parameters of the CDS table function (i. e. the names and data types - which are always elementary - specified there are used). As the return value, a standard table with an empty key is generated based on the structured row type including the components as specified in the CDS table function.

Example for an AMDP functions declaration implementing a CDS table function:

...
PUBLIC SECTION.

  CLASS-METHODS:
    table_func FOR TABLE FUNCTION some_ddl_source.

...

Notes on the CDS DDL source of a CDS table function:

• You have defined (and activated) a CDS DDL source, i. e. a CDS entity, with the notation DEFINE TABLE FUNCTION
• You can specify optional input parameters using ... WITH PARAMETERS parameter1, parameter2, ...
• You must specify an element list using ... RETURNS { element1; element2; ...; } .... The elements determine the components of the structured data type represented by a CDS table function.
• You have specified the IMPLEMENTED BY METHOD addition followed by a fully qualified method name in the form of amdp_class=>amdp_method using the names of the AMDP class and method.
• More information here.

The CDS DDL source might look like this:

//Here go annotations.
define table function some_ddl_source
  returns
  {
    client : abap.clnt;
    field1 : abap.char(5);
    field2 : abap.char(5);
  }
  implemented by method amdp_class=>amdp_method;

You can then use the CDS table function as source for a SELECT statement, for example: SELECT * FROM some_ddl_source INTO ....

⬆️ back to top

More Information

... on AMDP in ABAP for Cloud Development

Find more information in the subtopics of the ABAP Keyword Documentation (ABAP for Cloud Development).

💡 Note

• In ABAP for Cloud Development, AMDP methods must be client-safe. This means that the SQLScript code should only access artifacts that restrict access to a single client, such as CDS view entities, or are client-independent. Therefore, all objects used in the USING list must be client-safe. This also applies to CDS table functions implemented as AMDP methods. Accessing client-dependent data using Native SQL is not supported in ABAP for Cloud Development.
• The AMDP example for ABAP for Cloud Development is designed differently compared to the AMDP example for Standard ABAP. Instead of using demo database tables, CDS view entities are used in the USING list. Additionally, the client handling is adjusted for the AMDP methods by including appropriate additions in the AMDP method declaration part.

... on AMDP in Standard ABAP

Find more information in the subtopics of the ABAP Keyword Documentation (Standard ABAP).

✔️ Hint
Checking if AMDP is supported on the system:

IF NOT cl_abap_dbfeatures=>use_features(
    EXPORTING requested_features =
    VALUE #( ( cl_abap_dbfeatures=>call_amdp_method ) ) ).

  "Result: Current database system does not support AMDP procedures
RETURN.
ENDIF.

⬆️ back to top

Executable Example

zcl_demo_abap_amdp

💡 Note

• The executable example covers the following topics:
  • AMDP procedures, calling AMDP procedures from SQLScript
  • AMDP table functions for AMDP methods
  • AMDP table functions for CDS table functions
• The steps to import and run the code are outlined here.
• Disclaimer