ABAP与HANA集成 2:ABAP调用HANA存储过程或SQL语句

作者 idan lian 如需转载备注出处

需求

虽然是做BW模块，但是最近项目上种种，都需要给ABAP人员或者前台用户提供能供他们使用的表，就稍微研究了下ABAP和HANA的集成问题，因为我们BW更擅长的还是HANA，而且HANA的运行效率也会比ABAP层面快很多，这次举一个简单的需求，就是我在HANA有一段存储过程，是执行一段SELECT语句，并且把数据存到一个透明表，供ABAP开发去使用。

原理

通过标准的ABAP类去调用HANA的存储过程，在ABAP层面去触发执行HANA的存储过程，这个方法是一个同事告诉我的，特别感谢张同学。先说下原理，主要是用到一个标准类CL_SQL_CONNECTION下的方法GET_CONNECTION，我的理解就是用来连接数据库，并且调用数据库的语句。

类的代码如下：感兴趣的朋友可以再研究一下

具体步骤

需求是调用存储过程，那我们得先创建一段存储过程，直接在HANA对应的目录下执行语句，创建即可，上一段我的代码,就是很简单的把某个视图的数据插入到透明表中，这样abap就可以使用了

create procedure "SAPHANADB".ZBW_insert_ZCV_DT_BPCas
/********* Begin Procedure Script ************/
BEGIN
trunca tetable "SAPHANADB"."ZCV_DT_BPC";
insert into "SAPHANADB"."ZCV_DT_BPC"
SELECT
"RLDNR",
"RBUKRS",
"GJAHR",
"BELNR",
"DOCLN",
"TIME",
CASEWHEN"ACCOUNT"ISNULLTHEN''ELSE"ACCOUNT"ENDAS"ACCOUNT",
"ENTITY",
"ENTITY_TEXT",
"INTERCO",
"INTERCO_TEXT",
"AUDITTRAIL",
"CATEGORY",
"SCOPE",
"RHCUR",
"RWCUR",
"TYPE",
"MISC",
"KUNNR",
"KIDNO",
"ZZYLIU6",
"ZZYLIU11",
"VTWEG",
CASEWHEN"FLOW"ISNULLTHEN''ELSE"FLOW"ENDAS"FLOW",
CASEWHEN"ZHBHB"ISNULLTHEN''ELSE"ZHBHB"ENDAS"ZHBHB", 	
CASEWHEN"ZLOGIC_CODE"ISNULLTHEN''ELSE"ZLOGIC_CODE"ENDAS"ZLOGIC_CODE", 
CASEWHEN"ZLOGIC_VALUE"ISNULLTHEN''ELSE"ZLOGIC_VALUE"ENDAS"ZLOGIC_VALUE", 
"RSTGR",
"RFAREA",
"DRCRK",
"RASSC",
"MATKL",
CASEWHEN"MATKL_HB"ISNULLTHEN''ELSE"MATKL_HB"ENDAS"MATKL_HB",
CASEWHEN"CODE_QD_D"ISNULLTHEN''ELSE"CODE_QD_D"ENDAS"CODE_QD_D",
CASEWHEN"CODE_QD_E"ISNULLTHEN''ELSE"CODE_QD_E"ENDAS"CODE_QD_E",
"RACCT",
"RMVCT",
CASEWHEN"LAND1"ISNULLTHEN''ELSE"LAND1"ENDAS"LAND1",
CASEWHEN"REGION"ISNULLTHEN''ELSE"REGION"ENDAS"REGION",
"ZZYLIU10",
"MATNR",
CASEWHEN"MATNR_TEXT"ISNULLTHEN''ELSE"MATNR_TEXT"ENDAS"MATNR_TEXT",
"BTTYPE",
"ZDATE",
CASEWHEN"PSPID"ISNULLTHEN''ELSE"PSPID"ENDAS"PSPID",
CASEWHEN"AUFNR"ISNULLTHEN''ELSE"AUFNR"ENDAS"AUFNR",
CASEWHEN"ZZYLIU9"ISNULLTHEN''ELSE"ZZYLIU9"ENDAS"ZZYLIU9",
CASEWHEN"ATTRIBUTE1"ISNULLTHEN''ELSE"ATTRIBUTE1"ENDAS"ATTRIBUTE1",
CASEWHEN"ATTRIBUTE2"ISNULLTHEN''ELSE"ATTRIBUTE2"ENDAS"ATTRIBUTE2",
CASEWHEN"ATTRIBUTE3"ISNULLTHEN''ELSE"ATTRIBUTE3"ENDAS"ATTRIBUTE3",
CASEWHEN"HKONT_SR"ISNULLTHEN''ELSE"HKONT_SR"ENDAS"HKONT_SR",
CASEWHEN"HKONT_CB"ISNULLTHEN''ELSE"HKONT_CB"ENDAS"HKONT_CB",
"ANLN1",
sum("HSL") AS"HSL",
sum("WSL") AS"WSL",
sum("MSL") AS"MSL"
FROM"_SYS_BIC"."ZP_DFYH_BW.ZP_LL/COPYOFZCV_DT_BPC"
WHERE"RBUKRS"IN('YH10','YH20','YH12') 
GROUPBY"RLDNR",
"RLDNR",
"RBUKRS",
"GJAHR",
"BELNR",
"DOCLN",
"TIME",
CASEWHEN"ACCOUNT"ISNULLTHEN''ELSE"ACCOUNT"END,
"ENTITY",
"ENTITY_TEXT",
"INTERCO",
"INTERCO_TEXT",
"AUDITTRAIL",
"CATEGORY",
"SCOPE",
"RHCUR",
"RWCUR",
"TYPE",
"MISC",
"KUNNR",
"KIDNO",
"ZZYLIU6",
"ZZYLIU11",
"VTWEG",
CASEWHEN"FLOW"ISNULLTHEN''ELSE"FLOW"END,
CASEWHEN"ZHBHB"ISNULLTHEN''ELSE"ZHBHB"END, 	
CASEWHEN"ZLOGIC_CODE"ISNULLTHEN''ELSE"ZLOGIC_CODE"END, 
CASEWHEN"ZLOGIC_VALUE"ISNULLTHEN''ELSE"ZLOGIC_VALUE"END, 
"RSTGR",
"RFAREA",
"DRCRK",
"RASSC",
"MATKL",
CASEWHEN"MATKL_HB"ISNULLTHEN''ELSE"MATKL_HB"END,
CASEWHEN"CODE_QD_D"ISNULLTHEN''ELSE"CODE_QD_D"END,
CASEWHEN"CODE_QD_E"ISNULLTHEN''ELSE"CODE_QD_E"END,
"RACCT",
"RMVCT",
CASEWHEN"LAND1"ISNULLTHEN''ELSE"LAND1"END,
CASEWHEN"REGION"ISNULLTHEN''ELSE"REGION"END,
"ZZYLIU10",
"MATNR",
CASEWHEN"MATNR_TEXT"ISNULLTHEN''ELSE"MATNR_TEXT"END,
"BTTYPE",
"ZDATE",
CASEWHEN"PSPID"ISNULLTHEN''ELSE"PSPID"END,
CASEWHEN"AUFNR"ISNULLTHEN''ELSE"AUFNR"END,
CASEWHEN"ZZYLIU9"ISNULLTHEN''ELSE"ZZYLIU9"END,
CASEWHEN"ATTRIBUTE1"ISNULLTHEN''ELSE"ATTRIBUTE1"END,
CASEWHEN"ATTRIBUTE2"ISNULLTHEN''ELSE"ATTRIBUTE2"END,
CASEWHEN"ATTRIBUTE3"ISNULLTHEN''ELSE"ATTRIBUTE3"END,
CASEWHEN"HKONT_SR"ISNULLTHEN''ELSE"HKONT_SR"END,
CASEWHEN"HKONT_CB"ISNULLTHEN''ELSE"HKONT_CB"END,
"ANLN1";
END/********* End Procedure Script ************/

创建好存储过程后，要把技术名称copy下来，后面需要使用

接下来在abap层面，也就是GUI中SE38创建程序，直接copy下面这段代码即可，需要替换的地方我后面会标注出来

REPORT ZBW_EXECUTE_HANA_PROC.
PARAMETERS:ZVARIANT(2550) TYPE C. "存储过程
DATA remote_exception  TYPE REF TO CX_SFW_REMOTE_ERROR.
DATA msg TYPE c LENGTH 255.
DATA:ZRESULT(10) TYPE C.
TYPES:
     BEGIN OF result_t,
        key TYPE i,
        value TYPE string,
     END OF result_t.

DATA: stmt_ref TYPE REF TO cl_sql_statement,
      cx_sql_exception TYPE REF TO cx_sql_exception,
      lv_text TYPE string,
      res_ref TYPE REF TO cl_sql_result_set,
      d_ref TYPE REF TO DATA,
      result_tab TYPE TABLE OF result_t,
      result_line TYPE result_t,
      row_cnt TYPE i,
      con_ref TYPE REF TO cl_sql_connection.

TRY.
        con_ref = cl_sql_connection=>get_connection( 'DBMS_USER_MGT' ).
        stmt_ref = con_ref->create_statement( ).

         CONCATENATE 'CALL  ' ZVARIANT ' ()' into  lv_text .

*         lv_text = 'CALL  "CRRC"."CRRC.PRO::SP_CRRC_D" ()'.
         stmt_ref = con_ref->create_statement( ).
         res_ref = stmt_ref->execute_query( lv_text ).
         con_ref->COMMIT( ).

  CATCH CX_SFW_REMOTE_ERROR INTO remote_exception.
    msg = remote_exception->get_text( ).
    WRITE / msg.
ENDTRY.

下面是需要更改的地方，其实主要就是HANA的链接名称,以及存储过程的技术名称，LV_TEXT中，其实就是HANA中调用存储过程的语句，LV_TEXT这里其实就是HANA CALL FOUNCION的完整语句，我理解其实除了存储过程，别的语句也可以执行，比如增删改查，或者一些赋值的代码