[ABAP] How to use Native SQL (사용법)

REPORT  YMEMO.

TYPES: BEGIN OF TY_DATA,
  MANDT TYPE MANDT,
  BUKRS TYPE BUKRS,
  BELNR TYPE BELNR_D,
  GJAHR TYPE GJAHR,
  BKTXT TYPE BKTXT,
  END OF TY_DATA.

DATA: GS_DATA TYPE TY_DATA,
      GT_DATA TYPE TABLE OF TY_DATA.

DATA: LV_DATE TYPE SY-DATUM.

" 단건 선택 SELECT SINGLE LINE
CLEAR GS_DATA.
EXEC SQL.
  SELECT *
    INTO :GS_DATA
    FROM BKPF
   WHERE MANDT = :SY-MANDT
     AND BUKRS = 'C100'
     AND GJAHR = '2009'
     AND BELNR = '0100000000'
ENDEXEC.

" 단건 INSERT SINGLE LINE
EXEC SQL.
  INSERT INTO YMEMO (MANDT, BUKRS, BELNR, GJAHR, BKTXT)
         VALUES (:GS_DATA-MANDT, :GS_DATA-BUKRS, :GS_DATA-BELNR,
                 :GS_DATA-GJAHR, :GS_DATA-BKTXT)
ENDEXEC.

" 여러건 선택 SELECT MULTIPLE LINE
CLEAR GT_DATA.
EXEC SQL PERFORMING APPEND_ITAB.
  SELECT *
    INTO :GS_DATA
    FROM BKPF
   WHERE MANDT = :SY-MANDT
     AND BUKRS = 'C100'
     AND GJAHR = '2010'
ENDEXEC.

" 여러건 INSERT MULTIPLE LINE
" case 1. 직접 INSERT DIRECTLY
EXEC SQL.
  INSERT INTO YMEMO(MANDT, BUKRS, BELNR, GJAHR, BKTXT)
         SELECT MANDT, BUKRS, BELNR, GJAHR, BKTXT
           FROM BKPF
          WHERE BUKRS = 'C100'
            AND GJAHR = '2011'
ENDEXEC.

" case 2. 인터널 테이블에서 INSERT FROM INTERNAL TABLE
LOOP AT GT_DATA INTO GS_DATA.
  PERFORM INSERT_ITAB.
ENDLOOP.

" Native SQL 명령어 모두 사용 가능
" You can use any commands of your Native SQL
CLEAR LV_DATE.
EXEC SQL.
  SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')
    INTO :LV_DATE
    FROM DUAL
ENDEXEC.

WRITE: / LV_DATE.

*&---------------------------------------------------------------------*
*&      Form  APPEND_ITAB
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM APPEND_ITAB.

  APPEND GS_DATA TO GT_DATA.
  CLEAR GS_DATA.

ENDFORM.                    "APPEND_ITAB
*&---------------------------------------------------------------------*
*&      Form  INSERT_ITAB
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM INSERT_ITAB.

  EXEC SQL.
    INSERT INTO YMEMO (MANDT, BUKRS, BELNR, GJAHR, BKTXT)
       VALUES (:GS_DATA-MANDT, :GS_DATA-BUKRS, :GS_DATA-BELNR,
                          :GS_DATA-GJAHR, :GS_DATA-BKTXT)
  ENDEXEC.

ENDFORM.                    " INSERT_ITAB

 

HANA 버전 사용자에게는 거의 쓸모가 없어졌지만 여전히 ECC 사용자에겐 가끔 필요한 경우가 있어서 정리..

UPDATE, DELETE는 어차피 똑같기 때문에 생략합니다.

Native SQL 명령어는 본인 시스템에 설치 된 DB 버전 확인(BC문의) 후 구글링 하세요 

 

You won't use Native SQL if you use S/4 Hana, but you still need it sometimes if you use ECC...

I didn't write how to use UPDATE AND DELETE commands because they are same anyway...

If you want to know what is command of Native SQL, you should ask your BC which DB you use and google it...

 

반응형