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 数据库
输入数据库用户名密码
连接信息的格式为 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_codeENDEXEC......."提交或回滚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_codeENDEXEC......."提交或回滚IF lv_error_text IS NOT INITIAL. EXEC SQL. rollback ENDEXEC.ELSE. EXEC SQL. commit ENDEXEC.ENDIF."--------------------@斌将军--------------------** —— 希望本篇文章对您有所帮助 —— **
来源说明：本文内容由「SAP操作外部数据库.md」整理生成，仅用于内部技术分享与学习交流。