오라클에서 누적 합계를 구하기 위해서는 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 ,..
오라클에서는 칼럼의 값을 자동으로 증가시키는 기능이 없기 때문에 INSERT 작업 시 직접 일련번호를 매번 후 데이터를 입력해야 한다. 가장 많이 사용하는 방법은 일련번호 칼럼의 마지막 값을 가져와서 +1 하여 사용한다. 그러나 MAX+1의 단점은 테이블의 트랜잭션이 과도하게 발생하면 중복으로 번호가 채번 되어서 입력 시 오류가 발생할 수 있다. MAX + 1 사용하는 방법 시퀀스 사용하는 방법 테이블의 INSERT 건수가 많이 없거나 프로그램을 잘 설계하였다면 MAX + 1의 방법으로 채번을 해도 큰 문제가 없지만, INSERT가 많이 발생한다면 중복 채번을 예방하기 위해서 시퀀스를 사용할 것을 권장한다. MAX + 1 사용하는 방법 INSERT INTO emp( empno , ename , job..
오라클에서 쿼리문을 작성하다 보면 서브 쿼리(subquery)를 자주 접하게 된다. 서브 쿼리를 처음 접하면 아주 복잡하게 느껴지는데, 기능을 조금만 익히면 쿼리문을 작성할 때 아주 유용하게 사용할 수 있다. 서브 쿼리는 메인 쿼리 내부에 작성하는 쿼리를 뜻하며, 주로 아래의 3가지 서브 쿼리로 분류한다. 서브 쿼리 사용 위치 설명 스칼라 서브 쿼리 SELECT 절 단일 칼럼, 단일 행을 반환 (1개의 값) 인라인 뷰 FROM 절 View와 사용적인 측면에서 동일함 (임시 뷰, 임시 테이블) 중첩 서브 쿼리 WHERE 절, HAVING 절 다중 칼럼 또는 다중 행을 반환 위의 3가지 분류 외에도 연관성 없는 서브 쿼리, 연관성 있는 서브 쿼리로 분류하기도 한다. 연관성 없는 서브 쿼리는 메인 쿼리의 테이..
오라클에서 테이블 목록을 조회할 때는 오라클에서 제공하는 3가지 시스템 뷰를 많이 사용한다. 3가지 시스템 뷰는 all_tables, all_tab_comments, all_objects이며 접근 권한에 따라서 접두어(all)를 바꿔서 사용할 수 있다. 테이블 목록 조회 방법 3가지 SELECT * FROM all_tables all_tables는 권한을 가진 모든 테이블이 조회되며 통계 정보를 쉽게 확인할 수 있다. SELECT * FROM all_tab_comments WHERE table_type = 'TABLE' all_tab_comments는 권한을 가진 모든 테이블 목록과 테이블 설명(COMMENT)을 조회할 때 사용한다. SELECT * FROM all_objects WHERE object_..