CS/데이터베이스시스템

저장 객체 - 저장 프로시저/함수/트리거

haong_ 2025. 4. 27. 18:16

데이터베이스 언어의 특징

SQL

  • DBMS에 대한 강력한 작업 지시 기능을 제공
  • 인간의 언어와 매우 유사하고 간단, 명료
  • 비절차적 언어, 필요한 데이터만 기술

비절차적 언어(non-procedural language)

  • 필요한 데이터만 기술하고 수행 절차는 기술하지 않음
  • 높은 가독성과 동작 순서에 대한 구체적 기술이 없어 오류가 상대적으로 적음
  • 프로그램의 성능 최적화, 디버깅, 오류 추적 및 복잡한 로직 구현에 한계 

저장 객체의 이해

저장 객체란

  • SQL문을 확장하여 절차적으로 처리하기 위한 기능을 제공하는 언어 
  • SQL/PSM(Stored Procedure Language) 기반의 확장 언어 

저장 객체의 장단점

  • 장점
    • 네트워크 전송 효율: 클라이언트와 서버 간 데이터 교환량을 줄일 수 있음
    • 효율적 실행 속도: 서버 내에서 실행되므로 빠름
    • 모듈화: 복잡한 로직을 모듈 단위로 관리할 수 있음
    • 보안성 향상: 직접 테이블 접근을 막고 프로시저를 통해 제어할 수 있음
  • 단점
    • 처리 성능 저하: 과도한 사용 시 오히려 부하가 될 수 있음
    • 어려운 디버깅: 저장 객체 내 로직 디버깅이 쉽지 않음

저장 객체의 종류 

  • 저장 프로시저: 자주 사용되거나 복잡한 과정을 거치는 SQL문을 하나의 개체로 저장
  • 함수: 저장 프로시저와 비슷하지만 결과값 반환에 특화, SELECT만 사용 
  • 트리거: 데이터 변경 이벤트에 반응하여 자동으로 동작하는 특별한 형태의 저장 프로시저

저장 프로시저

응용작업중 자주 사용되거나 다수의 SQL문으로 구성되는 복잡한 과정이 저장된 개체 

저장 프로시저의 특징 

  • 함수와 달리 이름을 이용해 값 반환하지 않음
  • 매개변수 받거나 반환
  • 프로시저에서 또 다른 프로시저 호출가능
  • 실행과 관련된 제어값과 메시지 반환 

저장 프로시저의 생성

생성 구문 

CREATE PROCEDURE 프로시저명 (매개변수)
BEGIN
    -- SQL 로직
END;

매개변수 정의 예시

CREATE PROCEDURE example_proc(IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    SELECT col_name INTO param2 FROM example_tbl WHERE id = param1;
END;

저장 프로시저의 호출

CALL example_proc(1, @result);
SELECT @result;

저장 프로시저의 수정과 삭제

  • 수정: ALTER PROCEDURE 명령 사용, 보안 속성 변경에 한정 
  • 실질적 수정은 삭제 후 재생성 
  • 삭제: DROP PROSEDURE 프로시저명; 

매개변수의 사용

역할 외부로부터 주어지는 값이나 기본값을 매개변수를 통해 저장 프로시저로 전달하고 실행한 결과값 반환 
  • IN, OUT, INOUT 세 타입의 매개변수 제공 
    • IN: 기본 타입, 외부로부터 값을 전달
    • OUT: 반환값을 저장
    • INOUT: IN과 OUT의 목적으로 모두 사용 

매개변수의 활용 예시 

CREATE PROCEDURE get_user_name(IN user_id INT)
BEGIN
    SELECT name FROM users WHERE id = user_id;
END;

변수의 사용 

저장객체에서 처리하는 응용 과정에서 발생하는 임시적인 값을 저장하고 재사용하는 기능제공 

변수 종류

  • 사용자 정의 변수: @ 시작, 별도의 선언 없이 사용
  • 로컬 변수: 저장 객체 내에서만 사용, DECLARE로 선언 
  • DECLARE 변수명 데이터타입 [DEFAULT 기본값];

변수값 할당

  • SET 명령: SET 변수명 = 값;
  • SELECT INTO 문: SELECT 컬럼명 INTO 변수명 FROM 테이블명 WHERE 조건;

선택 구조 

IF 구문

IF condition THEN
    -- 실행문
ELSE
    -- 다른 실행문
END IF;

CASE 구문

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END CASE;

반복 구조 

WHILE 

WHILE 조건 DO
    -- 실행문
END WHILE;

REPEAT 

REPEAT
    -- 실행문
UNTIL 조건
END REPEAT;

LOOP

[레이블] LOOP
    -- 실행문
    LEAVE [레이블];
END LOOP;

함수

  • DBMS는 문자함수, 숫자함수, 날짜함수, 집계함수등 많은 편리한 함수를 제공 
  • 사용자 정의 함수: 사용자가 직접 프로그래밍하여 필요한 기능을 구현할 수 있음 
  • 저장 프로시저와 함수의 차이 
    • 저장 프로시저는 OUT 매개변수를 통해 결과를 반환하고 CALL 명령으로 실행
    • 함수는 RETURN문으로 값을 반환하며 SELECT문 안에서 호출

함수의 생성

CREATE FUNCTION get_user_email(IN user_id INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE email VARCHAR(255);
    SELECT email INTO email FROM users WHERE id = user_id;
    RETURN email;
END;
  • 매개변수: IN 타입의 매개변수만 사용 가능 

트리거

테이블에 데이터 변경(insert, update, delete) 이벤트가 발생했을 때 자동으로 실행되는 저장 객체
  • 전체적인 데이터베이스의 무결성과 일관성을 유지하도록 동작
  • 조건에 따른 데이터 적합성 검사 가능
  • 연속적 트리거 실행 시 다른 데이터베이스나 서버에 대한 광범위한 참조 무결성도 검사가능 

주의사항 

  • 실제 DBMS 내부에서 동작시 어떤 트리거 발동되었는지 유추하기 힘듬
  • 연쇄적인 트리거 발생은 DBMS 실패 유발할 수 있음

트리거의 종류 

  • 트리거 이벤트: 자동으로 반응하는 SQL 문
  • 트리거 시점: 트리거가 실핻될 시점을 명시 

insert, update, delete 의 before/after 6개 

트리거의 생성

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 실행할 SQL문
END;

트리거의 활용 

PostgreSQL에서 updated_at 자동 갱신 트리거 예시 

CREATE  FUNCTION updated_tbl()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = current_timestamp;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER example_updated_at
    BEFORE UPDATE
    ON
        example_tbl
    FOR EACH ROW
EXECUTE PROCEDURE updated_tbl();

 

저작자표시