나태쿤의 개발이야기

공부를 하다 시간이없어 바로 벌크 쿼리를 야생형으로 공부해보았다.

미래의 나를 위해 정리를 해보자

우선.

벌크 쿼리란?

단순하게 10개의 데이터를 DB에 넣어야할때, 

10번을 나눠넣냐 1번으로 넣냐 이렇게 생각하면 쉽다(수정도 마찬가지)

좀더 쉽게 예를들면

엑셀에서 10,000개의 행을 읽었을때 과연 나는 for 문을 돌면서 10,000 번 insert 를 해야할때

부담을 느낄 수 밖게 없다.

이럴때 쓰는게 벌크 쿼리이다.

다음과 같이 테스트 하기위해서 테이블을 만들었다.

여기다 insert 할 것이다.

그리고 데이터가 있는 테이블을 하나 정했는데 다음과 같으며

딱 3개의 칼럼만 사용할 것이니 부담없이 보자

ename, job, sal

이제  PL / SQL 문을 알아볼것인데

이는 하나의 프로그래밍 언어 레벨로 이해하면된다.

따라서 반복문, 조건문, 예외처리 같은 문법들도 사용할 수 있다.

심플하게 문법을 알아보자

구조는 다음 과 같다

DECLARE (1)

     ...

BEGIN (2)

     ...

END;

(1) 영역 :

타입을 선언하고, 변수에 타입을 바인딩한다

자바로 비유하면 인스턴스 멤버들을 정의한다, 즉  String 이라는 타입을 정의하고, name 이라는 필드에 타입을 바인딩한다. 결론은 String name; 을 만드는것이다. 이때 초기화도 직접해줄수 있다

또 자바에서도 다른 클래스를 타입으로 가질수있는데 private Book book, private  Contents contents 이런식으로

이또한 가능하다

사진으로 이해해보자

EMP 테이블의 하나의 로우를 타입으로 갖는 "타입"을 정의하고

l_emp 라는 변수가 그 "타입"이라고 정의했다.

EMP 테이블의 레코드가 타입이라는건 EMP%ROWTYPE 이라고 작성한다

만약 String name, int age 처럼 하나의 칼럼만 정의하고싶다면

이렇게 하면된다

EMP.job%TYPE => EMP 테이블의 job 칼럼의 타입을 사용한다

만약에 초기화를 직접 하고싶다면

이런식의 문법으로 하면된다

여기서 주의할게 죄다 List 처럼 컬렉션이라고 생각하면 편안하다

이제 begin ~ end 문은 실제 동작할 부분을 작성하는 곳인데 좀더 봐야한다 .

예제를 보면서 감을 익히자

첫번째 예제 - insert + 단순

1. 숫자 15개를 채울 수 있는 배열을 만든다

2. 숫자 15개를 채울 수 있는 리스트를 만든다

3. 반복문을 돌면서 각각 출력해본다

4. 각 루프 마다 값이 짝수나 홀수이면

"배열", "짝/홀수", "값" ,  "리스트","짝/홀수", "값" 

이런식으로 my_bulk 테이블에 insert 한다

출력은  DBMS.OUTPUT.PUT_LINE() 을 이용하면 된다 

실제 출력 결과는 다음처럼 나왓다

my_bulk 테이블은 다음과같이 데이터가 들어왔다

잘들어왔다

지금 상황은

직접 값을 정의한다음 그 값을 반복문과 조건문을 써서 insert 한 경우이다

두번째 예제 - Update 

미안하다, 

PK 가 필요해서 두번째 테이블을 만들었다... (update 벌크쿼리를 위해)

우선 아까 만든 PL/SQL 에 한줄씩 추가해서 기본데이터가 들어가게 만들었다

자 이제 pk도 있겠다. 준비는 되었다

update 벌크 쿼리에 대해서 알아보자 

여기서부턴 좀 자세히 알아볼건데

세가지 유형을 알아볼건데, update 상황에서 이를 알아보자 (insert문도 가능)

첫번째 유형  - 기존 방식

두번째 유형 - CURSOR 방식

세번쨰 유형 - FORALL 방식 

1. 기존 방식

insert 에서 하던 방식인데 결국 이와 비슷하다 볼 수 있다

여기서 update 쿼리를 날리진 않았지만 DB 를 더 괴롭히는 방식이라고 볼 수있다

결과는 다음과 같다

2. CURSOR 도입

자바를 공부했다면 JDBC 를 가지고 JAVA로만 직접 select 를하고

결과셋을 가지고 반복문 돌면서 데이터를 다룬적이 있을 것이다 그때도 CURSOR (커서) 를 이용하는데

그와 같은 개념이다 (포인터)

언제사용하는가? 소량의 데이터에는 적합하다. 대량의 데이터는 FORALL 문을 써야한다

커서는 루프마다 실제 쿼리가 나간다,

즉 에러가나면 그 루프에서 멈추고 이전까지 작업된 내용은 반영되어있다.

다음은 c1 칼럼값이 리스트인 데이터들을 2건씩 가져와서

c3 값을 10씩 증가시키는 PL / SQL 문이다

기존 데이터

결과 데이터

3. FORALL 도입

FORALL 을 사용하면 커서때와 다르게

JPA 처럼 SQL 쓰기저장소에 쭉쭉쌓였다가 마지막 한번에 나간다.

즉, 1번만 나간다고 생각하면된다 따라서 대용량 데이터 작업에 유용하다.

또한 원자성을 지키기 때문에 하나라도 실패하면 전체 실패하게된다.

전체 실패, 전체 성공 두개뿐이다.

주의할게 있는데 FORALL 내에서는 insert, update, delete 문을 쓸수있고 

for문처럼 콘솔에 찍는것은 할 수 없다 for문에서 해야한다.

그리고 순회하는 타입도 레코드형이면안되고 단순 타입이어야한다

자바로 비유하면 List<Book> 은 안된다 (커서쓸때는 가능)

List<String> , List<Integer> 형태만 된다 .

코드는 좀더 짧아졌다.

앞으로 테이블 결과는 10씩 증가되는거니 생략하겠다.

3. CURSOR + FORALL  조합 

인간의 욕심은 끝도없다

아무리 한방이 좋다해도 메모리가 부담될 수 있다.

이때는 커서와 조합해서 써야한다

하지만 역시 주의할게. 트랜잭션 단위이다

만약 forall 를 loop 가 감싸고있다면 이 루프 갯수만큼 트랜잭션이 있는 것이다.

따라서 savepoint 가 필요하고, 예외처리도 필요하다.

환경 19c

역시나 테이블은 이거쓸꺼다

p_store 별로 합계를 구할때 보통 이렇게 쓸것이다

이건데 이걸 말하고싶은게 아니다.

바로 누계(부분합)를 구하고싶다.

문법은 간단하니 사진으로 대체 하겠다.

환경 19c

이전 글 

https://nataekoon.tistory.com/179

이 테이블의 데이터를 또 쓸것이다

이전 블로그를 참고했다면 문법은 거의 똑같다

사진 한장으로 대체 하겠다

19C 환경

웹개발시 엑셀이나 어떤 표를 그릴때 적합할 것 같다.

테이블이 다음과 같은 데이터가 있다고 보자

P_STORE 별로 P_DATE 일자가 있는것 같고, 그에 따른 P_TOTAL 값이 보인다 (코드, 수량은 무시하자)

이제 P_STORE 가 1000 인 제품의 표를 만들고 싶은데 하나의 칼럼을 추가하여 이전행의 값이 나왓으면 좋겠다

그리고 다음 칼럼에는 이전행과의 차이값을 나타냈으면 좋겠다. (증분값)

말로 설명하면 어려우니 한단계 씩 차례를 밟아보자

그림으로 보면 알 수 있을 것이다 

LAG(칼럼명, 건너뛸 로우수, 디폴트 값)  에서 건너뛸 로우수

즉, offset 을 1 로 했기에 이전행 = 1줄의 이전행 이라는 것이다

현재행 - 이전행 = 차이 를 구할 수 있다

이제 where 문을 없에고 p_store 별로 구해보자

partition by 칼럼명 문을 추가하면  되겠다.

19c 환경

사진 한장으로 대체한다

참고로 다음과 같이 over 의 괄호안에 partition by 구문을 사용하면

특정 파트별로 랭크를 구할 수 있다

pivot, unpivot 을 알아보자

환경: 19C XE

엑셀에 있는 피벗과 똑같다.

(필자는 엑셀이 너무 어려워서 저 비유가 통하지 않았었다(쓰긴했지만))

피벗과 언피벗은 행렬과 그 반대의 역행렬의 관계이다

피벗은 행의 기준(ROW)을 열의 기준(COLUMN) 으로 변경한다

언피벗은 반대로 열의 기준(COLUMN) 을 행의 기준(ROW) 으로 변경한다

우선 피벗에 대해 먼저 알아보자

테이블과 샘플 데이터 먼저 넣자

피벗을 행의 관점을 열의 관점으로 변환한다고 했다.

현재 6개의 로우 들이 GRADE 칼럼에 값으로서 구분을 하고있다.

피벗은 이를 열의 관점으로 바꾸게 해준다.

문법은 다음과 같다

타겟 칼럼 GRADE 의 값을  열의 관점으로 NORMAL, GOLD, GUEST 칼럼으로 전개가 되었다.

문법 설명은 잠깐 참고. 다시한번 관찰하자

로우의 관점 에서 열의 관점으로 바뀌었다. 즉, 칼럼이 늘어났다

이정도로도 괜찮지만,  만약 여기서 요구사항이

GRADE 별 최대 지불액을 구해라 ...

이라면 from 절의 메인 테이블에서 user_id 를 제외한다음 작성하면 된다

문법을 그래도 보긴 봐야하는데

해석의 순서는 개인적으로  2 -> 3 -> 1 순서가 좋은것 같다

2: 열로 바꿀 기존 테이블의 칼럼을 명시하고

3: 그 해당 칼럼의 값의 경우의 수를 나열 해준다

1: 해당 경우의수에서 가져올 기준을 명시한다 최대, 갯수, 최소

이것도 좋다, 하지만 이전 쿼리 결과에서는 그래도 각 GRADE 별로 인원을 볼 수 있었다

예를들면 NORMAL 은 user1, user2, user3 총 3명이 있었다.

이렇게 기준 칼럼의 대상의 갯수도 다음처럼 작성하면 가져올 수 있다

위의 사진과 같이 pivot 안에 count 를 새어버리면, 각 칼럼을 대상으로 "칼럼명_COUNT"  형태로 자동으로 칼럼이 생긴다

이렇게하면 몇건에 대해서 MAX를 구했는지도 알 수 있다

unpivot 은 졸려서 다음에 이어서 올리겠다..

자, UNPIVOT에 대해서 알아보자 

칼럼기준을 로우기준으로 바꾸는것이다.

예를 들기위해서 위에서 썻던 이 쿼리를 그대로 쓸 것 이다

칼럼이 늘어난것을 다시 확인할 수 있다.

자 이제, 언피벗으로 다시 되돌려보자

로우-----▶ 칼럼 -----▶ 로우 

       [피벗]       [언피벗]

이렇게 되돌리면 원래대로 돌아갈 수 있다.

물론, 다른 테이블을 가지고 해도되는데, 예제가 막당히 없다 .

직관적이니 설명은 생략한다

우선 이미지는 다음 처럼 받는다

docker pull doctorkirk/oracle-19c

(참고, https://hub.docker.com/r/doctorkirk/oracle-19c)

docker run --name oracle-19c \
-p 1521:1521 \
-e ORACLE_SID=XE \
-e ORACLE_PWD=패스워드 \
-e ORACLE_CHARACTERSET=KO16MSWIN949 \
-v 내볼륨위치:/opt/oracle/oradata \
doctorkirk/oracle-19c

* 안전하게 내 볼륨위치 권한 777 주는게 정신건강에 이로움

-✨ sys 패스워드를 변경하려면 변경

# alter user sys identified by "[ 새로운패스워드입력 ]";

✨ 내가 사용할 계정 만들기

# CREATE USER [계정명] IDENTIFIED BY "[패스워드]";

✨  생성한 계정에 기본적인 연결 및 리소스 사용 권한 부여
GRANT CONNECT, RESOURCE TO [계정명] ;

✨ 생성한 계정에 무제한 테이블스페이스 사용 권한 부여
GRANT UNLIMITED TABLESPACE TO [계정명];

✨ 커밋

commit;

쌩 리눅스로 오라클 11 버전을 설치하면 고통이 따른다

이럴때 역시 도커가 만능이다

여기서 사용하는 이미지는 해당 이미지이

https://hub.docker.com/r/oracleinanutshell/oracle-xe-11g

명령어는 다음과 같다

oracle developer 로 접속해보자

컨테이너의 데이터를 보존하고싶으면 바인드 마운트보단 볼륨 마운트를 사용하자

+ 추가

생성한 컨테이너에 접속한다 bash 쉘로

sqlplus 명령어로 오라클 쉘에 접속하여

초기 계정은 system / oracle 의 패스워드이다

password 명령어로 암호를 변경하고

나는 공부하는데 hr 계정이 필요해서 권한도 풀어주고 체크했다