说明:在工作中有一个需求是根据日期去oracle数据库中读取数据,但是Oracle的日期到SAP的内表中的格式有些奇怪,例如“2020-11-10”,会展示为“11-NOV-20”,所以我们要进行一下转换,才能在where条件中生效,这边写了一个函数来转换。
函数名:Z_ORACLE_DATE
入参:I_DATE
出参:O_DATE
函数代码:
FUNCTION z_oracle_date.
*"----------------------------------------------------------------------
*"*"本地接口:
*" IMPORTING
*" REFERENCE(I_DATE)
*" EXPORTING
*" REFERENCE(O_DATE)
*"----------------------------------------------------------------------
DATA:lv_years TYPE char02.
DATA:lv_month TYPE char04.
DATA:lv_month1 TYPE char02.
DATA:lv_date TYPE char02.
lv_years = i_date+2(2).
lv_date = i_date+6(2).
lv_month1 = i_date+4(2).
CASE lv_month1.
WHEN '01'.
lv_month = 'JAN'.
WHEN '02'.
lv_month = 'FEB'.
WHEN '03'.
lv_month = 'MAR'.
WHEN '04'.
lv_month = 'APR'.
WHEN '05'.
lv_month = 'MAY'.
WHEN '06'.
lv_month = 'JUN'.
WHEN '07'.
lv_month = 'JUL'.
WHEN '08'.
lv_month = 'AUG'.
WHEN '09'.
lv_month = 'SEPT'.
WHEN '10'.
lv_month = 'OCT'.
WHEN '11'.
lv_month = 'NOV'.
WHEN '12'.
lv_month = 'DEC'.
ENDCASE.
o_date = lv_date && '-' && lv_month && '-' && lv_years.
ENDFUNCTION.
具体的使用场景如图。
CALL FUNCTION 'Z_ORACLE_DATE'
EXPORTING
i_date = p_date
IMPORTING
o_date = lv_date.
*调用NATIVE SQL 语句获取数据( OPEN SQL 不支持 join 一个SQL语句的查询结果,native sql 支持.可以简化开发).
DATA conn TYPE dbcon-con_name.
conn = 'MTD'. “链接名
EXEC SQL.
CONNECT TO :conn
ENDEXEC.
* EXEC SQL.
* SET CONNECTION :conn.
* ENDEXEC.
EXEC SQL.
OPEN dbcur FOR
SELECT
zmatnr1,
eantp,
ean11,
zmatkl,
zzbrand,
zwherr,
umrez,
meins,
ferth,
groes,
ztaxm1,
zeinr,
hoehe,
breit,
laeng,
mhdhb,
bismt,
raube,
magrv,
labor,
maktx,
zmstae,
to_char( DATAB , 'yyyy-mm-dd' ) AS datab, "Oracle的日期拿过来也要用to_data函数转一下
to_char( ZDATE , 'yyyy-mm-dd' ) AS zdate
FROM DANIANG.ztmd014_JTERP_01
where zdate >= :lv_date "经过转化的日期
ENDEXEC.
"将取到的数据写入内表
DO.
EXEC SQL.
FETCH NEXT dbcur INTO :GT_DATA1-zmatnr1,
:GT_DATA1-eantp,
:GT_DATA1-ean11,
:GT_DATA1-zmatkl,
:GT_DATA1-zzbrand,
:GT_DATA1-zwherr,
:GT_DATA1-umrez,
:GT_DATA1-meins,
:GT_DATA1-ferth,
:GT_DATA1-groes,
:GT_DATA1-ztaxm1,
:GT_DATA1-zeinr,
:GT_DATA1-hoehe,
:GT_DATA1-breit,
:GT_DATA1-laeng,
:GT_DATA1-mhdhb,
:GT_DATA1-bismt,
:GT_DATA1-raube,
:GT_DATA1-magrv,
:GT_DATA1-labor,
:GT_DATA1-maktx,
:GT_DATA1-zmstae,
:GT_DATA1-DATAB,
:GT_DATA1-ZDATE
ENDEXEC.
IF sy-subrc <> 0.
EXIT.
ELSE.
READ TABLE lt_table1 INTO DATA(lw_table) WITH KEY zmatnr1 = gt_data1-zmatnr1 BINARY SEARCH.
IF sy-subrc <> 0.
gt_data1-date_c = sy-datum.
gt_data1-time_c = sy-uzeit.
gt_data1-datab = gt_data1-datab(4) && gt_data1-datab+5(2) && gt_data1-datab+8(2).
gt_data1-zdate = gt_data1-zdate(4) && gt_data1-zdate+5(2) && gt_data1-zdate+8(2).
APPEND gt_data1 .
DATA(lv_lines) = lines( gt_data1 ).
IF lv_lines = 500000.
lv_sum = lv_sum + lv_lines.
MOVE-CORRESPONDING gt_data1[] TO gt_save1[].
MODIFY ztmd014_jterp_01 FROM TABLE gt_save1.
IF sy-subrc = 0.
REFRESH:gt_data1[],gt_save1[].
CLEAR : lv_lines.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
ENDDO.
EXEC SQL.
CLOSE dbcur
ENDEXEC.
作者:小飞猪猪猪猪猪猪猪–CSDN
注意:转发请注明作者