一个关于native sql的程序

*&---------------------------------------------------------------------*
*& Report  ZHR_BPM11 业务行事日历同步
*&
*&---------------------------------------------------------------------*
*&创建者：caizjian  日期：20141010
*&
*&---------------------------------------------------------------------*

REPORT zhr_bpm11.

TYPES:BEGIN OF typ_db,
  pernr TYPE pa0001-pernr,                    "员工号
  datum TYPE ptpsp-datum,      "计划工作时间
  tprog TYPE ptpsp-tprog,      "工作计划规则
  timestamp TYPE timestamp,    "JOB同步时间
  END OF typ_db.

TYPES:BEGIN OF typ_yg,
  pernr TYPE pa0001-pernr,
  END OF typ_yg.

DATA:gt_db TYPE TABLE OF typ_db,
     gs_db LIKE LINE OF gt_db,
     gt_yg TYPE TABLE OF typ_yg,
     gs_yg LIKE LINE OF gt_yg.

DATA:con_ora_name TYPE dbcon_name VALUE 'BPMFLOW',
      tp_time1(19) TYPE c,    "开始插入时间
      tp_time2(19) TYPE c.    "结束插入时间

data: exc_ref    TYPE ref TO cx_sy_native_sql_error,
      error_text TYPE string.

INITIALIZATION.
  IF sy-mandt EQ '800'.
    MOVE 'PORTAL' TO con_ora_name.
  ENDIF.

START-OF-SELECTION.

  PERFORM get_data.
  PERFORM frm_connect.
*&---------------------------------------------------------------------*
*&      Form  GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_data .
  DATA:l_datum1 TYPE sy-datum,
       l_datum2 TYPE sy-datum,
       l_return TYPE bapireturn1,
       lt_perws  TYPE TABLE OF ptpsp,
       ls_perws LIKE LINE OF lt_perws.

*员工号
  SELECT pernr INTO TABLE gt_yg
    FROM pa0001
    WHERE begda <= sy-datum
     AND  endda >= sy-datum
     AND  persk LIKE '%3'.

  l_datum1 =  sy-datum.
  l_datum2 = l_datum1 + 2.

  LOOP AT gt_yg INTO gs_yg.


    CLEAR:lt_perws,
          l_return.

    CALL FUNCTION 'ZHR_GET_DWS'
      EXPORTING
        pernr  = gs_yg-pernr
        begda  = l_datum1
        endda  = l_datum2
      IMPORTING
        return = l_return
      TABLES
        perws  = lt_perws.

    LOOP AT lt_perws INTO ls_perws.
      gs_db-pernr = gs_yg-pernr.
      gs_db-datum = ls_perws-datum.
      gs_db-tprog = ls_perws-tprog.
      APPEND gs_db TO gt_db.
      CLEAR:gs_db, ls_perws.
    ENDLOOP.
  ENDLOOP.



ENDFORM.                    " GET_DATA
*&---------------------------------------------------------------------*
*&      Form  FRM_CONNECT
*&---------------------------------------------------------------------*
FORM frm_connect .
  DATA:l_emp_id TYPE char8,
       l_time TYPE char8,
       l_error TYPE c.

  DATA:wa_log TYPE zhrbpm_log,
       l_timestamp TYPE string.


  TRY .
      EXEC SQL.
        CONNECT TO: CON_ORA_NAME.
      ENDEXEC.
      IF sy-subrc <> 0 .
*       出错时
        RAISE EXCEPTION TYPE cx_sy_native_sql_error.
      ENDIF.
      EXEC SQL.
        OPEN dbcur for
        select Emp_id from EMP_WORK_SCHEDULE
      ENDEXEC.
      DO.
        EXEC SQL.
          FETCH NEXT dbcur INTO :L_Emp_id
        ENDEXEC.
        IF sy-subrc <> 0.
          EXIT.
        ENDIF.
      ENDDO.
      EXEC SQL.
        CLOSE dbcur
      ENDEXEC.

*     获取开始插入时间
      GET TIME.
      CONCATENATE sy-datum+0(4)
                  sy-datum+4(2)
                  sy-datum+6(2)
           INTO   tp_time1
      SEPARATED BY '-'.
      CLEAR l_time.
      CONCATENATE sy-uzeit+0(2)
                  sy-uzeit+2(2)
                  sy-uzeit+4(2)
             INTO l_time
      SEPARATED BY ':'.
      CONCATENATE tp_time1
                  l_time
             INTO tp_time1
      SEPARATED BY space.
      EXEC SQL.
        UPDATE SYNC_RECORD_TBL set START_TIME = to_date(:TP_TIME1,'YYYY-MM-DD HH24:MI:SS') WHERE  SID = '13'
      ENDEXEC.
      EXEC SQL.
        COMMIT
      ENDEXEC.
      IF l_emp_id IS NOT INITIAL.
        EXEC SQL.
          DELETE FROM EMP_WORK_SCHEDULE
        ENDEXEC.
      ENDIF.
      IF sy-subrc <> 0.
        l_error = 'X'.
      ENDIF.
      IF l_error  IS INITIAL.
        LOOP AT  gt_db INTO gs_db.
          IF gs_db-pernr IS INITIAL OR gs_db-datum IS INITIAL.
            CLEAR gs_db.
            CONTINUE.
          ENDIF.
          CALL FUNCTION 'CONVERT_INTO_TIMESTAMP'      "获取时间戳
            EXPORTING
              i_datlo     = sy-datum
              i_timlo     = sy-uzeit
              i_tzone     = 'UTC+8'
            IMPORTING
              e_timestamp = gs_db-timestamp.
          EXEC SQL.
            INSERT INTO EMP_WORK_SCHEDULE(EMP_ID,WORK_DATE,WORK_RULE,CREATE_DATE)
            values:(:gs_db-PERNR,:gs_db-DATUM,:gs_db-TPROG,:gs_db-timestamp)
          ENDEXEC.
          IF sy-subrc <> 0.
            l_error = 'X'.
            " CRQ000000003316-HR抛BPM出错写日志
            wa_log-prog = sy-repid."当前程序名
            wa_log-tabnam = 'EMP_WORK_SCHEDULE'."表名
            l_timestamp = gs_db-timestamp.
            CONCATENATE gs_db-pernr
                        gs_db-datum
                        gs_db-tprog
                        l_timestamp
                        INTO wa_log-msg
                        SEPARATED BY '/'.

            PERFORM write_bpm_log USING wa_log.
            EXIT.
          ENDIF.
        ENDLOOP.
      ENDIF.
      IF l_error IS INITIAL.
*       获取插入结束时间
        CONCATENATE sy-datum+0(4)
                    sy-datum+4(2)
                    sy-datum+6(2)
             INTO   tp_time2
        SEPARATED BY '-'.
        CLEAR l_time.
        GET TIME.
        CONCATENATE sy-uzeit+0(2)
                    sy-uzeit+2(2)
                    sy-uzeit+4(2)
               INTO l_time
        SEPARATED BY ':'.
        CONCATENATE tp_time2
                    l_time
               INTO tp_time2
        SEPARATED BY space.
        EXEC SQL.
          UPDATE SYNC_RECORD_TBL set END_TIME = to_date(:TP_TIME2,'YYYY-MM-DD HH24:MI:SS') WHERE  SID = '13'
        ENDEXEC.

        EXEC SQL.
          COMMIT
        ENDEXEC.
        WRITE: '插入数据成功！'.
      ELSE.
        EXEC SQL.
          ROLLBACK
        ENDEXEC.
        WRITE: '插入数据失败！'.
      ENDIF.
      EXEC SQL.
        DISCONNECT :CON_ORA_NAME
      ENDEXEC.
    CATCH cx_sy_native_sql_error INTO exc_ref.
      error_text = exc_ref->get_text( ).
      EXEC SQL.
        ROLLBACK
      ENDEXEC.
      IF sy-batch = 'X'.
        WRITE: '插入数据失败！',
               / error_text.
      ELSE.
        MESSAGE error_text TYPE 'I'.
      ENDIF.
      LEAVE PROGRAM.

  ENDTRY.

ENDFORM.                    " FRM_CONNECT
*&---------------------------------------------------------------------*
*&      Form  WRITE_BPM_LOG
*&---------------------------------------------------------------------*
FORM write_bpm_log  USING wa TYPE zhrbpm_log.
  CALL FUNCTION 'ZHR_BPM_WLOG'
    EXPORTING
      wa_log = wa
    EXCEPTIONS
      nodata = 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.

ENDFORM.                    " WRITE_BPM_LOG