오라클에서 NULL 값을 치환할 때는 NVL, NVL2, COALESCE 함수를 사용한다. COALESCE 함수는 NVL 함수의 기능이 조금 더 확장된 함수라고 생각하면 된다. NVL 함수와 동일하게 사용할 수도 있지만, 여러 개의 칼럼의 NULL 값을 판별해야 할 경우 NULL이 아닌 첫 번째 칼럼의 값을 반환할 때 유용하게 사용할 수 있다. COALESCE(exp1, exp2, exp3, exp4 ...) 와 같이 함수의 인자의 개수는 동적으로 입력할 수 있다. 기본 사용법 WITH customers AS ( SELECT 1 AS c_id, 'Tammy Bryant' AS c_name, '010-1234-5678' AS tel_no1, '02-345-6789' AS tel_no2, NULL AS te..
오라클에서 조회된 데이터에서 특정 칼럼을 기준으로 하나의 행만 조회해야 하는 경우가 있다. 중복된 칼럼의 데이터에서 그룹별로 최신의 행 하나만 가져오거나, 특정 칼럼으로 정렬하여 최상위 하나의 행만 조회할 때 아래의 2가지 방법을 사용할 수 있다. 아래의 방법은 행 전체를 중복 체크를 하여 제거하는 방법(DISTINCT)이 아니고 특정 칼럼을 기준으로 중복을 제거하는 방법이다. 그룹별로 순번을 지정하여 하나의 행만 추출SELECT empno , ename , job , hiredate FROM ( SELECT empno , ename , job , hiredate , ROW_..
오라클에서 상위 3개, 5개, 10개, 100개 등 n개의 데이터를 뽑기 위해서는 서브 쿼리를 활용하면 쉽게 해결할 수 있다. 서브 쿼리(Inline View)를 사용하여 데이터를 먼저 정렬한 후, 원하는 개수만큼 데이터를 추출하면 된다. 대부분 ROWNUM 키워드를 사용하며, 특수한 경우에 순위 함수(DENSE_RANK)를 사용하여 순위를 부여한 후 순위로 데이터를 추출하기도 한다. 상위 n개의 행(ROW) 추출 방법 SELECT empno , ename , sal FROM ( SELECT empno , ename , sal FROM emp ORDER BY sal DESC ) WHERE ROWNUM
오라클에서 누적 합계를 구하기 위해서는 SUM 함수와 OVER 절을 사용하면 된다. OVER 절을 사용하면 GROUP BY 절을 사용하지 않고도 SELECT 절에서 단독으로 합계를 구할 수 있다. OVER 절 내부의 ORDER BY 절의 칼럼 순서로 누적 합계가 표시되며, 조회된 결과도 해당 칼럼으로 정렬된다. ORDER BY 절에 선언된 칼럼의 값에 따라서 누적 합계 표시 형식이 달라질 수 있으므로 주의해야 한다. 누적 합계 구하기 SELECT empno , ename , job , sal , SUM(sal) OVER(ORDER BY empno) AS sal_sum FROM emp WHERE job IN ('MANAGER', 'SALESMAN') SUM 함수와 OVER 절을 사용하여 순차적으로 누적 합..
오라클에서 KEEP 키워드를 사용하면 행 그룹(GROUP BY) 내에서 최고 또는 최고 순위 행으로 집계를 할 수 있다. 그룹별로 MAX 또는 MIN 수치 값은 쉽게 표시할 수 있지만, MAX에 해당하는 행의 값 중에서 특정 텍스트 값을 같이 표시하기 위해서는 서브 쿼리를 사용하는 방법이 많이 사용된다. 그러나 KEEP 키워드를 사용하면 한 번의 쿼리문으로 최저 또는 최고에 해당하는 행의 값들을 쉽게 가져올 수 있다. KEEP 키워드는 GROUP BY 절 또는 OVER 절과 함께 사용해야 한다. KEEP을 처음 사용한다면 순위 함수(DENSE_RANK)를 알고 있으면 조금 더 쉽게 이해할 수 있다. [Oracle] RANK, DENSE_RANK 순위 함수 사용법 오라클에서 성적, 급여, 매출 등 순위를 ..
오라클에서 OVER 절에 WINDOWING 절을 처음 사용할 때 ROWS와 RANGE가 어떤 차이점이 있는지 많이 헷갈릴 수 있다. 간단히 설명하면 ROWS는 각 행의 위치고, RANGE는 값의 범위라고 생각하면 된다. 아래의 예제를 여러 번 반복해서 보면 많이 어렵지 않게 이해할 수 있을 것이다. 아래의 키워드 의미부터 숙지를 하고 예제를 보면 더욱 이해하기 쉬울 것이다. 키워드 설명 ROWS 물리적인 행 단위 RANGE 논리적인 행 집합 CURRENT ROW 현재 행 UNBOUNDED PRECEDING 첫 번째 행 UNBOUNDED FOLLOWING 마지막 행 [위치] PRECEDING [위치] 이전 행 [위치] FOLLOWING [위치] 다음 행 ROWS와 RANGE 차이점 ROWS : 조회된 데이..
오라클에서 두 날짜의 월 차이를 계산해서, 개월 수를 산출할 때는 MONTHS_BETWEEN 함수를 사용한다. 개월 수 계산은 나이, 근속일수를 계산할 때 자주 사용하며, 개월 수를 산출할 때는 함수의 첫 번째 인자의 날짜에서 두 번째 인자의 날짜를 빼는 방식으로 개월 수가 계산된다. MONTHS_BETWEEN 함수의 첫 번째 인자의 날짜가 크면 양수의 값이 반환되고, 작으면 음수의 값이 반환된다. 상황에 맞게 날짜 인자의 순서를 바꿔서 입력하면 된다. 기본 사용법 SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2021-11-01', 'YYYY-MM-DD')) AS month_diff FROM dual 오늘 날짜(2022-01-19)와 비교 날짜(2021-11-01)의 개월 수 ..
오라클에서 해당 월(MONTH)의 마지막 일자를 구하기 위해서는 LAST_DAY 함수를 사용하면 된다. 월별로 마지막 일자(28일, 29일, 30일, 31일)가 틀리기 때문에 조회를 할 때마다 직접 계산을 하는 건 많이 번거롭다. LAST_DAY 함수를 사용하면 이번 달의 마지막 일자, 특정 일자에 해당하는 월의 마지막 일자를 쉽게 구할 수 있다. 기본 사용법 SELECT LAST_DAY(TO_DATE('2022-01-17', 'YYYY-MM-DD')) FROM dual 특정일자에 해당하는 월의 마지막 일자를 구하는 예제이다. 1월의 마지막 일자 31일 반환된다. 활용 예제 이번 달 시작 일자 종료 일자 구하기 SELECT TRUNC(SYSDATE, 'MM') AS first_day_of_month , ..
오라클에서 조인을 할 때 오라클 조인(Oracle Join)과 안시 조인(ANSI JOIN)을 사용할 수 있다. 오라클 9i 까지는 오라클 조인만 사용할 수 있으며, 오라클 10g부터는 안시 조인을 추가로 사용할 수 있다. 최근 구축되는 시스템은 대부분 안시 조인을 사용하지만, 과거에 구축되어 있는 시스템은 오라클 조인을 많이 사용하고 있기 때문에 오라클 조인 방식도 꼭 알고 있어야 한다. 조인 (INNER JOIN) 아우터 조인 (LEFT OUTER JOIN) 아우터 조인 (RIGHT OUTER JOIN) 크로스 조인 (CROSS JOIN) 풀 아우터 조인 (FULL OUTER JOIN) 조인은 크게 위의 5가지 정도로 분류할 수 있다. 조인 (INNER JOIN)과 아우터 조인 (LEFT OUTER ..
오라클에서 INSERT 할 때 PK가 중복되어서 오류가 발생하는 경우가 많다. 이럴 경우 중복을 무시하거나 중복을 제외하고 INSERT를 하거나, 미리 중복을 체크하여 INSERT가 되지 않도록 해야 한다. ORA-00001 : 유일성 제약조건에 위배됩니다 (unique constraint violated) 아래의 3가지 방법을 사용하여 INSERT 할 때 중복을 제외하고 데이터를 입력하면 오류를 방지할 수 있다. NOT EXISTS 연산자를 사용하여 중복 제외 MERGE 절을 사용하여 중복 제외 PRODEDURE를 사용하여 중복 입력 시 예외처리 NOT EXISTS 연산자를 사용하여 중복 제외 INSERT INTO emp (empno, ename, job, hiredate) SELECT 8000 ,..