AMDP: Scalar Functions

Introduction- Short Recap

Database Procedures (sometimes referred to as Stored Procedures or Procs) are subroutines that can contain one or more SQL statements that perform a specific task. They can be used for data validation, access control, or to reduce network traffic between clients and the DBMS servers. Extensive and complex business logic can be embedded into the subroutine, thereby offering better performance.

Like every database, SAP HANA support procedure implemented using native scripting language (HANA SQL Script in case of HANA database). These procedures are created and executed on the database layer. HANA DB is column based and reading data is very performance cost. To tackle this problem, ABAP Managed Database Procedure (known as AMDP) is defined which is used a top-down approach. It is written in ABAP layer but pointing to HANA DB.

In other words, AMDP is a technique in AS ABAP allowing developers to write database procedures directly in ABAP environment using ABAP methods and ABAP data types. AMDP is the recommended patterns for use in ABAP code optimization on SAP HANA database. It is implemented as ABAP class methods. The global class, that contains the interfaces “IF_AMDP_MARKER_HDB” is called AMDP Class. The suffix HDB indicates the database system for which the ABAP managed database procedure can be implemented in the AMDP method of AMDP class. AMDP class can contain both normal regular methods as well as AMDP methods. It can contain one or more AMDP methods for each database system specified by the special tag interface. The development is only supported in the Eclipse-based development environment (ABAP Development Tools) and NOT in SAP GUI.

Database function of the SAP HANA database is implemented in an AMDP method implementation of an AMDP class and is replicated from there to the database system. There are two types of AMDP methods, AMDP Procedure Implementation and AMDP Function Implementation. There are AMDP table functions or AMDP scalar functions. An AMDP Function implementation is marked in the method implementation with addition- BY DATABASE FUNCTION. AMDP scalar functions are supported from release 7.53, alongside AMDP table functions.

AMDP Scalar Functions

In general, SQL Server scalar function takes one or more parameters and returns a single value.  In SQL Script, scalar functions can be used as operands in the same places as elementary variables or columns of database tables or views. Similarly, In the SAP HANA database, scalar function is a database function with a scalar or elementary return value. A scalar function can be managed as an AMDP function in an AMDP class. The scalar functions help you simplify your code. For example, you may have a complex calculation that appears in many queries. Instead of including the formula in every query, you can create a scalar function that encapsulates the formula and uses it in each query.

To be more precise, an AMDP function is declared in an AMDP class like a regular static method or instance method in any visibility section.  An AMDP function implementation like this cannot be identified as such in the declaration part of the class and the same basically applies as to AMDP table functions for AMDP methods with the following differences:

◉ The data type of the return value is elementary.

◉ The data types of input parameters must also be elementary.

◉ No class-based exceptions can be declared using RAISING in the interface of the AMDP function implementation for AMDP scalar functions.

◉ An AMDP scalar function can be called in ABAP like a regular method and can be used as a functional method in a functional method call.

The AMDP scalar function defined using an AMDP function implementation like this can be used in other AMDP methods in accordance with the rules for scalar functions.

These functions can be used in non-AMDP-managed database procedures or database functions, like any database scalar function, but this is not recommended. It is possible for an AMDP scalar function to specify the database-specific option DETERMINISTIC after OPTIONS. This buffers the result of the function for the duration of a query.

Example

This example accesses AMDP function that is declared and implemented in the AMDP class CL_DEMO_AMDP_FUNCTIONS_INPCL. The method GET_MAX_FLTIME_SPFLI is an AMDP function implementation for an AMDP scalar function. It has an explicitly declared interface and can be called in ABAP.

METHOD get_max_fltime_spfli 

       BY DATABASE FUNCTION FOR HDB 

       LANGUAGE SQLSCRIPT 

       OPTIONS READ-ONLY 

               DETERMINISTIC 

       USING spfli. 

  SELECT MAX(fltime) INTO max_fltime 

               FROM spfli 

                 WHERE mandt = :clnt AND 

                       carrid = :carrid; 

ENDMETHOD.

Looking at the Spfli table, we find these flights offered by Lufthansa airline:

Now we can call the suggested method in our test report to just select the flight of this airline with the longest flight time

DATA carrid TYPE s_carr_id VALUE 'LH'.

SELECT carrid, connid, cityfrom, cityto, fltime

               FROM spfli

               WHERE carrid = @carrid AND

                     fltime = @( NEW cl_demo_amdp_functions_inpcl(

                                )->get_max_fltime_spfli(

                                     EXPORTING clnt = sy-mandt

                                               carrid = carrid ) )

               INTO TABLE @DATA(LongestFlight).

The output of this program is

Source: sap.com