ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → AMDP - ABAP Managed Database Procedures → AMDP - Methods → METHOD - BY DATABASE PROCEDURE, FUNCTION
AMDP - SQL Script for the SAP HANA Database
SQL Script is a script language, documented in the
SAP HANA SQLScript Reference, and used to program
SQLScript procedures and
SQLScript functions in the
SAP HANA database. L is the implementation language of an
AMDP method that specifies the addition
FOR HDB LANGUAGE SQLSCRIPT when the statement
METHOD is used.
7.31 | 7.40 | 7.54
- Before AS ABAP is able to manage SQL Script procedures and functions on the SAP HANA database, it requires the following authorizations as a user of the database system:
- Privilege Execute on the object GET_PROCEDURE_OBJECTS of the schema SYS
- Privilege Execute on the object TRUNCATE_PROCEDURE_OBJECTS of the schema SYS
- When used in AMDP, SQL Script replaces calls of database procedures using database procedure proxies and the statement CALL DATABASE PROCEDURE. The required procedure can either be implemented in full in an AMDP method or an existing database procedure can be called from an AMDP method. In this case, the AMDP method replaces the database procedure proxy. Database procedure proxies are still recommended for scenarios in which secondary connections are used to access existing procedures in SAP HANA database alongside the current standard AS ABAP database.
AMDP procedures and functions in SAP HANA
The SQLScript implementation of an AMDP method with the name
meth and an
class is saved by the ABAP runtime environment as an SQLScript procedure under the name CLASS=>METH in the
ABAP database schema
of the SAP HANA database. Note that these names are case-sensitive when used in the database system.
AS ABAP is responsible for all transports, administration, and access control. SQLScript procedures and functions managed using AMDP can be accessed as follows:
- Calls from ABAP
- Call of an AMDP procedure or
scalar function with standard method call meth( ...
) in ABAP programs. For
meth, the associated AMDP procedure implementation is specified.
- Calls an AMDP table function by specifying the assigned CDS table function or a CDS view that uses a CDS table function in an ABAP SQL read statement.
- Calls from other AMDP procedures or functions
- An AMDP procedure or function implemented using SQL Script in the same class or a different class can call another AMDP procedure with the usual
CALL "CLASS=>METH"( f1 => a1, f2 => a2, ... );
The called AMDP procedure implementation CLASS=>METH must be specified after the addition
USINGof the calling method. The usual visibility rules from ABAP Objects apply. A database procedure or database function implemented in a class cannot call a procedure implemented in a private AMDP procedure implementation of a different class, unless a friendship exists between the classes.
- An AMDP procedure or function implemented using SQLScript in the same class or a different class can call another AMDP function implemented with SQLScript using the usual
SELECT ... FROM "CLASS=>METH"( f1 => a1, f2 => a2, ... );
The specified AMDP function implementation CLASS=>METH must be specified after the addition
USINGof the calling method. An AMDP function implementation is always public.
- Calls from regular database procedures
- Access In SAP Web IDE for SAP HANA
The syntax of a SQL Script procedure or function written in SQL Script is exactly as described under SAP Hana SQLScript Reference, with one exception: The character at the start of a line indicates a comment line, as in ABAP. When the procedure or function is saved in the database system, the asterisk, , is transformed to the usual double hyphens, --.
All SQL Script operators CE_... that support views can be used. SQL Script operators, which do not support any views (for example, CE_COLUMN_TABLE only works with the technical attribute Column Store) cannot be used.
In an SQLScript procedure, the function SESSION_CONTEXT is used for read access to the session variables in the SAP HANA database. When called from ABAP, the ABAP-specific session variables are set to the values of the corresponding ABAP system fields. When accessing a CDS table function implemented in an AMDP table function via ABAP SQL, the session variable CDS_CLIENT is the optional value set in addition USING CLIENT. However, write access to session variables with SQLScript statement SET is not permitted in an SQLScript procedure or function.
- Support for ABAP comments introduced using
*enables the use of change entries inserted by the Modification Assistant tool in ABAP programs. This property should not be used when creating AMDP methods in the regular way.
- The syntax is checked only on an AS ABAP whose standard database is a SAP HANA database. Here, a temporary database procedure or function is created that checks the syntax.
- The use of the dynamic options under SQLScript syntax is strongly discouraged due to the reasons specified under AMDP. This applies in particular to statements such as EXEC, EXECUTE IMMEDIATE, or APPLY FILTER.
As shown in the mapping tables, the elementary ABAP types of interface parameters of an AMDP method are mapped to the appropriate types in SQLScript.
The parameter interface of an SQL Script procedure uses input parameters declared using IN, output parameters declared using OUT, and input/output parameters declared using INOUT. All parameters can be scalar and tabular with the exception of input/output parameters, which can only be scalar. When used in the procedure, certain operand positions expect or allow the name of an input parameter to be prefixed with a colon (:).
The parameter interface of an AMDP procedure implementation that implements an SQL Script procedure is transformed accordingly:
- An input parameter of the method defined using
IMPORTINGbecomes an input parameter of the procedure declared using IN.
- An output parameter of the method defined using
EXPORTINGbecomes an output parameter of the procedure declared using OUT.
CHANGINGis used to define a
- scalar input/output parameter of the method, the parameter becomes an input/output parameter of the database procedure declared using INOUT and if it is used to define a
- tabular input/output parameter of the method, it is transformed to a pair of input and output parameters
declared using IN and OUT on the database,
since SQL Script does not support INOUT parameters. The OUT
parameter has the name of the
CHANGINGparameter and is used instead of this parameter in the AMDP procedure. The IN parameter is created with an internal name comprising the name of the
CHANGINGparameter and a postfix __IN__. The IN parameter is assigned to the OUT parameter before the start of the actual AMDP procedure. This transformation and the associated call are generally transparent for all ABAP developers. It becomes visible only when the database procedure is displayed, for example, in SAP Web IDE for SAP HANA or called from another database procedure (see the executable example).
Elementary and tabular method parameters become scalar and tabular parameters of the database procedure respectively. As well as the restrictions for the parameter interface of an AMDP method described under AMDP, the following restrictions apply in the implementation with SQL Script:
- An input/output
parameter declared using
CHANGINGcannot have the type
xstring. An exception this rule are parameters that are typed with reference to the predefined type SSTRING ABAP Dictionary.
- The length of a parameter typed with the type
nis restricted to a maximum of 5000 characters.
- Parameters of the types
xtringcannot be assigned a replacement value using
DEFAULT, which means they cannot be optional parameters.
In SQL Script, tabular parameters are handled as temporary database tables. An internal table passed to SQL Script can be accessed there just like a database table.
The parameter interfaces of an SQLScript function supports input parameters declared using IN and a return value declared using RETURNS. The input parameters of a scalar function must be scalar and the input parameters of a table function can be scalar and tabular. The return value of a scalar function is scalar and the return value of a table function is tabular.
The parameter interface of an AMDP function implementation that implements an SQL Script procedure is transformed accordingly:
- An input parameter of the method defined using
IMPORTINGbecomes an input parameter of the function declared using IN.
- The tabular return value of the method defined using
RETURNbecomes the tabular return value of the procedure declared using RETURNS.
The same restrictions apply as in SQLScript procedures, as well as the following:
- AMDP scalar functions cannot have any output parameters except the return value.
- AMDP table functions for CDS table functions can only have scalar input parameters.
As described above, SQL Script procedures and functions managed in the SAP HANA database using AMDP can be used in ABAP programs and in other AMDP procedures. How they can be used is determined by the visibility of the AMDP methods. Recursive and cyclical calls are forbidden in the HANA database system, which means that
- an SQLScript procedure or function cannot use itself
- an AMDP method that implements an SQLScript procedure cannot call any database procedures or use any database functions that themselves use the AMDP procedure or function.
Furthermore, an AMDP procedure or function can use regular SQL Script procedures and functions created in
SAP Web IDE for
SAP HANA or using Native SQL. These procedures or functions cannot and must not be specified after
USING of the calling AMDP method.
Access to ABAP Types
In an SQLScript procedure, the AMDP macro $ABAP.type can be used to access ABAP types, which are assigned to corresponding types of SQLScript in the mapping tables.
Access to Database Schemas
In an SQLScript procedure, it is possible to access objects in the same database schema directly. The name of the current database schema must not be specified here. To access an object in another database schema, its name can be prefixed and separated by a period (as usual in SQLScript). As well as specifying the name directly, the AMDP macro $ABAP.schema can be used to specify a logical schema to which a physical database schema is mapped.