天津制作网站的公司电话,网站建设启示,优化推广排名网站教程,网站建设的未来作者 idan lian 如需转载备注出处
需求
虽然是做BW模块#xff0c;但是最近项目上种种#xff0c;都需要给ABAP人员或者前台用户提供能供他们使用的表#xff0c;就稍微研究了下ABAP和HANA的集成问题#xff0c;因为我们BW更擅长的还是HANA#xff0c;而且HANA的运行效…作者 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,
CASEWHENACCOUNTISNULLTHENELSEACCOUNTENDASACCOUNT,
ENTITY,
ENTITY_TEXT,
INTERCO,
INTERCO_TEXT,
AUDITTRAIL,
CATEGORY,
SCOPE,
RHCUR,
RWCUR,
TYPE,
MISC,
KUNNR,
KIDNO,
ZZYLIU6,
ZZYLIU11,
VTWEG,
CASEWHENFLOWISNULLTHENELSEFLOWENDASFLOW,
CASEWHENZHBHBISNULLTHENELSEZHBHBENDASZHBHB,
CASEWHENZLOGIC_CODEISNULLTHENELSEZLOGIC_CODEENDASZLOGIC_CODE,
CASEWHENZLOGIC_VALUEISNULLTHENELSEZLOGIC_VALUEENDASZLOGIC_VALUE,
RSTGR,
RFAREA,
DRCRK,
RASSC,
MATKL,
CASEWHENMATKL_HBISNULLTHENELSEMATKL_HBENDASMATKL_HB,
CASEWHENCODE_QD_DISNULLTHENELSECODE_QD_DENDASCODE_QD_D,
CASEWHENCODE_QD_EISNULLTHENELSECODE_QD_EENDASCODE_QD_E,
RACCT,
RMVCT,
CASEWHENLAND1ISNULLTHENELSELAND1ENDASLAND1,
CASEWHENREGIONISNULLTHENELSEREGIONENDASREGION,
ZZYLIU10,
MATNR,
CASEWHENMATNR_TEXTISNULLTHENELSEMATNR_TEXTENDASMATNR_TEXT,
BTTYPE,
ZDATE,
CASEWHENPSPIDISNULLTHENELSEPSPIDENDASPSPID,
CASEWHENAUFNRISNULLTHENELSEAUFNRENDASAUFNR,
CASEWHENZZYLIU9ISNULLTHENELSEZZYLIU9ENDASZZYLIU9,
CASEWHENATTRIBUTE1ISNULLTHENELSEATTRIBUTE1ENDASATTRIBUTE1,
CASEWHENATTRIBUTE2ISNULLTHENELSEATTRIBUTE2ENDASATTRIBUTE2,
CASEWHENATTRIBUTE3ISNULLTHENELSEATTRIBUTE3ENDASATTRIBUTE3,
CASEWHENHKONT_SRISNULLTHENELSEHKONT_SRENDASHKONT_SR,
CASEWHENHKONT_CBISNULLTHENELSEHKONT_CBENDASHKONT_CB,
ANLN1,
sum(HSL) ASHSL,
sum(WSL) ASWSL,
sum(MSL) ASMSL
FROM_SYS_BIC.ZP_DFYH_BW.ZP_LL/COPYOFZCV_DT_BPC
WHERERBUKRSIN(YH10,YH20,YH12)
GROUPBYRLDNR,
RLDNR,
RBUKRS,
GJAHR,
BELNR,
DOCLN,
TIME,
CASEWHENACCOUNTISNULLTHENELSEACCOUNTEND,
ENTITY,
ENTITY_TEXT,
INTERCO,
INTERCO_TEXT,
AUDITTRAIL,
CATEGORY,
SCOPE,
RHCUR,
RWCUR,
TYPE,
MISC,
KUNNR,
KIDNO,
ZZYLIU6,
ZZYLIU11,
VTWEG,
CASEWHENFLOWISNULLTHENELSEFLOWEND,
CASEWHENZHBHBISNULLTHENELSEZHBHBEND,
CASEWHENZLOGIC_CODEISNULLTHENELSEZLOGIC_CODEEND,
CASEWHENZLOGIC_VALUEISNULLTHENELSEZLOGIC_VALUEEND,
RSTGR,
RFAREA,
DRCRK,
RASSC,
MATKL,
CASEWHENMATKL_HBISNULLTHENELSEMATKL_HBEND,
CASEWHENCODE_QD_DISNULLTHENELSECODE_QD_DEND,
CASEWHENCODE_QD_EISNULLTHENELSECODE_QD_EEND,
RACCT,
RMVCT,
CASEWHENLAND1ISNULLTHENELSELAND1END,
CASEWHENREGIONISNULLTHENELSEREGIONEND,
ZZYLIU10,
MATNR,
CASEWHENMATNR_TEXTISNULLTHENELSEMATNR_TEXTEND,
BTTYPE,
ZDATE,
CASEWHENPSPIDISNULLTHENELSEPSPIDEND,
CASEWHENAUFNRISNULLTHENELSEAUFNREND,
CASEWHENZZYLIU9ISNULLTHENELSEZZYLIU9END,
CASEWHENATTRIBUTE1ISNULLTHENELSEATTRIBUTE1END,
CASEWHENATTRIBUTE2ISNULLTHENELSEATTRIBUTE2END,
CASEWHENATTRIBUTE3ISNULLTHENELSEATTRIBUTE3END,
CASEWHENHKONT_SRISNULLTHENELSEHKONT_SREND,
CASEWHENHKONT_CBISNULLTHENELSEHKONT_CBEND,
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_connectionget_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的完整语句我理解其实除了存储过程别的语句也可以执行比如增删改查或者一些赋值的代码 HANA的链接名称通过TCODE DBCO去查询 上面的程序创建后激活执行就可以使用了作为BW开发也可以把他放到处理链里去作业调度 更详细的参考链接如下开发者日志ABAP通过辅助数据库连接HANA | SAP Blogs
