连接SQL，操作数据库（一）

连接SQL，操作数据库

1：定义游标

 

DEFINE IBL_FETCH_DATA.

 

  TRY.

      EXEC SQL.

        FETCH NEXT &3 INTO :&1

      ENDEXEC.

 

    CATCH CX_SY_NATIVE_SQL_ERROR.

      &2 = '99'.

  ENDTRY.

 

  IF &2 NE 99.

    &2 = SY-SUBRC.

  ENDIF.

 

END-OF-DEFINITION.

 

*  Close SQL Cursor

DEFINE IBL_CLOSE_CURSOR.

 

  TRY.

      EXEC SQL.

        CLOSE &1

      ENDEXEC.

 

    CATCH CX_SY_NATIVE_SQL_ERROR.

      &2 = '99'.

  ENDTRY.

 

  IF &2 NE 99.

    &2 = SY-SUBRC.

  ENDIF.

 

END-OF-DEFINITION.

 

 

  DATA: L_DBCONNAME     LIKE DBCON-CON_NAME.

  DATA: L_SUBRC         LIKE SY-SUBRC.

  DATA: L_DBOPEN        LIKE SY-SUBRC.

  DATA: L_DBCON(10) VALUE 'HKMIBL_DG'.

MOVE L_DBCON TO L_DBCONNAME.

2：检查连接的数据库在DBCO中是否存在

PERFORM CHECK_DBCON USING  L_DBCONNAME L_SUBRC.

 

FORM CHECK_DBCON USING  P_DBCONNAME

                        P_SUBRC.

  TABLES:DBCON.

  CLEAR DBCON.

  SELECT SINGLE *  FROM  DBCON

         WHERE  CON_NAME  = P_DBCONNAME.

  IF SY-SUBRC NE 0.

    MESSAGE E398(00)

      WITH 'DB Connection ' P_DBCONNAME

           'has not been set up.'

           'Please check table DBCON.'.

    P_SUBRC = SY-SUBRC.

    CLEAR P_DBCONNAME.

  ELSE.

    P_SUBRC = SY-SUBRC.

  ENDIF.

ENDFORM.                    "CHECK_DBCON

 

3：连接数据库

  PERFORM CONNECT_DBCON USING  L_DBCONNAME

                      CHANGING L_SUBRC.

 

FORM CONNECT_DBCON USING P_DBCONNAME LIKE DBCON-CON_NAME

                   CHANGING P_SUBRC.

  TRY.

      EXEC SQL.

        CONNECT TO :P_DBCONNAME

      ENDEXEC.

      EXEC SQL.

        SET CONNECTION :P_DBCONNAME

      ENDEXEC.

 

    CATCH CX_SY_NATIVE_SQL_ERROR.

      P_SUBRC = '99'.

  ENDTRY.

ENDFORM.                    " connect_dbcon

 

 

检查数据库的返回值是否 L_DBOPEN= 0.

 

 

 

@@查询数据

FORM PROCESS_DATA USING L_SUBRC.

 

  CLEAR L_SUBRC.

  TRY.

      EXEC SQL.

 

        OPEN C11A FOR

        SELECT * FROM CHKWIPQTY

               where from_date = :FMDATE and to_date = :ENDATE.

      ENDEXEC.

 

      L_SUBRC = SY-SUBRC.

 

    CATCH CX_SY_NATIVE_SQL_ERROR.

      L_SUBRC = '99'.

  ENDTRY.

ENDFORM.                    " PROCESS_DATA

 

 

将查询到的结果集填充到内表中

    DO.

      CLEAR TIBL.

      IF L_SUBRC = 0.

        IBL_FETCH_DATA TIBL L_SUBRC C11A.

        APPEND TIBL.

“（因为在游标中只能一条一条的读数据，所以用DO循环，并且APPEND到内表中）

      ELSE.

        EXIT.

      ENDIF.

    ENDDO.

 

“（必须关闭游标）

    IBL_CLOSE_CURSOR C11A L_SUBRC.

 

@@添加数据

FORM INSERT_DATA USING L_SUBRC.

  CLEAR L_SUBRC.

  TRY.

      EXEC SQL.

        insert ChkWipQty(sap_ship_to_vw,sap_ship_to_oth,

        sap_vw_ship_out,vw_move_out,from_date,to_date)

        values( :DNQTY1, :DNQTY2, :DNQTY3, :DNQTY4, :FMDATE, :ENDATE )

      ENDEXEC.

 

      COMMIT WORK. 

 “（操作数据库必须以COMMIT WORK 语句结束，否则不执行UPDATE INSERT DELETED 语句）

 

      L_SUBRC = SY-SUBRC.

 

    CATCH CX_SY_NATIVE_SQL_ERROR.

      L_SUBRC = '99'.

  ENDTRY.

ENDFORM.                    " INSERT_DATA

 

 

断开连接

    PERFORM DISCONNECT_DBCON USING L_DBCONNAME

                             CHANGING L_SUBRC.

 

 

FORM DISCONNECT_DBCON USING P_DBCONNAME  LIKE DBCON-CON_NAME

                      CHANGING P_SUBRC.

  TRY.

      EXEC SQL.

        DISCONNECT :P_DBCONNAME

      ENDEXEC.

 

    CATCH CX_SY_NATIVE_SQL_ERROR.

      P_SUBRC = '99'.

  ENDTRY.

 

ENDFORM.                    " connect_dbcon