🏛️
Oracle DB 치트시트

ROWNUM/OFFSET 페이징, SYSDATE 날짜 연산, 계층형 쿼리(CONNECT BY), MERGE INTO, 인덱스 힌트 및 실무 딕셔너리 레퍼런스
#oracle#sql#database#rdbms#plsql#connect-by#rownum최종 업데이트: 2026-09-26
Oracle DB 치트시트 핵심 명령어 및 실전 가이드

ROWNUM/OFFSET 페이징, SYSDATE 날짜 연산, 계층형 쿼리(CONNECT BY), MERGE INTO, 인덱스 힌트 및 실무 딕셔너리 레퍼런스
## 1. 페이징 쿼리 (Pagination: 12c+ vs ROWNUM)
```sql
-- 1. Oracle 12c 이상 최신 표준 문법 (권장)
SELECT employee_id, first_name, salary
FROM employees
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- 21번째부터 10개 조회

-- 2. Oracle 11g 이하 ROWNUM 3중 인라인 뷰 (레거시 표준)
SELECT *
FROM (
  SELECT a.*, ROWNUM AS rnum
  FROM (
    SELECT employee_id, first_name, salary
    FROM employees
    ORDER BY employee_id
  ) a
  WHERE ROWNUM <= 30 -- 종료 번호
)
WHERE rnum >= 21;    -- 시작 번호
```

## 2. 날짜 & 시간 함수 (Date & Timestamp)
```sql
-- 현재 일시
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

-- 날짜 포맷 변환 (TO_CHAR / TO_DATE)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('2026-09-26 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 날짜 연산 (기본 일 단위)
SELECT SYSDATE + 1          AS 내일       FROM DUAL;
SELECT SYSDATE + 1/24       AS 1시간_후   FROM DUAL;
SELECT SYSDATE + 10/(24*60) AS 10분_후    FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 1) AS 1달_후   FROM DUAL;
SELECT LAST_DAY(SYSDATE)    AS 이번달_말일 FROM DUAL;
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2026-01-01', 'YYYY-MM-DD')) FROM DUAL;
SELECT TRUNC(SYSDATE, 'DD') AS 오늘자정, TRUNC(SYSDATE, 'MM') AS 이번달1일 FROM DUAL;
```

## 3. NULL 처리 & 분기 조건문
```sql
-- 1. NVL: null이면 대체값 반환
SELECT NVL(commission_pct, 0) FROM employees;

-- 2. NVL2(expr, not_null_val, null_val)
SELECT NVL2(commission_pct, '보너스 대상', '기본급') FROM employees;

-- 3. COALESCE: null이 아닌 첫 번째 값 반환
SELECT COALESCE(phone, mobile, email, '연락처 없음') FROM users;

-- 4. DECODE: Oracle 전용 if-else 동등 비교
SELECT DECODE(dept_id, 10, '영업', 20, '개발', '기타') FROM employees;

-- 5. 표준 CASE WHEN 구문
SELECT 
  CASE 
    WHEN salary >= 5000 THEN '고액'
    WHEN salary >= 3000 THEN '중간'
    ELSE '일반'
  END AS sal_grade
FROM employees;
```

## 4. 문자열 조작 & 정규식
```sql
-- 문자열 결합: || 연산자 (또는 CONCAT 함수)
SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- 부분 문자열 & 검색 (1부터 시작)
SELECT SUBSTR('ORACLE', 1, 3) FROM DUAL;  -- 'ORA'
SELECT INSTR('ORACLE', 'A')   FROM DUAL;  -- 3 (위치)

-- 공백/패딩 (LPAD / RPAD)
SELECT LPAD('123', 5, '0')    FROM DUAL;  -- '00123' (고정자릿수 코드 생성 시)

-- 여러 행의 문자열을 한 줄로 합치기 (LISTAGG)
SELECT dept_id, 
       LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS names
FROM employees
GROUP BY dept_id;
```

## 5. 계층형 쿼리 (START WITH & CONNECT BY)
```sql
-- 조직도, 카테고리 트리 등 계층 데이터 조회
SELECT 
  LEVEL,                                     -- 계층 깊이 (1부터 시작)
  LPAD(' ', (LEVEL - 1) * 2) || emp_name AS name, -- 들여쓰기 시각화
  emp_id, 
  manager_id,
  SYS_CONNECT_BY_PATH(emp_name, ' > ') AS path,  -- 루트부터의 경로
  CONNECT_BY_ISLEAF AS is_leaf               -- 최하위 자식 노드 여부 (1 or 0)
FROM employees
START WITH manager_id IS NULL                -- 루트 노드 조건
CONNECT BY PRIOR emp_id = manager_id         -- 부모-자식 연결 관계
ORDER SIBLINGS BY emp_name;                  -- 계층 구조 유지하며 형제간 정렬
```

## 6. 시퀀스 & DUAL & MERGE INTO
```sql
-- 시퀀스 생성 및 사용
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1 NOCACHE;
SELECT user_seq.NEXTVAL FROM DUAL; -- 번호 발급
SELECT user_seq.CURRVAL FROM DUAL; -- 현재 번호

-- MERGE INTO (Upsert: 존재하면 수정, 없으면 등록)
MERGE INTO users u
USING DUAL
ON (u.user_id = :id)
WHEN MATCHED THEN
  UPDATE SET u.name = :name, u.updated_at = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (user_id, name, created_at)
  VALUES (:id, :name, SYSDATE);
```

## 7. 윈도우 & 분석 함수 (Window Functions)
```sql
SELECT 
  emp_id, dept_id, salary,
  -- 1. 순위 매기기
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
  DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rk,
  
  -- 2. 이전행 / 다음행 가져오기
  LAG(salary, 1, 0)  OVER (PARTITION BY dept_id ORDER BY hire_date) AS prev_sal,
  LEAD(salary, 1, 0) OVER (PARTITION BY dept_id ORDER BY hire_date) AS next_sal,

  -- 3. 부서별 누적 합계
  SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS running_sum
FROM employees;
```

## 8. 옵티마이저 힌트 (Hints)
```sql
-- 1. 인덱스 강제 사용
SELECT /*+ INDEX(e idx_emp_salary) */ * 
FROM employees e WHERE salary > 3000;

-- 2. 풀 테이블 스캔 강제
SELECT /*+ FULL(e) */ * 
FROM employees e WHERE dept_id = 10;

-- 3. 조인 순서 및 방식 지정 (Nested Loops / Hash Join)
SELECT /*+ LEADING(d e) USE_NL(e) */ *
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id;

-- 4. 병렬 쿼리 실행 (대용량 배치 작업)
SELECT /*+ PARALLEL(e, 4) */ COUNT(*) FROM large_table e;
```

## 9. 실행계획 확인 (Explain Plan)
```sql
-- 실행계획 생성
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE email = 'SKING';

-- 실행계획 포맷 출력
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 실제 수행 통계(Actual Rows/Cost) 확인 (GATHER_PLAN_STATISTICS 힌트 사용 시)
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
```

## 10. 실무 필수 데이터 딕셔너리 뷰
```sql
-- 1. 현재 접속자 및 락(Lock) 확인 & 세션 킬
SELECT sid, serial#, username, status, machine, program 
FROM v$session WHERE status = 'ACTIVE';

-- 세션 강제 종료 (ALTER SYSTEM KILL SESSION 'sid,serial#')
-- ALTER SYSTEM KILL SESSION '142,35123';

-- 2. 내가 보유한 테이블 / 컬럼 목록
SELECT table_name FROM user_tables ORDER BY table_name;
SELECT column_name, data_type, data_length, nullable 
FROM user_tab_columns WHERE table_name = 'EMPLOYEES';

-- 3. 인덱스 및 제약조건 확인
SELECT index_name, table_name, uniqueness FROM user_indexes;
SELECT constraint_name, constraint_type, table_name FROM user_constraints;
```
💬
Oracle DB 치트시트

의견 및 질문

댓글 수정

댓글 삭제
