Oracle DB 실습 – 시퀀스, 트리거, 프로시저, 함수, 인덱스, 실행계획

 

1. 시퀀스 (SEQUENCE)

기본 개념

시퀀스는 고유한 숫자 값을 자동으로 생성하기 위한 객체다. 보통 PK(기본키) 값 자동 증가에 많이 사용한다.

CREATE SEQUENCE seq_product_id
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE;

• START WITH 1 → 시작값 지정
• INCREMENT BY 1 → 1씩 증가
• CACHE 20 → 메모리에 20개 미리 로드해두고 빠르게 제공
• NOCYCLE → 최댓값 도달 시 다시 1로 돌아가지 않음

 CACHE vs NOCACHE

• CACHE : 속도 ↑ (미리 번호 확보) / 단, DB 비정상 종료 시 일부 번호 건너뛸 수 있음
• NOCACHE : 항상 디스크에서 번호 가져옴 → 안정적이지만 느림

---

2. 트리거 (TRIGGER)

 개념

트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동 실행되는 코드 블록이다.

예제: 상품 가격 변경 시 로그 기록

CREATE OR REPLACE TRIGGER trg_product_price_log
AFTER UPDATE OF unit_price ON cy_product
FOR EACH ROW
BEGIN
    INSERT INTO cy_product_log (product_id, old_price, new_price, changed_at)
    VALUES (:OLD.product_id, :OLD.unit_price, :NEW.unit_price, SYSDATE);
END;

• AFTER UPDATE → 가격 수정 후 실행
• :OLD, :NEW → 변경 전/후 값 참조 가능
• FOR EACH ROW → 각 행 단위로 실행

 실무에서 자주 쓰이는 패턴: 변경 이력 테이블 관리

---

3. 프로시저 (PROCEDURE)

개념

프로시저는 일련의 SQL 작업을 묶어 재사용 가능한 형태로 만든 코드다. 함수와 달리 RETURN 값이 없어도 된다.

 예제: 재고 입고 처리 프로시저

CREATE OR REPLACE PROCEDURE add_stock(
    p_product_id IN NUMBER,
    p_wh_id IN VARCHAR2,
    p_qty IN NUMBER
) AS
BEGIN
    INSERT INTO cy_stock_io (product_id, wh_id, qty, io_type, io_date)
    VALUES (p_product_id, p_wh_id, p_qty, 'IN', SYSDATE);
    
    UPDATE cy_inventory
    SET quantity = quantity + p_qty
    WHERE product_id = p_product_id
      AND wh_id = p_wh_id;
END;

 장점: 재고 추가 로직을 하나로 묶어 호출만 하면 됨

---

4. 함수 (FUNCTION)

개념

함수는 반드시 하나의 값을 RETURN 해야 한다. SELECT 문 안에서도 호출 가능하다.

예제: 금액 계산 함수

CREATE OR REPLACE FUNCTION calc_total_price(
    p_unit_price IN NUMBER,
    p_qty IN NUMBER
) RETURN NUMBER IS
BEGIN
    RETURN p_unit_price * p_qty;
END;

사용 예시:

SELECT product_id, calc_total_price(unit_price, 10) AS total
FROM cy_product;

 함수는 SELECT 문에서 호출 가능하다는 점이 프로시저와 가장 큰 차이

---

5. 인덱스 (INDEX)

 개념

인덱스는 테이블 검색 속도를 높이는 자료구조(B*Tree 구조).

CREATE INDEX idx_product_name ON cy_product(product_name);

• 장점: 검색 속도 향상
• 단점: INSERT/UPDATE/DELETE 시 인덱스 갱신 비용 발생

복합 인덱스

CREATE INDEX idx_product_vendor ON cy_product(vendor_id, product_name);

 자주 쓰는 컬럼 조합에 걸면 효과적

---

6. 실행계획 (Execution Plan)

 EXPLAIN PLAN 예제

EXPLAIN PLAN FOR
SELECT * FROM cy_product WHERE product_name = 'TV';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• TABLE ACCESS FULL → 테이블 전체 스캔
• INDEX RANGE SCAN → 인덱스를 통해 범위 검색
• INDEX UNIQUE SCAN → 고유 인덱스를 통한 빠른 검색

 실행계획을 보고 인덱스 필요 여부, 조인 방식(NESTED LOOP, HASH JOIN 등)을 확인할 수 있다.

---

7. 실무에서 묶어 쓰는 패턴

1. 시퀀스 + 트리거
   → INSERT 시 PK 자동 채번
2. 트리거 + 로그 테이블
   → 변경 이력 관리
3. 프로시저로 CRUD 캡슐화
   → 복잡한 로직을 DB 내부에서 처리
4. 함수로 계산식 통일
   → 금액 계산, 상태 코드 변환 등에 활용
5. 인덱스 + 실행계획 확인
   → 성능 튜닝의 핵심

---

 

• 시퀀스: 고유 번호 생성 (CACHE 옵션 주의)
• 트리거: 이벤트 발생 시 자동 실행 (로그 관리, 제약조건 강화)
• 프로시저: 여러 SQL 로직을 묶어 재사용
• 함수: 반드시 RETURN, SELECT 안에서도 호출 가능
• 인덱스: 검색 성능 개선 (삽입·수정 시 오버헤드 주의)
• 실행계획: 쿼리 성능 분석 필수