[HANA] ABAP调用HANA存储过程实例

最近测试了用HANA存储过程写取数SQL，SAP中调用获取数据再做处理。
这种方式能大幅优化提升代码运行效率。
首先是存储过程代码

CREATE TYPE "SAPHANADB"."tt_structure" 
AS TABLE ( "BUKRS" VARCHAR(4) CS_STRING,
	 "GJAHR" VARCHAR(4) CS_STRING,
	 "BELNR" VARCHAR(10) CS_STRING,
	 "BUZEI" VARCHAR(3) CS_STRING,
	 "POPER" VARCHAR(3) CS_STRING,
	 "BUDAT" VARCHAR(8) CS_STRING,
	 "LIFNR" VARCHAR(10) CS_STRING,
	 "RACCT" VARCHAR(10) CS_STRING,
	 "FISCYEARPER" VARCHAR(7) CS_STRING,
	 "HSL" DECIMAL(23,2) CS_FIXED );
	 
CREATE Procedure P_TEST01
(   I_BUKRS         varchar(4),
    I_BUDAT         varchar(8),
    I_AUGDT         varchar(8),
    i_MANDT		    varchar(3),
    OUT O_TAB1      tt_structure
) Language SQLScript
as
Begin

O_TAB1 = 
WITH  ACD AS (
  SELECT A.RCLNT,
         A.RBUKRS,
         B.gjahr,
         B.belnr,
         B.buzei,
         A.poper,
         A.fiscyearper,
         A.budat,
         '00000000' AS augdt,
         A.zuonr,
         A.lifnr,
         A.racct,
         CASE WHEN ifnull(A.zuonr,'')<>'' THEN lpad( ltrim( A.zuonr,' ' ), 10, '0' )
         ELSE '' END AS LIFNR_L,
         substring( A.zuonr,1,10 )             as EBELN_L,
         A.EBELP,
         A.HSL
    FROM ACDOCA AS A 
    INNER JOIN BSIS AS B
      ON A.RBUKRS = B.BUKRS  AND A.gjahr  = B.gjahr
     AND A.belnr  = B.belnr  AND A.buzei  = B.buzei
     AND A.RCLNT  = B.MANDT
   WHERE A.RBUKRS = :I_BUKRS AND A.budat <= :I_BUDAT
     AND A.RACCT LIKE '260%' AND A.RCLNT = :I_MANDT
  UNION
  SELECT A.RCLNT,
         A.RBUKRS,
         B.gjahr,
         B.belnr,
         B.buzei,
         A.poper,
         A.fiscyearper,
         A.budat,
         B.augdt,
         A.zuonr,
         A.lifnr,
         A.racct,
         CASE WHEN ifnull(A.zuonr,'')<>'' THEN lpad( ltrim( A.zuonr,' ' ), 10, '0' )
         ELSE '' END AS LIFNR_L,
         substring( A.zuonr,1,10 )             as EBELN_L,
         A.EBELP,
         A.HSL
    FROM ACDOCA AS A 
    INNER JOIN bsas AS B
      ON A.RBUKRS = B.BUKRS  AND A.gjahr  = B.gjahr
     AND A.belnr  = B.belnr  AND A.buzei  = B.buzei
     AND A.RCLNT  = B.MANDT
  WHERE A.RBUKRS = :I_BUKRS AND A.budat <= :I_BUDAT
     AND B.AUGDT  > :I_AUGDT
     AND A.RACCT LIKE '260%' AND A.RCLNT = :I_MANDT
  ),
   DT AS ( 
    SELECT A.rbukrs,
           A.gjahr,
           A.belnr,
           A.buzei,
           A.poper,
           A.fiscyearper,
           A.budat,
           A.racct,
           A.hsl,
           CASE WHEN ifnull(a.lifnr,'')<>'' THEN a.lifnr
                WHEN ifnull(e1.lifnr,'')<>'' THEN e1.lifnr
                WHEN ifnull(ek.lifnr,'')<>'' THEN ek.lifnr
                ELSE a.lifnr_l
           END AS lifnr
      FROM  ACD AS a
                INNER JOIN bkpf ON a.rbukrs = bkpf.bukrs
                               AND a.belnr = bkpf.belnr
                               AND a.gjahr = bkpf.gjahr
                               AND A.RCLNT = bkpf.MANDT
         LEFT JOIN acdoca AS e1 ON a.rbukrs = e1.rbukrs
                               AND a.belnr  = e1.belnr
                               AND a.gjahr  = e1.gjahr
                               AND ifnull(e1.lifnr,'')<>''
                               AND a.RCLNT  = e1.RCLNT
           LEFT JOIN ekko AS ek ON a.ebeln_l = ek.ebeln 
     WHERE A.RCLNT = :I_MANDT
   )
   SELECT distinct
           DT.rbukrs AS bukrs,
           DT.GJAHR,
           DT.BELNR,
           DT.BUZEI,
           DT.POPER,
           DT.BUDAT,
           DT.lifnr,
           DT.RACCT,
           DT.FISCYEARPER,
           DT.HSL
     FROM  DT
     ORDER BY  DT.rbukrs, DT.gjahr, DT.belnr, DT.buzei;

END;

调用测试：

call P_TEST01('1000','20210101','20210101','300',NULL);

测试数据后在SAP层写调用

    DATA(lv_mandt) = sy-mandt.
    DATA: ls_data TYPE ty_menge.

    DATA: lv_option TYPE string.
    DATA: lv_sts TYPE string.

    lv_option = ' CALL "P_TEST01" (  '.
    lv_sts = '  ' && i_bukrs && ' ,'.
    lv_option = lv_option && lv_sts.
    lv_sts = '  ' && i_budat && ' ,'.
    lv_option = lv_option && lv_sts.
    lv_sts = '  ' && i_augdt && ' ,'.
    lv_option = lv_option && lv_sts.
    lv_sts = '  ' && sy-mandt && ' ,'.
    lv_option = lv_option && lv_sts.
    lv_sts = ' NULL ) '.
    lv_option = lv_option && lv_sts.

    TRY.
        DATA(lo_stmt) = NEW cl_sql_statement(  ).

        DATA(lo_res) = lo_stmt->execute_query( statement = lv_option  ).
        lo_res->set_param_table( itab_ref = REF #( e_tab ) ).
        "read the result into the internal table
        lo_res->next_package( ).
      CATCH cx_sql_exception INTO DATA(lx_02).
        DATA(lv_exception) = | An exception occurred with SQL_MESSAGE = { lx_02->sql_message }|.
        e_message = lv_exception.
    ENDTRY.