原本的添加数据方式:
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( ).