以导出多表数据为例看ABAP如何逐步优化

最新推荐文章于 2024-09-15 11:40:06 发布

SAP_森林木

最新推荐文章于 2024-09-15 11:40:06 发布

阅读量36

点赞数

文章标签： c++ 数据库开发语言

原文链接：https://mp.weixin.qq.com/s?__biz=MzU1MDAzMDY3MQ==&mid=2247495590&idx=1&sn=34210336bb48f059e61ea620881fd618&chksm=fa8064aee4cf9d1aed3e71d8a0d19d3963dd0bb2f359999dccd160a99a1f674747fcbc9f1c79&scene=126&sessionid=0

版权

问：老师，我要导出SAP的好多个表的全部表内容，请问有没有合适的程序给我用用呢？

答：这个程序很简单，随便写一下就行了：

REPORT zexport_table NO STANDARD PAGE HEADING.


TABLES dd02l.
DATA: gt_tabs  TYPE TABLE OF tabname WITH HEADER LINE.
DATA: go_table TYPE REF TO data.
DATA: gv_lfile TYPE string.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE.


SELECT-OPTIONS s_tabnam FOR dd02l-tabname.


START-OF-SELECTION.
  SELECT tabname INTO TABLE gt_tabs FROM dd02l
    WHERE tabname  IN s_tabnam AND
          as4local EQ 'A'      AND
          tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
  LOOP AT gt_tabs.
    CREATE DATA go_table TYPE TABLE OF (gt_tabs).
    ASSIGN go_table->* TO <gt_table>.


    SELECT * FROM (gt_tabs) INTO TABLE <gt_table>.
    CHECK sy-subrc = 0.


    gv_lfile = 'D:\tmp\' && gt_tabs && '.txt'.
    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        filename          = gv_lfile
        filetype          = 'DAT'
        confirm_overwrite = ' '
      TABLES
        data_tab          = <gt_table>
      EXCEPTIONS
        OTHERS            = 22.
    IF sy-subrc NE 0.
      MESSAGE i000(oo) WITH '文件下载错误：' gt_tabs.
    ENDIF.
  ENDLOOP.

问：太好了！小小的代码大大的能量，果然导出来了。可是导出的文件没有列名呢，对数非常不方便啊！
答：那就加上字段名作为列名吧，除了程序中用的的方法，也可以在表DD03L中取列名。

TABLES dd02l.
DATA: gt_tabs   TYPE TABLE OF tabname WITH HEADER LINE.
DATA: go_table  TYPE REF TO data.
DATA: gv_lfile  TYPE string.
DATA: go_tdescr TYPE REF TO cl_abap_tabledescr,
      go_sdescr TYPE REF TO cl_abap_structdescr,
      gs_comps  LIKE LINE OF cl_abap_structdescr=>components,
      gt_field  TYPE TABLE OF dd03l.


FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE.


SELECT-OPTIONS s_tabnam FOR dd02l-tabname.


START-OF-SELECTION.
  SELECT tabname INTO TABLE gt_tabs FROM dd02l
    WHERE tabname  IN s_tabnam AND
          as4local EQ 'A'      AND
          tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
  LOOP AT gt_tabs.
    CREATE DATA go_table TYPE TABLE OF (gt_tabs).
    ASSIGN go_table->* TO <gt_table>.


    SELECT * FROM (gt_tabs) INTO TABLE <gt_table>.
    CHECK sy-subrc EQ 0.


    CLEAR gt_field[].
    go_tdescr ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
    go_sdescr ?= go_tdescr->get_table_line_type( ).
    LOOP AT go_sdescr->components INTO gs_comps.
      APPEND gs_comps-name TO gt_field.
    ENDLOOP.


    gv_lfile = 'D:\tmp\' && gt_tabs && '.txt'.
    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        filename          = gv_lfile
        filetype          = 'DAT'
        confirm_overwrite = ' '
      TABLES
        data_tab          = <gt_table>
        fieldnames        = gt_field
      EXCEPTIONS
        OTHERS            = 22.
    IF sy-subrc NE 0.
      MESSAGE i000(oo) WITH '文件下载错误：' gt_tabs.
    ENDIF.
  ENDLOOP.

问：太好啦！可是这种前台导出太慢了，导出一个几十万数据的表都要好久呢，等的花儿都谢了！
答：确实是这样，谁知道你要导出这么大的表呢！前台用GUI_DOWNLOAD导出数据性能是比较差的，我给你改成后台运行吧，然后把数据存到服务器上，再从服务器下载就非常非常快了。程序执行完在JOB日志可以看的文件名，然后可以用CG3Y的BIN格式下载，或者写一个程序下载。文件放到应用服务器的DIR_HOME目录哦，用AL11可以看到存放的文件。

TABLES dd02l.
DATA: gt_tabs  TYPE TABLE OF tabname WITH HEADER LINE.
DATA: go_table TYPE REF TO data.
DATA: gv_line  TYPE string.
DATA: gv_afile TYPE string.
DATA: go_tdesc TYPE REF TO cl_abap_tabledescr,
      go_sdesc TYPE REF TO cl_abap_structdescr,
      gs_comps LIKE LINE OF cl_abap_structdescr=>components.


FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE,<gs_table>,<gv_fld>.


SELECT-OPTIONS s_tabnam FOR dd02l-tabname.


START-OF-SELECTION.
  SELECT tabname INTO TABLE gt_tabs FROM dd02l
    WHERE tabname  IN s_tabnam AND
          as4local EQ 'A'      AND
          tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
  LOOP AT gt_tabs.
    CREATE DATA go_table TYPE TABLE OF (gt_tabs).
    ASSIGN go_table->* TO <gt_table>.


    SELECT * FROM (gt_tabs) INTO TABLE <gt_table>.
    CHECK sy-subrc EQ 0.


    CLEAR gv_line.
    go_tdesc ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
    go_sdesc ?= go_tdesc->get_table_line_type( ).
    LOOP AT go_sdesc->components INTO gs_comps.
      gv_line = gv_line && %_horizontal_tab && gs_comps-name.
    ENDLOOP.
    SHIFT gv_line.


    gv_afile = gt_tabs && '_' && '_' && sy-dbcnt && '.TXT'.
    OPEN DATASET gv_afile FOR OUTPUT IN TEXT MODE
                         ENCODING UTF-8 WITH WINDOWS LINEFEED.
    TRANSFER gv_line TO gv_afile.


    LOOP AT <gt_table> ASSIGNING <gs_table>.
      CLEAR gv_line.
      DO.
        ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <gv_fld>.
        IF sy-subrc NE 0.
          EXIT.
        ENDIF.


        gv_line = gv_line && %_horizontal_tab.
        gv_line = gv_line && <gv_fld>.
      ENDDO.
      SHIFT gv_line.
      TRANSFER gv_line TO gv_afile.
    ENDLOOP.
    CLOSE DATASET gv_afile.
    MESSAGE s000(oo) WITH gv_afile.
  ENDLOOP.

问：太棒了老师！运行快多了。可是我在下载一个超大表的时候程序崩掉了呢，数据量太大了，把内表撑爆了！

答：你都是导出啥表啊竟然能撑爆内表！到目前为止呢，我们都是一次性取出数据库表的数据到内表，如果数据量非常大，超过了系统参数所限定的内存大小就DUMP了。这样吧，我们取数据改为使用游标的方式，每次取出来100000条数据，等保存完后再继续取下一个100000条，如此往复，就不怕内表撑爆了。另外，我知道你还会遇到另外的问题，所以咱们先把文件在程序内转成XSTRING再存放，至于为啥等你遇到下一个问题就知道啦

TABLES dd02l.
DATA gt_tabs   TYPE TABLE OF tabname WITH HEADER LINE.
DATA go_table  TYPE REF TO data.
DATA gv_cursor TYPE cursor.
DATA gv_afile  TYPE string.
DATA gv_header TYPE string.
DATA gv_line   TYPE string.
DATA gv_lines  TYPE string.
DATA gv_xstr   TYPE xstring.
DATA go_tdesc  TYPE REF TO cl_abap_tabledescr.
DATA go_sdesc  TYPE REF TO cl_abap_structdescr.
DATA gs_comps  LIKE LINE OF cl_abap_structdescr=>components.


FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE,<gs_table>,<gv_fld>.


SELECT-OPTIONS s_tabnam FOR dd02l-tabname.


START-OF-SELECTION.
  SELECT tabname INTO TABLE gt_tabs FROM dd02l
    WHERE tabname  IN s_tabnam AND
          as4local EQ 'A'      AND
          tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
  LOOP AT gt_tabs.
    CREATE DATA go_table TYPE TABLE OF (gt_tabs).
    ASSIGN go_table->* TO <gt_table>.


    CLEAR gv_header.
    go_tdesc ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
    go_sdesc ?= go_tdesc->get_table_line_type( ).
    LOOP AT go_sdesc->components INTO gs_comps.
      gv_header = gv_header && %_horizontal_tab && gs_comps-name.
    ENDLOOP.
    SHIFT gv_header.


    OPEN CURSOR gv_cursor FOR SELECT * FROM (gt_tabs).
    DO.
      FETCH NEXT CURSOR gv_cursor INTO TABLE <gt_table> PACKAGE SIZE 100000.
      IF sy-subrc NE 0. EXIT. ENDIF.


      gv_afile = gt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.TXT'.


      OPEN DATASET gv_afile FOR OUTPUT IN BINARY MODE.
      gv_lines = gv_header && %_cr_lf.
      LOOP AT <gt_table> ASSIGNING <gs_table>.
        CLEAR gv_line.
        DO.
          ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <gv_fld>.
          IF sy-subrc NE 0. EXIT. ENDIF.


          gv_line = gv_line && %_horizontal_tab.
          gv_line = gv_line && <gv_fld>.
        ENDDO.
        SHIFT gv_line.
        gv_line  = gv_line && %_cr_lf.
        gv_lines = gv_lines && gv_line.
      ENDLOOP.


      CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
        EXPORTING
          text     = gv_lines
          mimetype = 'UTF-8'
        IMPORTING
          buffer   = gv_xstr.


      TRANSFER gv_xstr TO gv_afile.
      CLOSE DATASET gv_afile.
      MESSAGE s000(oo) WITH gv_afile.
    ENDDO.
    CLOSE CURSOR gv_cursor.
  ENDLOOP.

问：哇哈哈，果然内表不会爆掉了！不过我真的又遇到问题了呢，文件太大了，把应用服务器给撑爆了，磁盘空间都没有啦！老师你刚才说会遇到新的问题，是不是这个问题呢？

答：就是这个问题，哈哈哈，我们之所以把数据转为XSTRING再存放就是为了解决这个问题。我们知道SAP数据这种二维表的数据冗余是非常严重的，稍微压缩一下就能有一个很好的压缩比，有的时候能压缩到原大小的十分之一甚至百分之一呢！

TABLES dd02l.
DATA gt_tabs   TYPE TABLE OF tabname WITH HEADER LINE.
DATA go_zip    TYPE REF TO cl_abap_zip.
DATA go_table  TYPE REF TO data.
DATA gv_cursor TYPE cursor.
DATA gv_afile  TYPE string.
DATA gv_fname  TYPE string.
DATA gv_head   TYPE string.
DATA gv_line   TYPE string.
DATA gv_lines  TYPE string.
DATA gv_xstr   TYPE xstring.
DATA go_tdesc TYPE REF TO cl_abap_tabledescr.
DATA go_sdesc TYPE REF TO cl_abap_structdescr.
DATA gs_comps LIKE LINE OF cl_abap_structdescr=>components.


FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE,<gs_table>,<gv_fld>.


SELECT-OPTIONS s_tabnam FOR dd02l-tabname.


START-OF-SELECTION.
  SELECT tabname INTO TABLE gt_tabs FROM dd02l
    WHERE tabname  IN s_tabnam AND
          as4local EQ 'A'      AND
          tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
  LOOP AT gt_tabs.
    CREATE DATA go_table TYPE TABLE OF (gt_tabs).
    ASSIGN go_table->* TO <gt_table>.


    CLEAR gv_head.
    go_tdesc ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
    go_sdesc ?= go_tdesc->get_table_line_type( ).
    LOOP AT go_sdesc->components INTO gs_comps.
      gv_head = gv_head && %_horizontal_tab && gs_comps-name.
    ENDLOOP.
    SHIFT gv_head.


    OPEN CURSOR gv_cursor FOR SELECT * FROM (gt_tabs).
    DO.
      FETCH NEXT CURSOR gv_cursor INTO TABLE <gt_table> PACKAGE SIZE 100000.
      IF sy-subrc NE 0. EXIT. ENDIF.


      gv_afile = gt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.ZIP'.
      gv_fname = gt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.TXT'.


      OPEN DATASET gv_afile FOR OUTPUT IN BINARY MODE.
      gv_lines = gv_head && %_cr_lf.
      LOOP AT <gt_table> ASSIGNING <gs_table>.
        CLEAR gv_line.
        DO.
          ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <gv_fld>.
          IF sy-subrc NE 0. EXIT. ENDIF.


          gv_line = gv_line && %_horizontal_tab.
          gv_line = gv_line && <gv_fld>.
        ENDDO.
        SHIFT gv_line.
        gv_line  = gv_line && %_cr_lf.
        gv_lines = gv_lines && gv_line.
      ENDLOOP.


      CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
        EXPORTING
          text     = gv_lines
          mimetype = 'UTF-8'
        IMPORTING
          buffer   = gv_xstr.
      CONCATENATE cl_abap_char_utilities=>byte_order_mark_utf8
                  gv_xstr INTO gv_xstr IN BYTE MODE.


      CREATE OBJECT go_zip.
      go_zip->add( name    = gv_fname
                   content = gv_xstr ).
      gv_xstr = go_zip->save( ).
      FREE go_zip.


      TRANSFER gv_xstr TO gv_afile.
      CLOSE DATASET gv_afile.
      MESSAGE s000(oo) WITH gv_afile.
    ENDDO.
    CLOSE CURSOR gv_cursor.
  ENDLOOP.

问：破费科特！老师棒棒的！一下子文件就小了很多，一边取数一边在服务器下载然后删除，文件不会撑爆服务器了。不过用CG3Y下载好烦啊，老师你不是说可以写一个下载程序。。。。

答：算了，救人救到底送佛送到西，我给你整理成一个完整的下载程序吧，把DB下载到服务器和服务器下载到本地放到一个程序里面，这样就能愉快的下着数据唱着歌了。

*&---------------------------------------------------------------------*
*& Report  ZEXPORT_TABLE
*&
*&---------------------------------------------------------------------*
*& 导出多个表数据
*& Baitianzhen
*&---------------------------------------------------------------------*
REPORT zexport_table NO STANDARD PAGE HEADING.


TABLES: dd02l,flsysrqst.
DATA: gv_afile TYPE string,
      gt_files TYPE TABLE OF string.


PARAMETERS: pr_sele RADIOBUTTON GROUP g1 USER-COMMAND u1 DEFAULT 'X'.
PARAMETERS: pr_down RADIOBUTTON GROUP g1.


SELECT-OPTIONS s_tabnam FOR dd02l-tabname MODIF ID a.
SELECT-OPTIONS s_dbfile FOR flsysrqst-file_name NO INTERVALS MODIF ID b.
PARAMETERS p_down AS CHECKBOX MODIF ID a.
PARAMETERS p_dele AS CHECKBOX DEFAULT 'X' MODIF ID b.


AT SELECTION-SCREEN OUTPUT.
  %_pr_sele_%_app_%-text  = '保存表数据到服务器文件'.
  %_pr_down_%_app_%-text  = '下载服务器文件到本地'.
  %_s_tabnam_%_app_%-text = '表/视图'.
  %_s_dbfile_%_app_%-text = '服务器文件'.
  %_p_down_%_app_%-text   = '直接下载到本地'.
  %_p_dele_%_app_%-text   = '下载后删除服务器文件'.
  LOOP AT SCREEN.
    CASE 'X'.
      WHEN pr_sele.
        IF screen-group1 CA 'B'.
          screen-active = '0'.
        ENDIF.
      WHEN pr_down.
        IF screen-group1 CA 'A'.
          screen-active = '0'.
        ENDIF.
    ENDCASE.
    MODIFY SCREEN.
  ENDLOOP.


AT SELECTION-SCREEN.
  IF sy-ucomm = 'ONLI' AND pr_sele = 'X' AND s_tabnam[] IS INITIAL.
    SET CURSOR FIELD 'S_TABNAM-LOW'.
    MESSAGE e000(oo) WITH '表名必输'.
  ENDIF.


START-OF-SELECTION.
  CASE 'X'.
    WHEN pr_sele.
      PERFORM getdata.
    WHEN pr_down.
      LOOP AT s_dbfile.
        gv_afile = s_dbfile-low.
        COLLECT gv_afile INTO gt_files.
      ENDLOOP.
      PERFORM download.
  ENDCASE.


*&---------------------------------------------------------------------*
*&      Form  getdata
*&---------------------------------------------------------------------*
FORM getdata.
  DATA lo_tdesc  TYPE REF TO cl_abap_tabledescr.
  DATA lo_sdesc  TYPE REF TO cl_abap_structdescr.
  DATA lo_zip    TYPE REF TO cl_abap_zip.
  DATA lo_table  TYPE REF TO data.
  DATA ls_comps  LIKE LINE OF cl_abap_structdescr=>components.
  DATA lt_tabs   TYPE TABLE OF tabname WITH HEADER LINE.
  DATA lv_cursor TYPE cursor.
  DATA lv_fname  TYPE string.
  DATA lv_head   TYPE string.
  DATA lv_line   TYPE string.
  DATA lv_lines  TYPE string.
  DATA lv_xstr   TYPE xstring.
  FIELD-SYMBOLS: <lt_table> TYPE STANDARD TABLE,<ls_table>,<lv_fld>.


  SELECT tabname INTO TABLE lt_tabs FROM dd02l
    WHERE tabname  IN s_tabnam AND
          as4local EQ 'A'      AND
          tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
  LOOP AT lt_tabs.
    CREATE DATA lo_table TYPE TABLE OF (lt_tabs).
    ASSIGN lo_table->* TO <lt_table>.


    CLEAR lv_head.
    lo_tdesc ?= cl_abap_typedescr=>describe_by_data( <lt_table> ).
    lo_sdesc ?= lo_tdesc->get_table_line_type( ).
    LOOP AT lo_sdesc->components INTO ls_comps.
      lv_head = lv_head && %_horizontal_tab && ls_comps-name.
    ENDLOOP.
    SHIFT lv_head.


    OPEN CURSOR lv_cursor FOR SELECT * FROM (lt_tabs).
    DO.
      FETCH NEXT CURSOR lv_cursor INTO TABLE <lt_table> PACKAGE SIZE 100000.
      IF sy-subrc NE 0. EXIT. ENDIF.


      gv_afile = lt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.ZIP'.
      lv_fname = lt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.TXT'.
      OPEN DATASET gv_afile FOR OUTPUT IN BINARY MODE.


      lv_lines = lv_head && %_cr_lf.
      LOOP AT <lt_table> ASSIGNING <ls_table>.
        CLEAR lv_line.
        DO.
          ASSIGN COMPONENT sy-index OF STRUCTURE <ls_table> TO <lv_fld>.
          IF sy-subrc NE 0. EXIT. ENDIF.


          lv_line = lv_line && %_horizontal_tab.
          lv_line = lv_line && <lv_fld>.
        ENDDO.
        SHIFT lv_line.
        lv_line  = lv_line && %_cr_lf.
        lv_lines = lv_lines && lv_line.
      ENDLOOP.


      CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
        EXPORTING
          text     = lv_lines
          mimetype = 'UTF-8'
        IMPORTING
          buffer   = lv_xstr.
      CONCATENATE cl_abap_char_utilities=>byte_order_mark_utf8
                  lv_xstr INTO lv_xstr IN BYTE MODE.


      CREATE OBJECT lo_zip.
      lo_zip->add( name    = lv_fname
                   content = lv_xstr ).
      lv_xstr = lo_zip->save( ).
      FREE lo_zip.


      TRANSFER lv_xstr TO gv_afile.
      CLOSE DATASET gv_afile.
      APPEND gv_afile TO gt_files.
      MESSAGE s000(oo) WITH gv_afile.
    ENDDO.
    CLOSE CURSOR lv_cursor.
  ENDLOOP.
  IF p_down = 'X' AND sy-batch = ''.
    PERFORM download.
  ENDIF.
ENDFORM.                    "getdata


*&---------------------------------------------------------------------*
*&      Form  down
*&---------------------------------------------------------------------*
FORM download.
  DATA lv_file TYPE string.
  DATA lt_btab TYPE TABLE OF w3mime  WITH HEADER LINE.
  DATA lv_xstr TYPE xstring.
  DATA lv_blen TYPE i.


  LOOP AT gt_files INTO gv_afile.
    OPEN DATASET gv_afile FOR INPUT IN BINARY MODE.
    IF sy-subrc = 0.
      READ DATASET gv_afile INTO lv_xstr LENGTH lv_blen.
      CLOSE DATASET gv_afile.


      CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
        EXPORTING
          buffer        = lv_xstr
        IMPORTING
          output_length = lv_blen
        TABLES
          binary_tab    = lt_btab.


      lv_file = 'D:\tmp\' && gv_afile.
      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          bin_filesize      = lv_blen
          filename          = lv_file
          filetype          = 'BIN'
          confirm_overwrite = ' '
        TABLES
          data_tab          = lt_btab
        EXCEPTIONS
          OTHERS            = 22.
      IF sy-subrc = 0.
        IF p_dele = 'X' OR p_down = 'X'.
          DELETE DATASET gv_afile.
        ENDIF.
      ELSE.
        MESSAGE i000(oo) WITH '文件下载错误：' gv_afile.
      ENDIF.
    ELSE.
      MESSAGE i000(oo) WITH '服务器文件打开错误：' gv_afile.
    ENDIF.
  ENDLOOP.
  CLEAR gt_files.
ENDFORM.                    "download