SAP连接外部数据库

1、文档说明

本文档介绍SAP对外围系统的数据库进行CRUD操作。

主要分为两步：

1、建立SAP与外围数据库的连接

2、运行代码实现CRUD

2、连接外围数据库

2.1、连接ORACLE

事务码：DBCO

维护DBMS为ORA，代表ORACLE数据库

输入数据库用户名密码

连接信息的格式为："数据库IP：端口（通常为1521）/ 数据库名称（SID/SERVICE NAME）"

运行程序ADBC_TEST_CONNECTION测试，

发现报错，这个需要BASIS协助处理

解决后再次测试，连接成功

 除了DBCO，还可以通过DB2C进行配置和测试

2.2、连接SQL Server

维护DBMS为MSS，代表Microsoft SQL Server数据库

输入数据库用户名密码

连接信息的格式为：MSSQL_SERVER=数据库IP,端口（通常为1433）"空格" MSSQL_DBNAME=数据库名称XXX

同样运行程序ADBC_TEST_CONNECTION测试

3、代码实现CRUD

以ORACLE数据库为例，需要创建连接，执行SQL语句，CUD操作还需要进行事务处理，之后关闭连接

3.1、查询

查询代码

其中日期查询需要转换一下格式

"--------------------@斌将军--------------------
TYPES:BEGIN OF ty_data,
        batch_name      TYPE zppt_ora_bg-batch_name, "BATCH_NAME
        order_number    TYPE zppt_ora_bg-order_number, "ORDER_NUMBER
        line_name       TYPE zppt_ora_bg-line_name, "LINE_NAME
        recipe_number   TYPE zppt_ora_bg-recipe_number, "RECIPE_NUMBER
        recipe_version  TYPE zppt_ora_bg-recipe_version, "RECIPE_VERSION
        batch_number    TYPE zppt_ora_bg-batch_number, "BATCH_NUMBER
      END OF ty_data.

DATA:gt_data TYPE STANDARD TABLE OF ty_data,
     gs_data TYPE ty_data.

DATA: lv_dbs TYPE dbcon-con_name VALUE 'ZORA'.

TRY .

      EXEC SQL.
        connect to :lv_dbs
      ENDEXEC.

      EXEC SQL.
        SET CONNECTION :lv_dbs
      ENDEXEC.

      EXEC SQL PERFORMING loop_output.
        SELECT
          BATCH_NAME,
          ORDER_NUMBER,
          LINE_NAME,
          RECIPE_NUMBER,
          RECIPE_VERSION,
          BATCH_NUMBER
        INTO :gs_data
        FROM I_ORDERS_V
        WHERE STATION_IDENT = 'M'
          AND LINE_NAME = 'P'
          AND ( to_char(START_DATE,'yyyyMMdd') = :lv_last_day
            or to_char(START_DATE,'yyyyMMdd') = :lv_day)
      ENDEXEC.

  CATCH  cx_sy_native_sql_error INTO DATA(g_obj_sqldb).
      CALL METHOD g_obj_sqldb->get_text
        RECEIVING
          result = DATA(lv_error_text).
  ENDTRY.

  IF lv_error_text IS NOT INITIAL.
    MESSAGE lv_error_text TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.

      EXEC SQL.
        DISCONNECT :lv_dbs
      ENDEXEC.

FORM loop_output.
  APPEND gs_data TO gt_data.
ENDFORM.
"--------------------@斌将军--------------------

查询单条

"--------------------@斌将军--------------------       
 "查询数据
        CLEAR:lv_plnum.
        EXEC SQL.
          Select
            pori_order_number
          into :lv_plnum
          from customer.prod_orders_imp
          where pori_line_name = :gs_alv-pori_line_name
            and pori_order_number = :gs_alv-pori_order_number
            and pori_recipe_code = :gs_alv-pori_recipe_code
        ENDEXEC.
"--------------------@斌将军--------------------

3.2、新增

新增数据

其中传入日期需要转换格式

"--------------------@斌将军--------------------
FORM save_data.

  DATA: lv_dbs         TYPE dbcon-con_name VALUE 'ZORA',
        lv_pori_status TYPE char10,
        lv_plnum       TYPE plaf-plnum.

  TRY .

      EXEC SQL.
        connect to :lv_dbs
      ENDEXEC.
    CATCH  cx_sy_native_sql_error INTO DATA(g_obj_sqldb).
      CALL METHOD g_obj_sqldb->get_text
        RECEIVING
          result = DATA(lv_error_text).

      IF lv_error_text IS NOT INITIAL.
        MESSAGE '数据库连接失败：' && lv_error_text TYPE 'S' DISPLAY LIKE 'E'.
        RETURN.
      ENDIF.
  ENDTRY.

  TRY .

      LOOP AT gt_alv INTO gs_alv.

        "插入数据
        EXEC SQL.
          insert into customer.prod_orders_imp
          (pori_line_name, pori_order_number, pori_recipe_code, pori_recipe_version,pori_batch_quantity_set,
          pori_order_weight, pori_pror_blocked, pori_function, pori_start_date )
          values
          (:gs_alv-pori_line_name,:gs_alv-pori_order_number,:gs_alv-pori_recipe_code, :gs_alv-pori_recipe_version, :gs_alv-pori_batch_quantity_set,
          :gs_alv-pori_order_weight, :gs_alv-pori_pror_blocked, :gs_alv-pori_function, to_date( :gs_alv-pori_start_date,'yyyy.mm.dd hh24:mi.ss') )
        ENDEXEC.
        CLEAR:gs_alv.
      ENDLOOP.

    CATCH  cx_sy_native_sql_error INTO g_obj_sqldb.
      CALL METHOD g_obj_sqldb->get_text
        RECEIVING
          result = lv_error_text.

  ENDTRY.

  IF lv_error_text IS NOT INITIAL.
    EXEC SQL.
      rollback
    ENDEXEC.
  ELSE.
    EXEC SQL.
      commit
    ENDEXEC.
  ENDIF.

  EXEC SQL.
    DISCONNECT :lv_dbs
  ENDEXEC.

ENDFORM.
"--------------------@斌将军-------------------- 

3.3、修改

更新数据

"--------------------@斌将军--------------------
"更新数据
EXEC SQL.
  UPDATE customer.prod_orders_imp
  SET pori_recipe_version = :gs_alv-pori_recipe_version,
      pori_batch_quantity_set = :gs_alv-pori_batch_quantity_set
  WHERE pori_line_name = :gs_alv-pori_line_name
    AND pori_order_number = :gs_alv-pori_line_name
    AND pori_recipe_code = :gs_alv-pori_recipe_code
ENDEXEC.

......

"提交或回滚
IF lv_error_text IS NOT INITIAL.
  EXEC SQL.
    rollback
  ENDEXEC.
ELSE.
  EXEC SQL.
    commit
  ENDEXEC.
ENDIF.
"--------------------@斌将军--------------------

3.4、删除

删除数据

"--------------------@斌将军--------------------
"删除数据
EXEC SQL.
  DELETE FROM customer.prod_orders_imp WHERE pori_line_name = :gs_alv-pori_line_name
                                         AND pori_order_number = :gs_alv-pori_order_number
                                         AND pori_recipe_code = :gs_alv-pori_recipe_code
ENDEXEC.

......

"提交或回滚
IF lv_error_text IS NOT INITIAL.
  EXEC SQL.
    rollback
  ENDEXEC.
ELSE.
  EXEC SQL.
    commit
  ENDEXEC.
ENDIF.
"--------------------@斌将军--------------------