오라클에서 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_..
오라클에서 문자열의 특정 구분자를 기준으로 자르기 위해서는 SUBSTR, INSTR 함수를 사용한다. 그러나 오라클 10g부터 REGEXP_SUBSTR 정규식 함수를 사용하면 간편하게 문자열을 구분자로 자를 수 있다. 예제 1 - SUBSTR, INSTR 함수 사용 WITH tab AS ( SELECT '123,456' AS str FROM dual ) SELECT a.str , SUBSTR(a.str, 1, INSTR(a.str, ',') - 1) AS str1 , SUBSTR(a.str, INSTR(a.str, ',') + 1) AS str2 FROM tab a SUBSTR([문자열], [자를시작위치], INSTR([문자열], [구분자])) 문자열 '123,456'의 구분자 쉼표(',')를 기준으로 ..
오라클 쿼리문을 보다 보면 이해하기 힘든 쿼리 형태를 발견하는 경우가 많다. SUM(1) 또한 처음 봤을때는 어떤 의미가 있는지 궁금한 적이 있다. SUM(1)은 잘못된 사용법은 아니며 상황에 따라서 한 두 번은 사용할 수도 있는 문법이다. 그러나 잘못된 사용은 쿼리문을 복잡하게 만들 수 있다. SUM(1)은 COUNT(*)와 동일하게 조회된 행의 건수를 집계할 때 사용한다. 그러나 조회된 데이터가 없을때 반환 값이 다르니 이점을 유의해야 하며 상황에 따라서 필요한 방식을 사용하면 된다. SELECT SUM(1) FROM emp SUM(1)과 COUNT(*) 모두 동일하게 조회된 데이터 건수를 반환한다. 단순히 데이터 건수를 집계할때는 COUNT(*)를 사용할 것을 권장한다. COUNT(*)를 사용하면 ..