abap 调用sql

&---------------------------------------------------------------------
*& Report Z_YY_PRG_005
&---------------------------------------------------------------------
*&
&---------------------------------------------------------------------
REPORT Z_YY_PRG_005.

PARAMETERS P1 TYPE CHAR1 AS CHECKBOX.

TYPES:
BEGIN OF TY_RESULT,
IDX TYPE I,
COL1 TYPE STRING,
COL2 TYPE STRING,
END OF TY_RESULT.

DATA : CONNECTION TYPE DBCON-CON_NAME VALUE ‘DBCO_DF_KFMRP’,
SQL_CONNECTION TYPE REF TO CL_SQL_CONNECTION,
SQL_STATEMENT TYPE REF TO CL_SQL_STATEMENT,
SQL_EXCEPTION TYPE REF TO CX_SQL_EXCEPTION,
LV_TEXT TYPE STRING,
SQL_RESULT TYPE REF TO CL_SQL_RESULT_SET,
D_REF TYPE REF TO DATA,
GT_TAB TYPE TABLE OF TY_RESULT,
GS_LINE TYPE TY_RESULT,
ROW_CNT TYPE I.

  SQL_CONNECTION = CL_SQL_CONNECTION=>GET_CONNECTION( CONNECTION ).
  SQL_STATEMENT = SQL_CONNECTION->CREATE_STATEMENT( ).

data D_REF2 TYPE REF TO DATA.

data : sql type string.

data : key1 type i,
key2 type i.

  TRY.

•    SQL_RESULT = SQL_STATEMENT->EXECUTE_QUERY( 'SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY fldItemNoS) AS IDX  , fldItemNoS,fldItemNameS FROM dbo.tbl_Active_Item_1 ' ).
  

•    SQL_RESULT = SQL_STATEMENT->EXECUTE_QUERY( 'exec sp_testSAP ' ).
  

•    sql = 'UPDATE dbo.tbl_TestSAP SET col2=''A''; UPDATE dbo.tbl_TestSAP SET col3=''B'' '.
  

•    SQL_STATEMENT->EXECUTE_UPDATE( sql ).
  
    key1 = 1.
    key2 = 2.
  
    GET REFERENCE OF key1 INTO D_REF.
  
    GET REFERENCE OF key2 INTO D_REF2.
  
  SQL_STATEMENT->set_param( D_REF ).
  SQL_STATEMENT->set_param( D_REF2 ).
  
  
  
    CALL METHOD SQL_STATEMENT->EXECUTE_PROCEDURE
      EXPORTING
        PROC_NAME             = 'sp_testSAP'
  

•        PROC_SUFFIX           =
  

•        WRITE_SYSLOG_ON_ERROR = ABAP_TRUE
      RECEIVING
        ROWS_PROCESSED        =  ROW_CNT
        .
  

•     CATCH CX_SQL_EXCEPTION .
  
      lv_Text = row_cnt.
  

•    GET REFERENCE OF GT_TAB INTO D_REF.
  

**

•    SQL_RESULT->SET_PARAM_TABLE( D_REF ).
  

•    DO.
  

•    ROW_CNT = SQL_RESULT->NEXT_PACKAGE( ).
  

•      IF ROW_CNT = 0.
  

•        EXIT.
  

•      ENDIF.
  

•    ENDDO.
  
    CATCH CX_SQL_EXCEPTION INTO SQL_EXCEPTION.
      LV_TEXT =  SQL_EXCEPTION->GET_TEXT( ).
  
  ENDTRY.
  

•  LOOP AT GT_TAB INTO GS_LINE.
  

•      WRITE : / 'IDX', GS_LINE-IDX, 'COL1: ',GS_LINE-COL1 ,  'COL2: ' , GS_LINE-COL2.
  

•  ENDLOOP.
  

  write : / 'row_cnt: ’ ,row_cnt.