如何使用原生SQL更改数据库后台表

问题提出

在 SAP 开发过程中，有时我们需要从我们的程序中更新我们的标准表，为此我们有许多方法，如写更新查询和直接更新我们的表或从工作区修改。

例如使用如下的代码：

1. 使用内表更改方式

MODIFY mara FROM TABLE gt_mara.
IF sy-subrc = 0.
      COMMIT WORK AND WAIT.
ENDIF.

2. 使用工作区方式

MODIFY table FROM ls_wa.
IF sy-subrc = 0.
  COMMIT WORK AND WAIT.
  MESSAGE '保存成功！' TYPE 'S'.
ELSE.
  ROLLBACK WORK.
  MESSAGE '保存失败！' TYPE 'S' DISPLAY LIKE 'E'.
  RETURN.
ENDIF.

但这种方式更改表是不太可取，因为 SAP 的表关联比较紧密，只更改一个表并不知到后台表有多少数据没有做到同步。

使用 RSDU_TABLE_UPDATE_SYB

但更新标准表最安全的方法是使用标准函数 RSDU_TABLE_UPDATE_SYB。使用原生 SQL 语句更新命令。

在下面这个程序例子中，我们将看到如何从这个 RSDU_TABLE_UPDATE_SYB 标准函数中更新标准表。

为了使用这个函数 RSDU_TABLE_UPDATE_SYB，我们将在函数中声明用于导出和导入的变量，其类型为 RSDU_T_RECORD 的表，工作区域为 RSDU_S_RECORD。这个系统函数需要传入四个参数:

• I_TABLNM，必填
• I_T_SET，必填
• I_T_WHERE
• I_TX_WHERE

*&---------------------------------------------------------------------*
*& Report  ZMCCS_UPD_VENDOR
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zmccs_upd_vendor.
TABLES : lfa1, adr6.

TYPES : BEGIN OF ty_log,
          lifnr TYPE lfa1-lifnr,
          msg   TYPE string,
        END OF ty_log.

DATA: it_set   TYPE rsdu_t_record,
      it_where TYPE rsdu_t_record,
      xt_set   TYPE rsdu_s_record.

DATA : it_fcat   TYPE slis_t_fieldcat_alv,
       wa_fcat   TYPE slis_fieldcat_alv,
       wa_layout TYPE slis_layout_alv.

DATA : it_log TYPE TABLE OF ty_log,
       wa_log TYPE            ty_log.

TYPE-POOLS:rsdu.

SELECTION-SCREEN : BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: p_del RADIOBUTTON GROUP r1 DEFAULT 'X' USER-COMMAND rd.
SELECTION-SCREEN COMMENT (20) FOR FIELD p_del.
PARAMETERS:p_ins RADIOBUTTON GROUP r1.
SELECTION-SCREEN COMMENT (20) FOR FIELD p_ins.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN: END OF BLOCK b2.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_lifnr FOR lfa1-lifnr NO INTERVALS MODIF ID pd.
PARAMETERS: p_file TYPE rlgrap-filename MODIF ID ins.

SELECTION-SCREEN: END OF BLOCK b1.

AT SELECTION-SCREEN OUTPUT.

  LOOP AT SCREEN.

    IF p_del = 'X'.
      IF screen-group1 = 'INS'.
*        p_file = 'abc.'.
        screen-invisible = '1'.
        screen-active = '0'.
        screen-input = '0'.
      ENDIF.
    ELSEIF p_ins = 'X'.
      IF screen-group1 = 'PD'.
*        s_lifnr = 'abc'.
        screen-invisible = '1'.
        screen-active = '0'.
        screen-input = '0'.
      ENDIF.
    ENDIF.
    MODIFY SCREEN.
  ENDLOOP.



START-OF-SELECTION.

  SELECT * FROM lfa1 INTO TABLE @DATA(it_lfa1) WHERE lifnr IN @s_lifnr.

  IF it_lfa1 IS NOT INITIAL.

    SELECT * FROM adr6 INTO TABLE @DATA(it_adr6)
      FOR ALL ENTRIES IN @it_lfa1
      WHERE addrnumber = @it_lfa1-adrnr.
    IF it_adr6 IS NOT INITIAL.

      LOOP AT it_adr6 INTO DATA(wa_adr6).

        READ TABLE it_lfa1 INTO DATA(wa_lfa1) WITH KEY adrnr = wa_adr6-addrnumber.

        IF wa_adr6-smtp_addr = 'einfonet.in'
          OR wa_adr6-smtp_addr = 'einfonet.IN'
          AND wa_adr6-persnumber IS INITIAL.

          xt_set-fieldnm = 'SMTP_ADDR'.
          xt_set-chavl = ''.
          xt_set-quote = 'X'.
          APPEND xt_set TO it_set.

          xt_set-fieldnm = 'SMTP_SRCH'.
          xt_set-chavl = ''.
          xt_set-quote = 'X'.
          APPEND xt_set TO it_set.

          xt_set-fieldnm = 'ADDRNUMBER'.
          xt_set-chavl = wa_adr6-addrnumber.
          xt_set-quote = 'X'.
          APPEND xt_set TO it_where.

          CALL FUNCTION 'RSDU_TABLE_UPDATE_SYB'
            EXPORTING
              i_tablnm        = 'ADR6'
              i_t_set         = it_set
              i_t_where       = it_where
*        IMPORTING
*             E_SQLERR        =
*        CHANGING
*             C_PROCESSED     = RS_C_FALSE
            EXCEPTIONS
              sql_error       = 1
              duplicate_error = 2
              not_found       = 3
              OTHERS          = 4.
          CLEAR it_where[].
          CLEAR it_set[].

          IF sy-subrc <> 0.
* Implement suitable error handling here
            wa_log-lifnr = wa_lfa1-lifnr.
            wa_log-msg = 'Error Occured'.
            APPEND wa_log TO it_log.
            CLEAR wa_log.
*          WRITE :/ 'Error Occured'.
          ELSE.
            wa_log-lifnr = wa_lfa1-lifnr.
            wa_log-msg = 'Success!!'.
            APPEND wa_log TO it_log.
            CLEAR wa_log.
*          WRITE :/ 'Success!!'.
          ENDIF.

        ELSEIF wa_adr6-smtp_addr = 'einfonet.in'
        OR wa_adr6-smtp_addr = 'einfonet.in'
        AND wa_adr6-persnumber IS NOT INITIAL.

          wa_log-lifnr = wa_lfa1-lifnr.
          wa_log-msg = 'Person number exist.'.
          APPEND wa_log TO it_log.
          CLEAR wa_log.

*        MESSAGE 'Person number exist.' TYPE 'E'.

        ELSEIF wa_adr6-smtp_addr NE 'einfonet.in'
          OR wa_adr6-smtp_addr = 'EINFONET.IN'.

          wa_log-lifnr = wa_lfa1-lifnr.
          wa_log-msg = 'einfonet.in is not exsting id.'.
          APPEND wa_log TO it_log.
          CLEAR wa_log.
*        MESSAGE 'einfonet.in is not exsting id' TYPE 'E'.

        ENDIF.
        CLEAR: wa_adr6, wa_lfa1.
      ENDLOOP.

    ELSEIF it_adr6 IS INITIAL.
*
*    wa_log-linfr = wa_lfa1-lifnr.
*    wa_log-msg = 'Check vendor detail.'.
*    APPEND wa_log TO it_log.
*    CLEAR wa_log.
      MESSAGE 'Check vendor detail.' TYPE 'E'.

    ENDIF.

  ELSEIF it_lfa1 IS INITIAL.
    MESSAGE 'Check vendor detail/ No mail id exist.' TYPE 'E'.
  ENDIF.


  REFRESH it_fcat.
  CLEAR: wa_layout, wa_fcat.

  wa_fcat-fieldname = 'LIFNR'.
  wa_fcat-seltext_l = 'Vendor'.
  APPEND wa_fcat TO it_fcat.
  CLEAR wa_fcat.

  wa_fcat-fieldname = 'MSG'.
  wa_fcat-seltext_l = 'Status Message'.
  APPEND wa_fcat TO it_fcat.
  CLEAR wa_fcat.

  wa_layout-colwidth_optimize = 'X'.
  wa_layout-zebra = 'X'.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program = sy-repid
*     I_CALLBACK_TOP_OF_PAGE            = ' '
      is_layout          = wa_layout
      it_fieldcat        = it_fcat
    TABLES
      t_outtab           = it_log
* EXCEPTIONS
*     PROGRAM_ERROR      = 1
*     OTHERS             = 2
    .
  IF sy-subrc <> 0.
* Implement suitable error handling here
  ENDIF.