[Oracle] 기초 문법 정리

 

 

 

select distinct column as col from t1.table tbl;

 

 

select 문법으로 테이블 또는 스키마의 테이블에서 열들을 조회할 수 있다. (schema.table.column 구조를 가진다)

distinct, unique 를 함께 사용해 중복된 조회 시 나오는 결과에서 중복된 column을 제거할 수 있고, as 를 사용해 조회하는 열의 별칭을 설정할 수 있다. (예시에서는 테이블에도 별칭을 설정했다)

 

별칭을 적절히 사용해 쿼리의 가독성을 높이자.

 

 

 

select 'literal' as lit, col from table SAMPLE (20);
SAMPLE [BLOCK] (sample_percent) [SEED (seed_value)]

 

 

SAMPLE 문법을 사용해 테이블의 일부 값을 추출해서 조회할 수 있다.

BLOCK으로 블록 샘플링 또는 로우 샘플링을 지정할 수 있고, sample_percent로 조회할 비율을 설정한다.

 

문자 리터럴을 사용하면 전체 행에서 동일한 값을 반환한다.

 

 

 

select DATE '2023-03-25' as d1, TO_DATE('2023-03-25 01:01:01', 'YYYY-MM-DD HH24:MI:SS') as d2 from table;

 

DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE 같은 날짜 리터럴을 사용해 날짜 형식으로 값을 다룰 수 있다.

날짜를 지정하고 쿼리를 날릴 때 자주 사용된다.

 

 

select a,
	case b when 10 then 1 when 20 then 2 else 3 end as aa 
    from tbl;

 

 

CASE 문법을 사용해 if-else 논리를 구현할 수 있다.

else를 작성하지 않아도 되지만 해당되는 값이 없으면 null로 채워진다.

 

 

 

select CHR (67) from tbl; /* C */
select ASCII ('C') from tbl; /* 67 */
select LOWER ('ABC') from tbl; /* abc */
select INITCAP ('abc') from tbl; /* Abc */
select LPAD ('AB', 1) from tbl; /* A */
select RPAD ('AB', 1) from tbl; /* A */

 

CHR 함수는 해당하는 숫자에 대한 유니코드를 반환해준다. 키보드로 입력이 불가능한 문자를 다룰 때 사용한다.

LPAD 함수는 해당 문자열을 좌측으로 n만큼 늘린다. 문자열의 형식을 맞출 때 사용한다.

 

이 외에도 TRIM, SUBSTR, REPLACE 등 자바에서 문자열을 다룰 때 사용하는 여러 기능들을 SQL에서도 동일하게 제공하고 있으니 필요에 따라 찾아서 사용하자.

 

 

ABS (n)
SIGN (n)
ROUND (n1, [n2])
TRUNK (n1, [n2])

 

통계를 산출할 때 자주 사용되는 함수이다.

기능은 직관적으로 예상할 수 있다. 절댓값, 부호, 반올림, 내림 에서 사용된다.

 

이 외에도 CEIL, FLOOR, MOD, POWER 등 여러 함수를 제공하니 역시 필요할 때 찾아서 사용하자. 

 

 

select NEXT_DAY (DATE '2023-03-25', '월') from tbl;
select ADD_MONTHS (DATE '2023-03-25', 1) from tbl;
select MONTHS_BETWEEN (DATE '2023-03-25', DATE '2023-05-26') from tbl;

 

날짜 관련 함수를 사용해 쿼리를 편하게 작성할 수 있다.

 

NEXT_DAY 함수는 date 이후 char에 지정한 요일에 가장 가까운 날짜 값을 반환한다.

이 때 요일은 월 - 1 화 - 2 형식으로 사용하는 편이 합리적이다.

ADD_MONTHS 함수는 DATE에서 개월 수를 가감한 날짜 값을 반환한다. 해당 월의 마지막 날인 경우 월말일로 설정된다.

MONTHS_BETWEEN 함수는 두 날짜 사이의 개월 수를 반환한다.

 

이 외에도 EXTRACT, ROUND, TRUNK 등 숫자를 다룰 때 사용하던 함수를 날짜에서도 사용할 수 있으니 필요에 따라 사용하자.

 

 

TO_CHAR (n, [format])
TO_CHAR (1234.5, '99G999G990D00') /* 1234.50 */

 

숫자나 날짜를 문자 값으로 변환할 때 사용한다.

G와 D를 포맷 요소로 사용해 구분자와 소숫점을 명시할 수 있다. 숫자를 시각화할 때 사용된다.

 

문자와 숫자를 함수를 사용해 다룰 때는 format을 적절하게 지정해 주는 부분이 중요하다.

적절하게 지정하지 않은 경우 데이터베이스 엔진이 해석할 수 없게 되고 에러를 뱉는다.

 

 

CAST (expression as type [format])
VALIDATE_CONVERSION (expression as type [format])

 

 

CAST 함수를 사용해 expression을 type에 지정한 데이터 타입으로 변환할 수 있다.

VALIDATE_CONVERSION 함수를 사용해 데이터 타입의 변환이 가능한지를 확인한다. 

 

 

NVL (1, 2)
NVL2 (1, 2, 3)
COALESCE (NULL, 1, 2)
NULLIF (ex1, ex2)

 

위의 함수들은 NULL 관련 함수이다.

NVL : 1이 NULL 이면 2를, 아니면 1을 반환한다.

NVL2 : 삼항연산자이다. 1이 NULL이면 3 아니면 2를 반환한다.

COALESCE : NULL이 아닌 첫 번째 값을 반환한다.

NULLIF : ex1과 ex2가 다르면 ex1, 같으면 NULL을 반환한다.

 

응용해서 특정 값이 NULL이면 ~~를, NULL이 아니면 ~~를 하도록 쿼리를 작성할 수 있다.

 

 

DECODE (expression, search, res, [default])
DUMP (expression, [format])

 

DECODE 함수는 expression이 search와 일치할 때 res를 반환하고 일치하지 않으면 default를 반환한다.

DUMP 함수는 expression의 데이터 타입과 바이트 길이를 반환한다. (format 지정 가능)

 

데이터베이스의 백업과 복구에 사용된다.

 

 

SYS_CONTEXT ('namespace', 'parameter')

 

오라클에서는 데이터베이스의 인스턴스, 세션, 서버에 대한 정보에 접근할 때 SYS_CONTEXT 함수를 사용한다.

namespace는 USERENV로 사용하고 parameter에 접근하려는 값을 입력하는 방식으로 사용한다.

 

 

select a, TO_CHAR (b, "999,999") as b from emp
	where tbl
    order by tbl.b;

 

order by 문법으로 조회되는 결과를 특정 기준으로 정렬해서 반환받을 수 있다.

기본적으로는 ASC (오름차순) 을 기준으로 정렬되고, DESC / NULLS FIRST / NULLS LAST 등 여러 속성으로 정렬할 수 있다.

 

위의 예시에서는 tbl.b를 기준으로 정렬할 때와 b를 기준으로 정렬할 떄의 결과가 서로 다르다.

별칭으로 사용 시 문자 값으로 정렬되고, 별칭이 아니라 열으로 결과를 정렬할 경우 열의 데이터타입으로 정렬이 수행된다.

 

order by 부분에 숫자를 넣어주면 select 절에서 입력된 요소들이 순서대로 설정된다.

 

정렬 기준을 여러 개로 설정해서 첫 번째 정렬기준으로 정렬할 수 없는 경우 두 번째 정렬기준으로 정렬하도록 설정할 수 있고, DECODE, 바인드 변수 등과 함께 사용해 정렬 순서를 동적으로 설정할 수 있으니 적절하게 사용하자.

 

 

COUNT (expression)
MIN (expression)
MAX (expression)
SUM (expression)
AVG (expression)

 

집계 함수를 사용해 다중 행을 입력받아서 단일 행을 반환받을 수 있다.

 

COUNT 함수는 입력된 행의 개수를 반환하고, expression에 어떤 요소가 입력되는지에 따라 다르게 동작한다.

* : 전체 행의 개수를 반환한다.

expression : 널이 아닌 expression의 개수를 반환한다.

DISTINCT expression : 널이 아니면서 고유한 expression의 개수를 반환한다.

 

MIN 함수는 직관적으로 이해한 바와 같이 expression의 최솟값을 반환한다. (널 값은 무시한다)

SUM 함수도 마찬가지이다. expression의 합계를 반환한다. (숫자만 가능하다)

AVG 함수는 expression의 평균값을 반환한다.

 

역시 다른 문법들과 함께 사용해서 쿼리를 효과적으로 작성할 수 있다. 

 

이 외에도 표준편차, 분산, 최빈값 등 통계와 관련된 다양한 기능을 사용할 수 있으니, 필요에 따라 찾아서 사용하자.