【应用场景】

1. 往对方中间库表中直接新增数据库表记录

2. 通过数据库中间件直接读取对方系统数据库表

【知识点】1. 连接数据库  2.读取数据  3.关闭数据库连接   4. 游标的使用

【前置条件】DB2配置对应对方系统的数据库连接是通的。有时因为密码过期或改了会导致数据库连接不上，需要检查。

【应用实例代码1】-带游标

DATA g_conexion   TYPE dbcon-con_name VALUE 'ECCBI_DEV'.
DATA lr_error     TYPE REF TO cx_sy_native_sql_error.
DATA lv_mess      TYPE string.
DATA ev_msg TYPE char255.
"--  1. 连接SQL SERVER
TRY.EXEC SQL.connect to :g_conexionendexec.exec sql.set connection :g_conexionENDEXEC.CATCH cx_sy_native_sql_error INTO lr_error.CALL METHOD lr_error->if_message~get_textRECEIVINGresult = lv_mess.CONCATENATE lv_mess '，无法连接至SQL数据库   连接名:' g_conexion INTO ev_msg.
ENDTRY.TRY."付款单"--  读取游标EXEC SQL.open dbcur1 forselecta.factualpayamount,a.fpayeraccountbankid,b.fbankaccountnumber,a.fpayeename,a.fpayeebank,a.fpayeeaccountbank,a.ffeetypeid,c.fname_l2 as zyname,a.fnumber,a.fbizdate,a.fcompanyid,d.FName_l2 as fcompanyname,a.fcreatetimefrom SLT_EAS.T_CAS_PAYMENTBILL aleft join SLT_EAS.T_BD_AccountBanks bon a.fpayeraccountbankid=b.fidleft join SLT_EAS.T_CAS_FeeType con a.ffeetypeid=c.fidleft join SLT_EAS.T_ORG_Company don a.fcompanyid=d.fidwhere a.FBankPayState = '6'and a.fcreatetime >= :f_fcreatetimeand a.fcreatetime <= :e_fcreatetimeand c.fname_l2 = '退房款'and a.FisCommittoBe = '1'ENDEXEC.DO.EXEC SQL.fetch next dbcur1 INTO :gs_fkd.ENDEXEC.IF sy-subrc <> 0.EXIT.ELSE.APPEND gs_fkd TO gt_fkd.ENDIF.ENDDO."--  关闭游标EXEC SQL.CLOSE dbcur1ENDEXEC.CATCH cx_sy_native_sql_error INTO lr_error.CALL METHOD lr_error->if_message~get_textRECEIVINGresult = lv_mess."gv_errorstr = lr_error->get_text( ).CONCATENATE '读取数据出错,'  lv_mess ': EAS视图' INTO ev_msg.
ENDTRY.
"-- 断开连接
EXEC SQL.disconnect :g_conexion
ENDEXEC.

【应用实例代码2】

DATA LS_ZSFI0343 TYPE ZSFI0343.DATA:LV_ERRO_TEXT    TYPE C LENGTH 200,LV_ERRO_TEXT1   TYPE C LENGTH 200,LC_ERRO_OBJECT  TYPE REF TO CX_SY_NATIVE_SQL_ERROR,LC_ERRO_OBJECT1 TYPE REF TO CX_SY_NATIVE_SQL_ERROR.CONSTANTS: C_DATABASE TYPE CHAR11 VALUE 'SAP2HFM_MID'.CONSTANTS C_DATABASE1 TYPE CHAR11 VALUE 'SAP2HFM_RES'.DATA:LV_DATABASE TYPE CHAR30.
* 按系统来源，确定更新那个数据库CASE I_TARGET_SYS.WHEN 'PVKHB01'.LV_DATABASE = C_DATABASE.WHEN 'PVKHB02'.LV_DATABASE = C_DATABASE1.WHEN OTHERS.LV_DATABASE = C_DATABASE.ENDCASE.
*---data define*---get dataPERFORM FRM_GET_DATA_0060 TABLES ET_ZSFI0343USING I_BUKRSI_GJAHRi_monatI_BATCH_IDI_FREQUESTNOCHANGING E_ERRE_MSG.*---get dataIF ET_ZSFI0343[] IS INITIAL.E_ERR = 'S'.EXIT.ENDIF.E_ERR = 'S'.* 获取函数文本PERFORM FRM_GET_FUNC_NAME USING 'ZFII_0060'CHANGING E_MSG.*---update database
*使用标准方法捕获异常、异常处理（如果有异常，输出记录异常日志回滚数据；如果无异常，执行后续程序）TRY .EXEC SQL.CONNECT TO :LV_DATABASEENDEXEC.CATCH CX_SY_NATIVE_SQL_ERROR INTO LC_ERRO_OBJECT.LV_ERRO_TEXT = LC_ERRO_OBJECT->GET_TEXT( ).ENDTRY.IF LV_ERRO_TEXT IS NOT INITIAL.E_ERR = 'F'.E_MSG = E_MSG && ':' && LV_ERRO_TEXT.EXIT.ENDIF.LOOP AT ET_ZSFI0343 INTO LS_ZSFI0343.TRY." SAP_STAGE.EXEC SQL.INSERT INTO CT_GL_MIDCFCHECK( FID,FREQUESTNO,FSOURCESYSTEM,FCOMPANYNUMBER,FCOMPANYNAME,FYEAR,FMONTH,FPERIODNUMBER,FACCOUNTNUMBER,FACCOUNTNAME,FCURRENCY,FORIGINALAMOUNT,FLOCALAMOUNT,FCREATORID,FCREATETIME,FLASTUPDATEUSERID,FLASTUPDATETIME,F_BATCH_ID,SCENARIO,RBUSA,GTEXT)VALUES(:LS_ZSFI0343-FID,:LS_ZSFI0343-FREQUESTNO,:LS_ZSFI0343-FSOURCESYSTEM,:LS_ZSFI0343-FCOMPANYNUMBER,:LS_ZSFI0343-FCOMPANYNAME,:LS_ZSFI0343-FYEAR,:LS_ZSFI0343-FMONTH,:LS_ZSFI0343-FPERIODNUMBER,:LS_ZSFI0343-FACCOUNTNUMBER,:LS_ZSFI0343-FACCOUNTNAME,:LS_ZSFI0343-FCURRENCY,:LS_ZSFI0343-FORIGINALAMOUNT,:LS_ZSFI0343-FLOCALAMOUNT,:LS_ZSFI0343-FCREATORID,:LV_TIME,:LS_ZSFI0343-FLASTUPDATEUSERID,:LV_TIME,:LS_ZSFI0343-F_BATCH_ID,:LS_ZSFI0343-SCENARIO,:LS_ZSFI0343-RBUSA,:LS_ZSFI0343-GTEXT)ENDEXEC.
*****捕获异常CATCH CX_SY_NATIVE_SQL_ERROR INTO LC_ERRO_OBJECT.LV_ERRO_TEXT = LC_ERRO_OBJECT->GET_TEXT( ).ENDTRY.IF LV_ERRO_TEXT IS INITIAL.
*    如果无异常，提交插入数据PERFORM FRM_NATIVE_COMMIT.ELSE.
*    如果捕获到异常，记录日志，回滚PERFORM FRM_NATIVE_ROLLBACK.E_ERR = 'F'.E_MSG = E_MSG && ':' && LV_ERRO_TEXT .EXIT.ENDIF.CLEAR LV_ERRO_TEXT.ENDLOOP."关闭连接TRY .EXEC SQL.DISCONNECT :LV_DATABASEENDEXEC.CATCH CX_SY_NATIVE_SQL_ERROR INTO LC_ERRO_OBJECT1.LV_ERRO_TEXT1 = LC_ERRO_OBJECT1->GET_TEXT( ).E_MSG = E_MSG && ':' && LV_ERRO_TEXT1 .E_ERR = 'F'.ENDTRY.IF E_ERR = 'S'.E_ERR = 'S'.E_MSG = TEXT-002 && ':' && E_MSG.ELSE.E_ERR = 'F'.E_MSG = TEXT-003 && ':' && E_MSG.ENDIF.*---uadate databaseENDFUNCTION.

FORM frm_connect_db USING p_dbname.

  DATA: l_obj_sqldb  TYPE REF TO cx_sy_native_sql_error.
  DATA: l_error_text TYPE string.


  TRY.
      EXEC SQL.
        CONNECT TO :p_dbname
      ENDEXEC.
*****连接Oracle数据库时，捕获异常，如果连接失败，输出异常信息
    CATCH CX_SY_NATIVE_SQL_ERROR INTO L_OBJ_SQLDB.
      CALL METHOD L_OBJ_SQLDB->GET_TEXT
        RECEIVING
          RESULT = L_ERROR_TEXT.

      GV_MSG = L_ERROR_TEXT.
  ENDTRY.


ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_NATIVE_COMMIT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM FRM_NATIVE_COMMIT .
  EXEC SQL.
    commit
  ENDEXEC.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_NATIVE_ROLLBACK
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_native_rollback .
  EXEC SQL.
    rollback
  ENDEXEC.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_DISCONNECT_DB
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_disconnect_db USING p_dbname .

*断开数据库连接
  EXEC SQL.
    DISCONNECT :p_dbname
  ENDEXEC.

ENDFORM.