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
post SAP-Garson
原文链接:https://blog.csdn.net/weixin_41886784/article/details/119032480文章来自于网络,如果侵犯了您的权益,请联系站长删除!