목차


DTO란?

An object that carries data between processes in order to reduce the number of method calls.

  • 즉, 메서드 호출 횟수를 줄이기 위한 목적으로 프로세스들 간의 데이터를 전송하는 객체이다.
  • 메서드 호출을 클라이언트-서버의 네트워크 통신으로 바꾸면 네트워크 통신 비용을 줄이는 목적으로도 생각할 수 있다.
  • 예를 들어 Artist와 Album 정보를 얻기 위해 REST API를 두 번 호출하는 것보다 Album정보에 Artist가 포함된 DTO를 한번에 응답 하는게 하는게 통신 비용을 줄일 수 있겠다.
  • 최근에는 단순히 네트워크/메서드 호출 횟수를 줄이는 목적 보다 더 넓게 데이터 전송 역할로 많이 사용되는 것 같다.
  • 단순히 같은 하나의 엔티티를 조회하는데 도메인 엔티티가 Presentation 레이어로 노출되지 않아야 하니 DTO를 사용하는 경우도 왕왕 있다.
  • 그래서 더 적합한 용례로 MVVM 패턴의 ViewModel로 부르자는 목소리도 있다.
  • Presentation 레이어만 생각하면 좋아보이긴 하는데, 복잡한 통계용 쿼리 결과를 반환하는 Repository 레이어에서 DTO를 사용한다. (QueryDSL 공식 메뉴얼 예제에서 DTO 사용 등)
  • 결론적으로 DTO는 레이어간 데이터 전송 객체로써 역할을 함과 동시에 원래의 목적 또는 이점은 메서드/통신 호출 비용을 줄이는 것으로 이해하면 된다.

또 다른 이점

  • 메서드 호출 횟수를 줄이는 것 이외에도 직렬화(serialization) 매커니즘을 캡슐화하는 장점이 있다.
  • 그럼 직렬화란 무엇인가?

직렬화란

In computing, serialization is the process of translating a data structure or object state into a format that can be stored (for example, in a file or memory data buffer) or transmitted (for example, over a computer network) and reconstructed later (possibly in a different computer environment).

  • 컴퓨터 공학에서 직렬화는 데이터 구조체 또는 객체 상태를 저장할 수 있는 포맷(파일, 메모리 데이터 버퍼 등)또는 전송된(예, 컴퓨터 네트워크) 후 재구성(아마 다른 환경의 컴퓨터에서)할 수 있는 형식으로 변환하는 프로세스를 말한다.
  • Java에서는 Object(객체)를 Byte Stream으로 변환하는 것을 말한다. Deserialization은 그 반대이다.
  • Object는 메모리에 올려져 있는 Data Buffer이기 때문에 파일이나 네트워크 통신을 위해선 Byte Stream으로 변환이 필요한데, 이 때 직렬화가 필요하다.
  • 더 자세한 것은 추후에 다루고 여기선 DTO와 연관되어 얘기해보려 한다.

다시 돌아가 DTO의 직렬화 이점

  • 클라이언트에서 들어온 값을 서버에서 사용하기 위해 직렬화 과정이 필요한데, 각 컨트롤러(Facade)에서 직렬화를 처리해야한다.
  • DTO를 사용한다면 이 직렬화 처리를 캡슐화해서 가질 수 있어 컨트롤러에서 직렬화하는 책임을 없앨 수 있다.

DTO와 VO의 차이


VO란?

  • Value Object의 약자이다.
  • 과거 VO는 DTO와 비슷한 용례로 사용이 되곤 했다.
  • 마틴 파울러의 정의에 따르면 VO는 다음과 같다.

A small simple object, like money or a date range, whose equality isn't based on identity.

  • 동일성을 기반으로 하지 않는 동등성을 가진 돈, 날짜와 같은 작고 단순한 객체

동일성(Identity)과 동등성(Equality)

  • 동일성은 실제 같은 인스턴스(레퍼런스 주소가 같음)이며 Java에서는 == 연산자로 비교한다.
  • 동등성은 실제 인스턴스는 다르지만 같은 정보를 가진다는 뜻으로 eqauls() 메서드를 오버라이드 하여 구현한다.

다시 돌아가 VO

  • 동일성을 기반으로 하지 않는 다는 것은 레퍼런스 주소를 비교하지 않는다는 것이다.
  • 즉, VO는 eqauls() 메서드를 오버라이드 하여 같은 값을 가진 객체들끼리는 동등하게 취급하는 객체이다.
  • VO는 마치 원시 타입처럼 취급하여 레퍼런스 타입과 구분하기 용이하기 위해 사용한다.
  • DDD(Domain Driven Design)에서는 VO에 불변성(Imuutable) 특징을 더 한다. 즉, VO는 수정할 경우 VO의 값을 변경하는 것이 아닌 수정된 값을 인자로 인스턴스를 새로 생성해야 한다.

그래서 DTO와 VO 차이는?

  • 예전처럼 VO를 DTO처럼 쓰는게 아닌 VO는 불변한 값 동등성을 가진 객체로 사용하고, DTO는 데이터 전송 객체로 사용하는 차이가 있다.

DTO 활용

클라이언트 요청 파라미터 받기

@RequestMapping("/boards")
@RestController
public class BoardController {

    private final BoardService boardService;

    public BoardController(BoardService boardService) {
        this.boardService = boardService;
  }

    @GetMapping
    public ResponseEntity<List<BoardResponse>> getBoards(BoardQueryRequest boardQueryRequest) {
        return ResponseEntity.ok(boardService.getBoards(boardQueryRequest));
    }
}

public class BoardQueryRequest {
    private String title;
    private int parentBoardId;
    private LocalDate createDateStart;
    private LocalDate createDateEnd;
}
  • Board 목록을 조회하는 컨트롤러 및 검색용 Request DTO 객체(BoardQueryRequest)

클라이언트에 응답하기


@Service
public class BoardServiceImpl implements BoardService {

    private final BoardRepository boardRepository;

    public BoardService(BoardRepository boardRepository) {
        this.boardRepository = boardRepository;
    }

    public List<BoardResponse> getBoards(BoardQueryRequest boardQueryRequest) {
        return BoardResponse.ofs(boardRepository.findAllBy(boardQueryRequest));
    }

}

public class BoardResponse implements Serializable {

    // serial uid

    private String title;
    private String category;

    // getters...
    // builder

    public static List<BoardResponse> ofs(List<Board> boards) {
        return boards.stream()
                    .map(BoardResponse::of)
                    .collect(toList());
  }

    public static BoardResponse of(Board board) {
        return BoardResponse.builder()
                    .title(board.getTitle())
                    .category(board.getCategoryName())
                    .build();
  }

}

DTO 너무 귀찮아!?

  • 처음에 애플리케이션이 단순할 때, Entity를 도메인 레이어 경계 밖으로 데이터 전송하는 역할로 DTO를 사용할 경우 Entity의 속성과 DTO의 속성이 1:1로 매핑된다.
  • 이 때, Entity ↔ DTO의 변환 로직을 일일히 만드는 것이 귀찮고, 지루해 보일 수 있다.
  • 심지어는 코드 중복으로 생각될 수도 있다.
  • 그럴 때 ModelMapper 같은 라이브러리를 사용하면 편리하게 변환이 가능하다.
  • 개인적으로는 처음부터 분리하는게 여러모로 좋은 경우가 많아서 아주 단순한 CRUD라도 DTO를 꼭 사용하는 편이다.

참고자료

OAuth 2.0

OAuth 2.0 이란?

OAuth 2.0은 인가(Authorization)를 위한 산업표준 프로토콜이다. 2006년에 만들어진 OAuth 프로토콜을 대체한다. OAuth 2.0은 웹 애플리케이션, 데스크톱 애플리케이션, 휴대폰, IoT 장비 등을 위해 특정 인가 절차를 제공하면서 클라이언트 개발자 단순성에 집중한다. 이 표준과 이에 대한 확장은 IETF OAuth Working Group에서 개발되고 있다.

(출처: https://oauth.net/2)

용어 설명

  • 인가(Authorization): 인증된 클라이언트에게 허가된 권한을 부여하는 행위
  • 인증(Authentication): 클라이언트가 자신이 주장하는 신분을 증명하는 행위
  • 인가 서버(Authorization Server): 리소스주인 인증에 성공하고 권한을 얻은 후 클라이언트에게 엑세스 토큰(Access Token)을 발급하는 서버
  • 리소스 서버(Resource Server): 보호된 자원(예, 사용자 정보)을 호스팅하는 서버로, 엑세스 토큰을 사용한 요청을 받아 보호된 자원을 응답할 수 있다.
  • 리소스 주인(Resource Owner): 보호된 자원에 대한 접근 권한을 부여할 수 있는 객체로, 리소스주인이 사람일 경우 앤드유저(end-user)라고 한다.
  • 클라이언트(Client): 리소스주인을 대신하여 권한이 있는 보호된 자원을 요청하는 애플리케이션

인가 승인 방식

1. Authorization Code

권한 코드(Authorization Code)클라이언트리소스 주인 사이의 중개 역할을 하는 인가 서버를 통해 얻을 수 있다. 리소스 주인으로부터 직접 인가 요청을 받는 대신, 클라이언트리소스 주인RFC 2616에 정의된 에이전트(예, 웹 브라우저, 모바일 앱 등)를 통해 인가 서버로 보내고, 권한 코드와 함께 다시 클라이언트로 보냅니다.

리소스 주인권한 코드와 같이 클라이언트로 보내기 전에, 인가 서버리소스 주인을 인증하고 권한을 얻는다. 리소스 주인은 오직 인가 서버를 통해 인증을 하므로 클라이언트리소스 주인의 자격증명에 대해 절대로 클라이언트와 공유되지 않는다.

권한 코드는 중요한 보안 이점을 제공하는데, 클라이언트를 인증하는 기능과 리소스 주인이 사용하는 에이전트(예, 웹 브라우저)에 직접 액세스 토큰을 전달하지 않고 직접 클라이언트에 전송한다.

Authorization Code Grant Flow (출처:  https://oauthlib.readthedocs.io/en/latest/oauth2/grants/authcode.html )

 

2. Implicit

암시적 승인(Implicit Grant) 방식은 Authorization Code Grant 방식을 단순화한 방식이다. 자바스크립트 같은 스크립트 언어를 사용하여 웹 브라우저에서 구동되는 클라이언트에 최적화되었다. 암시적 승인 절차에서는 권한 코드를 클라이언트에게 발급하는 대신, 리소스 주인의 인가 결과로써 액세스 토큰이 직접 발급된다. 이 권한 유형은 엑세스 토큰을 얻기 위한 중간 자격 증명(예, 권한 코드)이 발급 되지 않기 때문에 암시적 승인이라고 한다.

암시적 승인 절차에서 엑세스 토큰을 발행할 때, 인가 서버는 클라이언트를 인증하지 않는다. 몇몇 경우에는 클라이언트 엑세스 토큰을 클라이언트에 전달하는데 사용된 redirect_uri를 통해 클라이언트 신분을 확인할 수 있다. 엑세스 토큰은 리소스 주인이 사용자 에이전트에 접근하여 리소스 주인 또는 다른 애플리케이션에 노출될 수 있다.

암시적 승인은 엑세스 토큰을 얻는데 필요한 왕복 횟수를 줄이므로 일부 클라이언트의 응답성과 효율성을 향상시킵니다. 그러나 이러한 편리함은 10.310.16에 설명한 것 처럼 보안에 좋지 않을 수 있다.

엑세스 토큰에 대한 검증이 생략되므로 엑세스 토큰이 오용될 수 있다는 얘기.

Implicit Grant Flow(출처:  https://oauthlib.readthedocs.io/en/latest/oauth2/grants/implicit.html )

 

3. Resource Owner Password Credentials

리소스 주인 패스워드 자격증명 승인 방식이다. 일반적으로 엔드 유저의 id, password를 사용하여 직접 인가 절차를 수행하여 엑세스 토큰을 얻는 방식이다. 리소스 주인과 클라이언트 간에 높은 수준의 신뢰도가 있을 경우 사용되어야 하며, 다른 인가 승인 방식은 사용되지 않아야 한다.

이 승인 방식은 비록 클라이언트가 리소스 주인의 자격증명에 직접 접근하게 되지만, 엑세스 토큰을 얻는 한번의 요청에만 자격증명을 사용한다. 클라이언트가 리소스 주인의 자격증명을 저장할 필요성이 없고 긴 유효기간을 가진 엑세스 토큰을 받아 쓸 수 있다.

Resource Owner Password Credential Flow(출처:  https://oauthlib.readthedocs.io/en/latest/oauth2/grants/password.html )

 

4. Client Credentials

클라이언트 자격증명 인가 방식이다. 권한 범위가 클라이언트가 통제하는 보호된 자원에 제한될 때 또는 보호된 자원이 이전에 인가 서버와 연결된 경우에 클라이언트 자격증명을 사용할 수 있다. 클라이언트 자격증명은 클라이언트가 자신을 대신해 행동할 때 또는 이전에 인가 서버와 연결된 보호자원에 접근하는 요청을 할 때 사용되는 인가 방식이다.

Client Credentials Grant Flow (출처:  https://oauthlib.readthedocs.io/en/latest/oauth2/grants/credentials.html )


풀어쓰는 OAuth 2.0 인가 절차 이야기

우리가 흔히 경험하는 OAuth 2.0 예는 대표적으로 소셜 로그인을 통한 제 3의 앱 사용이 있다.

예를 들어 애니팡 모바일 앱 게임을 하는데 카카오톡 계정으로 로그인하는 것이다.

이 과정을 조금 풀어쓰면 다음과 같다.

  1. 카카오톡 인가 서버 존재
  2. 카카오톡 계정정보 담당하는 API 서버(리소스 서버) 존재
  3. 써드파티 앱인 애니팡은 클라이언트로써 인가 서버에 클라이언트 등록을 한 상태
  4. 리소스 주인인 일반 사용자가 애니팡의 카카오계정으로 로그인 버튼 클릭
  5. 인가 서버가 설정한 혹은 애니팡이 클라이언트 등록시 설정한 승인타입(Grant Type)에 따라 인가 절차 수행
  6. 애니팡은 인가 서버가 사용하는 Authorization Endpoint(예, https://dev.kakao.com/oauth/authorize)로 3번에서 등록한 client_idresponse_type 파라미터를 필수로 전송해야한다. 이 때 response_type은 표준에서 정한 문자열(code, token 등) 혹은 인가 서버response_type에 설정한 값을 입력해야하며, redirect_uri를 선택적으로 파라미터에 추가할 수 있다.
    (https://dev.kakao.com/oauth/authoirze?client_id=anypang-client-id&response_type=code&redirect_uri=https://foward.anypang.com/game/main)
  7. 인가 서버가 설정한 사용자 인증(Authentication) 절차를 거쳐(예, 카카오톡 로그인, 제공 권한 동의) 인증한다.
  8. 인증이 완료되면 access_token을 발급하며 애니팡 게임 메인화면(redirect_uri에서 설정)으로 이동한다.
  9. 애니팡은 카카오톡 계정 API (리소스 서버)에 사용자 프로필, 친구목록 등을 조회하여 게임 화면에 표시하는데 사용한다.

위의 이야기, 절차 등은 Authorization Grant 방식, 인가서버 설정 방식에 따라 달라질 수 있다. (예시는 Implicit 방식으로 response_type=token 으로 동작한다, 인가코드에 대한 추가 승인 없이 사용자 인증이 완료되면 바로 access_token 발급하는 방식이다.)


참조

리액트 네이티브(React Native) 스플래쉬 스크린(Splash Screen) 구현


스플래쉬 스크린이란

단순하다. 앱이 실행될 때 처음에 보이는 화면이다. 단순하지만 중요한 화면이다.

그 이유로 첫째, 비어있는 메인 콘텐츠 화면이 표시되는 것을 막아 준다. 보통 메인 화면에는 다양한 정보가 담겨져 있다. 네이버 앱을 생각해보면, 알림 갯수, 날씨, 뉴스 등을 보여줘야하는데 다양한 콘텐츠를 불러오는데 시간이 조금 소요된다. 이 과정에서 빈 화면이 보여질 수 있는데, 이를 방지하기 위해 콘텐츠가 완전히 로딩 되기 전에 스플래쉬 스크린을 보여주면서 사용자 경험을 개선한다.

두번째는 앱의 첫인상을 보여준다. 스플래쉬 스크린에는 보통 앱의 로고와 이름, 더 나아가선 기업의 이름과 저작권 표시 등이 있다. 자신의 앱을 접하는 사람에게 이 앱이 어떤 것인지 알려주기도 하고, 특정 애니메이션을 통해 강렬한 인상을 주기도 한다. 그만큼 단순하면서도 중요한 스크린이다. 새롭게 받은 두 앱이 있다. A는 멋진 로고와 깔끔한 배경색과 화면이 사라질 때 적절한 애니메이션을 통해 당신이 사용하는 이 앱은 멋지고 예쁘고 힙한 앱이야. 라는 느낌을 준다. B는 튜토리얼 앱에서나 볼만한 구린 회색 배경에 텍스트가 달랑 떠 있다. 이 로딩화면이 사라지면 왠지 광고가 덕지덕지 붙어있는 공짜앱 느낌이 들지 않는가?

이 처럼 단순하면서도 중요한 스플래쉬 스크린을 리액트 네이티브에서 고맙게도 만들기 편한 라이브러리들이 있다. 특정 이미지를 스플래쉬 스크린 이미지로 만들어주는 라이브러리까지 있어서 꽤 쉽게 만들수 있다. 다음은 이에 대한 설명이다.


리액트 네이티브 스플래쉬 스크린

React Native Splash Screen(Git Repository) 라이브러리를 사용하면 쉽게 스플래쉬 스크린을 구현할 수 있다.

설치

다운로드

$ npm i react-native-splash-screen --save

플러그인 설치

react-native < 6 일 경우 link 연결이 필요하다. 이상일 경우에는 자동으로 링크된다.

$ react-native link react-native-splash-screen

플러그인 설정

Android

[MainActivity.java](http://mainactivity.java) 수정

import android.os.Bundle; // here
import com.facebook.react.ReactActivity;
// react-native-splash-screen >= 0.3.1
import org.devio.rn.splashscreen.SplashScreen; // here
// react-native-splash-screen < 0.3.1
import com.cboy.rn.splashscreen.SplashScreen; // here

public class MainActivity extends ReactActivity {
   @Override
    protected void onCreate(Bundle savedInstanceState) {
        SplashScreen.show(this);  // here
        super.onCreate(savedInstanceState);
    }
    // ...other code
}

iOS

AppDelegate.m 수정

#import "AppDelegate.h"

#import <React/RCTBundleURLProvider.h>
#import <React/RCTRootView.h>
#import "RNSplashScreen.h"  // here

@implementation AppDelegate

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    // ...other code

    [RNSplashScreen show];  // here
    // or
    //[RNSplashScreen showSplash:@"LaunchScreen" inRootView:rootView];
    return YES;
}

@end

스플래쉬 스크린 생성

react-native-splash-screen git에서 설명하는 것과 여기서 좀 다르다. 스플래쉬 스크린에 사용할 이미지를 만드는 단계이다. 위의 react-native-make 를 사용할 것이다.

먼저 로고용 이미지 하나를 준비한다. 이미지 요구사항은 다음과 같다.

  • png 확장자
  • 최소 3000px 높이 또는 가로
  • cover사이즈를 위해 로고에 1/3 정도 여백이 있으면 좋음

다음의 명령어로 리액트 네이티브의 iOS 내용을 대상으로 xcode 편집기를 연다.

$ xed ./ios

프로젝트 우클릭 > New File... > Launch Screen > Save as: SplashScreen 작업을 진행한다. 처음에는 단순히 프로젝트 이름 텍스트만 들어가 있는데 다음 단계를 진행하면서 로고용 이미지로 변경된다.

마지막으로 다음 명령어를 수행한다

react-native set-splash --path <path-to-image> --resize <[contain]|cover|center> --background "<background-color>"
  • path 옵션은 필수이며 스플래쉬 스크린에 사용할 로고용 이미지 경로를 입력한다.
  • resize는 위의 라이브러리 git을 참고하면 되는데 center를 추천한다.
  • background는 배경색을 지정하는데 로고용 이미지의 배경이 투명이면 원하는 배경색을 지정하고, 투명이 아닐 경우 로고용 이미지 배경색과 같은 색상을 지정하자

위의 명령어를 수행하면 android > app > src > main > resdrawable- 디렉토리가 생성된다. drawable-해상도 디렉토리에는 각 해상도에 맞는 로고용 이미지가 생성된다.

안드로이드 스튜디오에서 android > app > src > main > res > layout > launch_screen.xml 을 열면 스플래쉬 스크린 view를 확인할 수 있다. 내 경우에는 안드로이드 이미지에서 로고가 좀 커서 해당 파일을 직접 수정하여 크기를 조금 줄였다. 안드로이드에 대해 더 잘 안다면 직접 xml의 view를 수정해서 더 나은 화면을 만들 수 있겠다.

iOS도 xcode에서 ScreenSplash.storyboard를 열면 변경된 로고용 이미지로 스플래쉬 스크린이 생성된 것을 확인할 수 있다. 안드로이드 보다 조금 더 예쁘고 작게 만들어진다.

이후 App.js에서 SplashScreen 코드를 추가하자

import SplashScreen from 'react-native-splash-screen';

export default class App extends Component {
  componentDidMount() {
    SplashScreen.hide();
  }

  render() {
     // ...
  }
}

이제 리액트 네이티브를 실행하면 다음처럼 스플래쉬 이미지가 적용된 것을 확인할 수 있다.

iOS 스플래쉬 스크린

참고

AWS RDS 생성과 기본 설정

RDS 생성하기


RDS 데이터베이스 인스턴스 생성

RDS 데이터베이스 인스턴스 생성

  • AWS 콘솔에서 RDS 서비스 접속
  • 데이터베이스 생성 버튼 클릭

MariaDB 엔진 선택

프리티어/MariaDB 엔진 선택

  • MariaDB 엔진과 프리티어 템플릿을 선택한다.

MariaDB 엔진을 사용하는 이유는 다음과 같다.

  • MySQL과 PostgreSQL과 호환되는 완전 관리형 DB 엔진인 Amazon Aurora와 호환이 된다.
  • MariaDB는 MySQL 기반이기 때문이다. 그러나 MySQL는 오라클로 인수되면서 비용이 더 비싸졌으니 MariaDB가 더 좋은 선택이다.
  • (상용에서 오라클 라이선스로 MySQL 지원을 받는다면 MySQL을 사용해도 된다!)
  • MariaDB는 MySQL보다 더 향상된 성능, 기능을 가지고 있고 오픈소스이므로 커뮤니티가 더 활성화 되었다.

상세 설정

DB 상세설정-1

  • 어떤 용도의 DB 인스턴스인지 알아볼 수 있게 식별자를 입력한다.
  • DB 마스터 사용자 이름과 비밀번호를 입력한다.

 

DB 상세설정-2

  • 템플릿 그대로 따라간다

 

DB 상세설정-3

  • 퍼블릭 엑세스 기능 를 선택한다.

 

DB 상세설정-4

  • 초기 데이터베이스 이름을 입력한다. ( - 사용 불가)
  • 마지막으로 데이터베이스 생성한다.

 

RDS 인스턴스 생성된 화면

 

RDS 운영환경에 맞는 설정

  • RDS 생성시 타임존, Character Set, Max Connection 설정을 해줘야 한다.

파라미터 그룹

파라미터 그룹 선택

  • 파라미터 그룹 선택

 

파라미터 그룹 생성

  • 파라미터 그룹 생성 버튼 클릭

 

파라미터 그룹 정보 설정

  • 파라미터 그룹 정보를 입력하고 생성 버튼을 클릭한다.

 

파라미터 그룹 확인

  • 파라미터 그룹 목록 방금 생성한 파라미터 그룹 클릭

 

파라미터 편집

  • 기본 설정된 파라미터 목록들이 보이는데, 우측 상단 파라미터 편집 버튼 클릭

 

타임존 설정

타임존 설정

  • time_zone 검색 후 time_zone 항목 드롭다운 선택하여 Asia/Seoul 선택

 

문자열 셋 설정

다음의 문자열 셋 설정 항목들을 검색하여 설정한다

  • utf8mb4 로 설정할 것들 (utf8과의 차이는 이모지 저장 가능 여부로, utf8mb4는 이모지 저장 가능하다)
    • character_set_client
    • character_set_connection
    • character_set_database
    • character_set_filesystem
    • character_set_results
  • utf8mb4_general_ci 로 설정할 것들
    • collation_connection
    • collation_server

collation은 어떤 방식으로 정렬할 것인가로 utf8mb4_general_ci는 사람이 인식하는 대로 정렬한다.

Max Connection 설정

  • RDS의 Max Connection은 인스턴스 사양에 따라 자동으로 정해진다.
  • 현재 프리티어 사양은 약 60개의 커넥션만 가능하므로 늘려준다.

기본 max_connections 값
변경 max_connections 값

모든 설정이 완료되면 변경사항을 적용하자.

 

파라미터 그룹과 DB 인스턴스 연결

  • 대시보드에서 인스턴스 선택후 수정 버튼 클릭

 

DB파라미터 그룹 설정

  • DB파라미터 그룹을 선택한다.

 

즉시 적용

  • 수정사항을 반영하며 즉시 적용하여 수정한다.

 

RDS 보안 설정

  • 본인 집과 웹 서비스 EC2에서 RDS에 접근할 수 있게 설정하자.
  • 먼저 웹서비스용 EC2 인스턴스에서의 접근을 허용하자

RDS 인스턴스 보안 그룹

  • 클릭 하면 EC2 > 보안그룹 메뉴로 이동하며 선택한 RDS VPC 기본 보안 그룹이 필터링 되어있다.

 

RDS 보안 그룹으로 필터링된 EC2 보안 그룹 목록

  • 해당 보안 그룹 ID를 클릭하여 상세 페이지로 이동한다.

 

RDS 인스턴스 보안그룹 상세 페이지

  • 인바운드 규칙 편집을 클릭한다.

 

webservice EC2 보안그룹 선택

  • 규칙 추가버튼을 클릭하여 생성된 라인에서 MYSQL/Auora를 선택한다.
  • 사용자 지정 목록에서 웹서비스용 EC2의 보안 그룹을 선택한다.
  • 다음으로 본인 집 IP를 허용하도록 설정을 추가한다.

 

본인 집 IP 3306 포트 허용

  • 마찬가지로 규칙 추가버튼을 클릭하여 생성된 라인에서 사용자 지정 TCP를 선택한다.
  • 3306 포트를 입력한다.
  • 사용자 지정 유형으로 본인 IP를 입력한다. 드롭다운에서 본인IP/32 보여지고 선택하면 그림처럼 세팅된다.
  • 마지막으로 규칙 저장을 하면 적용된다.

 

DB에 접속해보기

엔드포인트 정보

  • RDS > DB 인스턴스 목록에서 DB를 선택 후 상세페이지의 연결 & 보안을 확인한다.
  • 엔드포인트를 복사한다.
  • 사용하는 Database 툴에서 MariaDB 연결 설정의 Host 정보로 등록한다
  • 여기서는 DataGrip(JetBrain 사의 DB 툴)을 예제로 사용하겠다.

Datagrip으로 연결

DataSource 설정

  • Data Source > MariaDB를 선택하여 설정 창을 연다.
    • 이름: 표시할 이름 입력
    • Host: AWS RDS 엔드포인트 입력
    • User: RDS 생성시 입력한 마스터 사용자명
    • Password: 마스터 사용자 비밀번호
    • Database: 접속시 연결할 기본 Database 이름 (입력 안해도 무방, 연결 후 Schma 선택 가능)
    • URL: 위의 입력 정보를 바탕으로 자동으로 세팅 된다. 반대로 URL을 입력하면 위의 정보가 세팅된다.
  • Test Connection을 클릭하여 커넥션 테스트

Test Connection 결과

 

설정 확인

use springboot_aws_study_webservice;

show variables like 'c%';

latin1 로 설정되어 안 바뀌는 모습
c 관련 설정 확인

ALTER DATABASE springboot_aws_study_webservice
CHARACTER SET = 'utf8mb4'
COLLATE = 'utf8mb4_general_ci';

변경된 모습 확인

  • 최종적으로 옵션들이 변경된 것을 확인할 수 있다.

 

  • 타임존 확인하자

select @@time_zone, now();

타임존 확인 결과

 

  • 한글 데이터 입력 되는지 확인하자
create table test (
    id bigint(20) not null auto_increment,
    content varchar(255) default null,
    primary key (id)
) engine=InnoDB;

insert into test(content) values ('테스트');

select * From test;

한글 데이터 입력 결과

 

EC2에서 접속하기

$ ssh springboot-aws-study-webservice

EC2 접속

  • 접속이 완료되면 mysql을 설치하자
$ sudo yum -y install mysql
# ... 설치완료
  • 설치가 완료되면 mysql 접속하자
# mysql -u [계정] -p -h [호스트명]
$ mysql -u hoonmaro -p -h springboot-aws-study-webservice.cbdudkejudka.ap-northeast-2.rds.amazonaws.com
Enter password: [패스워드 입력]

# -p 옵션으로 패스워드 입력 단계 표시
# 접속 완료

# Database 확인
> show tables;

# 이전에 추가한 springboot_aws_study_webservice 목록에 존재하는 것 확인

최종 완료

 

지금까지 배운 것

  • AWS RDS 생성
  • 기본 파라미터 그룹 설정
  • 보안 그룹 설정
  • Datagrip DB툴을 이용하여 연결
  • 기본 파라미터 확인 및 수동 파라미터 설정
  • EC2에서 mysql 설치
  • EC2에서 RDS 연결

 

참고

  • 이동욱. 2019. 스프링 부트와 AWS로 혼자 구현하는 웹 서비스. 프리렉

'프로그래밍 > AWS' 카테고리의 다른 글

AWS EC2(아마존 리눅스2) 생성과 Java 13 설치  (1592) 2020.06.28

AWS EC2(아마존 리눅스2) 생성과 Java 13 설치

스프링 부트와 AWS로 혼자 구현하는 웹 서비스의 나온 내용을 정리 해봤다. EC2는 자주 사용하지만 세세한 설정 등은 처음 본 것들도 있어서 공부한 보람이 있다.

책은 아마존 리눅스 1 이미지, JDK 8로 진행했으나 최근에 진행 중인 프로젝트가 아마존 리눅스 2, JDK 13이어서 책과는 다른 부분이 있으니 참고 바란다.

AWS EC2 인스턴스 생성

EC2 대시보드 → 인스턴스 시작


EC2 인스턴스 시작

이미지 선택


인스턴스 이미지 선택

Amazon Linx 2 AMI 선택

  • Centos 7 버전 내용과 호환
  • 아마존이 개발하고 있어 지원 받기 쉽다.
  • 레드햇 베이스이므로 레드햇 계열의 배포판을 다뤄본 사람일수록 사용하기 쉽다.
  • AWS의 각종 서비스와 상성이 좋다.
  • Amazon 독자적인 개발 리포지터리를 사용하고 있어 yum이 매우 빠르다.

인스턴스 유형 선택


인스턴스 유형 선택

  • t2.micro 선택

T 시리즈

  • 크레딧인란 CPU를 사용할 수 있는 포인트 개념 존재
  • 인스턴스 크기에 따라 정해진 비율로 CPU 크레딧을 계속 받으며 사용하지 않을 때 축적하고 사용할 때 크레딧을 계속 사용한다
  • 크레딧이 모두 사용되면 더이상 EC2를 사용할 수 없다.
  • 트래픽이 높은 서비스인 경우 다른 유형의 인스턴스를 사용한다.

인스턴스 세부 정보 구성


인스턴스 세부 정보 구성

  • 오토스케일링, 서브넷, VPC, 모니터링 등 용도에 맞게 세부적인 설정 가능
  • 지금은 크게 변경할 내용이 없다.

스토리지 추가


스토리지 추가

  • 프리 티어의 경우 30GB 까지 스토리지 사용 가능

태그 추가


태그 추가

  • 인스턴스에 태그 추가
  • 인스턴스가 많아질 경우 태그를 통해 관리가 용이하다.

보안 그룹 구성


보안 그룹 구성

  • 방화벽 설정
  • SSH 22 포트와 내 IP 규칙 추가
  • 웹 서비스 포트 Public 추가
  • HTTPS 포트 Public 추가

키 페어 생성


키 페어 생성

키 페어 등록

  • 다운로드 받은 키 페어를 등록하자
$ cd .ssh
$ mv ~/Downloads/springboot-aws-study-webservice.pem .
$ chmod 600 springboot-aws-study-webservice.pem
$ ssh-add springboot-aws-study-webservice.pem

인스턴스 대시보드에서 확인


인스턴스 대시보드 목록 확인

EIP 할당

  • AWS의 고정 IP 서비스 Elastic IP(EIP, 탄력적 IP)

  • 좌측 메뉴에서 탄력적 IP를 선택

 

  • 탄력적 IP 주소 할당 클릭

 

  • 할당 클릭

 

  • 이 탄력적 IP 주소 연결 또는 주소 Actions에서 탄력적 IP 주소 연결 선택

 

  • 생성한 EC2에 연결
  • 인스턴스와 프라이빗 주소의 드롭다운 목록에서 선택
  • 생성한 탄력적 IP를 EC2에 연결하지 않으면 비용이 발생하므로 바로 연결하는 것이 좋다.
  • 인스턴스가 없을 경우 탄력적 IP는 바로 삭제하는 것이 좋다.

인스턴스 정보 확인

인스턴스 정보 확인

  • 인스턴스 목록에서 인스턴스 선택 후 아래에 표시되는 세부 정보의 탄력적 IP에 할당된 모습 확인

터미널로 EC2 인스턴스 접속

pem키 등록

$ cd .ssh
$ mv ~/Downloads/springboot-aws-study-webservice.pem .
$ chmod 600 springboot-aws-study-webservice.pem
$ vim ~/.ssh/config

# Edit config                                                                                                                 Buffers
    # springboot-aws-study-webservice
  Host springboot-aws-study-webservice
      HostName [탄력적 IP]
        User ec2-user
      IdentityFile ~/.ssh/springboot-aws-study-webservice.pem

# config 저장 및 종료 :wq

# config 실행 권한 등록
$ chmod 700 ~/.ssh/config

SSH 호스트 Config

  • ssh [Host 이름]으로 바로 접속 가능하다.
ssh springboot-aws-study-webservice

접속 결과

인스턴스 아마존 리눅스 서버 설정

JDK 설치

  • openjdk13 설치를 진행한다.
  • 아마존 레포지토리에는 8버전까지 밖에 없어 13을 다운로드 한다.
$ curl -O https://download.java.net/java/GA/jdk13.0.2/d4173c853231432d94f001e99d882ca7/8/GPL/openjdk-13.0.2_linux-x64_bin.tar.gz

# 다운로드 하면서 아래와 비슷한 내용이 출력
% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  186M  100  186M    0     0  10.0M      0  0:00:18  0:00:18 --:--:-- 9961k

# 다운로드 완료된 것 확인
$ ls
openjdk-13.0.2_linux-x64_bin.tar.gz

# 압축 풀기
$ tar -xvf openjdk-13_linux-x64_bin.tar.gz
$ ls
jdk-13.0.2  openjdk-13.0.2_linux-x64_bin.tar.gz

# /opt/ 디렉토리로 이동
$ sudo mv jdk-13.0.2 /opt
$ cd /opt
$ ls
aws  jdk-13.0.2  rh

# 환결변수 설정
$ cd /etc/profile.d
$ vim jdk13.sh

# Edit jdk13.sh
export JAVA_HOME=/opt/jdk-13.0.2
export PATH=$PATH:$JAVA_HOME/bin
## 저장 및 종료 :wa

# 설정 반영
$ source /etc/profile.d/jdk13.sh

# 확인
$ echo $JAVA_HOME
/opt/jdk-13.0.2

$ java --version
openjdk 13.0.2 2020-01-14
OpenJDK Runtime Environment (build 13.0.2+8)
OpenJDK 64-Bit Server VM (build 13.0.2+8, mixed mode, sharing)
  • [참고사항] curl -O 링크 참고 주소: 자바 릴리즈 아카이브 에서 JDK 13 최신 메이저 버전에서 Linux 운영체제의 tar.gz의 링크를 복사해와서 curl로 받아온다.

서버 시간대(타임존) 변경

  • 기본 타임존은 UTC (세계 표준시)로 우리나라와 9시간 차이가 난다.
  • 한국시간대 (KST)로 변경하자
# 현재 시간 확인
$ date
Sun Jun 28 13:25:53 UTC 2020

# 시간대 정보 변경
$ sudo rm /etc/localtime
$ sudo ln -s /usr/share/zoneinfo/Asia/Seoul /etc/localtime

# 심링크 확인
$ ll /etc/ | grep localtime
lrwxrwxrwx  1 root root       30 Jun 28 22:26 localtime -> /usr/share/zoneinfo/Asia/Seoul

# 현재 시간 다시 확인
$ date
Sun Jun 28 22:27:26 KST 2020

호스트네임 변경

  • 여러 서버를 관리할 때 IP만으로 어떤 서비스의 서버인지 확인이 어렵다.
  • Hostname을 설정하자
$ sudo hostnamectl set-hostname springboot-aws-study-webserivce
$ hostname
springboot-aws-study-webserivce

$ sudo systemctl restart network

$ sudo reboot

 

참고

  • 이동욱. 2019. 스프링 부트와 AWS로 혼자 구현하는 웹 서비스. 프리렉

'프로그래밍 > AWS' 카테고리의 다른 글

AWS RDS 생성과 기본 설정  (1779) 2020.07.07

jEnv로 자바 환경 세팅

Java 설치

> brew search jdk

==> Formulae
openjdk ✔                                                              openjdk@11 ✔
==> Casks
adoptopenjdk                       adoptopenjdk12-openj9              adoptopenjdk14                     adoptopenjdk8-openj9-jre
adoptopenjdk10                     adoptopenjdk12-openj9-jre          adoptopenjdk14-jre                 adoptopenjdk8-openj9-jre-large
adoptopenjdk11                     adoptopenjdk12-openj9-jre-large    adoptopenjdk14-openj9              adoptopenjdk8-openj9-large
adoptopenjdk11-jre                 adoptopenjdk12-openj9-large        adoptopenjdk14-openj9-jre          adoptopenjdk9
adoptopenjdk11-openj9              adoptopenjdk13 ✔                   adoptopenjdk14-openj9-jre-large    jdk-mission-control
adoptopenjdk11-openj9-jre          adoptopenjdk13-jre                 adoptopenjdk14-openj9-large        oracle-jdk
adoptopenjdk11-openj9-jre-large    adoptopenjdk13-openj9              adoptopenjdk8 ✔                    oracle-jdk-javadoc
adoptopenjdk11-openj9-large        adoptopenjdk13-openj9-jre          adoptopenjdk8 ✔                    sapmachine-jdk
adoptopenjdk12                     adoptopenjdk13-openj9-jre-large    adoptopenjdk8-jre
adoptopenjdk12-jre                 adoptopenjdk13-openj9-large        adoptopenjdk8-openj9

> brew install openjdk
> brew cask install adoptopenjdk8

jEnv 설치

> brew install jenv

> echo 'export PATH="$HOME/.jenv/bin:$PATH"' >> ~/.zshrc
> echo 'eval "$(jenv init -)"' >> ~/.zshrc
> source ~/.zshrc

JDK jEnv 설정

# adoptopenjdk 8
> jenv add /Library/Java/JavaVirtualMachines/adoptopenjdk-8.jdk/Contents/Home

# openjdk (13)
> jenv add /usr/local/opt/openjdk

# 확인
> jenv versions
system
openjdk64-1.8.0.242
openjdk64-13.0.2

글로벌 Java 버전 설정

$ jenv global openjdk64-13.0.2

로컬 Java 버전 설정 (디렉토리별)

$ jenv local openjdk64-1.8.0.242

쉘 Java 버전 설정

$ jenv shell openjdk64-13.0.2

오라클 커서(Cursor)

기본 문법

DECLARE
    CURSUR [커서이름] IS [SELECT 문];
BEGIN
    OPEN [커서이름];
    FETCH [커서이름] INTO [변수];
    CLOSE [커서이름];
END;

예제 준비


오라클 커서란

커서는 SELECT문 또는 DML문 처리에 대한 정보를 저장하는 전용 SQL 메모리 영역에 대한 포인터이다. DML문의 커서 관리는 오라클 데이터베이스에서 처리하지만, PL/SQL은 커서를 정의하고 조작하여 SELECT문을 실행하는 여러 방법을 제공한다.

일반적인 방법들은 다음과 같다.

  • SELECT-INTO 문
  • 명시적 커서로부터 가져오기
  • 커서 FOR 반복문 사용
  • 동적 쿼리에 EXECUTE IMMEDAITE INTO 사용
  • 커서 변수 사용

SELECT-INTO문

SELECT-INTO는 SELECT문에서 하나의 행을 가져오는 가장 빠르고 간단한 방법을 제공한다.

SELECT select_list INTO variable_list FROM remainder_of_query

remainder_of_query에는 테이블 또는 뷰, WHERE 절, 기타 절이 포함된다. variable_list의 수와 유형은 select_list의 요소 수와 유형과 일치해야 한다.

SELECT문이 2개 이상의 행을 조회하면 TOO_MANY_ROWS 예외가 발생하고, 조회 데이터가 없으면 NO_DATA_FOUND 예외가 발생한다.

  • 기본 예제
-- * ORACLE Live SQL OE(Order Entry) 스키마를 활용한 SELECT INTO 커서 예제

-- 카테고리 이름이 office1인 데이터의 카테고리 설명을 가져온다.
DECLARE
    v_category_desc oe.categories_tab.category_description%TYPE;
BEGIN
    SELECT
        category_description INTO v_category_desc
    FROM oe.categories_tab
    WHERE category_name = 'office1';

    DBMS_OUTPUT.put_line(v_category_desc);
END;

-- > 결과: capitalizable assets (desks, chairs, phones ...)

-- * NO_DATA_FOUND 예외 발생 예제
DECLARE
    v_category_desc oe.categories_tab.category_description%TYPE;
BEGIN
    SELECT
        category_description INTO v_category_desc
    FROM oe.categories_tab
    WHERE category_name = 'office0';

    DBMS_OUTPUT.put_line(v_category_desc);
END;

-- > 결과: ORA-01403: no data found ORA-06512: at line 4

-- * TOO_MANY_ROWS 예외 발생 예제
DECLARE
    v_category_desc oe.categories_tab.category_description%TYPE;
BEGIN
    SELECT
        category_description INTO v_category_desc
    FROM oe.categories_tab;

    DBMS_OUTPUT.put_line(v_category_desc);
END;

-- > 결과: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4
  • 1행 전체를 SELECT INTO 예제
-- CATEGORIES_TAB 테이블에서 카테고리 이름이 office1인 데이터의 행 전체을 가져온다.
DECLARE
    v_category_tab oe.categories_tab%ROWTYPE;
BEGIN
    SELECT
        * INTO v_category_tab
    FROM oe.categories_tab
    WHERE category_name = 'office1';

    DBMS_OUTPUT.put_line(v_category_tab.category_name);
    DBMS_OUTPUT.put_line(v_category_tab.category_description);
END;

-- > 결과
/*
office1
capitalizable assets (desks, chairs, phones ...)
*/
  • 두 변수, 두 테이블 예제
-- 상품과 카테고리 테이블을 조인하여 상품 아이디 1797 데이터와 그 카테고리의 특정 정보를 조회한다.
DECLARE
    v_category_id oe.categories_tab.category_id%TYPE;
    v_category_name oe.categories_tab.category_name%TYPE;
    v_product_id oe.product_information.product_id%TYPE;
    v_product_name oe.product_information.product_name%TYPE;
BEGIN
    SELECT
        c.category_id, c.category_name, p.product_id  , p.product_name
        INTO
        v_category_id, v_category_name, v_product_id, v_product_name
    FROM oe.categories_tab c
    JOIN oe.product_information p ON c.category_id = p.category_id
    WHERE p.product_id = '1797';

    DBMS_OUTPUT.put_line(
        'PRODUCT[' || v_product_id || ']' || v_product_name 
        || ' belongs to CATEGORY[' || v_category_id || '] ' || v_category_name
    );
END;

-- > 결과: PRODUCT[1797] "Inkjet C/8/HQ" belongs to CATEGORY[12] "hardware2"
  • SELECT INTO 예외
  1. ORA-00947 not enough values: SELECT 조회 결과 컬럼 수보다 변수가 적음
  2. ORA-00913 too many values: SELECT 조회 결과 컬럼 수보다 변수가 많음
  3. ORA-06502 PL/SQL: numeric or value error: SELECT 조회 결과 컬럼과 변수의 자료형이 맞지 않음
-- PL/SQL: ORA-00947: not enough values
DECLARE
    v_category_id oe.categories_tab.category_id%TYPE;
    v_category_name oe.categories_tab.category_name%TYPE;
    v_product_id oe.product_information.product_id%TYPE;
    v_product_name oe.product_information.product_name%TYPE;
BEGIN
    SELECT
        c.category_id, c.category_name, p.product_id  , p.product_name
        INTO
        v_category_id, v_category_name, v_product_id
    FROM oe.categories_tab c
    JOIN oe.product_information p ON c.category_id = p.category_id
    WHERE p.product_id = '1797';

    DBMS_OUTPUT.put_line(
        'PRODUCT[' || v_product_id || '] "' || v_product_name 
        || '" belongs to CATEGORY[' || v_category_id || '] "' || v_category_name || '"'
    );
END;

-- PL/SQL: ORA-00913: too many values
DECLARE
    v_category_id oe.categories_tab.category_id%TYPE;
    v_category_name oe.categories_tab.category_name%TYPE;
    v_product_id oe.product_information.product_id%TYPE;
    v_product_name oe.product_information.product_name%TYPE;
    v_add_one VARCHAR2(10 Char);
BEGIN
    SELECT
        c.category_id, c.category_name, p.product_id  , p.product_name
        INTO
        v_category_id, v_category_name, v_product_id, v_product_name, v_add_one
    FROM oe.categories_tab c
    JOIN oe.product_information p ON c.category_id = p.category_id
    WHERE p.product_id = '1797';

    DBMS_OUTPUT.put_line(
        'PRODUCT[' || v_product_id || '] "' || v_product_name 
        || '" belongs to CATEGORY[' || v_category_id || '] "' || v_category_name || '"'
    );
END;

-- ORA-06502: PL/SQL: numeric or value error: character to number conversion error
DECLARE
    v_category_id oe.categories_tab.category_id%TYPE;
    v_category_name oe.categories_tab.category_name%TYPE;
    v_product_id oe.product_information.product_id%TYPE;
    v_product_name NUMBER;
BEGIN
    SELECT
        c.category_id, c.category_name, p.product_id  , p.product_name
        INTO
        v_category_id, v_category_name, v_product_id, v_product_name
    FROM oe.categories_tab c
    JOIN oe.product_information p ON c.category_id = p.category_id
    WHERE p.product_id = '1797';

    DBMS_OUTPUT.put_line(
        'PRODUCT[' || v_product_id || '] "' || v_product_name 
        || '" belongs to CATEGORY[' || v_category_id || '] "' || v_category_name || '"'
    );
END;

명시적 커서로부터 가져오기

SELECT INTO는 Oracle DB가 묵시적으로 SELECT문을 위한 커서를 열어 행을 가져오고 작업이 종료되거나 예외가 발생하면 자동으로 커서를 닫는다. 반면 사용자가 명시적으로 커서를 선언하고 열고, 가져오고, 닫는 동작을 할 수 있다. SELECT INTO 문과 달리 커서를 선언부에서 따로 초기화를 해야한다.

OE.ORDERS 테이블에서 SALES_REP_ID로 그룹화하여 ORDER_TOTAL의 합이 많은 순서대로 2000달러씩 보너스를 주는 커서를 프로그래밍해보자. 보너스는 10000달러가 있다고 가정한다.

DECLARE
    v_total_bonus INTEGER := 10000;

    CURSOR sales_rep_cur
    IS
        SELECT 
            * 
        FROM (
            SELECT
                sales_rep_id, SUM(order_total) sum_order_total
            FROM oe.orders
            WHERE sales_rep_id IS NOT NULL
            GROUP BY sales_rep_id
        )
        ORDER BY sum_order_total DESC
    ;

    v_sales_rep_id sales_rep_cur%ROWTYPE;

BEGIN
    OPEN sales_rep_cur;
    LOOP
        FETCH sales_rep_cur INTO v_sales_rep_id;
        EXIT WHEN sales_rep_cur%NOTFOUND;

        v_total_bonus := v_total_bonus - 2000;
        DBMS_OUTPUT.put_line('SALES[' || v_sales_rep_id.sales_rep_id || '] get bonus $2000');

        EXIT WHEN v_total_bonus <= 0;
    END LOOP;

    CLOSE sales_rep_cur;

END;
-- 결과
/*
SALES[161] get bonus $2000
SALES[156] get bonus $2000
SALES[154] get bonus $2000
SALES[158] get bonus $2000
SALES[159] get bonus $2000
*/

커서 FOR 반복문 사용

FOR 반복문을 사용하여 커서를 열고 닫는 것을 생략하고 좀더 간편하게 작성할 수 있다. 위에서 본 예제를 FOR 반복문을 사용하여 프로그래밍 해보자.

DECLARE
    v_total_bonus INTEGER := 10000;

    CURSOR sales_rep_cur
    IS
        SELECT 
            * 
        FROM (
            SELECT
                sales_rep_id, SUM(order_total) sum_order_total
            FROM oe.orders
            WHERE sales_rep_id IS NOT NULL
            GROUP BY sales_rep_id
        )
        ORDER BY sum_order_total DESC
    ;

BEGIN
    FOR sales_rep IN sales_rep_cur
    LOOP
        v_total_bonus := v_total_bonus - 2000;
        DBMS_OUTPUT.put_line('SALES[' || sales_rep.sales_rep_id || '] get bonus $2000'
            || ', sales $' || sales_rep.sum_order_total || ' in this month' 
        );
        EXIT WHEN v_total_bonus <= 0;
    END LOOP;

END;

-- 결과
/*
SALES[161] get bonus $2000, sales $661734.5 in this month
SALES[156] get bonus $2000, sales $202617.6 in this month
SALES[154] get bonus $2000, sales $171973.1 in this month
SALES[158] get bonus $2000, sales $156296.2 in this month
SALES[159] get bonus $2000, sales $151167.2 in this month
*/

-- 다른 변수가 없다면 선언부를 생략할 수 있다
BEGIN
    FOR sales_rep IN (
        SELECT 
            * 
        FROM (
            SELECT
                sales_rep_id, SUM(order_total) sum_order_total
            FROM oe.orders
            WHERE sales_rep_id IS NOT NULL
            GROUP BY sales_rep_id
        )
        ORDER BY sum_order_total DESC
    )
    LOOP
        DBMS_OUTPUT.put_line('SALESMAN[' || sales_rep.sales_rep_id || ']'
            || ' sales $' || sales_rep.sum_order_total || ' in this month' 
        );
    END LOOP;
END;

-- 결과
/*
SALESMAN[161] sales $661734.5 in this month
SALESMAN[156] sales $202617.6 in this month
SALESMAN[154] sales $171973.1 in this month
SALESMAN[158] sales $156296.2 in this month
SALESMAN[159] sales $151167.2 in this month
SALESMAN[155] sales $134415.2 in this month
SALESMAN[163] sales $128249.5 in this month
SALESMAN[153] sales $114215.7 in this month
SALESMAN[160] sales $88238.4 in this month
*/

동적 쿼리 사용(EXECUTE IMMEDIATE INTO)

EXECUTE IMMEDIATE INTO 문을 사용하여 SELECT문을 즉시 실행하여 변수에 담을 수 있다. Function과 Procedure를 활용하여 동적쿼리를 사용하는 PL/SQL을 프로그래밍 해보자.

실제 환경에서는 동적 쿼리는 SQL 인젝션 위험이 있어 보안에 주의 해야 한다.

-- 단일 결과 동적 쿼리
CREATE OR REPLACE FUNCTION 
single_number_value (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
   RETURN NUMBER
IS
   l_return   NUMBER;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      INTO l_return;
   RETURN l_return;
END;
-- Function created.

-- SQL Worksheet에서 따로 따로 써서 RUN 해야 한다.
BEGIN
   DBMS_OUTPUT.put_line (
      single_number_value (
                'oe.product_information',
                'category_id',
                'product_id=1797'));
END;
-- 결과: 12

-- 여러 결과 동적 쿼리 (BULK COLLECT 사용)
CREATE OR REPLACE PROCEDURE 
show_number_values (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
IS
   TYPE values_t IS TABLE OF NUMBER;

   l_values   values_t;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      BULK COLLECT INTO l_values;

   FOR indx IN 1 .. l_values.COUNT
   LOOP
      DBMS_OUTPUT.put_line 
      (l_values (indx));
   END LOOP;
END;
-- Procedure created.

BEGIN
   show_number_values (
      'oe.orders',
      'order_total',
      'order_total >= 25000 
       order by order_total desc');
END;
-- 결과
/*
295892
282694.3
268651.8
144054.8
120131.3
103834.4
103679.3
94513.5
92829.4
...
*/

커서 변수

커서 변수는 커서 또는 결과집합을 가리키는 변수이다. 명시적 커서와 달리 커서 변수를 프로시저나 함수의 인자로 전달할 수 있다. 커서 변수는 주로 결과 집합을 응답하는 PL/SQL을 작성할 때 많이 사용된다.

-- 매출이 가장 높은 상품 구하는 함수 생성
CREATE OR REPLACE FUNCTION
find_top_sales_product (
    result_count_in NUMBER
) RETURN SYS_REFCURSOR
IS
    cur SYS_REFCURSOR;
BEGIN
    OPEN cur FOR
    SELECT
            product_id, sales
        FROM (
            SELECT
                product_id, sum(quantity * unit_price) sales
            FROM oe.order_items
            GROUP BY product_id
            ORDER BY sales DESC
        )
        WHERE ROWNUM <= result_count_in;

    RETURN cur;
END;

-- 함수를 호출하여 상위 10개의 출력하는 PL/SQL
DECLARE
   v_products_cur   SYS_REFCURSOR;
   v_product_id NUMBER;
   v_sales NUMBER;
BEGIN
   v_products_cur := find_top_sales_product(10);

   LOOP
    FETCH v_products_cur INTO v_product_id, v_sales;
    EXIT WHEN v_products_cur%NOTFOUND;
      DBMS_OUTPUT.put_line('Product[' || v_product_id || '] sales $' || v_sales);
   END LOOP;

END;
-- 결과
/*
Product[2350] sales $922708.6
Product[3127] sales $364351
Product[2359] sales $180872.8
Product[2252] sales $134079
Product[3003] sales $97464.4
Product[2311] sales $89411.7
Product[3106] sales $82490
Product[2236] sales $79741.2
Product[2289] sales $78099
Product[2245] sales $61908
*/

적절한 방법 선택

  • 하나의 행을 조회할 때는 SELECT INTO 문 또는 동적 쿼리를 사용
  • 모든 행을 조회할 때 본문이 하나 이상의 DML문을 실행하지 않으면 FOR 반복문 사용
  • BULK COLLECT로 가져와야 할 경우 명시적 커서를 사용하지만 각 FETCH에서 조회 결과 수를 제한할 것
  • 쿼리 결과가 런타임에 달라질 경우 또는 결과를 PL/SQL이 아닌 환경으로 전달해야할 경우 커서 변수 사용
  • 코드를 작성하는 동안 SELECT 문을 완전히 구성할 수 없는 경우에만 동적 쿼리(EXECUTE IMMEDIATE) 사용

참고

'프로그래밍 > Oracle' 카테고리의 다른 글

마로의 Oracle 공부: DDL(3) - DROP  (471) 2016.12.14
마로의 Oracle 공부: DDL(2) - ALTER  (472) 2016.12.08
마로의 Oracle 공부: DDL(1) - CREATE  (477) 2016.10.26
마로의 Oracle 공부: SQL 기초  (488) 2016.01.04

+ Recent posts