SAP连接外部数据库后批量写入数据

原本的添加数据方式：

LOOP AT lt_mat INTO ls_mat.
  EXEC SQL.
    INSERT INTO SAPTEST (mtart,matnr) VALUES (:ls_mat-mtart, :ls_mat-matnr)
  ENDEXEC.
ENDLOOP.

利用SQL Server的变量后，改为批量方式：

 

注意DECLARE定义变量字符串上限8000，根据自己的语句长度来确定多少条数据进行一次性添加

 

*&---------------------------------------------------------------------*
*& Report ZPPRTEST
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZPPRTEST.

TYPES: BEGIN OF ty_mat,
         mtart TYPE mtart,
         matnr TYPE matnr,
       END OF ty_mat,
       BEGIN OF ty_msg,
         text TYPE string,
       END OF ty_msg.

DATA: p_connr TYPE dbcon-con_name VALUE 'PLMTEST',
      exc_ref TYPE REF TO cx_sy_native_sql_error,
      l_text  TYPE string.

DATA: lt_mat       TYPE TABLE OF ty_mat,
      lt_mat2      TYPE TABLE OF ty_mat,
      ls_mat       TYPE ty_mat,
      lt_msg       TYPE TABLE OF ty_msg,
      ls_msg       TYPE ty_msg,
      l_count      TYPE i,
      l_line       TYPE p DECIMALS 1,
      l_tabix_from TYPE sy-tabix,
      l_tabix_to   TYPE sy-tabix,
      l_sql        TYPE string.

*-->SAP取数
SELECT mtart matnr INTO TABLE lt_mat
          FROM mara
          UP TO 100 ROWS
          WHERE mtart = 'FERT'.

*-->计算内表行数
l_count = lines( lt_mat ).

*-->向上取整内表行数除以50（每50笔处理一次）
l_line = CEIL( l_count / 50 ).

EXEC    SQL.
  CONNECT TO :p_connr
ENDEXEC.

EXEC SQL.
  SET CONNECTION :p_connr
ENDEXEC.

IF sy-subrc = 0.
  DO l_line TIMES.
    "开始行
    l_tabix_from = l_tabix_to + 1.
    "结束行
    l_tabix_to = l_tabix_to + 50.

    APPEND LINES OF lt_mat FROM l_tabix_from TO l_tabix_to TO lt_mat2.

    l_sql = 'INSERT INTO SAPTEST (mtart,matnr) VALUES '.

    LOOP AT lt_mat2 INTO ls_mat.
      l_sql = l_sql && '(' && ''''.
      l_sql = l_sql && ls_mat-mtart && ''',''' && ls_mat-matnr.
      l_sql = l_sql && '''' && '),'.
    ENDLOOP.

    "去掉末尾 , 号
    DATA(len) = STRLEN( l_sql ).
    DATA(len2) = len - 1.
    l_sql = l_sql+0(len2).

    TRY.
      
        EXEC SQL.
          DECLARE @sql varchar(8000)

          SET @sql = :l_sql

          EXEC (@sql)
        ENDEXEC.
        "捕获异常
      CATCH cx_sy_native_sql_error INTO exc_ref.
        l_text = exc_ref->get_text( ).

    ENDTRY.

    IF NOT l_text IS INITIAL.
      "如果捕获到异常，记录日志，回滚
      EXEC SQL.
        rollback
      ENDEXEC.
      ls_msg-text = l_text.
      APPEND ls_msg TO lt_msg.
    ELSE.
      "如果无异常，提交插入数据
      EXEC SQL.
        commit
      ENDEXEC.
      ls_msg-text = '写入成功'.
      APPEND ls_msg TO lt_msg.
    ENDIF.

    "清除数据
    CLEAR: lt_mat2,l_sql,l_text.
  ENDDO.
ENDIF.

EXEC SQL.
  DISCONNECT :p_connr
ENDEXEC.

cl_demo_output=>write( lt_msg ).
cl_demo_output=>display(  ).