Procedure Trigger

출처 - https://github.com/jmxx219/CS-Study (opens in a new tab)

프로시저와 트리거

프로시저(Procedure)

데이터베이스에서 SQL을 통해 작업을 할 때, 하나의 쿼리문으로 원하는 결과를 얻지 못할 때가 있다. 원하는 결과를 얻기 위해 사용할 여러 쿼리문을 한 번의 요청으로 실행하고 싶을 때 사용하는 것이 프로시저이다.

개념
- SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL
  - 절차형 SQL: C, Java 등의 프로그래밍 언어와 같이 연속적인 실행이나 분기, 반복 등의 제어가 가능한 SQL
- 데이터베이스에 저장되어 수행되기 때문에 저장 프로시저(Stored Procedure)라고도 불림
  - 즉, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합임
- SQL Server에서 제공되는 프로그래밍 기능
특징
- 입력 매개 변수, 출력 매개 변수, 리턴 값 사용 가능
  - 애플리케이션에서 여러 상황에 따라 쿼리문이 필요할 때 인자 값만 전달하여 쉽게 원하는 결과물을 받을 수 있음
- 자주 사용되는 일반적인 쿼리를 모듈화시켜서 필요할 때마다 호출함
- 테이블처럼 각 데이터베이스 내부에 저장됨
- 시스템의 일일 마감 작업, 일괄 작업을 처리하기 위한 용도로 사용

프로시저 실행 과정

일반적인 쿼리(첫실행)

일반적인 쿼리를 보내게 되면 다음 과정을 진행하게된다.

구문분석
개체 이름확인
사용자권한 확인
최적화
컴파일 및 실행계획 등록(캐싱)
실행

일반적인 쿼리(재실행)

첫실행과정을 확인하면 캐싱되는 것을 확인할수 있다. 따라서 같은 쿼리에 대해서는 캐싱되어 빠르게 응답이 가능하다.

하지만 쿼리가 완전히 같아야만 캐싱된 결과가 나간다!

SELECT *
FROM user_table
u WHERE
u.name ='재표';
SELECT *
FROM user_table
u WHERE
u.name ='수빈';
SELECT *
FROM user_table
u WHERE
u.name ='종인';
SELECT *
FROM user_table
u WHERE
u.name ='지민';
SELECT *
FROM user_table
u WHERE
u.name ='지운';

where절의 값이 다르기 떄문에 캐싱되지 않고, 매번 최적화와 컴파일 과정을 수행해야한다.

저장 프로시저(정의단계)

프로시저의 경우에 다음 과정을 거친다.

구문분석
지연된 이름 확인
생성권한 확인
시스템 테이블에 등록

지연이름: 프로시저 실행시점에 테이블의 존재를 확인하기 때문에 없는 테이블이어도 지정가능

저장 프로시저(첫실행)

개체 이름 확인
사용권한 확인
최적화
컴파일 및 실행계획 등록(캐싱)
실행

구문분석의 경우 정의단계에서 진행했기에 생략하고,개체 이름 확인 단계를 통해 지연된 이름이 유효한지 확인한다.

저장 프로시저(재실행)

일반 쿼리와 같이 첫실행과정에서 최적화과정을 캐싱하기 때문에 성능향상이 생긴다.

하지만 저장 프로시저의 경우를 변수지정이 where절이 달라도 성능향상을 할수 있다!

CREATE PROC
 
select_by_name
        @Name NVARCHAR(3)
 
AS
SELECT *
FROM userTbl
WHERE name =
@name;

프로시저의 장단점

장점

SQL 서버의 성능 향상
- 최적화와 캐시 사용
  - 프로시저의 최초 실행 시, 최적화 상태로 컴파일이 되고 그 이후 프로시저 캐시에 저장됨
  - 이후 저장 프로시저의 두 번째 실행부터는 캐시 메모리에서 가져오기 때문에 속도가 빨리짐
- 여러 개의 쿼리를 한 번에 실행 가능함
유지보수 및 재활용 측면
- C#, Java등으로 만들어진 응용프로그램에서는 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많음
- 이때 개발자는 수정 요건이 발생할 때 코드 내 SQL문을 수정하지 않고 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리함
  - 즉, 작업이 변경되면 다른 작업은 건드리지 않고 프로시저 내부만 수정하면 됨
보안 강화
- 사용자 별로 테이블에 접근 권한을 주지 않고, 저장 프로시저에 접근 권한을 줌으로써 좀 더 보안을 강화할 수 있음
네트워크 부하 저하
- 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송되면 네트워크에 큰 부하가 발생함
- 하지만 저장 프로시저를 사용하면 클라이언트는 직접 SQL문을 작성하지 않고, 프로시저명과 매개 변수 등만 담아 전달하면 됨
- 즉, SQL문이 서버에 이미 저장되어 있기 때문에 클라이언트와 서버 간 네트워크 상 트래픽이 감소됨

단점

디버깅 및 분석 어려움
- 개발된 프로시저가 여러 곳에서 사용될 경우, 수정했을 때의 영향을 분석하기 어려움
- 또한, 에러가 발생했을 때 어디서 잘못되었는지 디버깅하거나 추적하기 어려움
낮은 처리 성능
- 문자와 숫자 연산에서 사용하면 C와 Java보다 성능이 느릴 수 있음

주의점

프로시저가 항상 성능 향상시키는것은 아니다!

최적화 과정에서 인덱스의 사용여부를 결정하는데, 첫실행 이후에는 최적의 방법이 아닐수 있다!

프로시저의 경우 처음 최적화된 방법으로 캐싱되어 계속 사용하기때문에 이런경우 재컴파일을 해줘야한다.

// 저장 프로시저를 생성하는 시점에서 실행시마다 재컴파일되도록 설정한 DML
DROP PROC
sp_recompile_test
        GO
CREATE PROC
sp_recompile_test
	[매개변수]
WITH RECOMPILE
AS
	[
사용 될
쿼리문]
GO

프로시저 생성 및 호출

CREATE
OR REPLACE PROCEDURE 프로시저명
(
    -- 프로 시저 실행 시, 받을 매개변수
    param1 IN NUMBER, 
    param2 IN VARCHAR2
)
 
-- IS: 프로시저에서 선언할 지역변수 선언
IS
[
    변수명1 데이터타입;
    변수명2
데이터타입;
]
 
-- BEGIN: 실행 내용(필요한 기능 작성)
BEGIN
INSERT INTO TEST_TABLE (NO, NAME, SITE)
    VALUE (param1, param2, param3);
 
 
END;
 
-- 프로시저 실행(호출)
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

트리거(Trigger)

개념
- 데이터베이스 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL
특징
- 사용자가 직접 호출하는 것이 아닌 데이터베이스에서 자동으로 실행됨
- 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용됨
- 트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에는 오류가 발생함
프로시저와의 차이점
- 프로시저는 EXECUTE, EXEC 명령어로 실행하지만, 트리거는 생성하면 자동 실행됨
- 프로시저는 커밋과 롤백이 가능하지만, 트리거는 불가능함
- 트리거 내부에는 프로시저 정의가 가능하지만, 프로시저 내부에는 트리거 정의가 불가능함(트리거는 자동 호출되기 때문)
- 프로시저와 달리 트리거는 매개 변수 값이나 코드를 반환할 수 없음

트리거의 장단점

장점

데이터 무결성 강화(참조 무결성)
- 참조 무결성: 관계형 데이터베이스 모델에서 참조 관계에 있는 두 테이블의 데이터가 항상 일관된 값을 갖도록 유지되는 것
- 트리거를 사용함으로서 자동으로 작업이 처리되기 때문에 무결성을 강화할 수 있음
업무 처리 자동화 가능

단점

유지보수의 어려움
- 문서화 하지 않는다면, 중간 투입된 개발자들은 파악이 어려움
과도한 사용 시, 복잡한 상호 의존성을 야기함
- 하나의 트리거 활성화 –> 이 트리거 내의 SQL문 수행 –> 그 결과로 다른 트리거를 활성화함 –> 그 트리거의 SQL 문 수행 –> 반복 (트리거의 연쇄)

저장 프로시저와 트리거는 MySQL 버전 5.0 이상에서 사용 가능하다.

DMZ 테이블 스캔과 인덱스 스캔