ABAP连接db2并插入数据demo

FUNCTION Z_FMEQS_CONNECTDB2.
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     VALUE(PI_TAB_NAME) TYPE  STRING OPTIONAL
*"     VALUE(PI_CON_NAME) TYPE  DBCON-CON_NAME OPTIONAL
*"  EXPORTING
*"     VALUE(PE_RETURN) TYPE  C
*"     VALUE(PE_MESSAGE) TYPE  STRING
*"     VALUE(PE_ROW) TYPE  INT4
*"  TABLES
*"      PT_TAB TYPE  STANDARD TABLE OPTIONAL
*"      PT_RETURN TYPE  BAPIRET2_TAB OPTIONAL
*"----------------------------------------------------------------------

************************************************************************
* PROGRAM ID    : Z_FMEQS_CONNECTDB2
* DESCRIPTION   : 向db2 插入或者删除数据
* AUTHOR        : 
* CREATE DATE   :  
* RELEASE FOR   : SAP ECC6
* ---------------------------------------------------------------------*
* VERSION CONTROL                                                      *
* DATE        AUTHOR     TRANSPORT        DESCRIPTION                  *
* ---------------------------------------------------------------------*
*
************************************************************************
*       Global data declarations
  DATA: lv_con_name TYPE dbcon-con_name,
        lst_return  TYPE bapiret2.


  TRY.
      "数据库连接
      PERFORM: fm_connect  USING pi_con_name gr_con_ref,
      "删除数据
               fm_delete_data TABLES pt_tab USING gr_con_ref pi_tab_name,
      "插入数据
               fm_insert_data TABLES pt_tab USING gr_con_ref pi_tab_name .

      gr_con_ref->commit( ). "数据提交
      pe_return  = '1'. "数据插入成功！
      pe_message = '数据发送成功！' .
      pe_row     = gv_row .

    CATCH cx_sql_exception INTO gr_sqlerr_ref.
      PERFORM fm_handle_sql_exception USING gr_sqlerr_ref CHANGING pe_message.
      pe_return = '0'. "插入失败！
      pe_row = gv_row .
      CLEAR gv_row .
      IF gr_con_ref IS NOT INITIAL .
        gr_con_ref->rollback( ). "数据回滚
      ENDIF.

      lst_return-type   = 'E'.
      lst_return-id     = 'ZMES001'.
      lst_return-number = '008'.
      lst_return-message = pe_message.
*      lst_return-message_v1 = gv_row.
*      lst_return-message_v2 = pe_message.
      APPEND lst_return TO pt_return.
      CLEAR lst_return.
  ENDTRY.

  IF gr_con_ref IS NOT INITIAL .
    lv_con_name = gr_con_ref->get_con_name( ).
    CHECK lv_con_name <> cl_sql_connection=>c_default_connection.
    gr_con_ref->close( ).  "关闭连接
    "free: con_ref .
  ENDIF.

ENDFUNCTION.
*&---------------------------------------------------------------------*
*&      Form  fm_delete_data
*&---------------------------------------------------------------------*
*       按主键删除数据
*----------------------------------------------------------------------*
*      -->PT_TAB       插入数据
*      -->PR_CON_REF   数据库连接
*      -->PU_TAB_NAME  操作表名称
*----------------------------------------------------------------------*
FORM fm_delete_data TABLES pt_tab
                     USING pr_con_ref  TYPE REF TO cl_sql_connection
                           pu_tab_name TYPE string
                     RAISING cx_sql_exception.

  DATA:
*       lit_zteqs0002    TYPE STANDARD TABLE OF zteqs0002,
*       lst_zteqs0002    TYPE zteqs0002,
       lv_stmt          TYPE string,
       lcl_prepstmt_ref TYPE REF TO cl_sql_prepared_statement,
       lst_tab_line     TYPE REF TO data,
       lv_tab           TYPE ddobjname ,
       lt_table         TYPE TABLE OF dfies,
       ls_table         TYPE dfies,
       lv_tabix         TYPE sy-tabix,
       l_value          TYPE string ,
       lv_where         TYPE string ,
       lv_value1        TYPE string ,
       lv_name          TYPE string.

  FIELD-SYMBOLS: <fs_wa>   TYPE any,
                 <fs_line> TYPE any.

  CREATE DATA lst_tab_line LIKE LINE OF pt_tab.
  ASSIGN lst_tab_line->* TO <fs_wa>.

  CONCATENATE 'ZT' pu_tab_name  INTO lv_tab.
  CALL FUNCTION 'DDIF_NAMETAB_GET'
    EXPORTING
      tabname   = lv_tab
    TABLES
*     X031L_TAB =
      dfies_tab = lt_table
    EXCEPTIONS
      not_found = 1
      OTHERS    = 2.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  "获取EQS系统中表SAP删除数据依赖的 主键
*  SELECT *
*  INTO CORRESPONDING FIELDS OF TABLE lit_zteqs0002
*  FROM zteqs0002
*  WHERE tabname    = pu_tab_name
*    AND delkeyflag = 'X'.
*
*  SORT lit_zteqs0002 BY tabname ASCENDING position_eqs ASCENDING.
  "按本次提交的数据，将对方的数据删除
  LOOP AT pt_tab INTO <fs_wa>.
    CLEAR lv_tabix.
    CLEAR lv_stmt.
    CLEAR lv_where.
    CLEAR lv_value1.
    CLEAR l_value.

    LOOP AT lt_table INTO ls_table WHERE keyflag = 'X' AND fieldname <> 'MANDT'.
      "非第一个条件，加上AND

      IF lv_tabix >= 1.
        CONCATENATE lv_where 'AND' INTO lv_where SEPARATED BY space.
      ENDIF.
      lv_tabix = lv_tabix + 1.
      "根据字段名称读取字段类型属性
      "拼接查询条件(删除)
      ASSIGN COMPONENT ls_table-fieldname OF STRUCTURE <fs_wa>  TO <fs_line>.
      IF sy-subrc = 0 .

        CONCATENATE  ls_table-fieldname '=' INTO l_value SEPARATED BY space .

        CASE ls_table-inttype.
          WHEN 'D'."日期
            CONCATENATE  '''' <fs_line>+0(4) '-' <fs_line>+4(2) '-' <fs_line>+6(2) '''' INTO lv_value1 .
          WHEN 'C'."字符型
            CONCATENATE   '''' <fs_line> '''' INTO lv_value1 .
          WHEN OTHERS."其他，数值
            CONCATENATE  '' <fs_line> ''  INTO lv_value1.
        ENDCASE.

        CONCATENATE lv_where l_value lv_value1 INTO lv_where SEPARATED BY space .

      ENDIF.

    ENDLOOP.

    "拼接SQL语句
    CONCATENATE 'delete from' pu_tab_name 'WHERE' lv_where INTO lv_stmt SEPARATED BY space.
    "预执行
    lcl_prepstmt_ref = pr_con_ref->prepare_statement( lv_stmt ).
    "执行SQL语句
    lcl_prepstmt_ref->execute_update( ).
    lcl_prepstmt_ref->close( ).

    "假如ACTION字段存在,且标识为DELETE,从DB2删除后,再将此记录从内表中删除
    ASSIGN COMPONENT 'ACTION' OF STRUCTURE <fs_wa>  TO <fs_line>.
    IF sy-subrc = 0 .
      IF <fs_wa> = 'DELETE'.
        DELETE pt_tab FROM <fs_wa>.
      ENDIF.
    ENDIF.
  ENDLOOP.

ENDFORM.                    "fm_delete_data
*&---------------------------------------------------------------------*
*&      Form  fm_insert_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->PT_TAB       插入数据
*      -->PR_CON_REF   数据库连接
*      -->PU_TAB_NAME  操作表名称
*----------------------------------------------------------------------*
FORM fm_insert_data TABLES pt_tab
                     USING pr_con_ref  TYPE REF TO cl_sql_connection
                           pu_tab_name TYPE string
                     RAISING cx_sql_exception..



  TYPE-POOLS:abap.
  DATA:  lv_stmt           TYPE string,
         lv_stmt_field     TYPE string,
         lcl_prepstmt_ref1 TYPE REF TO cl_sql_statement.

*        END OF headtab.
  DATA: lr_struc TYPE REF TO cl_abap_structdescr,
        lr_table TYPE REF TO cl_abap_tabledescr,
        lr_type TYPE REF TO cl_abap_typedescr,
        l_string TYPE string,
        lt_comp TYPE abap_component_tab,
        ls_comp LIKE LINE OF lt_comp,
        e_wa TYPE REF TO data,
        e_table TYPE REF TO data,
        descr_ref TYPE REF TO cl_abap_structdescr,
        lwa_tab_line TYPE REF TO data,
        l_tabix LIKE sy-tabix ,
        lt_table TYPE TABLE OF dfies,
        ls_table TYPE dfies,
        c_tab TYPE ddobjname ,
        l_value TYPE string ,
        lv_flag TYPE c ,
        lv_tabixn LIKE sy-tabix,
        lv_name TYPE string ,
        lv_rows TYPE i ,
        lv_tot LIKE sy-tabix .

  FIELD-SYMBOLS:
                 <comp_wa> TYPE abap_compdescr ,
                 <fs_wa>   TYPE any,
                 <fs_line> TYPE any ,
                 <fs_table> TYPE STANDARD TABLE,
                 <fs_struc> TYPE any.


  CREATE DATA lwa_tab_line LIKE LINE OF pt_tab.
  ASSIGN lwa_tab_line->* TO <fs_wa>.
  descr_ref ?= cl_abap_typedescr=>describe_by_data( pt_tab ).
  "获取当前表的数据结构
  CONCATENATE 'ZT' pu_tab_name  INTO c_tab.
  CALL FUNCTION 'DDIF_NAMETAB_GET'
    EXPORTING
      tabname   = c_tab
    TABLES
*     X031L_TAB =
      dfies_tab = lt_table
    EXCEPTIONS
      not_found = 1
      OTHERS    = 2.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  LOOP AT lt_table INTO ls_table.

    CONCATENATE ls_table-tabname '-' ls_table-fieldname INTO l_string.
    ls_comp-name = ls_table-fieldname.
    CALL METHOD cl_abap_datadescr=>describe_by_name
      EXPORTING
        p_name         = l_string
      RECEIVING
        p_descr_ref    = lr_type
      EXCEPTIONS
        type_not_found = 1
        OTHERS         = 2.
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
      WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

    ls_comp-type ?= lr_type.
    APPEND ls_comp TO lt_comp.
    CLEAR ls_comp.
  ENDLOOP.

  CALL METHOD cl_abap_structdescr=>create
    EXPORTING
      p_components = lt_comp
    RECEIVING
      p_result     = lr_struc.

  CALL METHOD cl_abap_tabledescr=>create
    EXPORTING
      p_line_type = lr_struc
    RECEIVING
      p_result    = lr_table.

  CREATE DATA e_wa TYPE HANDLE lr_struc.
  CREATE DATA e_table TYPE HANDLE lr_table.
  ASSIGN e_wa->* TO <fs_struc>.
  ASSIGN e_table->* TO <fs_table>.

  LOOP AT descr_ref->components ASSIGNING <comp_wa>.

    IF <comp_wa>-name <> 'ACTION'.
      l_tabix = l_tabix + 1.
    ENDIF.

  ENDLOOP.

  lv_tabixn = l_tabix + 4 .
  lv_tot = lv_tabixn + 2.
  READ TABLE descr_ref->components ASSIGNING <comp_wa> INDEX 1 .
  lv_name =  <comp_wa>-name .

  CLEAR : gv_row .
  LOOP AT pt_tab INTO <fs_wa>.
    gv_row = gv_row + 1 .

    CLEAR lv_rows.
    CLEAR lv_stmt_field.
    DO  l_tabix TIMES.
      lv_rows = lv_rows + 1.
      IF lv_rows > 1.
        READ TABLE lt_table INTO ls_table INDEX lv_rows.
        IF sy-subrc = 0 .
          "生成字段名称列表
          IF lv_rows = l_tabix.
            CONCATENATE lv_stmt_field ls_table-fieldname INTO lv_stmt_field.
          ELSE.
            CONCATENATE lv_stmt_field ls_table-fieldname ',' INTO lv_stmt_field.
          ENDIF.

          ASSIGN COMPONENT sy-index OF STRUCTURE <fs_wa>  TO <fs_line>.
          l_value = <fs_line> .
          CONDENSE l_value NO-GAPS .
          IF l_value = '0000-00-00'.
            l_value = space .
          ENDIF.

          IF  l_value  IS INITIAL.
            "字段内容为空
            IF sy-index EQ l_tabix .
              "最后一个字段
              IF lv_flag IS INITIAL .
                CONCATENATE lv_stmt 'NULL' INTO lv_stmt.
              ENDIF.
            ELSE .
              "非最后一个字段
              IF lv_flag IS INITIAL .
                CONCATENATE  lv_stmt  'NULL'  ','  INTO lv_stmt.
              ENDIF.
            ENDIF.
          ELSE ."字段内容不为空
            IF sy-index EQ l_tabix ."最后一个字段
              IF lv_flag IS INITIAL .
                CASE ls_table-inttype.
                  WHEN 'C'.
                    CONCATENATE  lv_stmt  '''' l_value '''' INTO lv_stmt .
                  WHEN 'D'.
                    IF l_value = '00000000'.
                      CONCATENATE  lv_stmt  'NULL' INTO lv_stmt .
                    ELSE.
                      CONCATENATE  lv_stmt  '''' l_value+0(4) '-' l_value+4(2) '-' l_value+6(2) '''' INTO lv_stmt .
                    ENDIF.
                  WHEN OTHERS.
                    CONCATENATE  lv_stmt  '' l_value '' INTO lv_stmt .
                ENDCASE.
              ENDIF.
            ELSE."非最后一个字段
              IF lv_flag IS INITIAL .
                CASE ls_table-inttype .
                  WHEN 'C'.
*                    CONCATENATE  lv_stmt  '''' l_value '''' ','  INTO lv_stmt .
*----start-----add by troy on 20160411------
                    SEARCH l_value FOR '-' .
                    if sy-subrc = 0.
                      REPLACE '-' with '' into l_value.
                      CONDENSE l_value NO-GAPS .
                      CONCATENATE '-' l_value into l_value.
                      CONDENSE l_value NO-GAPS .
                    endif.
                    CONCATENATE  lv_stmt  '''' l_value '''' ','  INTO lv_stmt  .
*------end-------------
                  WHEN 'D'.
                    IF l_value = '00000000'.
                      CONCATENATE  lv_stmt  'NULL'  ','  INTO lv_stmt .
                    ELSE.
                      CONCATENATE  lv_stmt  '''' l_value+0(4) '-' l_value+4(2) '-' l_value+6(2) '''' ','  INTO lv_stmt .
                    ENDIF.
                  WHEN OTHERS.
*                    CONCATENATE  lv_stmt  '' l_value '' ','  INTO lv_stmt .
*----start-----add by troy on 20160411------
                    SEARCH l_value FOR '-' .
                    if sy-subrc = 0.
                      REPLACE '-' with '' into l_value.
                      CONDENSE l_value NO-GAPS .
                      CONCATENATE '-' l_value into l_value.
                      CONDENSE l_value NO-GAPS .
                    endif.
                    CONCATENATE  lv_stmt  '' l_value '' ','  INTO lv_stmt.
*------end-------------
                ENDCASE.
              ENDIF .

            ENDIF.
          ENDIF.
        ENDIF.
      ENDIF.
    ENDDO.
    IF lv_flag IS INITIAL .

      CONCATENATE
     'insert into'  pu_tab_name '(' lv_stmt_field ')'  'values' '(' lv_stmt ')'
      INTO lv_stmt SEPARATED BY space.
    ELSE .

    ENDIF.
    lcl_prepstmt_ref1 =   pr_con_ref->create_statement( ).
    lv_rows =    lcl_prepstmt_ref1->execute_update( lv_stmt  ).

    CLEAR :lv_stmt ,lv_flag .
  ENDLOOP.
* don't forget to close the prepared statement in order to free
* resources on the database
  "L_PREPSTMT_REF->CLOSE( ).


ENDFORM.                    "fm_insert_data
*&---------------------------------------------------------------------*
*&      Form  FM_HANDLE_SQL_EXCEPTION
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_SQLERR_REF  text
*      -->C_MESSAGE     text
*----------------------------------------------------------------------*
FORM fm_handle_sql_exception USING pu_sqlerr_ref TYPE REF TO cx_sql_exception
                              CHANGING pc_message TYPE string.

  DATA:lv_sql_code       TYPE c,
       lv_internal_error TYPE string.

  IF pu_sqlerr_ref->db_error = 'X'.
    lv_sql_code =  pu_sqlerr_ref->sql_code.
    CONCATENATE  'SQL error occured:' lv_sql_code  pu_sqlerr_ref->sql_message INTO pc_message. "#EC NOTEXT
  ELSE.
    lv_internal_error = pu_sqlerr_ref->internal_error.
    CONCATENATE 'Error from DBI (details in dev-trace):' lv_internal_error INTO pc_message. "#EC NOTEXT
  ENDIF.

ENDFORM.                    "handle_sql_exception
*&---------------------------------------------------------------------*
*&      Form  fm_CONNECT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->PU_CON_NAME       text
*      -->PU_CON_REF        text
*      -->RAISING           text
*      -->CX_SQL_EXCEPTION  text
*----------------------------------------------------------------------*
FORM fm_connect  USING pu_con_name TYPE dbcon-con_name
                       pu_con_ref  TYPE REF TO cl_sql_connection
                       RAISING cx_sql_exception.

* if CON_NAME is not initial then try to open the connection, otherwise
* create a connection object representing the default connection.
  IF pu_con_name IS INITIAL.
    CREATE OBJECT pu_con_ref.
  ELSE.
    pu_con_ref = cl_sql_connection=>get_connection( pu_con_name ).
  ENDIF.

ENDFORM.                    " connect