반응형

프록시 서버란

프록시란 클라이언트와 서버 중간에 위치하여 중개 역할을 하는 서버이다. 위키피디아에서는 아래와 같이 설명한다.

 

프록시 서버(영어: proxy server 프록시 서버[*])는 클라이언트가 자신을 통해서 다른 네트워크 서비스에 간접적으로 접속할 수 있게 해 주는 컴퓨터 시스템이나 응용 프로그램을 가리킨다. 서버와 클라이언트 사이에 중계기로서 대리로 통신을 수행하는 것을 가리켜 '프록시', 그 중계 기능을 하는 것을 프록시 서버라고 부른다.
- wikipedia

 

프록시를 사용하는 목적은 다양하다.

일반적으로 사내망에서 외부망과 통신을 한 곳에서 관리하기 위하여 포워드 프록시를 사용하는 경우가 있으며, 클라이언트의 요청을 한곳에서 받아 로드밸런싱 하기 위하여 리버스 프록시를 사용하는 경우가 있다.

 

포워드, 리버스 프록시

프록시는 포워드 프록시와 리버스 프록시로 구분하여 사용한다.

 

포워드 프록시

  • 클라이언트가 서버에 직접 요청을 하는 것이 아닌, 프록시 서버가 해당 요청을 받아 클라이언트가 요청한 서버에 프록시가 대신 요청하여 응답을 받아 전달해주는 형태
  • 클라이언트는 자신이 요청할 서버를 알고 있음
  • 일반적으로 사내망과 외부망을 분리하기 위해 사용

포워드 프록시

리버스 프록시

  • 클라이언트가 프록시에 요청을 하면, 프록시가 해당 요청을 적절한 서버에 요청하여 응답을 받아 전달해주는 형태
  • 클라이언트는 프록시 서버만 알고 있음. 즉, 해당 요청을 처리할 서버를 알지 못함
  • 일반적으로 로드밸런싱을 위해 사용

포워드 프록시

 

포워드 프록시 경우의 수

포워드 프록시는 요청의 프로토콜, 프록시 서버의 프로토콜에 따라 경우의 수는 다음과 같다.

 

1. HTTP to HTTP

HTTP to HTTP

 

2. HTTP to HTTPS

HTTP to HTTPS

 

3. HTTPS to HTTP

HTTPS to HTTP

4. HTTPS to HTTPS

HTTPS to HTTPS

반응형

'서버' 카테고리의 다른 글

Grafana 설치 및 실행  (0) 2021.09.16
반응형

이 포스팅은 토비의 스프링 3.1을 읽고 글쓴이가 좀 더 이해하기 쉽도록 정리한 내용입니다.

이 포스팅의 대상은 토비의 스프링을 읽어보신 분들이 한 번쯤 다시 기억을 상기시키고자 하는 분들입니다. 

글쓴이가 이미 알고 있는 내용에 대해서는 생략합니다. 또한 소스코드도 생략이 많으니 유의해서 읽어주시기 바랍니다.


스프링은 자바를 기반으로 한 기술이다. 스프링은 자바를 자바답게 사용하도록 명쾌한 기준을 마련해준다.

 

이 장에서는 자바를 이용해 오브젝트와 오브젝트의 의존관계를 구현하면서 문제가 되었던 부분을 스프링에서는 어떠한 초점을 가지고 오브젝트가 어떻게 설계와 구현, 사용되어야 하는지를 설명한다.

 

토비의 스프링을 읽다 보면 좋은 글귀가 많다. 1-2. DAO의 분리에서 설명하는 내용을 먼저 읽어보고 1장에 들어가자.

 

변화는 먼 미래에만 일어나는 게 아니다. 며칠 내에, 때론 몇 시간 후에 변화에 대한 요구가 갑자기 발생할 수 있다. 객체지향 설계와 프로그래밍이 이전의 절차적 프로그래밍 패러다임에 비해 초기에 좀 더 많은 번거로운 작업을 요구하는 이유는 객체지향 기술 자체가 지니는, 변화에 효과적으로 대처할 수 있다는 기술적인 특징 때문이다. 객체 지향 기술은 흔히 실세계를 최대한 가깝게 모델링 해낼 수 있기 때문에 의미가 있다고 여겨진다. 하지만 그보다는 객체지향 기술이 만들어내는 가상의 추상세계 자체를 효과적으로 구성할 수 있고, 이를 자유롭고 편리하게 변경, 발전, 확장시킬 수 있다는 데 더 의미가 있다. 
 - 토비의 스프링

 

1-1. 초난감 DAO

1-1장에서는 사용자의 정보를 표현할 User 클래스와 사용자의 정보를 실제로 저장할 DB 테이블인 USER 테이블, 그리고 사용자의 정보를 DB에 넣고 관리할 수 있는 UserDao 클래스를 생성한다.

 

UserDao 클래스는 JDBC를 이용한 가장 기본적인 코드 방식으로 add()와 get()를 구현하였다.

public class UserDao{

	public void add(User user) throws ClassNotFoundException, SQLException {
          // 1. DB 연결
          Class.forName("com.mysql.jdbc.Driver");
          Connection c = DriverManager.getConnection(...);
          
          // 2. SQL 수행
          PreparedStatement ps = c.prepareStatement("INSERT INTO USER(ID, NAME, PASSWORD) VALUES(?,?,?)");
          ps.setString(1, user.getId());
          ps.setString(2, user.getName());
          ps.setString(3, user.getPassword());

          ps.executeUpdate();

          // 3. 리소스 반납
          ps.close();
          c.close();
	}
		...

 

테스트를 위해 main() 메서드를 구현하여 테스트하였다.

 

이렇게 JDBC를 이용한 가장 기본적인 코드 방식으로 작성하고, main() 메서드로 테스트를 하면 무엇이 문제가 될까?

하나씩 개선해보자.

 

 

1-2. DAO의 분리

관심사의 분리(Separation of Concerns)

  • 프로그래밍의 기초 개념 중에 하나인 관심사의 분리
  • 변화는 대체로 집중된 한 가지 관심에 대해서만 일어난다. 따라서 관심이 같은 것끼리는 하나의 객체 안으로 또는 친한 객체로 모이게 하고, 관심이 다른 것은 가능한 한 따로 떨어져서 서로 영향을 주지 않도록 분리를 하게 되면 변화에 대처하기 쉽다.

 

UserDao.add()를 다시 봐보자. add의 관심사는 몇 개일까? 어찌 보면 사용자 정보를 DB에 저장하기 위한 관심사 하나로 생각할 수 있지만, 좀 더 작게 세분화하면 다음과 같다.

  1. DB와 연결을 위한 커넥션에 대한 관심사 : DB가 mysql이 아니라면? DB 접속 정보가 달라진다면?
  2. SQL 문장 : SQL 문장을 변경해야 한다면? 바인딩 변수가 달라진다면?
  3. 리소스 반납(close)

 

첫 번째 관심사부터 보자.

 

DB와 연결을 위한 커넥션에 대한 코드는 SQL을 수행하려는 곳에서는 항상 필요하다. 만약 중복으로 코드를 작성한 상태에서 DBMS를 변경하던지, DB 접속 정보가 달라진다면 모든 곳을 다 고쳐야 한다.

따라서 가장 기본적인 메서드 추출 기법으로 중복 코드를 제거한다.

public class UserDao{

	public void add(User user) throws ClassNotFoundException, SQLException {
          // 1. DB 연결
          Connection c = getConnection()
          
          ...
	}

	public Connection getConnection() throws ClassNotFoundException, SQLException {
          Class.forName("com.mysql.jdbc.Driver");
          return DriverManager.getConnection(...);
	}

 

중복 코드는 제거되었다. 하지만 DBMS정보나 접속 정보가 변경되면 UserDao를 수정해야 한다. 그렇다면 DB 연결 정보는 필요한 곳에서 직접 구현하도록 하면 어떨까?

간단하게 UserDao를 추상 클래스로 변경하면 가능하다.

public abstract class UserDao{

	public void add(User user) throws ClassNotFoundException, SQLException {
          // 1. DB 연결
          Connection c = getConnection()
          
          ...
	}

	public abstract Connection getConnection() throws ClassNotFoundException, SQLException;

 

위와 같이 추상 클래스를 만들어서 서브 클래스에서 필요에 맞게 메서드를 구현해서 사용하도록 하는 방법을 템플릿 메서드 패턴(template method pattern)이라고 한다. 이렇게 변경하게 되면 DB와 연결을 위한 커넥션에 대한 관심사는 UserDao가 아닌 하위 계층 클래스로 독립되었다.

 

또한 위의 코드를 다시 보면 getConnection()이라는 메서드는 java.sql.Connection 인터페이스를 구현한 클래스를 하위 클래스로부터 리턴 받는다. 이렇게 하위 클래스에서 구체적인 오브젝트 생성 방법을 결정하게 하는 방식을 팩토리 메서드 패턴(factory method pattern)이라고 한다. 

 

팩토리 메서드 패턴템플릿 메서드 패턴은 둘 다 상속을 통해서 기능을 확장하게 하는 패턴이기 때문에 디자인 패턴을 처음 접하면 헷갈린다. 간단하게 구별하자면 팩토리 메서드 패턴은 객체를 생성하는 관심사를 메서드로 빼놓는 방식이며, 템플릿 메서드 패턴은 부모 클래스에서 구현한 기능에 대해서 추가적인 기능을 확장할 수 있도록 메서드로 빼놓는 방식이다. 따라서 템플릿 메서드 패턴에서 만약 추가적인 기능이라는 것이 객체를 생성하는 기능이라면 템플릿 메서드 패턴이면서 팩토리 메서드 패턴인 것이다.  

 

하지만 자바를 조금 공부해봤다면 상속은 그다지 좋지 않은 구현 방식이라는 이야기를 들어본 적이 있을 것이다. 그 이유는 다음과 같다.

  • 상속은 다중 상속을 허용하지 않으므로, 후에 다른 목적으로 상속을 적용하기 힘들다.
  • 상속을 통한 상하위 클래스의 관계는 생각보다 밀접하다. 부모와 자식 계층으로 관심을 분리하긴 하였지만 결국엔 크게 보면 긴밀한 결합을 허용한다. 예를 들어 자식 클래스는 부모 클래스의 기능을 직접 사용할 수 있으므로 부모 클래스의 변경이 있을 때 자식 클래스도 수정하거나 다시 개발해야 할 수도 있다.

 

1-3. DAO의 확장

1-2장에서는 관심사를 분리하기 위해 DB와 연결을 위한 커넥션에 대한 관심사를 메서드로 추출해보았고, 이후에는 템플릿 메서드 패턴 또는 팩토리 메서드 패턴을 이용해 관심사를 분리하였다. 하지만 상속이라는 단점이 있으므로 개선해보자.

 

상속이 단점이 있다면, 상속관계가 아닌 완전히 독립적인 클래스로 나눠보자.

  1. UserDao.getConnection() 코드를 그대로 가지고와 SimpleConnectionMaker.makeNewConnection()으로 독립된 클래스를 만든다.
  2. UserDao에서 SimpleConnectionMaker를 인스턴스 변수로 관리하도록 선언하고, 생성자로 초기화하는 로직을 추가한다.
  3. 이후 UserDao.getConnection() 호출한 부분을 SimpleConnectionMaker.makeNewConnection()으로 변경한다.
public class UserDao {
	private SimpleConnectionMaker connectionMaker;

	public UserDao() {
		connectionMaker = new SimpleConnectionMaker();
	}

	public void add(User user) throws ClassNotFoundException, SQLException {
		// 1. DB 연결
		Connection c = connectionMaker.makeNewConnection();
		...
	}
}
public class SimpleConnectionMaker {
	public Connection makeNewConnection() throws ClassNotFoundException, SQLException {
		...
	}
}

 

 

이제 UserDao는 SimpleConnectionMaker를 가지고 DB와 연결을 위한 커넥션을 가지고 온다. 하지만 만약 DB와 연결을 위한 커넥션이 변경되어 SimpleConnectionMaker가 아닌 NewConnectionMaker로 변경해야 한다면 UserDao 코드를 변경해야 한다. 또한 UserDao는 SimpleConnectionMaker의 어떠한 메서드가 DB와 연결을 위한 커넥션을 가지고 오는 메서드인지 알아야 하는 문제점이 있다.

 

감이 오는가? 자바를 공부한다면 항상 나오는 이야기이다. UserDao와 SimpleConnectionMaker는 강한 결합이 되어있으며, 메서드에 대한 정의를 알 수 없다. 따라서 이제 자바의 인터페이스사용해보자.

 

인터페이스는 자바가 추상화를 위해 제공하는 도구이다. 인터페이스를 사용하는 곳에서는 인터페이스만 알면 어떤 구현 클래스가 오던지 상관없이, 공통된 메서드를 호출하여 사용할 수 있다.

 

이제 인터페이스를 만들어보자.

  1. ConnectionMaker 인터페이스를 만들고 makeNewConnection() 메서드를 정의한다.
  2. SimpleConnectionMaker를 ConnectionMaker 인터페이스를 구현하도록 한다.
  3. UserDao에서 SimpleConnectionMaker를 인스턴스 변수로 관리하도록 선언했던 부분을 ConnectionMaker로 변경한다.
public interface ConnectionMaker {
	public Connection makeNewConnection() throws ClassNotFoundException, SQLException;
}
public class SimpleConnectionMaker implements ConnectionMaker {
	@Override
	public Connection makeNewConnection() throws ClassNotFoundException, SQLException {
		...
	}
}
public class UserDao {
	private ConnectionMaker connectionMaker;

	... // 이전과 동일
}

 

인터페이스를 적용해보았다. 하지만 위처럼 변경하였지만 앞에서 언급한 문제가 하나는 해결되지 않았다. SimpleConnectionMaker가 아니라 NewConnectionMaker를 사용하고 싶다면 객체를 생성했던 생성자 부분을 변경해줘야 한다.

 

위 문제는 어떻게 해결해야 할까? 사실 이 책이 스프링 책이라서 처음에는 어라 어떻게 해야 하지?라고 생각할 수도 있지만, 이 책이 자바 책이었다면 바로 생각할 수 있을 것이다. 간단하다. 생성자에 인자를 추가하여 ConnectionMaker 구현체를 입력받거나 setter를 통해 구현체를 할당하면 된다.

 

생성자를 통해 ConnectionMaker 구현체를 입력받는다는 의미는 UserDao가 어떤 구현체를 사용할지 결정하는 것이 아닌 UserDao를 사용하는 사람(클라이언트)이 정하도록 하는 것이다. 즉 이것도 어떤 구현체를 사용할지의 관심사를 UserDao가 아닌 다른 곳으로 분리하는 개념으로 보면 된다. 이것을 관계 설정 책임의 분리라고 한다.

 

관계 설정은 오브젝트들 간의 관계를 설정해주는 것을 의미하여 책임의 분리는 제 3자가 두 오브젝트 간의 관계를 설정한다는 의미이다.

 

한번 생성자를 통해 관계 설정 책임을 분리해보자.

  1. UserDao 생성자에 ConnectionMaker를 입력받도록 변경하고, 입력받은 값을 세팅한다.
  2. UserDao를 사용하는 클라이언트에서 원하는 구현체를 입력하여 UserDao를 객체를 생성한다.
public class UserDao {
	private ConnectionMaker connectionMaker;

	public UserDao(ConnectionMaker connectionMaker) {
		this.connectionMaker = connectionMaker;
	}
    
    ... // 동일
public class UserDaoTest {
	public static void main(String[] args) {
		UserDao userDao = new UserDao(new SimpleConnectionMaker());
	}
}

 

이 책은 스프링에 대해서만 다루지 않는다. 한 번쯤 알아두면 좋은 내용들에 대해서도 중간중간 설명을 끼워 넣는다.

지금까지 개선해온 내용을 가지고 객체지향 기술의 여러 가지 이론을 설명한다. 아래 내용들은 객체 지향을 설명하는 곳에서는 항상 나오는 내용이므로 잘 기억하도록 하자.

 

개방 폐쇄 원칙(Open-closed Principle)

  • OCP로 줄여서 말하며 확장에는 열려 있어야 하고 변경에는 닫혀 있어야 한다는 의미이다. 말은 쉽지만 어렵다.
  • UserDao는 이제 DB 연결 설정 관점에서 OCP를 따른다. DB 연결 설정을 변경(확장)은 쉽게 가능하면(열려있으면)서 UserDao는 코드는 변경되지 않는다(닫혀있다).
  • UserDao가 OCP를 따르도록 하는 주요한 이유는 추상화인 인터페이스를 사용하였기 때문이다. 일반적으로 인터페이스를 사용해 OCP 원칙을 따르게 할 수 있다.
  • OCP는 객체지향 설계 원칙 다섯 개중 하나인 원칙이다. 객체지향 설계 원칙을 SOLID로 줄여서 말하며 각각은 다음과 같다.
    • SRP(Single Responsibility Principle) : 단일 책임 원칙으로 한 클래스나 단 하나의 책임만을 가져야 한다.
    • OCP(Open Closed Principle) : 개방 폐쇄 원칙으로 확장에는 열려 있어야 하고 변경에는 닫혀 있어야 한다.
    • LSP(Liskov Substitution Principle) : 리스 코프 치환 원칙으로 자식 클래스는 부모 클래스에서 가능한 행위를 수행할 수 있어야 한다.
    • ISP(Interface Segregation Principle) : 인터페이스 분리 원칙으로 인터페이스는 꼭 필요한 메소들만 선언되어 있어야 한다. 즉 크게 만들지 말고 작게 만들어야 한다.
    • DIP(Dependency Inversion Principle) : 의존관계 역전 원칙

 

높은 응집도와 낮은 결합도(High coherence and low coupling)

  • 응집도가 높으면, 변화가 일어날 때 변하는 부분이 크기 때문에 한 번에 확인할 수 있으므로, 어디를 고쳐야 할지도 알기 쉬우며 테스트도 그 해당 부분만 하면 된다.
  • 1-1장에서 본 UserDao는 여러 관심사가 얽혀 있었다. 즉 같은 관심 사끼 리 응집해있지 않았으므로, DB 연결 설정에 대한 코드를 수정해야 한다면, 어디를 고쳐야 할지 알기 쉽지 않았고, 테스트도 전체적으로 다시 해야 했다.
  • 하지만 관심사를 분리하여 UserDao는 SQL에만 관심을 가지도록 응집도를 높이고, ConnectionMaker 구현 클래스에서는 DB 연결 설정에 대한 관심을 가지도록 응집도를 높였다.
  • 오브젝트의 변경이 일어날 때에 관계를 맺고 있는 다른 오브젝트에게 변화를 요구하는 정도(결합도)가 낮아야 한다. 결합도가 낮다면 다른 오브젝트가 변경이 일어난다 하더라도 다른 오브젝트는 변화가 없을 것이다.
  • UserDao는 ConnectionMaker 인터페이스를 통해 결합도를 낮췄다. ConnectionMaker의 구현체가 변경되더라도 UserDao는 변화될 필요가 없다.

 

전략 패턴(Strategy Pattern)

  • 전략 패턴이란 어떠한 행위를 할 때 사용할 전략을 쉽게 변경할 수 있도록 하기 위한 패턴이다. 예를 들어 전통 게임인 스타크래프트 테란으로 게임을 할 때, 건물을 짓는 순서를 하나의 행위라고 한다면, 어떠한 순서(전략)로 건물을 지을지는 게이머가 결정한다.
  • 전략 패턴의 정의는 자신의 기능(행위)에서 필요에 따라 변경이 필요한 알고리즘(전략)을 인터페이스를 통해 통째로 외부로 분리시키고, 이를 구현한 구체적인 알고리즘(전략) 클래스를 필요에 따라 외부에서 바꿔서 사용할 수 있게 하는 디자인 패턴이다.
  • UserDao는 DB 연결 설정이라는 기능을 ConnectionMaker 인터페이스를 통해 해당 구현체가 제공하는 알고리즘(전략)을 UserDaoTest(외부)에서 전달받기 때문에 전략 패턴에 해당한다고 볼 수 있다. 

 

1-4. 제어의 역전(IoC)

이제 UserDaoTest-UserDao-ConnectionMaker 구조로 리팩터링을 하였다. 그런데 한번 UserDaoTest를 다시 봐보자!

 

UserDaoTest는 UserDao가 사용할 특정 ConnectionMaker의 구현체를 생성해서 UserDao 객체를 생성하고 나서 add(), get() 등을 테스트하는 클래스이다. 다시 보니 UserDaoTest는 객체를 생성하는 관심사와 테스트하는 관심사, 즉 관심사가 두 개다! 처음부터 해왔던 관심사의 분리인데.. 또다시 관심사가 모여있다니.. 어쩔 수 없다. 다시 관심사를 분리해보자. 메서드 추출보다는 독립된 클래스로 만들어보자.

 

일반적으로 객체지향에서 객체를 생성하는 목적으로 만들어진 클래스를 팩토리(Factory) 클래스라고 한다. 물건을 여러 가지 생산해내는 공장처럼 객체를 여러 가지 생성하기 때문에 붙여진 이름이다. UserDaoTest의 객체를 생성하는 관심사를 팩토리 클래스로 빼내어 만들어보자.

  1. DaoFactory 클래스를 만들고 DaoFactory.userDao() 메서드를 만들어 UserDaoTest의 UserDao 객체 생성 로직을 작성하자.
  2. UserDaoTest에서는 DaoFactory.userDao()를 이용하여 UserDao 객체를 얻는다.
public class DaoFactory {
	public UserDao userDao() {
		return new UserDao(new SimpleConnectionMaker());
	}
}
public class UserDaoTest {
	public static void main(String[] args) {
		// 관계 설정
		UserDao userDao = new DaoFactory().userDao();

		// UserDao 테스트
	}
}

 

현재 Dao는 UserDao 뿐이기 때문에 DaoFactory에는 메서드가 하나뿐이지만, Dao가 추가된다면 메서드를 추가하여 다른 Dao 객체를 만드는 로직을 넣으면 된다. 하지만 어떤 ConnectionMaker 구현체를 사용할지는 코드가 중복되므로 메서드로 추출해서 작성하자.

public class DaoFactory {
	public UserDao userDao() {
		return new UserDao(connectionMaker());
	}
	
	public DeptDao deptDao(){
		return new DeptDao(connectionMaker());
	}
	
	public ConnectionMaker connectionMaker(){
		return new SimpleConnectionMaker();
	}
}

 

지금까지 객체를 생성해주는 DaoFactory를 구현해보았다. DaoFactory는 DaoFactory는 애플리케이션의 어떠한 비즈니스적인 로직은 담고 있지 않는다. 애플리케이션에서 사용할 UserDao와 ConnectionMaker의 관계를 설정하였다. 즉 DaoFactory는 객체 간의 관계를 정의한 설계도와 같은 역할을 하였다.

 

1-1장에서 구현한 UserDao를 보게 되면, UserDao는 직접적으로 자신이 어떠한 객체를 사용할지를 직접 결정(제어)하였다. 어찌 보면 이렇게 구현하는 게 이해하기 쉽고 직관적이다. 하지만 우리는 리팩터링 과정을 통해 이제는 DaoFactory가 그 역할을 대신(제어를 역전)하게 되었다.

 

이것이 바로 제어의 역전이다. 스프링을 공부한 적이 없다고 하더라도 스프링은 IoC(제어의 역전) 사용한다는 이야기는 많이 들어봤을 것이다. 그게 바로 이것이다(?)

 

사전적인 정의로는 다음과 같다.

제어의 역전이란 오브젝트가 자신이 사용할 오브젝트를 스스로 선택하지 않는다. 모든 제어 권한을 자신이 아닌 다른 대상에게 위임한다.

 

지금까지 짠 코드를 보면 스프링은 하나도 사용하지 않고 순수한 자바로만 작성하였다. 이렇듯 제어의 역전은 스프링이 나오기도 전에 사용된 용어이며 스프링이 아닌 일반적인 개념으로 보면 된다.

 

IoC를 적용함으로써 설계가 깔끔해지고 유연성이 증가하며 확장성이 좋아지기 때문에 필요할 때면 IoC 스타일의 설계와 코드를 만들어 사용하면 된다. 

스프링은 IoC를 모든 기능의 기초가 되는 기반기술로 삼고 있으며, IoC를 극한까지 적용하고 있는 프레임워크이다.
- 토비의 스프링

 

1-5. 스프링의 IoC

지금까지 순수 자바로 IoC 방식대로 구현하였다. 이번장에서는 순수 자바로 구현한 코드를 기준으로 스프링에서는 어떻게 IoC를 구현(설정)하는지를 설명한다.

 

먼저 설명하기 전에 스프링에서 사용하는 용어를 나열한다. 일단 나열만 하고 설명은 하나씩 내용에서 하도록 한다.

 

스프링 IoC 용어 정리

  • 빈(bean) : 스프링이 IoC 방식으로 관리하는 오브젝트
  • 빈 팩토리(bean factory) : 스프링의 IoC를 담당하는 핵심 컨테이너. 빈 팩토리를 바로 사용하지 않고 이를 확장한 애플리케이션 콘텍스트를 이용한다.
  • 애플리케이션 콘텍스트(application context) : 빈 팩토리를 확장한 IoC 컨테이너다.
  • 설정 정보/설정 메타정보(configuration metadata) : IoC를 적용하기 위해 사용하는 메타정보
  • 컨테이너(container) 또는 IoC 컨테이너 : IoC 방식으로 빈을 관리한다는 의미에서 애플리케이션 콘텍스트나 빈 팩토리를 컨테이너 또는 IoC 컨테이너라고도 한다.

 

스프링이 관리하는 오브젝트를 빈(Bean)이라고 한다. 그렇다면 우리가 관계를 맺어주기 위해 관리했던 클래스는 무엇인가? UserDao와 ConnectionMaker이다. 따라서 이 두 클래스는 이제 스프링에서 관리해야 하니 빈으로 만들어야 한다. 빈으로 만드는 방법은 간단하다. 해당 객체를 리턴하는 메서드에 @Bean 어노테이션만 추가하면 된다.

public class DaoFactory {
	@Bean
	public UserDao userDao() {
		return new UserDao(connectionMaker());
	}
	
	@Bean
	public DeptDao deptDao(){
		return new DeptDao(connectionMaker());
	}
	
	@Bean
	public ConnectionMaker connectionMaker(){
		return new SimpleConnectionMaker();
	}
}

 

스프링은 오브젝트 간에 관계를 정의하는 내용을 담고 있는 빈 팩토리(Bean Factory) 또는 애플리케이션 콘텍스트(Application Context)을 가지고 있다. 그렇다면 관계를 담고 있는 빈 팩토리에 관계를 넣어주기 위해서는 어떻게 해야 할까? 당연하게도 관계 설정을 먼저 해야 한다. 그렇다면 우리가 관계를 맺어주도록 설정했던 클래스는 무엇인가? 바로 DaoFactory이다. 따라서 DaoFactory를 빈 팩토리에 넣어줘야 한다. 넣어주는 방법은 아래와 같다.

  1. DaoFactory 클래스에 @Configuration 어노테이션을 붙인다.
  2. 빈 팩토리 또는 ApplicationContext에 DaoFactory를 넣어준다.
@Configuration
public class DaoFactory {
	...
public class UserDaoTest {
	public static void main(String[] args) {
		// 관계 설정
		ApplicationContext context = new AnnotationConfigApplicationContext(DaoFactory.class);
		UserDao dao = context.getBean("userDao" , UserDao.class); 
		
		// UserDao 테스트
		...
	}
}

 

@Configuration 어노테이션은 해당 클래스가 스프링에서 사용할 어떠한 설정을 담당하고 있다고 표현하는 어노테이션이다. 여기서는 관계 설정을 담당하므로 해당 어노테이션이 붙여졌다.

 

위 소스를 보면 "userDao"로 하드 코딩된 부분이 있는데 이것은 빈의 이름이다. @Bean 어노테이션을 메서드에 붙였는데, 이때 메서드명이 빈의 이름이 된다.

 

최종적으로 ApplicationContext.getBean()을 통해서 userDao() 메서드가 호출되게 되고 UserDao 빈이 생성되어 얻게 된다.

 

스프링을 사용하면 더 편할 줄 알았지만, 오히려 구현한 순수 자바에 이것저것 추가되었다. 그렇다면 왜 스프링을 사용할까?

 

먼저 팩토리가 여러 개일 때를 생각해보자. DaoFactory뿐만 아니라 ServiceFactory 등 다양한 팩토리가 존재했을 때, 클라이언트는 자신이 필요한 객체를 얻기 위해 어느 팩토리에 해당 객체가 있는지 클래스를 알아야 한다. 하지만 스프링은 팩토리를 관리하는 것이 아닌 위에서 언급한 빈 팩토리 또는 애플리케이션 콘텍스트라는 설정을 모두 담고 있는 객체를 사용한다. 따라서 자신이 필요한 객체를 얻기 위해서 빈 팩토리 또는 애플리케이션 콘텍스트만 알면 된다.

 

DaoFactory는 단순하게 객체를 생성하는 역할만 제공한다. 하지만 스프링에서는 객체 생성뿐만 아니라, 이외의 부가적인 기능을 제공한다. 부가적인 기능들은 2장부터 설명하는 내용들이다.

 

DaoFactory는 객체를 생성하기 위해 메서드를 호출한다. 하지만 스프링은 빈을 검색하는 다양한 방법을 제공한다. 예를 들어 getBean()뿐만 아니라, 타입 또는 애노테이션만으로도 찾을 수 있다.

 

1-6. 싱글톤 레지스트리와 오브젝트 스코프

과연 스프링을 이용해서 getBean()으로 UserDao를 얻는 것과 이전 DaoFactory의 메서드를 호출하여 UserDao를 얻는 것의 차이는 무엇일까?

 

getBean()을 하면 결국에는 DaoFactory.userDao() 메서드가 호출되는 것이니 결국에는 동일한 것 아닌가? 라고 생각할 수 있다. 하지만 다르다. 무엇이 다른지 보자.

 

일반적으로 객체를 new 연산자를 사용해서 생성하면 논리적으로는(Object.equals()) 같을 수는 있어도 실제 메모리를 비교했을 때는 서로 다른 주소를 갖고 있는 객체이다. 아래 예제를 보면 값이 다르다.

	public static void main(String[] args) {
		DaoFactory daoFactory = new DaoFactory();
		UserDao dao1 = daoFactory.userDao();
		UserDao dao2 = daoFactory.userDao();

		System.out.println(dao1); // spring.toby.study.chapter1.UserDao@15db9742
		System.err.println(dao2); // spring.toby.study.chapter1.UserDao@6d06d69c
	}

 

getBean()을 이용해도 DaoFactory.userDao()를 호출하는 것이니 위와 같은 결과가 나올 것으로 생각되지만 그렇지 않다. 예제를 보자.

	public static void main(String[] args) {
		ApplicationContext context = new AnnotationConfigApplicationContext(DaoFactory.class);
		UserDao dao1 = context.getBean("userDao", UserDao.class);
		UserDao dao2 = context.getBean("userDao", UserDao.class);

		System.out.println(dao1); // spring.toby.study.chapter1.UserDao@309e345f
		System.out.println(dao2); // spring.toby.study.chapter1.UserDao@309e345f
	}

 

dao1과 dao2가 동일한 주소 값을 가지고 있다. 결국 같은 객체이다. 그 이유는 다음과 같다.

  1. 스프링은 getBean()을 사용하여 호출할 때 어느 저장소에 해당 빈이 존재하는지 확인한다.
  2. 존재한다면 해당 빈을 전달한다.
  3. 존재하지 않는다면 빈을 생성한다.

 

마치 해당 객체는 애플리케이션에서 오직 단 하나만 존재할 수 있도록 관리하고 있는 것 같다. 싱글톤처럼!

그렇다. 스프링은 싱글톤을 좋아하고 지지한다. 그래서 스프링은 직접 싱글톤 형태의 오브젝트를 만들고 관리하는 기능을 제공한다. 1번에서 어느 저장소라는 것은 싱글톤 레지스트리라는 저장소이다.

 

싱글톤은 안티 패턴이라고 하는 사람도 있고, 옹호론자도 있다. 그 이유는 서로 각각 다양하다. 즉 단점이 있다는 것인데 스프링은 이런 단점을 보완하기 위해 직접 싱글톤을 관리한다. 따라서 우리는 싱글톤을 위해서 클래스를 구현할 때 어떠한 작업도 할 필요가 없다.

 

어떠한 추가적인 작업을 할 필요가 없긴 하지만, 클래스를 구현할 때 조심스럽게 구현해야 한다. 싱글톤이라는 말은 항상 같은 객체를 사용한다. 만약 A 스레드와 B 스레드가 getBean()을 통해 동일한 빈을 가지고 와서 해당 빈을 동시에 수정하거나 작업을 하게 되면 꼬일 수가 있다. 즉 싱글톤은 멀티스레드 환경에서 주의해야 한다. 

 

멀리스 레드에서 빈에 대해 컨트롤하기가 힘들다면 클래스를 stateless 또는 immutable 하게 구현하면 된다. 그렇게 되면 해당 빈은 변경은 일어나지 않기 때문에 여러 스레드가 동시에 사용한다고 해도 문제가 없다. 마치 읽기 모드만 가능한 문서처럼 말이다.

 

하지만 가끔씩은 getBean()을 해오면 항상 새로운 객체를 받고 싶은 환경도 있을 것이다. 따라서 스프링에서는 이것을 설정으로 정할 수 있는데, 이를 스코프(Scope)라고 한다. 스코프 종류는 아래와 같다.

 

스코프(Scope) 종류 (카카오 면접 때 물어봤던 기억이 난다)

  • 싱글톤(singleton) : 오직 컨테이너당 한 개의 객체만 생성하여 사용
  • 프로토타입(prototype) : 요청할 때마다 매번 새로운 객체를 생성하여 사용
  • 요청(request) : HTTP 요청마다 매번 새로운 객체를 생성하여 사용
  • 세션(session) : 웹의 세션마다 객체를 생성하여 사용

 

 

1-7. 의존관계 주입(DI)

IoC와 마찬가지로 스프링을 공부한 적이 없다고 하더라도 스프링의 특징 중 DI(Dependency Injection), 즉 의존관계 주입이라는 이야기를 들어봤을 것이다.

 

IoC는 폭넓게 사용되는 용어이기 때문에 좀 더 스프링의 의도가 명확히 드러나는 DI라는 명칭을 스프링에서 사용하게 되었다.

 

그렇다면 의존 관계는 무엇인가? 아주 심플하다. 어떤 객체가 누구를 사용하냐에 따라 달려있다. 지금까지 구현한 예를 따지면 UserDao는 DB 연결을 위해 ConnectionMaker에 의존하는 관계를 맺고 있다.

 

그런데 ConnectionMaker는 인터페이스이며 어떤 구현체를 사용할지는 UserDao만을 봐서는 알 수가 없다. 즉 UserDao가 사용할 구현체는 런타임 시에 DaoFactory를 설정으로 사용한 애플리케이션 콘텍스트, 즉 제3의 존재가 결정하고 주입한다. 따라서 이를 DI라고 한다.

 

DI는 자신이 사용할 오브젝트에 대한 선택과 생성 제어권을 외부로 넘기고 자신은 수동적으로 주입받은 오브젝트를 사용한다는 점에서 IoC의 개념에 잘 들어맞는다. 스프링 컨테이너의 IoC는 주로 의존관계 주입 또는 DI라는 데 초점이 맞춰져 있다. 그래서 스프링을 IoC 컨테이너 외에도 DI 컨테이너 또는 DI 프레임워크라고 부르는 것이다.
  - 토비의 스프링

 

의존관계 주입뿐만 아니라, 의존관계를 맺는 방법을 스스로 검색을 이용하는 방법인 의존관계 검색이 있다.

 

의존관계 주입은 제 3자가 의존할 객체를 주입하는 것이다. 따라서 주입받을 때는 다양한 방식이 있겠지만 위 에제에서는 UserDao의 생성자에 ConnectionMaker를 인자로 받게 하여 제 3자로부터 주입받았다. 하지만 의존관계 검색은 제 3자가 주입하는 것이 아닌 자신이 직접 애플리케이션 콘텍스트에서 빈을 찾아 설정하는 방식이다. 여기서 의문이 생긴다. 지금까지 IoC와 DI의 장점을 설명하고, 관계를 외부에서 주입한다고 하였는데, 결국에는 다시 해당 클래스가 직접 빈을 찾아 설정한다니.. 처음 읽으면 이게 무슨 말인가 싶었다.

 

하지만 의존관계를 자신이 바로 설정하는 것이 아닌 빈을 검색해서 설정하는 방식이다. 즉 애플리케이션 콘텍스트에서 빈을 가져올 때, 그 빈에는 어떤 객체가 있는지는 알 수 없다. 검색해서 사용할 뿐이다.

 

의존 관계 검색은 사실 위 예제에서 해보았다. 바로 getBean()을 통해서 가져오는 방식이 의존관계 검색이었다.

public class UserDaoTest {
	public static void main(String[] args) {
		// 관계 설정
		ApplicationContext context = new AnnotationConfigApplicationContext(DaoFactory.class);
		UserDao dao = context.getBean("userDao" , UserDao.class); 
		
		// UserDao 테스트
		...
	}
}

 

일반적으로는 UserDao와 ConnectionMaker처럼 의존관계 주입을 통해서 의존관계를 설정한다. 하지만 애플리케이션을 시작 처음 main에서는 main을 제 3자가 의존성 주입할 수는 없으므로, 의존관계 검색을 사용한다.

 

DI의 개념을 알아보았다. 그렇다면 제 3자가 의존관계를 주입해주면 무엇이 좋은가?

  1. 기능 구현의 교환 : 기능 구현을 변경해야 한다면 자신의 소스는 변경할 필요 없이 기능이 변경된 객체를 제 3자가 주입해주면 된다. UserDao에서 DB 접속 정보가 바뀌어도, connectionMaker()의 빈을 다른 객체로 변경해주면 UserDao는 아무 변경 없이 기능이 변경된다. 아래 예제는 기존 SimpleConnectionMaker 구현체를 리턴하는 빈을 NewConnectionMaker 구현체를 리턴하도록 변경한 예제이다.
    public class DaoFactory {
    	@Bean
    	public UserDao userDao() {
    		return new UserDao(connectionMaker());
    	}
    	
    	@Bean
    	public DeptDao deptDao(){
    		return new DeptDao(connectionMaker());
    	}
    	
    	@Bean
    	public ConnectionMaker connectionMaker(){
    		return new NewConnectionMaker();
    	}
    }
  2. 부가기능 추가 : 위에 내용이랑 비슷하다. 추가적인 기능의 추가를 새로운 클래스를 만들어서 대체하는 것이 아닌 기존 객체를 인스턴스 변수로 활용해서 기능을 추가하는 방식이다. 예를 들어 ConnectionMaker.makeConnection()을 호출할 때마다 로깅을 하는 부가기능을 추가하고 싶다면 아래와 같다.
    public class LoggingConnectionMaker implements ConnectionMaker {
    
    	private final ConnectionMaker connectionMaker;
    
    	public LoggingConnectionMaker(ConnectionMaker connectionMaker) {
    		this.connectionMaker = connectionMaker;
    	}
    
    	public Connection makeNewConnection() throws ClassNotFoundException, SQLException {
    		System.out.println("logging");
    
    		return connectionMaker.makeNewConnection();
    	}
    
    }

 

의존관계 주입은 여러 가지 방식으로 할 수 있다. 지금까지는 아래와 같이 생성자를 사용해서 의존관계를 주입하였다. 

public class UserDao {
	private ConnectionMaker connectionMaker;

	public UserDao(ConnectionMaker connectionMaker) {
		this.connectionMaker = connectionMaker;
	}
    
    ... // 동일

 

일반 메서드로 가능하다. 아래는 setter 메서드로 변경하여 수정된 코드이다.

public class UserDao {
	private ConnectionMaker connectionMaker;

	public void setConnectionMaker(ConnectionMaker connectionMaker) {
		this.connectionMaker = connectionMaker;
	}
    
    ... // 동일
}
@Configuration
public class DaoFactory {

	@Bean
	public UserDao userDao() {
		UserDao userDao = new UserDao();
		userDao.setConnectionMaker(connectionMaker());
		return userDao;
	}

	... // 동일
}

 

1-8. XML을 이용한 설정

우리는 DaoFactory에 @Configuration 어노테이션을 붙여 의존 관계 설정으로 사용하였다. 하지만 의존관계 설정을 위해 자바 코드로 하나씩 타이핑하기도 귀찮고, 빌드하고 다시 컴파일하기도 귀찮다.

 

그래서 스프링은 다양한 방법을 통해서 의존관계 설정 정보를 정의할 수 있는데, 그중 대표적인 게 XML이다.

 

XML 설정 방식에 대한 설명은 생략하고 DaoFactory에서 설정한 의존관계 설정을 XML로 표현한 내용은 아래와 같다.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
						http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

	<bean id="myConnectionMaker" class="spring.toby.study.chapter1.SimpleConnectionMaker"></bean>
	<bean id="newConnectionMaker" class="spring.toby.study.chapter1.NewConnectionMaker"></bean>

	<bean id="userDao" class="spring.toby.study.chapter1.UserDao">
		<property name="connectionMaker" ref="myConnectionMaker" />
	</bean>
</beans>

 

빈은 <bean>으로 등록하고 의존 관계 주입은 <property>를 통해서 원하는 관계를 설정해주면 된다.

 

 

 

이제 UserDaoTest에서 DaoFactory가 아닌 위에서 만든 XML을 관계 설정으로 사용하도록 애플리케이션 콘텍스트 객체를 만드는 방법은 아래와 같다.

  1. XML 설정 방식을 작성한 파일을 applicationContext.xml로 저장한다.
  2. AnnotationConfigApplicationContext이 아닌 GenericXmlApplicationcontext 또는 ClassPathXmlApplicationContext를 사용한다.
public class UserDaoTest {
	public static void main(String[] args) {
		ApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml");

		...
	}
}

 

GenericXmlApplicationcontext는 클래스 패스 기준으로 applicationContext.xml에 경로를 입력하면 된다.

ClassPathXmlApplicationContext는 특정 클래스의 위치를 힌트로 주어 경로를 모두 입력하지 않아도 찾을 수 있도록 한다.

new GenericXmlApplicationContext("spring/toby/study/chapter1/applicationContext.xml");

// UserDao.class의 패키지는 spring.toby.study.chapter1
new ClassPathXmlApplicationContext("applicationContext.xml", UserDao.class);

 

 

DataSource 인터페이스로 변환

 

위에서 구현한 ConnectionMaker를 대체할 수 있는 javax.sql.DataSource라는 인터페이스를 자바에서는 이미 제공한다. 따라서 ConnectionMaker를 DataSource로 변경해보자. 

  1. ConnectionMaker를 사용했던 부분을 DataSource로 변경한다. 
  2. DatsSource를 빈으로 설정한다.
public class UserDao {
	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void add(User user) throws SQLException {

		// 1. DB 연결
		Connection c = dataSource.getConnection();

		//
	}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
						http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

	<bean id="myConnectionMaker" class="spring.toby.study.chapter1.SimpleConnectionMaker"></bean>
	<bean id="newConnectionMaker" class="spring.toby.study.chapter1.NewConnectionMaker"></bean>

	<bean id="dataSource" class="org.springframework.jdbc.datasource.Simp1eDriverDataSource">
		<property name="driverClass" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://loca1host/springbook" />
		<property name="username" value="spring" />
		<property name="password" value="book" />
	</bean>
	
	<bean id="userDao" class="spring.toby.study.chapter1.UserDao">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

 

 

위 DataSource의 빈을 설정할 때 ref가 아닌 value 속성을 사용해서 일반 값도 주입할 수 있다. value 값은 스트링이지만, 자동으로 알맞은 형태로 변환해준다.

 

 

반응형

'Spring > [책] 토비의 스프링' 카테고리의 다른 글

[토비의 스프링] 3장. 템플릿  (0) 2022.01.05
반응형

WITH절

SQL을 작성하다 보면 반복되는 서브 쿼리를 이곳저곳 복붙 해서 사용해야 하는 경우가 있다. 이곳저곳 흩어져 있기 때문에 가독성도 떨어지고, 만약 서브 쿼리를 수정해야 한다면 위치를 찾아 각각 다 고쳐줘야 한다.

 

WITH 절은 이러한 서브 쿼리의 단점을 해결하기 위해 서브 쿼리를 마치 함수처럼 이름을 가지도록 사용하는 방법이다.

 

약간 VIEW와 쓰임새가 비슷해보이지만 차이점은 WITH 절은 VIEW처럼 DBMS에 저장하여 사용하는 것이 아닌 실행할 쿼리문 내에 정의되어 있어, 해당 쿼리가 실행될 때마다 파싱 되고 실행계획을 세운다.

 

 

 

WITH절 기본

WITH절의 기본 구조 예제는 아래와 같다.

WITH
WITH_SUB AS (
	... -- 서브쿼리 작성
)
SELECT * FROM WITH_SUB; -- WITH절을 사용한 메인 쿼리 예시

 

WITH절은 여러 개 선언할 수 있으며, WITH절에서 다른 WITH 절을 사용할 수 있다.

WITH
WITH_SUB1 AS (
	... -- 서브쿼리 작성
),
WITH_SUB2 AS (
	SELECT * FROM WITH_SUB1 -- 다른 WITH절을 사용한 WITH절 
)
SELECT * FROM WITH_SUB2;

 

 

Recursive WITH절

WITH절을 가지고 자신이 자신을 호출하는 Recursive 한 방식을 사용할 수 있다. 이를 Recursive WITH절이라고 한다.

 

Recursive WITH 절을 사용하려면 UNION ALL을 가지고 초기값을 정해주는 초기 서브 쿼리와, 이후에 행위를 작성하는 Recurisive 서브 쿼리로 나뉜다. 단 항상 재귀함수에서 그랬듯이 무한루프에 빠지지 않도록 종료조건을 조건절로 표현을 해야한다.

 

아래는 1~9까지의 합을 구하는 예제이다.

WITH
SUM(NUM, RESULT) AS(
	-- 초기값을 설정해주는 초기 서브쿼리이다. 즉 NUM=1, RESULT=1 이다.
	SELECT 1,1 FROM DUAL 
	UNION ALL
	-- 이후 행위를 정의한 Recurisive 서브쿼리이다. NUM의 값은 1씩 증가시키며, RESULT 값에는 계속 (NUM + 1)을 더한다.
	SELECT NUM+1, (NUM+1) + RESULT FROM SUM WHERE NUM < 9-- 종료조건으로 NUM < 9를 작성했다.
)
SELECT NUM ,RESULT FROM SUM;

 

 

계층형 WITH절

오라클에서는 계층형 쿼리를 위해 CONNECT BY문을 지원한다. 하지만 이 문법은 오라클에서만 사용 가능하다. WITH절은 ANSI SQL에 포함되어 있기 때문에 여러 DBMS에서 지원한다. 따라서 WITH절은 DBMS가 변경되어도 쿼리를 변경할 필요가 없다는 장점이 있기때문에 계층형 쿼리를 작성할 때는 CONNECT BY보다는 WITH절을 사용하자.

 

아래 예제는 WITH절을 이용한 계층형 쿼리 예제이다.

WITH
WITH_TAB (LVL, NAME, EMP_ID, MGR_ID) AS
(
  -- 초기값을 설정해주는 초기 서브쿼리이다. 즉 LVL=1이고, MGR_ID가 NULL인 값이 최초 값이다.
  SELECT 1, NAME, EMP_ID, MGR_ID
  FROM HR.EMPLOYEES
  WHERE MGR_ID IS NULL -- 오라클의 START_WITH와 동일한 조건
  UNION ALL
  -- 이후 행위를 정의한 Recurisive 서브쿼리이다. 초기값의 EMP_ID의 값이 MGR_ID와 같은 행을 찾아 LVEL + 1을 해준다.
  SELECT B.LVL + 1 AS LVL, A.NAME, A.EMP_ID, A.MGR_ID
  FROM HR.EMPLOYEES A, WITH_TAB B
  WHERE A.MGR_ID = B.EMP_ID -- 오라클의 CONNECT BY PRIOR와 동일한 조건
)
SELECT LVL, NAME, EMP_ID, MGR_ID FROM WITH_TAB;

 

아래는 위와 같은 결과를 나타내는 오라클의 계층형 쿼리이다.

SELECT LEVEL, NAME, EMP_ID, MGR_ID
FROM HR.EMPLOYEES
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;
반응형
반응형

SQLD, SQLP 문제에서 자주 나오는 헷갈리는 문장 정리

CHECK

ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

Oracle의 컬럼 제약조건인 CHECK는 조건을 만족하지 못하는 값이 들어올 경우 에러가 발생한다.

 

NULL은 CHECK 조건을 무시하여 항상 조건을 통과한다.

 

 

DML문

INSERT

  • ORACLE은 VARCHAR에 ''을 입력하면 NULL로 입력된다.
  • SQL Server는 VARCHAR에 ''을 입력하면 ''로 입력된다.

DELETE

  • FROM 키워드는 생략 가능하다. ex) DELETE FROM 주문; = DELETE 주문

SELECT

  • 두 개 이상의 테이블을 참조하는 FROM절에 테이블에 대한 ALIAS를 사용했을 때, 테이블들이 중복된 칼럼을 가지고 있을 경우 반드시 ALIAS명을 사용해야 함. 

 

 

로깅을 하고 싶지 않다면 NOLOGGING 옵션을 사용한다.

ALTER TABLE EMP NOLOGGING;

 

ESCAPE

LIKE 연산자에서 '%'와 '_'는 특수한 용도로 사용되는 문자인데, 만약 LIKE을 통해 검색하고자 하는 단어에 '%'와 '_'를 포함할 경우에는 'ESCAPE'를 사용하면 된다. 원하는 특수문자를 앞에 작성하고 '%'와 '_'를 사용하면 된다. 아래 예시는 '#'이라는 특수한 문자를 지정하여 '_'를 포함하는 NAME을 검색한다.

SELECT * FROM EMP WHERE NAME LIKE '%#_%' ESCAPE '#';

 

IN, NOT IN

IN 절에 NULL이 있을 경우, 무시함

 

NOT IN 절에 NULL이 있을 경우, 항상 true로 리턴하게 되어 NOT IN 결과가 항상 0건임.

 

 

INDEX

인덱스는 내림차순으로 생성 및 정렬된다.

 

인덱스 구조에 따른 특징

  • 클러스터 인덱스
    • 인덱스의 리프 데이터가 곧 데이터 페이지다.
  • 비트맵 인덱스
    • 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 구조이다.
    • 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계 되었다.
  • B-tree 인덱스
    • 브랜치 블록과 리프 블록으로 구성되며 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 칼럼의 값으로 정렬된다.

 

 

 

ORDER BY

ORDER BY COUNT(*)은 가능하다.

 

ORACLE 은 NULL 을 가장 큰 값으로 취급하여 ORDER BY 시 맨 뒤로 정렬되고 SQL SERVER 반대로 가장 앞으로 정렬한다.

NULL의 순서를 조절하고 싶으면 ORDEY BY 마지막에 NULLS FIRST 또는 NULLS LAST를 사용한다.

 

ORDER BY 1, COL1 과 같이 숫자와 컬럼을 혼용하여 사용할 수 있다

 

 

Random Access

Netsted Loop는 이중포문이기 때문에 n * m 번만큼 랜덤 엑세스가 가장 많이 발생한다.

 

 

SQL Server - IDENTITY

IDENTITY [ ( seed , increment ) ]

  • SQL Server에서 사용할 수 있는 데이터타입으로 데이터를 식별할 수 있는 식별자 역할을 한다.
  • seed : 첫번째 행이 입력될 때의 시작값
  • increment: 증가되는 값
  • 해당 컬럼에 값을 넣을 경우 Error 발생 (Error 발생을 막기 위해서는 IDENTITY_INSERT 를 OFF 로 하면 되나 이런 경우 IDENTITY 를 쓴 의미가 없어짐)

 

Sub Query (서브 쿼리)

서브쿼리는 SELECT, FROM, HAVING, ORDER BY절 등에서 사용 가능

 

서브쿼리 자체에는 ORDER BY절을 사용할 수 없음

 

연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브 쿼리이다.

 

비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용한다.

 

인라인 뷰

  • FROM 절에 사용한 서브 쿼리.
  • Dynamic View(동적 뷰)라고도 함.

중첩된 서브쿼리

  • WHERE 절에 사용한 서브 쿼리

 

스칼라 서브 쿼리

  • 한 레코드당 정확히 하나의 값을 반환하는 서브 쿼리.
  • JOIN으로 동일한 결과를 추출할 수도 있음.

 

Single Row(단일 행) 서브쿼리

  • 실행 결과가 항상 1건 이하인 서브쿼리
  • =, <, <= 등 단일행 비교 연산자와 함께 사용됨

 

Multi Row(다중 행) 서브쿼리

  • 실행 결과가 여러 건인 서브쿼리
  • IN, ALL 등 다중행 비교 연산자와 함께 사용됨

 

Multi Column(다중 컬럼) 서브쿼리

  • 여러 컬럼을 반환하는 쿼리
  • 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있음. 단 컬럼 개수와 컬럼의 위치가 동일해야 함
  • Oracle에서만 지원하고, Server SQL에서는 지원하지 않음

 

Transaction

Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것

Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상이다. 트랜잭션 격리성이 낮은 경우 발생한다.

Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을 말한다. 트랜잭션 격리성이 낮은 경우 발생한다. 

 

저장점

  • 저장점을 정의하면 롤백 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있음
  • 동일한 이름의 저장점이 중복된다면, 가장 마지막의 저장점이 적용됨.
  • 아래는 SP2로 롤백하고 커밋하므로 SP2 이후에 쿼리문장은 적용되지 않는 예제임
    -- ORACLE 
    SAVEPOINT SP1;
    ...
    SAVEPOINT SP2;
    ...
    ROLLBACK TO SP2;
    COMMIT; 
    
    -- SQL Server
    SAVE TRANSACTION SP1;
    ...
    SAVE TRANSACTION SP2;
    ...
    ROLLBACK TRANSACTION SP1;
    COMMIt;

 

 

UNION ALL

중복 로우를 허용하는 합집합

 

칼럼 ALIAS는 첫번째 SQL 모듈 기준으로 표현 됨

 

정렬 기준은 마지막 SQL 모듈에 표시하면 됨

 

 

VIEW(뷰)

View는 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조된다.

 

참조한 테이블이 변경되면 View도 변경 된다.

 

View를 통해 입력, 수정, 삭제에는 제약이 있다.

 

한번 생성된 View는 Alter문을 통해 변경할 수 없고, 삭제 후 재 생성해야 한다.

 

하나의 테이블에 여러 개의 View를 생성 가능하다.

 

특정 컬럼만 조회할 수 있도록 할 수 있기에 보안 기능이 있다.

 

데이터 관리가 수월해지고, SELECT 문장이 간단하다.

 

View는 인덱스를 만들 수 없다.

계층형

ORACLE의 PRIOR은 CONNECT BY 뿐만 아니라, SELECT, WHERE절에서도 사용 가능함

 

CONNECT_BY_ISLEAF : leaf 데이터면 1, 아니면 0을 반환한다.

 

CONNECT_BY_ISCYCLE : 중복참조를 체크하는 함수로 CONNECT BY NOCYCLE일 경우에만 사용 가능하다. 서로가 서로를 부모로 가지는 경우를 찾을 때 사용하고, 중복참조 이면 1을 아니면 0을 반환한다. (루트까지의 경로에 존재하는 데이터를 의미한다라고도 사용함.)

 

CONNECT_BY_ROOT : 루트 노드의 정보

 

SYS_CONNECT_BY_PATH : 하위 레벨의 칼럼 표시

 

SQL Server는 계층형 질의문은 CTR(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개

 

 

비교연산자

CHAR형

  • 길이가 다르다면 작은 쪽에 SPACE 를 추가하여 길이를 같게 한 후에 비교한다

VARCHAR형

  • 서로 다른 문자가 나올 때까지 비교한다
  • 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다
  • 길이가 같고 다른 것이 없다면 같다고 판단한다

 

 

조인

NATURAL JOIN

  • 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인으로 반드시 조인하는 대상의 테이블들은 서로 같은 이름과 타입의 컬럼이 존재해야한다.
  • NATURAL JOIN에 사용된 컬럼은 식별자를 사용할 수 없다.
    -- EMP와 DEPT 테이블에 DEPOTNO 컬럼이 각각 존재하므로 DEPTNO로 NATURAL JOIN한다.
    -- 단, DEPNO를 가지고 식별자를 사용하였으므로 (EMP.DEPTNO) 아래 쿼리는 에러가 발생
    SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
    FROM EMP NATURAL JOIN DEPT;

 

USING JOIN

  • 두 개의 테이블의 컬럼명이 같을 경우 조인 조건을 길게 적지 않고 간단하게 적을 수 있도록 하는 역할
  • NATURAL JOIN과 동일하게 USING JOIN에 사용된 컬럼은 식별자를 사용할 수 없다.
    SELECT DEPT_NO
    FROM EMP INNER JOIN DEPT USING(DEPT_NO)

 

 

 

FULL OUTER JOIN

  • = LEFT OUTER JOIN UNION RIGHT OUTER JOIN (UNION이므로 중복을 제거함)

 

오라클 (+) 부호

  • 해당 테이블에 null을 허용한다는 의미이다.
  • 등호의 오른쪽 테이블에 (+)가 붙으면 오른쪽 테이블에 null을 허용하는 것이므로 LEFT OUTER JOIN
  • 등호의 왼쪽 테이블에 (+)가 붙으면 왼쪽 테이블에 null을 허용하는 것이므로 RIGHT OUTER JOIN이다.

 

참조문

DELETE(MODIFY) (액션들) : 참조무결성에서 부모가 삭제될 경우, 행할 액션을 지정

  • CASCADE : 부모 삭제시, 자식도 삭제
  • SET NULL : 부모 삭제시, 해당 필드 NULL로 설정
  • SET DEFAULT : 부모 삭제시, 해당 필드의 기본값 설정
  • RESTRICT : 자식 테이블에 PK 값이 없는 경우만 부모의 값을 삭제 가능
  • NO ACTION : 참조 무결성을 해치는 삭제가 불가능. 즉 자식에 해당하는 값이 있을 경우, 부모 삭제 불가능

 

INSERT (액션들) : 참조무결성에서 자식이 삽입될 경우, 행할 액션을 지정

  • AUTOMATIC : 자식을 삽입했는데 부모의 값이 없을 경우, 부모값을 생성하고, 자식값을 삽입
  • SET NULL : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 NULL로 설정
  • SET DEFAULT : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 기본값 설정
  • DEPENDENT : 자식을 삽입했는데 부모의 값이 없을 경우에는 삽입 불가능. 즉, 부모에 값이 없으면 자식은 삽입 불가능
  • NO ACTION : 참조 무결성을 해치는 입력이 불가능. 

 

ETC

Oracle의 경우 Select시 조회되는 Header Column명은 디폴트로 대문자로 표현한다.  

반응형
반응형

절차형 SQL

절차형 프로그래밍 언어인 C언어처럼 SQL에도 절차 지향적인 프로그래밍이 가능하도록 벤더별로 프로그래밍 언어와 같은 절차형 SQL을 제공한다.

 

오라클에서는 PL(Procedural Language)/SQL을 제공하며 SQL Server에서는 T-SQL을 제공한다.

 

본 포스팅은 오라클의 PL/SQL을 아주 간단하게만 설명한다.

 

 

 

PL/SQL 구조

PL/SQL은 기본적으로 BLOCK 구조로 되어있다. BLOCK 안에는 기본적으로 DECLARE, BEGIN, EXCEPTION으로 구성되어 있으며 마지막에는 END로 BLOCK의 끝을 표현한다.

 

DECLARE : 선언부라고 하며 BEGINE ~ END에서 사용할 변수(상수)에 대하여 정의하는 부분이다. 정의할 게 없다면 생략 가능하다.

  • 변수란 데이터의 임시 저장영역으로 반복해서 재사용하기 위해 사용한다.
  • 변수를 사용할 수 있는 범위는 해당 블록 시작(BEGIN)에서 블록의 끝(END)에서만 사용가능하다.
  • 변수의 명칭은 반드시 문자로 시작해야 하며, 문자나 숫자, 특수문자를 포함할 수 있다. 단 변수명은 30 bytes 이하로 명명해야 한다.
  • 특정 값으로 미리 초기화 가능하다. 
  • 데이터를 할당하려면 ':='을 사용한다.
  • 상수로 사용하려면 변수명 다음에 CONSTANT 키워드를 사용하면 된다.
  • 변수의 데이터 타입은 직접 지정할 수 있지만, 다른 테이블에서 참조해서 지정할 수 있다.
  • 아래는 변수 선언의 예시이다.
    DECLARE
    	V_PI CONSTANT NUMBER := 3.14;			-- 상수로 선언하였으며 변경 불가능하다.
    	v_name VARCHAR2(100) := '홍길동';			-- 변수로 선언하였으며 디폴트 값으로 미리 '홍길동'을 할당
    	v_addr VARCHAR2(100); 				-- 변수로 선언하였으며 디폴트 값을 선언하지 않았다.
    	v_you VHARCHAR2(100) := '&your_name'; 		-- 해당 블록을 실행할때마다 사용자한테 입력받는다.​
    	v_deptId DEPT.DEPT_ID%TYPE			-- DEPT 테이블의 DEPT_ID 컬럼의 데이터타입을 참조한다.
    	v_temp	DEPT%ROWTYPE		-- DEPT 테이블의 선언된 컬럼의 모든 데이터타입 아무거나 들어갈 수 있다.

 

BEGIN : 실행 부라고 하며 처리하고자 하는 SQL문과 필요한 로직을 기술하는 부분이다.

 

EXCEPTION : 예외처리 부라고 하며 실행 도중에 에러가 발생할 경우에 에러를 처리하는 로직을 기술하는 부분이다. 생략 가능하다.

EXCEPTION
WHEN 예외명1 THEN
	예외처리문장;
WHEN 예외명2 OR 예외명3 THEN
	예외처리문장;
WHEN OTHERS THEN
	나머지_예외처리문장;

 

 

 

PL/SQL 특징

DECLARE, BEGIN, EXCEPTION, END와 같은 예약어는 마지막에 세미콜론(;)을 붙이지 않는다. 이외의 명령어는 세미콜론(;)을 붙인다.

 

BLOCK안에 새로운 BLOCK을 포함할 수 있다. 이를 Nested Block이라고 표현한다.

 

IF, LOOP 등의 조건을 사용하여 절차적인 프로그래밍이 가능하다.

 

여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 

PL/SQL로 작성된 코드는 PL/SQL 엔진이 처리하고, 일반적인 SQL 문장은 SQL 실행기가 처리한다.

 

PL/SQL에서는 DML 문장은 직접적으로 쿼리를 작성하여 사용할 수 있다. 하지만 DDL, DCL, TCL은 직접적으로 지원하지 않는다. 동적 SQL(런타임에 문자열로 작성되는 SQL)을 사용하면 가능하긴 하다. 동적 SQL은 execute immediate를 이용한다.

BEGIN
	execute immediate 'TRUNCATE TABLE 사원';
END

 

PL/SQL을 저장해서 쉽게 사용할 수 있다. 저장하여 사용할 경우 Stored PL/SQL Block이라고 한다. 저장하지 않고 사용할 경우에는 Anonymous PL/SQL Block이라고 한다.

 

 

PL/SQL SELECT

  • PL/SQL의 SELECT는 SELECT 한 데이터를 INTO 뒤에 변수에 값을 저장한다.
  • SELECT 컬럼 항목의 수와 INTO 변수 항목의 수는 동일해야 한다.
  • 반드시 로우의 결과는 1건만 나타나야 한다.
  • 아래 예시는 사원 ID가 '1'인 사원의 사원명이 v_name에 저장된다.
    DECLARE
    	v_name VARCHAR2(10);
    
    BEGIN
    	SELECT 사원명 INTO v_name FROM 사원 WHERE 사원ID = '1'; 
    
    END​

 

 

PL/SQL DML (INSERT, UPDATE, DELETE)

  • 기본적인 DML문을 BEGIN 문장에 사용하면 된다.
  • 사용자로부터 입력을 받고자 한다면 선언부에 &을 사용하여 입력받는다.
  • 아래 예시는 사원 테이블에 INSERT를 하는 예시이다.
    DECLARE
    	v_id	VARCHAR2(4) := '&id';
    	v_name	VARCHAR2(10) := '&name';
        
    BEGIN
    	INSERT INTO 사원 VALUES(v_id, v_name);
    
    END

 

 

PL/SQL DML 제어문

IF 조건문

IF (조건) THEN
	실행문장;
ELSE
	실행문장;
END IF;


IF (조건) THEN
	실행문장;
ELSEIF (조건) THEN
	실행문장;
END IF;​

 

CASE 조건문

CASE 변수
	WHEN 조건1 THEN 결과1
	WHEN 조건1 THEN 결과1
	...
	WHEN 조건N THEN 결과N
ELSE 디폴트결과
END;

 

LOOP 반복문

LOOP
	문장;
    EXIT 조건;
ENd LOOP;

 

WHILE 반복문

WHILE 조건 LOOP
	문장;
END LOOP;

 

FOR 반복문

FOR 변수 IN 시작숫자..끝숫자 LOOP
	문장;
END LOOP;

 

 

Stored PL/SQL Block

PL/SQL을 마치 함수(프로그램)처럼 정의해서 사용할 수 있다. 이를 Stored PL/SQL Block이라고 한다. 저장하지 않고 이름 없이 사용하는 것은 Anonymous PL/SQL Block이라고 한다. 아래는 두 개의 차이점을 정리한 내용이다.

 

Stored PL/SQL Block Anonymous PL/SQL Block
한 번 컴파일되어 데이터베이스에 저장됨 사용시마다 컴파일되고, 데이터베이스에 저장하지 않음
파라미터와 리턴값이 준재 파라미터와 리턴값이 존재하지 않음
다른 프로그램에서 호출 가능 다른 프로그램에서 호출 불가능

 

Stored PL/SQL Block의 종류는 Procedure, Function, Package, Trigger가 있다.

 

 

Procedure(프로시저)

  • 가장 기본적인 유형으로 한 번 컴파일된 후 데이터베이스에 저장되어 사용되고, 다른 프로그램에서 호출 가능하다.
  • 아래는 데이터베이스에 Procedure를 생성하는 방법이다.
    CREATE PROCEDURE p_test -- 프로시저 명칭을 입력한다.
    (
    	파라미터1 IN 데이터타입,
    	리턴값1 OUT 데이터타입,
    	둘다가능 IN OUT 데이터타입
    )
    IS
    	변수1 데이터타입;
    
    PL/SQL Block​
  • 파라미터1은 호출하는 곳에서 전달해줘야 하는 파라미터를 의미하므로 IN을 사용한다. 생략 가능하다.
  • 리턴값1은 호출하는 곳에서 리턴 값을 받을 리턴값을 의미하므로 OUT을 사용한다.
  • 둘 다 가능은 파라미터와 리턴 값이 된다는 의미로 IN OUT을 사용한다.
  • Procedure을 호출하는 방법은 아래와 같다.
    EXEC p_test() -- 인자가 없는 Procedure일 경우
    
    EXEC p_test(파라미터1, 리턴값1, 둘다가능) -- 인자가 있는 Procedure일 경우

 

Function(함수)

  • Function은 반드시 리턴 값이 존재하는 Block이다.
  • 아래는 데이터베이스에 Function을 생성하는 방법이다.
    CREATE FUNCTION f_test -- 함수 명칭을 입력한다.
    (
    	파라미터1 IN 데이터타입,
    	리턴값1 OUT 데이터타입,
    	둘다가능 IN OUT 데이터타입
    )
    RETURN 데이터타입
    IS
    	변수1 데이터타입;
    
    PL/SQL Block​
  • 위에서 설명한 Procedure와 거의 동일하다. 차이점은 반드시 리턴 값이 존재하므로 RETURN을 사용한다. 그리고 리턴 값은 Blobk문에서 RETURN을 사용하여 리턴한다.
  • Function은 일반적으로 알고 있는 오라클 함수처럼 사용할 수 있다.
  • Procedure와 Function은 자율적으로 트랜잭션을 처리할 수 있다.

 

Package(패키지)

  • Package는 연광성이 있는 변수, 상수, PL/SQL Block 등을 논리적으로 하나의 그룹으로 만드는 특수한 형태이다. Package를 만들고 그 안에 여러 가지 항목을 작성하여 사용하면 된다.
  • Package는 인터페이스와 같이 사용방법을 기술한 선언부(스펙)와 실제 구현이 담겨 있는 바디부로 나뉜다.
  • 아래는 데이터베이스에 Package를 생성하는 방법이다.
    -- 선언부
    CREATE PACKAGE pack_test
    IS
      상수명 CONSTANT 데이터타입;
      예외명 EXCEPTION;
      변수명 데이터타입;
    
      PROCEDURE 프로시저명칭 (변수타입들..);
      FUNCTION 함수명칭 (변수타입들..) RETURN 반환타입;
    END pack_test;
    
    
    -- 바디부
    CREATE PACKAGE BODY pack_test
    IS
      상수명 CONSTANT 데이터타입;
      예외명 EXCEPTION;
      변수명 데이터타입;
    
      PROCEDURE 프로시저명칭 -- 프로시저 생성구문
          ...
      END 프로시저명칭;
    
      FUNCTION 함수명칭 -- 함수 생성구문
          ...
      END 함수명칭;
    END pack_test;
     
  • 호출하는 방법은 패키지명. 명칭처럼 사용하면 된다. ex) pack_test.getList;

 

Trigger(트리거)

  • Trigger는 특정 이벤트가 발생할 때 자동으로 수행되는 Block을 의미한다. 따라서 트리거는 직접적으로 실행할 수는 없다.
  • 자동으로 수행하게 하여 데이터의 무결성과 일관성을 유지하도록 한다.
  • 트리거는 TCL을 이용하여 트랜잭션을 제어할 수 없다.
  • 특정 이벤트가 발생하기 전(BEFORE EVENT), 특정 이벤트가 발생 후(AFTER EVENT) 등을 설정할 수 있다.
  • 이벤트에는 INSERT, UPDATE, DELETE, DDL, DROP, TRUNCATE 등을 사용할 수 있다.
  • DELETE ON TRIGGER 의 경우,  :OLD 는 삭제 전 데이터를 , :NEW 는 삭제 후 데이터를 나타낸다.
  • UPDATE TRIGGER 에서, :OLD 에는 수정 전, :NEW 에는 수정 후 값이 들어간다.
  • 아래는 Trigger를 생성하는 방법이다.
    CREATE [OR REPLACE] TRIGGER trigger_name
    BEFORE | AFTER
         trigger_event ON table_name
         [FOR EACH ROW]
         [WHEN (condition)]
         PL/SQL block
         
         
    CREATE TRIGGER 인서트제약트리거
    BEFORE UPDATE ON 사원연봉
    BEGIN
    	IF (:NEW.입력자) NOT IN ('대표') THEN
    		RAISE_APPLICATION_ERROR(-9999,'대표만 수정할 수 있습니다.');
    	END IF;
    END

 

CURSOR(커서)

커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있다. 

 

묵시적 커서는 바로 이전에 실행된 SQL문에 대하여 커서를 가지게 되어 특별한 키워드를 이용해 결과 집합에 대한 정보를 얻을 수 있다.

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT

 

명시적 커서는 직접 작성한 SQL 문장을 커서로 만들어 사용하는 방법이다. 순서는 다음과 같다.

  1. 커서 선언 : 사용할 커서에 이름을 부여하고 SQL문을 작성한다.
    CURSOR 커서명[(매개변수들)]
    IS
    SQL문장;
  2. 커서 열기 : 선언한 커서를 OPEN한다. 매개변수가 존재할 경우 매개변수를 함께 전달한다.
    OPEN 커서명[(매개변수들)];​
  3. 커서 패치 : 해당 SQL문을 실행하여 원하는 작업을 한다.
    -- 일반적으로 패치한 데이터를 모두 접근하기 위해 루프를 사용한다.
    LOOP
    FETCH 커서명 INTO 변수들; -- 수행한 SQL문에 데이터를 변수에 저장한다.
    EXIT WHEN 커서명%NOTFOUND; -- 더 이상 데이터가 없을 경우 루프를 종료한다.
    END LOOP;​
  4. 커서 닫기 : 커서 사용을 완료하면 닫아준다.
    CLOSE 커서명;​
반응형
반응형

윈도우 함수

SELECT WINDOW_FUNCTION([컬럼]) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;

 

윈도우 함수는 로우 간의 관계를 구할 수 있도록 만들어 놓은 함수이다. 로우 간의 순위, 집계, 순서, 비율 등을 구할 수 있는 함수를 제공한다.  인자가 있는 함수도 있고 없는 함수도 있다

 

 

ORDER BY 절을 사용하여 어떤 컬럼 기준으로 순위를 정할지 지정할 수 있다. 순위 함수가 아닐 경우에는 정렬로 사용한다.  아래는 ORDER BY를 사용하여 모든 로우에 대해서 매출액 기준으로 순위를 구하는 예시이다.

SELECT 부서번호, 사원번호, 사원명, 매출액
	,RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위;

 

 

모든 로우간의 관계를 구할 수도 있으며, PARTITION BY 절을 사용하여 특정 그룹 조건을 기준으로도 관계를 구할 수 있다. 아래는 PARTITION BY를 사용하여 같은 부서 안에서 매출액 기준으로 순위를 구하는 예시이다.

SELECT 부서번호, 사원번호, 사원명, 매출액
	,RANK() OVER (PARTITION BY 부서번호 ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 부서번호, 순위;

 

WINDOWING 절을 사용하여 윈도우 함수에 포함시킬 로우의 조건(범위)을 지정할 수 있다. 크게 ROWSRANGE를 사용할 수 있다.

 

ROWS : 현재 ROW 위치에서 물리적인 범위

RANGE : 현재 ROW 값을 기준으로 논리적인 범위

 

예를 들어 설명하기 위해 먼저 UNBOUNDED PRECEDING이라는 것을 가지고 ROWSRANGE를 비교하여 설명한다.

  • ROWS UNBOUNDED PRECEDING : ROWS UNBOUNDED PRECEDING은 현재 행과 현재 행 위에 있는 모든 행들을 함수에 사용하라는 의미이다. 아래 예시는 부서 번호 기준으로 행이 출력되며 아래로 내려가면서 매출액을 계속 SUM 하여 매출액 합계를 구하는 쿼리이다. 즉, 첫 번째 행은 자신의 매출액만 표현되고, 맨 아래 행은 모든 매출액의 합이 출력된다.
    SELECT 부서번호, 사원명, 매출액
    	,SUM(매출액) OVER (ORDER BY 부서번호 ROWS UNBOUNDED PRECEDING) AS 매출액합계
    FROM 사원;

    부서번호 사원명 매출액 매출액 합계
    1 A 500 500
    1 B 1000 1500
    2 C 700 2200
    3 D 300 2500
    3 E 600 3100
    3 F 900 4000
  • RANGE UNBOUNDED PRECEDING : RANGE UNBOUNDED PRECEDING은 현재 행의 값을 기준으로 이하의 값을 함수에 사용하라는 의미이다. 아래 예시는 부서 그룹내에서 자신의 매출액보다 작은 매출액을 모두 SUM하여 매출액 합계를 구하는 쿼리이다. 즉, 자신의 값 + 자신보다 작은 모든값의 합이 출력된다.
    SELECT 부서번호, 사원명, 매출액
    	,SUM(매출액) OVER (PARTITION BY 부서번호 ORDER BY 매출액 RANGE UNBOUNDED PRECEDING) AS 매출액합계
    FROM 사원;
    부서 번호 사원명 매출액 매출액 합계
    1 A 500 500
    1 B 1000 1500
    2 C 700 700
    3 D 300 600
    3 E 300 600
    3 F 900 900

 

 

UNBOUNDED PRECEDING 이외에 WINDOWING 절은 아래와 같이 다양하게 존재한다.

  • ROWS 숫자(N) PRECEDING : 현재 행과 바로 위의 행 N 개의 값을 함수에 사용하라는 의미이다. 아래 예시는 부서 번호 기준으로 행이 출력되며 첫 번째 행은 자신의 매출액만 표현되고, 두 번째 행부터는 자신 행의 바로 위의 행의 매출액과 자신의 매출액을 더한 매출액이 표현된다.
    SELECT 부서번호, 사원명, 매출액
    	,SUM(매출액) OVER (ORDER BY 부서번호 ROWS 1 PRECEDING) AS 매출액합계
    FROM 사원;
    부서 번호 사원명 매출액 매출액 합계
    1 A 500 500 (0 + 500)
    1 B 1000 1500 (500 + 100)
    2 C 700 1700 (1000 + 700)
    3 D 300 1000 (700 + 300)
    3 E 600 900 (300 + 600)
    3 F 900 1500 (600 + 900)

 

 

위에서 설명한 WINDOWING 절은 사실 BETWEEN이라는 구문이 생략되었다. 위에서 설명한 절을 BETWEEN으로 표현하면 다음과 같다.

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
  • ROWS BETWEEN 숫자(N) PRECEDING AND CURRENT ROW

 

 

BETWEEN은 윈도우의 시작과 끝 위치를 지정하는 것으로 생략 가능하다. 생략한다면 위와 같이 CURRENT ROW, 즉 현재 행을 끝 위치로 지정하는 것이다. 따라서 위에 결과를 보면 항상 현재 행에서 멈춘 것을 확인할 수 있다.

 

 

현재 행이 아닌 맨 마지막 행을 끝 위치로 표현하고 싶다면 AND 뒤에 UNBOUNDED FOLLOWING을 사용하면 된다.

 

 

자신보다 위에 있는 N개만큼을 함수에 포함하는 숫자(N) PRECEDING 처럼 FOLLOWING도 숫자(N) FOLLOWING 과 같이 사용하여 현재 행을 기준으로 끝 위치를 N개만큼 지정할 수 있다.

 

 

 

윈도우 순위 함수

RANK

SELECT 사원번호, 사원명, 매출액
	,RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위
사원번호 사원명 매출액 순위
364 A 1500 1
4267 B 1500 1
346 C 700 3
333 D 500 4

 

  • ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
  • 동일한 값에 대해서는 동일한 순위를 부여하고, 다음 순위는 비워둔다.

 

 

DENSE_RANK

SELECT 사원번호, 사원명, 매출액
	,DENSE_RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위
사원번호 사원명 매출액 순위
364 A 1500 1
4267 B 1500 1
346 C 700 2
333 D 500 3

 

  • ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
  • 동일한 값에 대해서는 동일한 순위를 부여하고, 다음 순위는 RANK 함수와 다르게 비워두지 않는다.

 

ROW_NUMBER

SELECT 사원번호, 사원명, 매출액
	,ROW_NUMBER() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위
사원번호 사원명 매출액 순위
364 A 1500 1
4267 B 1500 2
346 C 700 3
333 D 500 4

 

  • ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
  • 동일한 값이라도 고유한 순위를 부여한다. 오라클의 경우 ROWID가 작은 행이 우선순위가 높다.
  • 만약 동일한 값에 대해서 순서를 관리하고 싶다면 ORDER BY절에 다른 조건의 컬럼을 추가해야 한다.

 

 

윈도우 집계 함수

윈도우 집계 함수에는 SUM, MAX, MIN, AVG, COUNT가 있으며 특별하게 주의해야 할 점이 없으므로 설명은 생략한다.

 

 

윈도우 순서 함수

윈도우 순서 함수에는 FIRST_VALUE, LAST_VALUE, LAG, LEAD가 있다. FIRST_VALUE와 LAST_VALUE는 각각 집계 함수의 MAX와 MIN 함수와 동일한 결과를 출력한다.

 

 

LAG를 이용해 파티션 별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 반대로 LEAD이후 몇 번째 행의 값을 가져올 수 있다.

 

 

LAG

SELECT 사원번호, 사원명, 매출액
	,LAG(매출액, 1, 9999) OVER (ORDER BY 매출액 DESC) AS 내_바로_위_매출액
FROM 사원;

 

사원번호 사원명 매출액 내_바로_위_매출액
364 A 1500 9999
4267 B 1500 1500
346 C 700 700
333 D 500 500
  • LAG의 두 번째 인자는 이전 몇 번째 행의 값을 가져올지를 정하는 인수이며, 세 번째 인자는 이전 몇 번째 행의 값을 가져올 수 없거나 NULL일 경우에 세팅할 값을 설정한다.

 

LEAD

  • LAG와는 반대로 이후 몇 번째 행의 값을 가져올 수 있다. 함수 사용 방법은 LAG와 동일하다.

 

윈도우 비율 함수

CUME_DIST

SELECT 사원번호, 사원명, 매출액
	,CUME_DIST() OVER (ORDER BY 매출액 DESC) AS 누적분포율
FROM 사원;
사원번호 사원명 매출액 누적분포율
364 A 1500 1
( 4 / 4)
4267 B 1500
(4 / 4)
346 C 700 0.5
(2/ 4)
333 D 500 0.25
(1/ 4)
  • CUME_DIST는 누적 분포를 계산하는 것이다.
  • 즉 윈도우 중에 자신의 위치가 어느 정도인지 0~1 사이로 표현한다.
  • NULL 값도 계산에 포함한다. 그리고 가능한 가장 낮은 값으로 NULL을 취급한다.

 

PERCENT_RANK

SELECT 사원번호, 사원명, 매출액
	,PERCENT_RANK() OVER (ORDER BY 매출액 DESC) AS 백분율순위
FROM 사원;
사원번호 사원명 매출액 백분율순위
364 A 1500 1
( (4-1) / (4-1) ) 
4267 B 1500 1
( (4-1) / (4-1) )
346 C 700 0.333
( (2-1) / (4-1))
333 D 500 0
( (1-1) / (4-1))
  • 백분율의 순위를 계산한다.

 

NTILE

SELECT 사원번호, 사원명, 매출액
	,NTILE(2) OVER (ORDER BY 매출액 DESC) AS 타일번호
FROM 사원;
사원번호 사원명 매출액 타일번호
364 A 1500 1
4267 B 1500 1
346 C 700 2
333 D 500 2
  • 값을 N개의 타일(등분)하여 그룹핑 번호를 반환한다.

 

 

 

AWS 문서 참고 : https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/c_Window_functions.html

반응형
반응형

다중행 함수

  • 다중행 함수는 여러 행(로우)를 입력으로 하여 처리하는 함수이며, 단일 값을 반환한다.
  • 다중행 함수에는 그룹함수, 집계함수, 윈도우 함수 등이 있다.

 

그룹 함수

  • 여러 로우의 데이터를 가지고 한 번에 처리하여 결과 값 1건을 반환하는 함수이다.
  • 1건으로 만들기 때문에 복수행 함수 또는 집계 함수라고도 표현한다.
  • 그룹 함수는 일반적으로 NULL 값을 제외하여 처리한다.
  • 그룹 함수는 WHERE절에 사용할 수 없다.

 

기본 함수

COUNT(*)

  • NULL 값을 포함한 행의 수를 출력한다.

COUNT(표현식)

  • NULL 값을 제외한 행의 수를 출력한다.

SUM(표현식)

  • 표현식의 NULL 값을 제외한 합계를 출력한다.

AVG(표현식)

  • 표현식의 NULL 값을 제외한 평균을 출력한다.
  • NULL값을 0으로 하여 전체 평균을 구하고 싶다면 NVL 함수를 사용한다. 예시는 다음과 같다.
  • AVG(NVL(score, 0))

MAX(표현식) / MIN(표현식)

  • 표현식의 최대값, 최솟값을 출력한다.
  • 문자, 날짜 타입도 사용 가능하다.
  • 날짜의 최대값은 현재랑 가장 가까운 날이다.

STDDEV(표현식) / VARIAN(표현식)

  • 표현식의 표준편차, 분산을 출력한다.

 

GROUP BY

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY 칼럼
  • GROUP BY는 SELECT에 그룹 함수(= 복수행 함수 = 집계 함수)을 사용할 때, 특정 조건으로 그룹을 만들어 그룹별로 함수를 사용하여 결과를 출력한다.
  • GROUP BY을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
  • GROUP BY는 SELECT와 달리 ALIAS를 사용할 수 없다.

 

HAVING

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼] HAVING 그룹조건식
  • 그룹 함수는 WHERE절에 사용할 수 없다. WHERE절 대신 HAVING 절을 사용한다.
  • HAVING은 일반적으로 GROUP BY 뒤에 표현하지만, 앞에 표현해도 된다.
  • GROUP BY가 없어도 HAVING은 사용할 수 있다.

 

집계함수 : ROLLUP, CUBE, GROUPING SETS

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY ROLLUP(칼럼1, 칼럼2)

-- ROLLUP을 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1, 칼럼2)
UNION ALL
GROUP BY (칼럼1)
UNIN ALL
모든 집합 그룹 결과


SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY CUBE(칼럼1, 칼럼2)

-- CUBE를 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1, 칼럼2)
UNION ALL
GROUP BY (칼럼1)
UNION ALL
GROUP BY (칼럼2)
UNIN ALL
모든 집합 그룹 결과


SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY GROUPING SETS(칼럼1, 칼럼2)

-- GROUPING SETS을 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1)
UNION ALL
GROUP BY (칼럼2)
  • ROLLUP은 주어진 그룹핑의 소계를 표현한다.
  • CUBE는 소계 + 총계를 표현한다. 즉 ROLLUP 결과에 총계가 추가된다.
  • 예제는 아래와 같다.
SELECT 부서, 직급, COUNT(*) FROM 직원 GROUP BY CUBE(부서, 직급)
부서 직급 COUNT(*) 설명
    30 전체 총계
  사원 15 직급별 총계

  대리 10
  과장 5
001   5
소계

  사원 4
  과장 1
002   10

소계

  사원 6
  대리 2
  과장 2
003   15
소계

  사원 5
  대리 8
  과장 2

 

GROUPING

GROUPING은 해당 로우 데이터가 ROLLUP, CUBE 또는 GROUPING SETS을 이용할 때 만들어진 로우인지, 아니면 일반 로우의 데이터인지를 판별할 수 있는 함수이다.

즉, 해당 컬럼의 데이터가 Null인 경우에는 특수하게 생성된 로우이므로 1을 반환하고, 해당 컬럼의 데이터가 Null이 아닌 경우에는 기존 로우의 데이터를 이용한 것이므로 0을 반환한다.

SELECT CASE WHEN GROUPING(부서) = 0 THEN 부서 ELSE '합계' END AS 부서
, 직급, COUNT(*) FROM 직원 GROUP BY CUBE(부서, 직급)
부서 직급 COUNT(*) 설명
합계   30 전체 총계
합계 사원 15 직급별 총계

합계 대리 10
합계 과장 5
001   5
소계

합계 사원 4
합계 과장 1
002   10

소계

합계 사원 6
합계 대리 2
합계 과장 2
003   15
소계

합계 사원 5
합계 대리 8
합계 과장 2

 

반응형
반응형

단일행 함수

  • 한 번에 한 로우씩 처리하여  한 로우에 결과를 변경하는 함수이다.
  • 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET 절에서 사용 가능하다.
  • 단일행 함수는 1:M 조인이어도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용가 가능하다.
  • 단일행 함수는 입력되는 값에 따라 문자 함수, 숫자 함수, 날짜 함수, 변환 함수, 일반 함수로 구분된다.

 

문자 함수

UPPER(컬럼)

  • 모든 문자를 대문자로 변경(UPPER)

LOWER(컬럼)

  • 모든 문자를 소문자로 변경(LOWER)

INITCAP(컬럼)

  • 첫(INIT) 글자는 대문자(CAP), 나머지는 소문자로 변경

 

 

LPAD(컬럼, 자릿수, '빈자리를 채울 문자')

  • 두 번째 인자인 자리 수만큼 문자열의 부족한 자리를 왼쪽(L)에서부터 세 번째 인자로 채운다(PAD).

RPAD(컬럼, 자릿수, '빈자리를 채울 문자')

  • 두 번째 인자인 자리 수만큼 문자열의 부족한 자리를 오른쪽(R)에서부터 세 번째 인자로 채운다(PAD).

 

 

LTRIM(컬럼, '제거할 문자열')

  • 문자열의 왼쪽(L)에 두 번째 인자인 제거할 문자열이 존재하면 제거(TRIM)한다. '제거할 문자'는 옵션으로 디폴트 값은 공백이다.
  • '제거할 문자'를 동일한 값으로 여러 개 입력하여도 한 문자로 판단한다.
  • 예를 들어 LTRIM(컬럼, '0') 이나 LTRIM(컬럼 '0000000')이나 동일하다.

RTRIM(컬럼, '제거할 문자열')

  • 문자열의 오른쪽(R)에 두 번째 인자인 제거할 문자열이 존재하면 제거(TRIM)한다. 나머지 특징은 LTRIM과 동일하다.

TRIM( [BOTH | LEADING | TRAILING] '제거할 문자' FROM '문자열')

  • 문자열의 양쪽(BOTH), 앞쪽(LEADING) 또는 뒤쪽(TRAILING)에 제거할 문자가 존재하면 제거(TRIM)한다.
  • 문자열만 입력한다면 양쪽의 공백을 제거한다.
  • BOTH, LEADING, TRAILING 셋 중 아무것도 입력하지 않는다면, 디폴트 값은 BOTH이다.
  • '제거할 문자'는 '문자열'이 아니고 '문자'이다. 즉 char 하나만 입력받는다. 문자열을 입력하게 되면 에러가 발생한다.

 

 

SUBSTR(컬럼, 시작 위치, 길이)

  • 문자열에서 두 번째 인자인 시작 위치에서 세 번째 인자인 길이만큼 문자열을 잘라낼 때(SUBSTR) 사용하는 함수이다.
  • 시작 위치의 인덱스는 1부터 시작한다.
  • 시작 위치는 음수도 될 수 있다. 시작 위치가 -1이라면 맨 끝 문자가 시작 위치이다. 그리고 스캔하는 방향을 항상 오른쪽으로 한다. 아래 설명할 INSTR 함수랑 다르다.

INSTR(컬럼, 찾는 글자, 시작 위치, 횟수)

  • 문자열에서 특정 문자의 위치를 찾을 때(INSTR) 사용하는 함수이다. 찾을 때의 시작 위치를 입력받으며, 몇 번째로 해당 글자를 만났을 때 위치를 반환할지도 입력받는다.
  • 시작 위치와 횟수는 입력을 받지 않을 경우, 각각 디폴트 값은 1, 1이다.
  • 찾는 문자가 없으면 0을 반환한다.
  • 시작 위치는 음수도 될 수 있다. 시작 위치가 -1이라면 맨 끝 문자가 시작 위치이다. 그리고 스캔하는 방향은 항상 왼쪽이다. 위에 설명한 SUBSTR 함수랑 다르다.

REPLACE(컬럼, '찾을 문자' '변환 문자')

  • 문자열에서 특정 문자를 찾아 변환한다(REPLACE).

 

숫자 함수

ABS(숫자)

  • 절댓값을 구한다.

MOD(숫자, 나눌숫자)

  • 나머지를 구한다. 숫자 % 나눌숫자

SIGN(숫자)

  • 양수이면 1, 음수이면 -1, 0이면 0을 반환하는 함수이다.

 

 

ROUND(숫자, 자릿수)

  • 반올림한다.
  • 자릿수는 음수도 입력할 수 있다. 음수를 입력하면 소수점이 아닌 정수형 자릿수를 나타낸다.

TRUNC(숫자, 자릿수)

  • 내림한다.
  • 자릿수는 음수도 입력할 수 있다. 음수를 입력하면 소수점이 아닌 정수형 자릿수를 나타낸다.

CEIL(숫자)

  • 주어진 숫자와 가장 가까우면서 큰 정수를 구한다.
  • 소수점을 다 지우고 +1 하면 된다.

FLOOR(숫자)

  • 주어진 숫자와 가장 가까우면서 작은 정수를 구한다.
  • 소수점을 다 지우면 된다.

 

 

POWER(숫자, 승)

  • 숫자^승 값을 반환한다.

EXP(숫자)

  • e^숫자 값을 반환한다.

 

 

LOG(밑, 진수)

  • 로그를 구한다.

LN(진수)

  • 밑이 e인 로그를 구한다.

 

 

SIN(라디안), COS(라디안), TAN라디안)

  • 삼각함수를 구한다.

 

 

CHR(숫자)

  • 숫자와 대응되는 아스키코드의 문자를 반환한다.

 

 

날짜 함수

SYSDATE

  • 현재의 날짜와 시간을 출력하는 함수이다.

 

 

EXTRACT( [YEAR | MONTH | DAY | HOUR | MINUTE | SECOND] FROM 날짜 )

  • 입력된 날짜에서 추출하고자 하는 정보를 반환하는 함수
  • 변환 함수에서 설명할 TO_CHAR와 비슷하다.

 

 

ADD_MONTHS(날짜, 더할 숫자)

  • 입력된 날짜에 숫자만큼의 달을 더하는 함수이다.

MONTHS_BETWEEN(날짜1, 날짜2)

  • 두 날짜 사이의 개월 수를 출력하는 함수이다.
  • 날짜 1이 더 커야지 결과 값은 양수로 표현된다.

 

변환 함수

TO_CHAR(숫자 또는 날짜, 형식)

  • 숫자 또는 날짜의 값을 원하는 형식의 문자열로 변환하는 함수이다.
  • 9는 자리를 차지한다는 의미이다.
    TO_CHAR(1234,'99999') -- ' 1234'
    TO_CHAR(1234,'9999.99') -- '1234.00'​
  • 0은 해당 자리가 비어있다면 0으로 표현하라는 의미이다.
  • TO_CHAR(1234,'09999') -- '01234'​​
  • 날짜 함수인 EXTRACT(YEAR FROM SYSDATE)는 아래와 동일하다.
    TO_CHAR(SYSDATE, YYYY)​

 

 

TO_NUMBER(문자)

  • 문자를 숫자로 변환하는 함수이다.
  • 숫자로 변환할 수 없을 경우 에러가 발생한다.

 

 

TO_DATE(문자, 날짜 형식)

  • 문자를 날짜로 변환하는 함수이다.
  • 날짜 형식을 입력하지 않으면 입력받은 날짜 문자 형식대로 변환된다.

 

 

CAST(변환 대상 AS 데이터 타입)

  • 변환 대상을 특정한 데이터 타입으로 변환하는 함수이다.

 

 

일반 함수

NVL(컬럼, 변환 값)

  • 첫 번째 인자의 값이 NULL일 경우, 두 번째 값으로 표현한다.

NULLIF(표현식1, 표현식2)

  • 첫 번째 인자와 두 번째 인자의 값이 같다면 NULL을 반환한다. 만약 다르다면 첫 번째 인자를 반환한다.

COALESCE(표현식1, 표현식2, ... , 표현식N)

  • 입력받은 인자를 앞에서부터 확인하면서 NULL이 아니라면 해당 인자를 반환한다. 만약 모두 NULL이라면 NULL을 반환한다.

 

 

DECODE(표현식, 비교대상1, 반환값1, 비교대상2, 반환값2, ... , 비교대상N, 반환값N, 디폴트 값)

  • 첫 번째 인자를 앞에서부터 비교대상과 비교하고 같다면 해당 반환 값을 반환한다. 만약 모든 비교대상과 다르다면 맨 마지막 인자인 디폴트 값을 반환한다.
  • 디폴트 값은 입력하지 않으면 NULL이다.

 

 

CASE 표현식

      WHEN 비교대상(조건문)1 THEN 반환값1

      WHEN 비교대상(조건문)2 THEN 반환값2

                    ....

      WHEN 비교대상(조건문)N THEN 반환값N

      ELSE 디폴트값

END

  • 표현식을 앞에서부터 비교대상과 비교하고 같다면 해당 반환 값을 반환한다. 만약 모든 비교대상과 다르다면 맨 마지막 인자인 디폴트 값을 반환한다.
  • 비교 값만 입력할 경우 DECODE와 동일하게 동작한다.
  • BETWEEN 10 AND 20과 같이 조건문을 입력할 수도 있다.

 

반응형

+ Recent posts