[sqld 2장] SQL 저장 모듈(Stored Module) 개념 및 종류

📌 SQL 저장 모듈(Stored Module) 개념 정리

SQL 저장 모듈(Stored Module)은 데이터베이스에서 실행되는 SQL 코드 블록을 미리 저장하여 필요할 때 호출하여 실행할 수 있도록 만든 프로그램 객체. 일종의 sql 컴포넌트 프로그램. 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 프로그램. oracle의 저장모듈로는 Trigger, Prodedure, User defined Function이 있음.

 

 

✅ 1. SQL 저장 모듈의 종류

(1) 저장 프로시저 (Stored Procedure)

• 자주 실행하는 SQL 문을 미리 저장하고 필요할 때 호출하는 프로그램
• IN/OUT 매개변수를 사용할 수 있어 데이터 입력 및 반환이 가능
• 명시적으로 CALL 또는 EXECUTE를 통해 실행
• 비즈니스 로직을 캡슐화하여 데이터베이스 내에 저장해 높은 명령문 집합으로 코드 재사용성을 높임

📌 예제 (저장 프로시저 생성 및 실행)

-- 특정 직원의 급여를 인상하는 저장 프로시저
CREATE OR REPLACE PROCEDURE update_salary (p_emp_id NUMBER, p_amount NUMBER) AS
BEGIN
    UPDATE employees SET salary = salary + p_amount WHERE emp_id = p_emp_id;
    COMMIT;
END;
/

-- 저장 프로시저 실행
BEGIN
    update_salary(101, 500); -- 직원 ID 101의 급여를 500 증가
END;
/

 

(2) 저장 함수 (Stored Function)

• 저장 프로시저와 비슷하지만, 반드시 단일 값을 반환해야 함
• SELECT 문에서 컬럼 값을 계산하는 용도로 사용 가능
• 다른 sql문을 통하여 호출됨.
• RETURN 키워드를 통해 결과 값을 반환.(sql의 보조적인 역할)

📌 예제 (저장 함수 생성 및 실행)

-- 직원의 연봉을 계산하는 저장 함수
CREATE OR REPLACE FUNCTION get_annual_salary (p_emp_id NUMBER) RETURN NUMBER IS
    v_salary NUMBER;
BEGIN
    SELECT salary * 12 INTO v_salary FROM employees WHERE emp_id = p_emp_id;
    RETURN v_salary;
END;
/

-- 저장 함수 실행
SELECT get_annual_salary(101) FROM dual;

 

(3) 트리거 (Trigger)

• 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 SQL 블록
• 데이터 변경을 감지하고 자동으로 특정 작업 수행
• 예를 들어, 데이터 무결성을 유지하거나 변경 이력을 기록하는 데 사용

📌 예제 (트리거 생성 및 실행)

-- 직원 급여 변경 시 로그를 기록하는 트리거
CREATE OR REPLACE TRIGGER trg_salary_update
BEFORE UPDATE ON employees  -- employees 테이블에서 UPDATE 발생 전 실행
FOR EACH ROW
BEGIN
    INSERT INTO salary_log(emp_id, old_salary, new_salary, change_date)
    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

 

(4) 패키지 (Package)

• 여러 개의 저장 프로시저, 함수, 변수, 상수 등을 하나의 단위로 묶어 관리
• 캡슐화(Encapsulation)와 모듈화(Modularization)를 지원하여 코드 재사용성을 높임
• 패키지는 **패키지 선언부(Specification)**와 **패키지 본문(Body)**로 구성됨

📌 예제 (패키지 생성 및 실행)

-- 패키지 선언부 (Specification)
CREATE OR REPLACE PACKAGE emp_pkg AS
    PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER);
    FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

-- 패키지 본문 (Body)
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    -- 급여 업데이트 프로시저
    PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER) IS
    BEGIN
        UPDATE employees SET salary = salary + p_amount WHERE emp_id = p_emp_id;
        COMMIT;
    END;

    -- 연봉 계산 함수
    FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
        v_salary NUMBER;
    BEGIN
        SELECT salary * 12 INTO v_salary FROM employees WHERE emp_id = p_emp_id;
        RETURN v_salary;
    END;
END emp_pkg;
/

-- 패키지 실행
BEGIN
    emp_pkg.update_salary(101, 500);
    DBMS_OUTPUT.PUT_LINE(emp_pkg.get_annual_salary(101));
END;
/

 

 

---

https://belle-sooir.tistory.com/190

[sqld 2장] 프로시저 트리거 특징 차이