vue访问完整外部链接数据_SAP 链接外部数据库操作 NATIVE SQL

1. 数据库定义:

DATA:g_conexion LIKE dbcon-con_name VALUE 'mid'.

DATA:exec_ref TYPE REF TO cx_sy_native_sql_error.

2.连接数据库:

代码：

PERFORM frm_database_connect USING lv_msgtx. "连接数据库先注释

FORM frm_database_connect USING p_lv_msgtx.

TRY.

"-- 连接SQL SERVER

EXEC SQL.

CONNECT TO :G_CONEXION

ENDEXEC.

EXEC SQL.

SET CONNECTION :G_CONEXION

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exec_ref.

p_lv_msgtx = exec_ref->get_text( ).

CONCATENATE '无法连接至SQL数据库 连接名:' g_conexion p_lv_msgtx INTO p_lv_msgtx.

ENDTRY.

ENDFORM.

3. 读取对方数据库判断时新增还是修改：

代码：

LOOP AT gt_header INTO gs_header.

PERFORM frm_database_get USING gs_header. "数据库操作先注释

CLEAR:gs_header.

ENDLOOP.

FORM frm_database_get USING p_gs_header TYPE gty_header.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = p_gs_header-vbeln

IMPORTING

output = p_gs_header-vbeln

.

TRY.

EXEC SQL PERFORMING loop_output.

SELECT djbh

INTO :gs_sum-vbeln

FROM YW_DDHZ

WHERE djbh = :p_gs_header-vbeln

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exec_ref.

CLEAR:gs_out-msgtx.

gs_out-msgtx = exec_ref->get_text( ).

gs_out-traffic_lights = c_icon_red_light.

CONCATENATE '读取表失败：' gs_out-msgtx INTO gs_out-msgtx.

READ TABLE gt_out INTO gs_out WITH KEY vbeln = p_gs_header-vbeln.

IF sy-suBrc EQ 0.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = p_gs_header-vbeln

IMPORTING

output = p_gs_header-vbeln

.

MODIFY gt_OUT FROM gs_out TRANSPORTING msgtx t

FORM loop_output .

APPEND gs_sum TO gt_sum.CLEAR: gs_sum.

ENDFORM.

ENDIF.

ENDTRY.

ENDFORM.

M.

FORM loop_output .

APPEND gs_sum TO gt_sum.CLEAR: gs_sum.

ENDFORM.

4.判断是新增还是修改：如果gt_sum有值则为修改，如果没有则为新增。

PERFORM frm_database_update USING gs_header. "数据库操作先注释

FORM frm_database_update USING p_gs_header TYPE gty_header.

DATA: zWADAT TYPE char10.

"传入结算系统的状态

DATA: lv_lifsk TYPE likp-lifsk.

lv_lifsk = '20'.

DATA:lv_time TYPE char8 VALUE '00:00:00'.

DATA:zbmid TYPE string VALUE ''.

DATA:Zywy TYPE string VALUE ''.

DATA:zusername TYPE string VALUE ''.

DATA:zzhiyid TYPE string VALUE ''.

DATA:zhshgje TYPE p VALUE 0.

DATA:zhuikuanfs TYPE string VALUE ''.

DATA:zshe TYPE p VALUE 0.

DATA:zerp_tq TYPE string VALUE ''.

DATA:zkaipiaodjbh TYPE string VALUE ''.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = p_gs_header-vbeln

IMPORTING

output = p_gs_header-vbeln

.

zWADAT = p_gs_header-wadat+0(4) && '-' && p_gs_header-wadat+4(2) && '-' && p_gs_header-wadat+6(2).

"处理姓名：

DATA: lv_department TYPE ad_dprtmnt,

lv_addrnumber TYPE ad_addrnum,

lv_persnumber TYPE ad_persnum,

lv_uname TYPE sy-uname,

lv_name_first TYPE ad_namefir,

lv_name_last TYPE ad_namelas,

lv_name TYPE char20.

SELECT SINGLE persnumber addrnumber INTO

(lv_persnumber, lv_addrnumber)

FROM usr21

WHERE bname = p_gs_header-ernam.

IF sy-subrc = 0.

SELECT SINGLE name_first name_last INTO (lv_name_first,lv_name_last)

FROM adrp

WHERE persnumber = lv_persnumber.

IF sy-subrc = 0.

CONCATENATE lv_name_last lv_name_first INTO lv_name.

CONDENSE lv_name NO-GAPS.

ENDIF.

ENDIF.

TRY .

EXEC SQL.

UPDATE YW_DDHZ

SET djbh = :p_gs_header-VBELN,

djbs = :p_gs_header-Z_djbs,

ontime = :LV_TIME,

bmid = :zbmid,

ywy = :zywy,

USERNAME = :ZUSERNAME,

ZHIYID = :zZHIYID,

MX_COUNT = :LV_SUM,

HSHGJE = :ZHSHGJE,

HUIKUANFS = :ZHUIKUANFS,

SHE = :ZSHE,

ERP_TQ = :ZERP_TQ,

KAIPIAODJBH = :ZKAIPIAODJBH,

rq = :ZWADAT,

wldwid = :p_gs_header-KUNAG,

fhdusername = :LV_NAME,

jigid = :p_gs_header-Z_jigid,

bmname = :p_gs_header-ZZVKBUR,

djlx = :p_gs_header-Z_djlx,

ddusername = :p_gs_header-ZERNAM,

quyufl = :p_gs_header-ZBZIRK,

shengfen = :p_gs_header-ZVKGRP,

fukuanfs = :p_gs_header-ZKONDA,

ywyid = :p_gs_header-KUNNR,

beizhu = :p_gs_header-TEXT_0001,

xinyye = :p_gs_header-UKM_NET,

xgdjbh = :p_gs_header-BSTKD,

shenhe = :LV_LIFSK

WHERE djbh = :p_gs_header-VBELN

ENDEXEC.

IF sy-subrc = 0.

Gs_out-traffic_lights = c_icon_green_light.

Gs_out-msgtx = '处理成功！'.

"将字段更新到SAP 系统

ELSEIF sy-subrc <> 0.

Gs_out-traffic_lights = c_icon_red_light.

Gs_out-msgtx = '更新抬头表YW_DDHZ失败！'.

ENDIF.

CATCH cx_sy_native_sql_error INTO exec_ref.

CLEAR:Gs_out-msgtx.

Gs_out-msgtx = exec_ref->get_text( ).

Gs_out-traffic_lights = c_icon_red_light.

CONCATENATE '更新抬头表YW_DDHZ表失败：' Gs_out-msgtx INTO Gs_out-msgtx.

ENDTRY.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = p_gs_header-vbeln

IMPORTING

output = p_gs_header-vbeln

.

MODIFY gt_out FROM gs_out TRANSPORTING msgtx traffic_lights WHERE vbeln = p_gs_header-vbeln.

CLEAR:lv_lifsk,zWADAT,lv_name,lv_persnumber,lv_addrnumber,lv_name_last,lv_name_last,lv_time,zbmid,zywy,zusername,zZHIYID,lv_sum

,zhshgje,zhuikuanf

FORM frm_database_insert USING p_gs_header TYPE gty_header.

DATA: zWADAT TYPE char10.

"传入结算系统的状态

DATA: lv_lifsk TYPE likp-lifsk.

lv_lifsk = '20'.

DATA:lv_time TYPE char8 VALUE '00:00:00'.

DATA:zbmid TYPE string VALUE ''.

DATA:zywy TYPE string VALUE ''.

DATA:zusername TYPE string VALUE ''.

DATA:zZHIYID TYPE string VALUE ''.

DATA:zhshgje TYPE p VALUE 0.

DATA:zhuikuanfs TYPE string VALUE ''.

DATA:zshe TYPE p VALUE 0.

DATA:zerp_tq TYPE string VALUE ''.

DATA:zkaipiaodjbh TYPE string VALUE ''.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = p_gs_header-vbeln

IMPORTING

output = p_gs_header-vbeln

.

zWADAT = p_gs_header-wadat+0(4) && '-' && p_gs_header-wadat+4(2) && '-' && p_gs_header-wadat+6(2).

TRY .

EXEC SQL.

INSERT INTO YW_DDHZ

(djbh,

djbs,

ontime,

bmid,

ywy,

USERNAME,

ZHIYID,

MX_COUNT,

HSHGJE,

HUIKUANFS,

SHE,

ERP_TQ,

KAIPIAODJBH,

rq,

wldwid,

fhdusername,

jigid,

bmname,

djlx,

ddusername,

quyufl,

shengfen,

fukuanfs,

ywyid,

beizhu,

xinyye,

xgdjbh,

shenhe

)

VALUES

(:p_gs_header-VBELN,

:p_gs_header-Z_djbs,

:LV_TIME,

:zbmid,

:zywy,

:zUSERNAME,

:zZHIYID,

:LV_SUM,

:ZHSHGJE,

:ZHUIKUANFS,

:ZSHE,

:ZERP_TQ,

:ZKAIPIAODJBH,

:ZWADAT,

:p_gs_header-KUNAG,

:p_gs_header-ERNAM,

:p_gs_header-Z_jigid,

:p_gs_header-ZZVKBUR,

:p_gs_header-Z_djlx,

:p_gs_header-ZERNAM,

:p_gs_header-ZBZIRK,

:p_gs_header-ZVKGRP,

:p_gs_header-ZKONDA,

:p_gs_header-KUNNR,

:p_gs_header-TEXT_0001,

:p_gs_header-UKM_NET,

:p_gs_header-BSTKD,

:LV_LIFSK

)

ENDEXEC.

IF sy-subrc = 0.

Gs_out-traffic_lights = c_icon_green_light.

Gs_out-msgtx = '处理成功！'.

ELSEIF sy-subrc <> 0.

Gs_out-traffic_lights = c_icon_red_light.

Gs_out-msgtx = '插入抬头表YW_DDHZ失败！'.

ENDIF.

CATCH cx_sy_native_sql_error INTO exec_ref.

CLEAR:Gs_out-msgtx.

Gs_out-msgtx = exec_ref->get_text( ).

Gs_out-traffic_lights = c_icon_red_light.

CONCATENATE '插入抬头表YW_DDHZ表失败：' Gs_out-msgtx INTO Gs_out-msgtx.

ENDTRY.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = p_gs_header-vbeln

IMPORTING

output = p_gs_header-vbeln

.

MODIFY gt_out FROM gs_out TRANSPORTING msgtx traffic_lights WHERE vbeln = p_gs_header-vbeln.

CLEAR:zWADAT,lv_lifsk,lv_time,zbmid,zywy,zUSERNAME,zZHIYID,lv_sum,zhshgje,zhuikuanfs,zshe,

zerp_tq,zkaipiaodjbh.

ENDFORM.

sk,lv_time,zbmid,zywy,zUSERNAME,zZHIYID,lv_sum,zhshgje,zhuikuanfs,zshe,

zerp_tq,zkaipiaodjbh.

ENDFORM.

5.commit数据库：

FORM frm_database_commit USING p_lv_msgtx.

TRY .

EXEC SQL.

COMMIT

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exec_ref.

p_lv_msgtx = exec_ref->get_text( ).

CONCATENATE 'SQL数据库COMMIT失败：' g_conexion p_lv_msgtx INTO p_lv_msgtx.

ENDTRY.

ENDFORM.

6. 关闭数据库：

PERFORM frm_database_close USING lv_msgtx. "数据库操作先注释

FORM frm_database_close USING p_lv_msgtx.

TRY .

EXEC SQL.

DISCONNECT :G_CONEXION

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exec_ref.

p_lv_msgtx = exec_ref->get_text( ).

ENDTRY.

ENDFORM.

7.删除的语法:

FORM frm_database_delete_item .

TYPES: BEGIN OF gty_vbeln,

vbeln TYPE lips-vbeln,

END OF gty_vbeln.

DATA:gt_vbeln_temp TYPE TABLE OF gty_vbeln,

gs_vbeln_temp TYPE gty_vbeln.

MOVE-CORRESPONDING gt_sum1 TO gt_vbeln_temp.

SORT gt_vbeln_temp.DELETE ADJACENT DUPLICATES FROM gt_vbeln_temp COMPARING ALL FIELDS.

"--------------------------------------------------------------------------------删除

READ TABLE gt_vbeln_temp INTO gs_vbeln_temp INDEX 1.

IF sy-subrc EQ 0.

TRY .

EXEC SQL.

DELETE YW_DDMX

WHERE djbh = :gs_vbeln_temp-vbeln

ENDEXEC.

IF sy-subrc = 0.

Gs_out-traffic_lights = c_icon_green_light.

Gs_out-msgtx = '处理成功！'.

"将字段更新到SAP 系统

ELSEIF sy-subrc <> 0.

Gs_out-traffic_lights = c_icon_red_light.

Gs_out-msgtx = '删除行项目YW_DDMX失败！'.

ENDIF.

------------------------------------------------------------------------------------------------

作者：杨斌

日期：20200817

创作不易转载请备注作者，谢谢

------------------------------------------------------------------------------------------------