abap动态写入中间库数据

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

一些项目需要向第三方数据库写入数据

一、配置数据库链接

使用DB02配置数据链接，注意叫basis装驱动

二、创建函数

1.ZFMALL_UPDATE_DB

代码如下（示例）：

FUNCTION zfmall_update_db.
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     VALUE(PROGNAME) TYPE  PROGNAME OPTIONAL
*"     VALUE(I_TABLE) TYPE  STRING
*"     VALUE(CONNR) TYPE  DBCON-CON_NAME
*"  EXPORTING
*"     VALUE(E_RETURN) TYPE  ZSRETURN
*"  TABLES
*"      T_OUTTAB
*"      T_FIEL STRUCTURE  LVC_S_FCAT
*"----------------------------------------------------------------------
  DATA:num TYPE i.
  DATA: lr_dbconn     TYPE REF TO cl_sql_connection,
*        LV_CON        TYPE DBCON_NAME,
        lr_sql_env    TYPE REF TO cl_sql_statement,
        lr_sql_result TYPE  i,
*        lr_sql_result TYPE  i,
        lr_sql_exc    TYPE REF TO cx_sql_exception,         "#EC NEEDED
        lr_par_exc    TYPE REF TO cx_parameter_invalid.     "#EC NEEDED
*        DREF          TYPE REF TO DATA,
*        TAB_REF       TYPE REF TO DATA,
*        GT_COMPONENTS TYPE CL_ABAP_STRUCTDESCR=>COMPONENT_TABLE.
*        GS_COMPONENTS LIKE LINE OF GT_COMPONENTS.
  DATA:lv_str TYPE string.
  DATA:lv_ins TYPE string.
  DATA:lv_ins2 TYPE string.
*  DATA LV_EXISTS  TYPE ABAP_BOOL.
  DATA lv_result TYPE string.
  DATA lv_value TYPE string.
  CHECK t_fiel[] IS NOT INITIAL.
  CHECK t_outtab[] IS NOT INITIAL.
  num = lines( t_fiel ).
*  SELECT SINGLE con_name INTO lv_con FROM zdbt001 WHERE progname = progname.
*  IF sy-subrc NE 0.
*    e_return-type = 'E'.
*    e_return-msg = '请维护数据库连接数据'.
*    RETURN.
*  ENDIF.

  TRY.
      lr_dbconn = cl_sql_connection=>get_connection( con_name = connr ).
    CATCH cx_sql_exception INTO DATA(lr_sql).
      DATA(str) = lr_sql->get_text( ).

  ENDTRY.
  CREATE OBJECT lr_sql_env
    EXPORTING
      con_ref = lr_dbconn.
  DATA(tt_fiel) = t_fiel[].
  DELETE t_fiel[] WHERE fieldname = 'ICON'.
  DELETE t_fiel[] WHERE fieldname = 'MSG'.

  LOOP AT t_outtab ASSIGNING FIELD-SYMBOL(<fs>).
    CLEAR:e_return.
    IF str IS INITIAL.
      lv_str =  |DELETE { i_table } | & | WHERE |.
      lv_ins =  |INSERT INTO { i_table } | & | (|.
      lv_ins2 = | VALUES (|.
      LOOP AT t_fiel INTO DATA(s_fiel) .
        ASSIGN COMPONENT s_fiel-fieldname OF STRUCTURE <fs> TO FIELD-SYMBOL(<fs_key>).
        "更新数据库 INSERT
        lv_ins = lv_ins && |{ s_fiel-fieldname }| && ','.

        lv_value = <fs_key>.

        IF lv_value IS NOT INITIAL.
          REPLACE ALL OCCURRENCES OF `'` IN lv_value WITH `''`.
          IF s_fiel-currency NE 'X'.“注意currency = ’X' 可能导致前台ALV金额缩小或扩大10倍，可以其他字段替代判断
            lv_value = |'{ lv_value }'|.
          ELSE.
            CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
              CHANGING
                value = lv_value.
          ENDIF.

        ELSE.
          lv_value = 'NULL'.
        ENDIF.

        lv_ins2 = |{ lv_ins2 } { lv_value },|.


*        LV_INS2 = |{ LV_INS2 } | && `'` && <FS_KEY> && `'`  &&  ','.


        IF s_fiel-key = abap_true.
          "更新数据库 DELETE
          lv_str = lv_str && | { s_fiel-fieldname } = { lv_value } AND |.
        ENDIF.
      ENDLOOP.
      "更新数据库 INSERT
      CLEAR:num.
      DATA(sap_transdate) = sy-datum && sy-uzeit.
      lv_ins = lv_ins && 'SAP_TRANSDATE,'.
      lv_ins2 = lv_ins2 && `'` && sap_transdate && `',`.
      CLEAR:sap_transdate.
      num = strlen( lv_ins ).
      num = num - 1.
      lv_ins = lv_ins(num).
      lv_ins = lv_ins && ')'.
      CLEAR:num.
      num = strlen( lv_ins2 ).
      num = num - 1.
      lv_ins2 = lv_ins2(num).
      lv_ins2 = lv_ins2 && ')'.
*    "更新数据库 UPDATE
*    CLEAR:num.
*    num = strlen( lv_str ).
*    num = num - 4.
*    lv_str = lv_str(num).
*    lv_str = lv_str && ' WHERE'.

*      LOOP AT T_FIEL INTO S_FIEL WHERE KEY = 'X'.
*
*        ASSIGN COMPONENT S_FIEL-FIELDNAME OF STRUCTURE <FS> TO <FS_KEY>.
*        "更新数据库 DELETE
*        LV_STR = LV_STR && | { S_FIEL-FIELDNAME }|  && ' = ' && ` '` &&  <FS_KEY> && `'` && ' AND '.
*      ENDLOOP.
      num = strlen( lv_str ).
      num = num - 4.
      lv_str = lv_str(num).
      "  lv_str = lv_str && ';'.

*      REPLACE ALL OCCURRENCES OF `''` IN LV_STR WITH 'NULL'.
      TRY.
          lr_sql_result = lr_sql_env->execute_update( lv_str ).

        CATCH cx_sql_exception INTO DATA(lx_sql_exception).
          lv_result = lx_sql_exception->get_text( ).
          e_return-type = 'E'.
          e_return-msg = lv_result."'SQL错误请检查'.
*        RETURN.
        CATCH cx_parameter_invalid INTO DATA(lx_parameter_invalid).
          lv_result = lx_sql_exception->get_text( ).
          e_return-type = 'E'.
          e_return-msg = lv_result."'数据库错误'.
*        RETURN.
      ENDTRY.
      COMMIT WORK AND WAIT.
      lv_ins = lv_ins && lv_ins2 ."&& ';'.
*      REPLACE ALL OCCURRENCES OF `''` IN LV_INS WITH 'NULL'.
      TRY.
          lr_sql_result = lr_sql_env->execute_update( lv_ins ).

        CATCH cx_sql_exception INTO lx_sql_exception.
          lv_result = lx_sql_exception->get_text( ).
          e_return-type = 'E'.
          e_return-msg = lv_result." 'SQL错误请检查'.
        CATCH cx_parameter_invalid INTO lx_parameter_invalid.
          lv_result = lx_sql_exception->get_text( ).
          e_return-type = 'E'.
          e_return-msg =  lv_result. "'数据库错误'.
*        RETURN.
      ENDTRY.
    ELSE.
      e_return-type = 'E'.
      e_return-msg = str." 'SQL错误请检查'.
    ENDIF.
*    COMMIT WORK AND WAIT.
    IF e_return-type NE 'E'.
      COMMIT WORK .
      ASSIGN COMPONENT 'ICON' OF STRUCTURE <fs> TO FIELD-SYMBOL(<icon>).
      IF sy-subrc = 0.
        <icon> =  icon_green_light.
      ENDIF.
      ASSIGN COMPONENT 'MSG' OF STRUCTURE <fs> TO FIELD-SYMBOL(<msgtx>).
      IF sy-subrc = 0.
        <msgtx>  =  '推送成功'.
      ENDIF.
    ELSE.
      ASSIGN COMPONENT 'ICON' OF STRUCTURE <fs> TO <icon>.
      IF sy-subrc = 0.
        <icon> =  icon_red_light.
      ENDIF.
      ASSIGN COMPONENT 'MSG' OF STRUCTURE <fs> TO <msgtx>.
      IF sy-subrc = 0.
        <msgtx> = e_return-msg.
      ENDIF.
      ROLLBACK WORK .
    ENDIF.

  ENDLOOP.
*  IF e_return-type NE 'E'.
*    COMMIT WORK AND WAIT.
*  ENDIF.



  t_fiel[] = tt_fiel[].

  IF lr_dbconn IS NOT INITIAL.
    TRY.
        CALL METHOD lr_dbconn->close.
      CATCH cx_sql_exception.
    ENDTRY.
  ENDIF.
ENDFUNCTION.                                             "#EC CI_VALPAR

2.使用示例

总结

只测试过oracle数据库，其他数据库可能并不通用