[chapter03] OPEN SQL & NATIVE SQL

최초 작성일: 23년 2월 18일

최종 작성일: 23년 2월 21일

 

Chapter 03. open sql & native sql

01. overview

1.R/3 architecture

SAP R/3는 기본적으로 세개의 LAYER로 분류된다.

APPLICATION 서버와 DATABASE 서버는 물리적인 개별 서버로 구성된다.

Presentation layer

presentation layer는 쉽게 말해 개인 컴퓨터를 의미하며,

이 layer는 r/3 시스템과 사용자 간의 인터페이스를 제공한다.

sap gui(graphical user interface)는 개인 컴퓨터에 설치되는 터미널 화면이고 사용자는 이 gui를 통해서 데이터를 입력하거나 조회할 수 있다.

SAP GUI는 개인컴퓨터에 설치되는 터미널 화면이고 사용자는 이 GUI를 통해서 데이터를 입력하거나 조회할 수 있다.

Application layer

sap의 모든 프로그램은 application server에서 구동된다.

sap gui는 단지 터미널의 역할만 한다.

운영 서버의 application layer는 하나 또는 여러개의 application layer와 하나의 message server로 구성된다.

message server는 application server와 통신하며 부하가 적은 서버에 사용자가 로그온 되도록 한다.

여러개의 application server를 구동하는 것은 시스템의 부하를 분산하기 위한 목적이다.

sap에는 application server 레벨에서 local buffer가 존재하며

abap dictionary 테이블 레벨에서 버퍼 설정을 하게되면 데이터 베이스 까지 접근하지 않고.

local buffer에서 데이터를 읽어 온다.

이것은 sap의 성능을 향상시키는데 기여하며, open sql을 사용할 때만 가능하다.

dispatcher(분배기)는 work process를 적절히 분배하는 역할을 담당하게 된다.

사용 가능한 work process보다 더 많은 사용가자 application server에 접속해서 동시에 여러 프로그램을 실행할 수 있다.

Database layer

SAP의 모든 데이터를 데이터베이스 테이블에 저장하고 있는 계층이다.

데이터 베이스는 마스터 데이터, 트랜잭션 데이터 그리고 R/3 시스템의 모든 데이터를 저장하고 있다.

예를들면 데이터 베이스는 R/3 시스템의 실행환경을 정의하는 시스템 변수, 모든 프로그램의 소스 코드, 트랜잭션 데이터 등을 저장하고 이싿.

ABAP 프로그램은 코드, 스크랜, 함수, 테이블 등 다양한 항목들로 구성되어 있다.

이러한 것들은 R/3 Repository라 불리는 특별한 공간에 저장되어 있으며,

우리는 abap workbbench에서 repository 오브젝트들을 관리(생성/변경/조회) 하게 된다.

대부분의 언어는 application 계층과 database 계층이 분리 되어 있다.

application 계측에서는 사용자와 화면을 통해 상호작용을 하는 gui를 구성하고

데이터 베이스 레벨의 데이터를 조회하고 유지보수 하느 ㄴ역할을 주로 담당하게 된다.

database레벨에서는 DBMS를 통해 테이블/뷰/구조 등과 같은 데이터 베이스 오브젝트를 생성하고 데이터를 관리 할 수 있다.

이러한 환경의 개발자는 Application 의 GUI 화면을 통해 데이터를 수정하거나, 데이터 베이스에 직접 적속하여 SQL을 수행할 수 있다.

ABAP 프로그램에 사용되는 오브젝트들(Table, view, structure, types..)을 abap dictionary 라고 부른다.

이러한 오브젝트들의 정보를 metadata, data definition 또는 스키마 라고 정의 하며, 데이터 구조를 정의하고 관리하는 역할을 abap dictionary가 하게 된다.

2.SQL의 정의

sql이란 structured query language의 약자이다.

sql은 관계형 데이터 베이스에 사용되는 표준화된 언어이다.

• data manipulation langauge : 데이터 처리 언어 ( 테이블에 저장된 데이터를 검색, 삽입, 삭제, 갱신, 재구성하기 위해 사용되는 언어)
• data definition language : 데이터 정의 언어(데이터 베이스를 생성할 목적으로 사용하는 언어이다)
• data control language : 데이터 제어 언어(데이터를 보호하고 관리하는 목적으로 사용된다)

3.SQL의 종류

sql에는 open sql과 native sql 두 가지가 있다.

open sql은 abap언어 에서만 사용되며, database interface를 통해 native sql로 번역된다.

4.Open sql VS Native SQL

1)open sql

open sql은 데이터베이스 데이터를 조작할 수 있는 abap명령어로 구성되어 있음

oepn sql은 DDL,DCL을 사용할 수 없고 SELECT 문과 같은 DML만 사용할 수 있다.

2)Native SQL

데이터 베이스에 직접 접속하여 DML,DDL언어를 사용할 수 있다.

5.SQL과 local buffer

R/3 Architecture에서 지원하는 기술로서 데이터 베이스의 부하를 줄이는 중요한 역할을 담당한다.

단, 테이블의 technical setting에서 buffer를 사용한다고 설정해야한다.

6. OPEN SQL overview

OPEN SQL은 R/3 시스템에서 사용되며, 데이터베이스 데이터를 조회하고 변경하는 등의 기능을 수행한다.

데이터 베이스 시스템에 관계없이 SQL 결과와  error message가 반환된다.

abap dictionary에서 생성된 테이블, 뷰에만 적용되며,

7장에서 학습하는 cluster,pooled테이블에서 제한된 기능만을 수행하 ㄹ수 있다.

open sql은 기본적으로 클라이언트에 종속적이다.

r/3는 독립적인 여러 개의 클라이언트로 구성할 수 있다.

테이블은 클라이언트 독립 오브젝트로

100번 클라이언트에서 생성된 테이블은 200,300번 클라이언트에 반영되지만,

100번 클라이언트의 테이블에 생성된 데이터는 100번 클라이언트에만 존재한다.

open sql의 수행결과가 성공하면, 시스템 변수 sy-sbrc = 0 (성공하지 못하면 0 이외의 값을반화)

sy-dbcnt는 데이터 line 수를 반환한다.

 

open sql 명령어

select : 데이터 베이스 테이블에서 데이터를 읽음

insert : 데이터 베이스 테이블에서 데이터를 추가함

updata: 데이터베이스 테이블의 값을 변경함

modify: insert+update 기능을 수행함

update: 동일한 킷값이 있는 경우

insert:동일한 킷값이 존재하지 않는 경우

delect: 데이터 베이스 테이블의 값을 삭제함

 

 

02.OPEN SQL : 데이터 읽기

1.SELECT 구문

1) single line

sinlge 구문을 사용하면 데이터를 한 건만 가져오기 때문에 원하는 데이터의 조건을 정확하게 알고 있어야 한다.

SELECT SINGLE FROM WHERE

2) Several line

여러 라인을 조회할 때는 select 결과가 내부 테이블에 저장된다.

이것을 abap에서는 인터널 테이블이라 부른다.

distinct를 사용하게 되면, 중복된 값을 제외한다.

into구문의 결과가 저장되는 곳이 인터널 테이블이 아니라 필드 또는 work area일때에는 마지막에 endselect를 사용하여야 한다.

이 구문은 하나의 값을 읽어서 구조에 삽입하고 조건에 해당하는 값을 모두 읽어 올때까지 loop를 수행한다.

 

select ~ end select 구문이 반복 수행될 때마다 application server와 database 갑의 인터페이스가 수행되므로 한번에 데이터를 인터널 테이블에 저장하는 프로그램이 좀 더 효율적이다.

 

REPORT ZEDU17_01

DATA: gt_itab TYPE STANDARD TABLE OF sflight,
	 gs_wa TYPE sflight.
    
 SELECT * INTO gs_wa
 FROM sflight
 WHERE carrid EQ 'AA'.
 	WRITE: / gs_wa-carrid, gs_wa-connid.
 END SELECT.

REPORT ZED17_LV2

DATA: gt_itab TYPE STANDARD TABLE OF sflight,
      gs_wa TYPE sflight.
 
 SELECT * INTO TABLE gt_itab
 FROM sflgith
 WHERE  carrid EQ 'AA'.
 
 LOOP AT gt_itab INTO gs_wa.
 	WRITE: gs_wa-carrid, gs_wa-connid.
 END LOOP.

3) AS(alias)

4)동적인 select 구문 -> 이거 이해 필요(동적인 것의 의미가 무엇인건지 정확히 잘 모르겠음)

REPORT EDU17_LV2.
DATA: gt_itab TYPE STANDARD TABLE OF sflight,
      gs_wa LIKE LINE OF gt_itab.

DATA: gs_line(72) TYPE C.

gs_list LIKE TABLE OF gs_line(72).
gs_line = 'CARRID CONNID'.

SELECT DISTINCT (gs_line) INTO TABLE gs_line
FROM sflight.

IF SY-SUBRC EQ 0.
	LOOP AT gt_itab INTO gs_wa.
    	WRITE: / gs_wa-carrid, gs_wa-connid.
    ENDLOOP.
ENDIF.

select 구문의 칼럽을 동적으로 선언할 수 있다.

동적 구문을 저장하는 구조체 gs_line은 최대 72자까지 가능하며  char 타입 이어야 한다.

gs_line구조체가 null 값이면 * 와 같은 구문이 된다.

 

 

2.INTO 구문

SELECT 구문에서 조회한 결괏값을 변수(TARGET AREA)에 저장하는 기능을 수행한다.

1)구조체(Work area)

여러 칼럼의 한 라인만 조회하고자 할 경우에는 work area(변수, 구조체)에 값을 할당한다.

*기호를 사용하면 전체 칼럽의 값을 읽어오며,

corresponding fields of 구문을 사용하면 한번에 work area의 동일 필드명에 값을 할당한다.

2) internal table

여러 라인을 조회할 경우에는 인터널 테이블을 사용한다.

appending은 인터널 테이블에 추가로 insert하고

into는 인터널 테이블의 데이터를 삭제한 다음에 insert 한다.

package size는 인터널 테이블에 몇 개의 라인을 추가할 것인가를 설정한다.

package size5 라 설정하면 5개의 값을 여러 번 읽어와 인터널 테이블에 추가하게 된다.

이 경우에는 endselect 구문을 반드시 사용해야한다.

다른 테이블을 각각 참고하는 인터널 테이블을 기준으로 특정 조건의 값을 읽어올때

'into corresponding fields of '옵션을 사용해야 같은 필드명을 가진 인터널 테이블에 값이 전달된다.

3)single field

SELECT * INTO (f1,f2).
SELECT carrid, connid INTO (gv_carrid, gv_connid) FROM SFLIGHT.

3.FROM 구문

FROM 구문은 데이터를 SELECT할 대상 테이블(또는 뷰)를 지정한다.

FROM 구문 다음에는 하나의 테이블을 지정하거나 여러 개의 테이블을 JOIN할 수 있다.

ALIAS를 ㅅ사용하여 테이블 명에 별명을 붙일 수 있으며, 테이블 이름을 동적으로 선언할 수 있다.

FROM구문은 Table을 정의하는 부분과 데이터베이스 접근을 컨트롤하는 부분(option)으로 나누어진다.

SELECT * INTO FROM TABLE OPTION

•  CLIENT SPECIFIED : 자동 CLIENT 설정을 해제한다
• BYPASSING BUFFER: SAP Local buffer에서 값을 읽지 않는다. 테이블이 buffering이 설정되어 있더라도 바로 데이터 베이스 테이블에서 select 를 수행한다.
• UP TO n ROWS: SELECT에서 ROW 개수를 제한한다. 조회 조건에 날짜를 입력하지 않는 것과 같은 사용자 실수로 대량의 데이터를 요청할때 데이터 베이스 성능 저하를 예방할 수 있다.

3.1) 정적인 TABLE 선택

하나의 테이블을 정적으로 선언할 때 사용한다. ALIAS를 사용할 수 있으며 이 경우에는 테이블 명을 SELECT 구문에서 사용할 수 없다.

 

3.2) 동적인 TABLE 선택

테이블 이름을 동적으로 선언하여 사용할 수 있다.

이때 테이블 이름은 반드시 대문자로 지정하여야 하며, ABAP Dictionary에 존재하는 이름이어야 한다.

 

3.3) JOIN 구문

일반적으로 Primary key와 foreign key를 사용하여 join하는 경우가 대부분이지만 때로는 논리적인 값들의 연관으로 join하는 경우도 있다.

두 테이블 간의 연결조건은 on구문을 사용한다.

SELECT a~field1 b~field2 INTO gt_itab
FROM table1 as a, table2 as b
ON a~fiedl1 = b~field1.

join구문을 사용할때 inner구문을 명시적으로 언급하지 않아도 기본적으로 inner join으로 설정된다.

 

TYPES: begin of t_str,
  carrid type sflight-carrid,
  carrname type scarr-carrname,
  END OF t_str.
  
DATA: gs_str TYPE t_str.
  
SELECT a~carrid b~carrname
  INTO CORRESPONDING FIELDS OF gs_str
  FROM sflight AS a 
  INNER  JOIN scarr AS b
  ON  a~carrid EQ b~carrid
  WHERE a~carrid = 'AA'.
ENDSELECT.

  WRITE: / gs_str-carrid, gs_str-carrname.

 

 

3.4) inner join과 outer join

join에는 outer join과 inner join 두가지가 있으면 outer join은 아래와 같이 사용 된다

SELECT * INTO 
FROM sflgith AS a
OUTER JOIN scarr AS b
ON a~carrid EQ B~carrid
WHERE a~carrid = 'AA'.

기본적으로 모든 사원정보는 조회하고 자격증이 있는 경우에는 추가로 보여주고자 할 경우에는 outer join을 사용해야한다. abap open sql에는 left outer join만 사용할 수 있다.

3.5) line 수 제한

리포트 프로그램의 조회 조건인 SELECTION SCREEN에서 최대 적중 수를 제한 하는 목적으로 많이 사용된다.

SELECT * FROM UP TO <N> ROWS..

4.WHERE 구문

NE  같지 않음

<>  같지 않음

>< 같지 않음

 

1)Interval 조건

SELECT * FROM WHERE <s> BETWEEN <f1> AND <f2>

2)string 비교

문자열을 비교할 때는 LIKE 구문을 사용한다

COL2 = 'ABCDEFG'.

SELECT * FROM WHERE col2 LIKE 'ABC%'.

ABC로 시작하는 4자리 문자를 구할 경우, 예를들어 ABCD, ABCF과 같이 한자리만 비교할 경우에는 '_'문자를 사용한다

WHERE COL2 LIKE 'ABC_'.

3)LIST VALUE

IN 구문을 사용하여, 여러 조건에 속한 경우의 값을 가져온다.

SELECT * FROM WHERE 거주지 IN '서울','대구','부산'.

4)SELECTION TABLE

IN구문을 사용하여 SELECTION TABLE, RANGE 변수에 존재하는 값들을 조회할 수 있다.

SELECTION TABLE, RANGE 변수는 인터널 테이블과 유사하게 여러 ROW를 저장할 수 있는 변수 이다.

SELECT * WHERE <s> NOT IN 

5) Dynamic  조건

select 구문의 조건을 설정하는 where 구문을 동적으로 구성할 수 있다.

SELECT ~WHERE (<itab>)

6) for all entries 구문 -> 이거 다시 공부 요망

select for all entries IN (<itab>) WHERE

FOR ALL ENTRY 구문은 인터널 테이블과 데이터 베이스의 테이블을 JOIN하는 개념과 유사하며,

이것도 LOOP를 수행하면서 SQL을 수행한다.

데이터베이스에 반복적으로 접근하기 때문에 데이버베이스 테이블 간의 JOIN보다는 비효율적일 수 있지만 

ABAP언어 에서 많은 경우에 유용하게 활용된다.

 

5.GROUPING 구문

aggregate 함수를 사용하려면 select 구문에 group by를 기술해야한다.

group by 구문은 테이블의 특정칼럼에 같은 값들이 존재할 때 이 값들의 정보를 요약하여 한 줄의 정보로 조회되게 한다.

 

aggregate 함수의 종류

AVG : 평균

COUNT : 개수

MAX: 최댓값

MIN : 최소값

STDDEV: 표준 편차

SUM: 합계

VARIANCE: 분산

 

group by 구문도 동적으로 지정할 수 있다.. -> 동적으로 지정한다는 것이 무엇일까..

GROUP BY (itab).

DATA: gv_carrid TYPE sflight-carrid,
      gv_connid TYPE sflight-connid,
     gv_paymentsum TYPE i,

SELECT carrid connid AVG(paymentsum) 
INTO (gv_carrid, gv_connid, gv_paymentsum) **** 요거는 유용하게 쓰일듯
FROM sflgith GROUP BY carrid, connid.

WRITE: / gv_carrid, gv_connid, gv_paymentsum
ENDSELECT.

error가 왜 발생했는지 확인 요망

6.GROUPING 조건: HAVING

having 구문은 group by로 조회한 select 구문에 그룹의 조건을 추가한다.

where 조건에서처럼 동적(dynamic) 선언이 가능한다

SELECT carrid connid AVG(paymentsum)
INTO (gv_carrid, gv_connid, gv_paymentsum)
FROM sflight
GROUP BY carrid connid
HAVING AVG(paymentsum) > 10000.

7.SORT 구문

select 결과로 조회된 데이터가 order by에 기술된 칼럼 기준으로 정렬된다.

order by를 사용하지 않으면 임의로 정렬된 결과가 조회된다.

order by primary key

테이블의 key에 의해 정렬된다

select * 구문인 경우에만 사용할 수 있다

join구문 및 view 에는 사용할 수 있다.

order by [ascending|descending]

order gy구문도 동적 선언이 가능하다. itab 타입은 char이며 72자를 넘기지 못한다.

SELECT carrid connid AVG( paymentsum ) as paymentsum
INTO (gv_carrid,gv_connid,gv_paymentsum)
FROM sflight
group by carrid connid
ORDER BY paymentsum.  * order by 구문에서는 AVG(paymentsum)와 같은 aggregate 함수를 사용할 수 없어서 select 구문에서 as 명형어를
이용하여 'paymentsum'이라는 alias를 설정 하였다

WRITE:/ gv_carrid, gv_connid, gv_paymentsum.

ENDSELECT.

8.Subquery

subquery는 select 구문의 where 조건에 또 다른 select 구문을 추가하여 값을 제한하는 목적으로 사용한다.

8.1) scalar subquery

subquery를 이용해 특별한 조건을 where 구문에 추가할 수 있다.

subquery의 select 구문에는 칼럼 하나만 선언할 수 있다.

select 절 안에 기술된 select 절로 정의된다.

즉 하나의 행으로부터 하나의 칼럼 값 ( 또는 aggregate 함수)만을 반환하는 subquery로서 join구문과 유사한 역할을 한다

1) scalar subquery는 반드시 한 칼럼만을 반환해야한다.

2) scalar subquery는 nested loop방식으로 처리된다.

3)scalar subquery가 실행되는 횟수는 row 수 이다

4)반복되는 코드나 마스터 유형의 테이블을 조회하는 경우 사용하면 효율적이다.

 

SELECT carrid connid AVG( paymentsum ) as paymentsum
INTO (gv_carrid,gv_connid,gv_paymentsum)
FROM sflight as a
WHERE carrid IN ( SELECT carrid from spfli WHERE carrid = a~carrid
                  AND connid = a~connid)
AND a~carrid = 'AA' .

WRITE:/ gv_carrid, gv_connid, gv_paymentsum.

ENDSELECT.

8.2) non-scalar subquery

subquery 의 결과가 존재하면 true를 반환하여

존재하지 않으면 false를 반환한다.

exists구문을 이용하여 구현한다.

Report ZSELFPRACTICE.

DATA: gv_carrid TYPE sflight-carrid,
      gv_connid TYPE sflight-connid,
      gv_paymentsum TYPE sflight-paymentsum.

SELECT carrid connid paymentsum
INTO (gv_carrid,gv_connid,gv_paymentsum)
FROM sflight as a
WHERE EXISTS ( SELECT carrid from spfli WHERE carrid = a~carrid
                  AND connid = a~connid )
AND a~carrid = 'AA' .

WRITE:/ gv_carrid, gv_connid, gv_paymentsum.

ENDSELECT.

03.OPEN SQL: 데이터 변경

1.INSERT 구문

테이블에 하나 또는 여러 개의 데이터를 삽입한다.

<target>은 테이블 이름으로, 동적으로 선언할 수 있다.

INSERT INTO <target>
[ASCENDING|DESCENDING].

INSERT INTO dbtab [CLIENT SPECIFIED]
INSERT INTO <name> [CLIENT SPECIFIED].

1)single line

테이블에 하나의 line을 삽입하기 위한 문장이다.

<wa>는 테이블과 같은 구조로 선언되어야 한다.

INSERT <dbtab>구문을 쓰려면 tables:<dbtab>선언이 되어야 한다.

INSERT INTO <target> VALUES <wa>.
INSERT <target> FROM <wa>.

2) several lines

인터널 테이블의 모든 값을 한번에 테이블에 삽입한다.

같은 key 값을 insert 하게 되면, dump error가 발생하게 되는 데 이를 방지하지 위해,

accepting duplicate keys 구문을 사용한다.

insert 구문이 실패하게 되면 sy-subrc값 4를 반환한다.

INSERT INTO <target> FROM TABLE <itab> [ACCEPTING DUPLICATE KEYS]

 

테이블 데이터 확인하기

테이블의 데이터를 확인하는 방법은 크게 2가지가 존재한다.

T-CODE: SE11(ABAP DICTIONARY) : 테이블과 같은 오브젝트를 조회/수정/생성 하는 용도로 사용됨.

T-COD: SE16(Data Browser): 데이터를 조회하는 용도로 사용됨.

 

2.UPDATE 구문

DATA gs_spfli TYPE spfli.

MOVE 'KO' to gs_spfli-carrid.
MOVE '0001' to gs_spfli-connid.
MOVE 'Busan' to gs_spfli-cityfrom.

UPDATE spfli from gs_spfli.

UPDATE spfli      * 요걸 좀 유의깊게 보자
set CITYTO = ' Shanghai '
where carrid = 'KO'
AND connid = '0001'.

IF SY-SUBRC eq 0.
  write ' update has been succed'.
endif.

3.DELETE 구문

4.MODIFY(inserting or changing lines) 구문

modify는 update구문과 insert 구문을 합한 기능을 수행한다.

킷값을 가지는 데이터가 테이블에 존재하면 update하고

존재하지 않을 때에는 insert를 수행한다. 

1)single line

MODIFY <target> FROM <wa>.

MODIFY <dbtab>.  *modify <dbtab> 구문을 쓰려면, TABLES: <dbtab> 선언이 되어야 한다. -> 이게 궁금하다..

2)several line

MODIFY <target> FROM TABLE <itab>.

04.NATIVE SQL -> 사용 빈도수가 적을 것 같아 패스

1.기본구문

2.NATIVE SQL과 인터널 테이블

3.NATIVE SQL과 JOIN구문

4.NATIVE SQL과 SUBSTRING

5.NATIVE SQL과 DB LINK

6.NATIVE SQL 실행 툴