SAP AMDP HDB SQLSCRIPT



Get Example source ABAP code based on a different SAP table
  



ABAP_AMDP - SQLScript for the SAP HANA Database
SQLScript 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. It is the implementation language of an AMDP method that specifies the addition FOR HDB LANGUAGE SQLSCRIPT in the statement METHOD.
ITOC



Latest notes:
AS ABAP requires the following authorizations as a user of the database system to be able to manage SQLScript procedures and functions on the SAP HANA database:
Privilege Execute on the object GET_PROCEDURE_OBJECTS of the schema SYS
Privilege Execute on the object TRUNCATE_PROCEDURE_OBJECTS of the schema SYS Further authorizations are required for debugging AMDP methods in the ABAP Development Tools (ADT). Missing authorizations can be identified using transaction SICK.
NON_V5_HINTS
SQLScript implementations in AMDP methods replace calls of database procedures using database procedure proxies and the statement CALL DATABASE PROCEDURE. Database procedures can either be implemented completely 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.
ABAP_HINT_END

AMDP Procedures and Functions in SAP HANA
The SQLScript implementation of an AMDP method with the name meth that belongs to an AMDP class class is stored by the ABAP runtime framework as an SQLScript procedure under the name CLASS=>METH in the ABAP database schema of the SAP HANA database. The names must be written in uppercase letters 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 AMDP 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 AB-SQL read statement.
Calls from other AMDP procedures or functions
An AMDP procedure or function implemented using SQLScript in the same class or a different class can call another AMDP procedure with the usual SQLScript syntax:
CALL 'CLASS=>METH'( f1 => a1, f2 => a2, ... );
The called AMDP procedure implementation CLASS=>METH must be specified after the addition USING of 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 SQLScript syntax:
SELECT ... FROM 'CLASS=>METH'( f1 => a1, f2 => a2, ... );
The specified AMDP function implementation CLASS=>METH must be specified after the addition USING of the calling method. An AMDP function implementation is always public.
BEGIN_SECTION VERSION 5 OUT
Calls from regular database procedures An SQLScript procedure or function created in the regular way in SAP Web IDE for SAP HANA or using Native SQL can call an SQLScript procedure or function implemented in an AMDP method or access an SQLScript function, as long as the procedure or function exists in the database system. This is not recommended, however, since the AMDP procedures and functions are managed in AS ABAP and consistency can only be guaranteed for other AMDP procedures and functions from the same AS ABAP.
Access in SAP Web IDE for SAP HANA Once the ABAP runtime framework has created an SQLScript procedure or function managed by AMDP in the database system, it is visible in SAP Web IDE for SAP HANA and can even be edited. This is not recommended since this kind of change has no effect on the implementation in the AMDP method and can be overwritten by the ABAP runtime framework at any time.
END_SECTION VERSION 5 OUT

ABAP_SYNTAX
The syntax of a SQLScript procedure or function written in SQLScript is exactly as described under SAP Hana SQLScript Reference, with one exception: The character * at the start of a line indicates a comment, as in ABAP. When the procedure or function is saved in the database system, the asterisk, *, is transformed to the usual double hyphens, --.
BEGIN_SECTION VERSION 5 OUT
All SQLScript operators CE_... that support views can be used. SQLScript operators that do not support views, for example, CE_COLUMN_TABLE, which only works with the technical property 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 AB_SQL , the session variable CDS_CLIENT is set to the value specified in the optional addition USING CLIENT. However, write access to session variables with the SQLScript statement SET is not allowed in an SQLScript procedure or function.
END_SECTION VERSION 5 OUT



Latest notes:
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.
NON_V5_HINTS
ABAP_HINT_END

Parameter Interface
The elementary ABAP types of interface parameters of an AMDP method are mapped to the appropriate types in SQLScript. as shown in the mapping tables.

SQLScript Procedures
The parameter interface of an SQLScript procedure supports 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 must or can be prefixed with a colon (:) in certain operand positions.
The parameter interface of an AMDP procedure implementation that implements an SQLScript procedure is transformed as follows:
An input parameter of the method defined using IMPORTING becomes an input parameter of the procedure declared using IN.
An output parameter of the method defined using EXPORTING becomes an output parameter of the procedure declared using OUT.
If CHANGING is 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.
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 SQLScript does not support INOUT parameters. The CHANGING parameter becomes an OUT parameter of the procedure with the same name as the CHANGING parameter. The IN parameter is created with an internal name consisting of the name of the CHANGING parameter 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 corresponding call are generally transparent for all ABAP developers.
BEGIN_SECTION VERSION 5 OUT 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).
END_SECTION VERSION 5 OUT
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 SQLScript:
An input/output parameter declared using CHANGING cannot have the type string or xstring. An exception this rule are parameters that are typed with reference to the built-in type SSTRING from the ABAP Dictionary.
The length of a parameter typed with the type c or n is restricted to a maximum of 5000 characters.
Parameters of the types string and xstring cannot be assigned a replacement value using DEFAULT, which means they cannot be optional parameters. Since release ABAP_778 , parameters of the types f, decfloat16, decfloat34, and sstring can be assigned a replacement value using DEFAULT.



Latest notes:
In SQLScript, tabular parameters are handled as temporary database tables. An internal table passed to SQLScript can be accessed just like a database table.
NON_V5_HINTS
ABAP_HINT_END

SQLScript Functions
The parameter interface 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 SQLScript procedure is transformed as follows:
An input parameter of the method defined using IMPORTING becomes an input parameter of the function declared using IN.
The return value of the method defined using RETURN becomes the 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 besides the return value.
AMDP table functions for CDS table functions can only have scalar input parameters.

Use
As described above, SQLScript procedures and functions managed in the SAP HANA database using AMDP can be used in ABAP programs and in other AMDP procedures according to 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.
BEGIN_SECTION VERSION 5 OUT
Furthermore, an AMDP procedure or function can use regular SQLScript 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 the addition USING of the calling AMDP method.
END_SECTION VERSION 5 OUT

Access to ABAP Types
In an SQLScript procedure, the AMDP macro $ABAP.type can be used to access ABAP types that have a corresponding type in SQLScript. The type mapping is described 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, the name of the database schema can be specified as prefix separated by a period, as usual in SQLScript.
BEGIN_SECTION VERSION 5 OUT 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.
END_SECTION VERSION 5 OUT

ABAP_EXAMPLES_ABEXA
AMDP - Examples
ABAP_EXAMPLE_END