AMDP - ABAP DATABASE PROCEDURES, CDS Functions

[YogiPavan]

ABAP MANAGED DATABASE PROCEDURES (AMDP)
It is used to manage and call database procedures or stored procedures and database functions.
It supports for HANA database only.
Types of AMDP- AMDP Procedures, AMDP Functions
Interface for AMDP is IF_AMDP_MARKER_HDB.
AMDP method parameters are pass by value, scalar type or table type and not be deep
structures
Client handling is necessary for AMDP.
Can create or change AMDPs using ADT Tools.
Syntax - METHOD meth_name BY DATABASE PROCEDURE|FUNCTION
FOR db
LANGUAGE db_lang
[OPTIONS db_options]
[USING db_entities].

BY DATABASE PROCEDURE
Defines an AMDP procedure implementation for implementing a database procedure
BY DATABASE FUNCTION
Defines an AMDP function implementation for implementing a database function
FOR db
Defines the database system for which the AMDP method is used ‘HDB’.
LANGUAGE db_lang
Defines the database-specific language in which the AMDP is implemented ‘SQL SCRIPT’.
OPTIONS db_options
Defines the database-specific options for the AMDP procedure or function ‘READ- ONLY’.
USING db_entities
Defines the database objects managed by ABAP accessed in the AMDP procedure or function

Create Database Table for Purchase Order Header
@EndUserText.label : 'Purchase Order Header Table'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table ztb_po_hd {
key client : abap.clnt not null;
key po_num : ebeln not null;
doc_cat : bstyp;
type : abap.char(2);
comp_code : bukrs;
org : ekorg;
status : abap.char(1);
vendor : lifnr;
plant : werks_d;
create_by : abp_creation_user;
created_date_time : abp_creation_tstmpl;
changed_date_time : abp_locinst_lastchange_tstmpl;
local_last_changed_by : abp_locinst_lastchange_user;
}
Table Data

AMDP Procedures:
It allows import, export and changing parameters, but return parameter is not allowed.
It can perform READ, INSERT, and UPDATE.
Example for AMDP Procedures to get and Update the PO details
1. Class for AMDP Procedure
CLASS zcl_amdp_db_procd DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
TYPES: tt_po_hd TYPE TABLE OF ztb_po_hd.
METHODS: get_po_hd IMPORTING VALUE(im_po) TYPE ztb_po_hd-po_num
VALUE(im_mandt) TYPE sy-mandt
EXPORTING VALUE(it_po_hd) TYPE tt_po_hd,
update_po_hd IMPORTING VALUE(im_po) TYPE ztb_po_hd-po_num
VALUE(im_mandt) TYPE sy-mandt
VALUE(im_status) TYPE ztb_po_hd-status
EXPORTING VALUE(it_po_hd) TYPE tt_po_hd.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_amdp_db_procd IMPLEMENTATION.
METHOD get_po_hd BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING ztb_po_hd.
it_po_hd = SELECT * FROM ztb_po_hd WHERE po_num = im_po AND client = im_mandt;
ENDMETHOD.
METHOD update_po_hd BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING
ztb_po_hd.
UPDATE ztb_po_hd SET status = im_status WHERE po_num = im_po AND client =
im_mandt;
it_po_hd = SELECT * FROM ztb_po_hd WHERE po_num = im_po AND client = im_mandt;
ENDMETHOD.
ENDCLASS.

2. Report program to consume the AMDP Procedure
*&---------------------------------------------------------------------*
*& Report ZAMDP_DB_PROC
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zamdp_db_proc.
DATA(lr_obj) = NEW zcl_amdp_db_procd( ).
lr_obj->get_po_hd(
EXPORTING
im_po = '1000000001'
im_mandt = sy-mandt
IMPORTING
it_po_hd = DATA(it_po_hd) ).
cl_demo_output=>display( it_po_hd ).
lr_obj->update_po_hd(
EXPORTING
im_po = '1000000001'
im_mandt = sy-mandt
im_status = 'B'
IMPORTING
it_po_hd = DATA(it_po_hd1) ).

cl_demo_output=>display( it_po_hd1 ).
3. Execution of report program.
Output for first method call

Output for second method call

Table data after execution of AMDP Proced

AMDP Functions
It can have multiple importing parameters and return exactly one result.
It can perform READ only.
Functions are Table and CDS Functions
1. Table Functions:
Example 1 for AMDP Table Functions
1. Class for AMDP Table Functions
CLASS zcl_amdp_functions DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
TYPES: tt_po_hd TYPE STANDARD TABLE OF ztb_po_hd WITH EMPTY KEY.
CLASS-METHODS: get_po_hd_tb_fn IMPORTING VALUE(im_po) TYPE ztb_po_hd-po_num
VALUE(im_mandt) TYPE sy-mandt
RETURNING VALUE(it_po_hd) TYPE tt_po_hd.
METHODS: get_po_hd_db_pr IMPORTING VALUE(im_po) TYPE ztb_po_hd-po_num
VALUE(im_mandt) TYPE sy-mandt
EXPORTING VALUE(it_po_hd) TYPE tt_po_hd.
PROTECTED SECTIONPRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_functions IMPLEMENTATION. METHOD get_po_hd_tb_fn BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READONLY USING ztb_po_hd.
RETURN select * from ztb_po_hd WHERE po_num = im_po and client = im_mandt;
ENDMETHOD.
METHOD get_po_hd_db_pr BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS
READ-ONLY USING zcl_amdp_functions=>get_po_hd_tb_fn.
it_po_hd = SELECT * FROM "ZCL_AMDP_FUNCTIONS=>GET_PO_HD_TB_FN"( :im_po ,
:im_mandt );
ENDMETHOD.
ENDCLASS.

2. Report program to consume the AMDP Functions through Procedures
*&---------------------------------------------------------------------*
*& Report ZAMDP_DB_FUNC
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZAMDP_DB_FUNC.
DATA(lr_obj) = new zcl_amdp_functions( ).
lr_obj->get_po_hd_db_pr(
EXPORTING
im_po = '1000000001'
im_mandt = sy-mandt
IMPORTING
it_po_hd = DATA(it_po_hd) ).
cl_demo_output=>display( it_po_hd ).

3. Output

Example 2 for AMDP Functions
1. Class for AMDP Functions
CLASS zcl_amdp_functions2 DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
TYPES: tt_po_hd TYPE STANDARD TABLE OF ztb_po_hd WITH EMPTY KEY.
METHODS: get_po_num_func RETURNING VALUE(ir_po_num) TYPE ztb_po_hd-po_num.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_functions2 IMPLEMENTATION.
METHOD get_po_num_func BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS
READ-ONLY USING ztb_po_hd.
SELECT PO_NUM INTO ir_po_num FROM ztb_po_hd WHERE status = 'B';
ENDMETHOD.
ENDCLASS.

2. Report program to consume the AMDP Functions
*&---------------------------------------------------------------------*
*& Report ZAMDP_DB_FUNC2
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zamdp_db_func2.
START-OF-SELECTION.
DATA(lr_obj) = NEW zcl_amdp_functions2( ).
SELECT * FROM ztb_po_hd WHERE po_num = @( lr_obj->get_po_num_func( ) )
INTO TABLE @DATA(it_po).
cl_demo_output=>display( it_po ).

3. Output

2. CDS Functions:
Example for CDS functions
1. Data definition for CDS functions
@EndUserText.label: 'AMDP CDS Function'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function ZAMDP_CDS_FUNCTION
with parameters
@Environment.systemField: #CLIENT
clnt : abap.clnt,
po : ebeln
// sel_opt : abap.char( 1000 )
returns
{
client : abap.clnt;
po_num : ebeln;
org : ekorg;
status : abap.char(1);
vendor : lifnr;
plant : werks_d;
}
implemented by method
zcl_amdp_cds_fun=>get_po;

2. Implementation class for CDS function
CLASS zcl_amdp_cds_fun DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
CLASS-METHODS GET_PO FOR TABLE FUNCTION ZAMDP_CDS_FUNCTION.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_cds_fun IMPLEMENTATION.
METHOD get_po BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING ztb_po_hd.
RETURN SELECT client, po_num, org, status, vendor, plant FROM ztb_po_hd WHERE
po_num = po AND client = clnt;
ENDMETHOD.
ENDCLASS.

3. Execution of the CDS Function- Enter the PO ‘1000000001’

1. Output

Example 2 for CDS Functions using Select options
1. Data definition for CDS Function with select options
@EndUserText.label: 'AMDP CDS Function'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function ZAMDP_CDS_FUNCTION
with parameters
/* @Environment.systemField: #CLIENT
clnt : abap.clnt,
po : ebeln */
sel_opt : abap.char( 1000 )
returns
{
client : abap.clnt;
po_num : ebeln;
org : ekorg;
status : abap.char(1);
vendor : lifnr;
plant : werks_d;
}
implemented by method
zcl_amdp_cds_fun=>get_po;

2. Implementation class for CDS Function with select options
CLASS zcl_amdp_cds_fun DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
CLASS-METHODS get_po FOR TABLE FUNCTION zamdp_cds_function.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_cds_fun IMPLEMENTATION.
METHOD get_po BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING ztb_po_hd.
* RETURN SELECT client, po_num, org, status, vendor, plant FROM ztb_po_hd WHERE
po_num = po AND client = clnt;

it_po = select client, po_num, org, status, vendor, plant FROM ztb_po_hd;
it_filter = apply_filter ( :it_po, :sel_opt);
RETURN select :it_filter.client,
:it_filter.po_num,
:it_filter.org,
:it_filter.status,
:it_filter.vendor,
:it_filter.plant
from :it_filter;
endmethod.
ENDCLASS.

3. Report program for CDS function with select options

*&---------------------------------------------------------------------*
*& Report ZAMDP_CDS_FUNC
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zamdp_cds_func.
DATA : lv_po TYPE ebeln.
SELECT-OPTIONS : so_po FOR lv_po.
TRY.
DATA(l_where) = cl_shdb_seltab=>combine_seltabs(
EXPORTING it_named_seltabs = VALUE #( ( name = 'PO_NUM' dref = REF #( so_
po[] ) ) ) iv_client_field = 'CLIENT' ).
CATCH cx_shdb_exception.
ENDTRY.
SELECT * FROM zamdp_cds_function( sel_opt = @l_where ) INTO TABLE @DATA(it_po
).
IF sy-subrc EQ 0.
cl_demo_output=>display( it_po ).
ENDIF.

1. Output

lv_where  = CLIENT = '800' AND ( PO_NUM = '1000000001')

References:

• https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abenamdp.htm
• https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapmethod_by_db_proc.htm

[Sandra_Rossi]

Post your blog post in the blog post area, not in the Q&A area. https://community.sap.com/t5/technology-blogs-by-members/bg-p/technology-blog-members