[DB] 저장 프로시저 (Stored Procedure)의 개념과 예시

저장 프로시저 (SP, Stored Procedure)란?

: 준비된 SQL문으로, 재사용이 가능하도록 저장한 SQL문을 의미
- 매개변수를 설정할 경우, 매개변수의 값에 따라 SQL문을 실행시킬 수 있음

사용 이유

1. 성능 향상

- SP 생성 이후, 첫 실행을 하게 되면 최적화, 컴파일 단계를 거쳐 그 결과를 캐시(메모리)에 저장
- 이 후 해당 SP 실행시 캐시(메모리)에 있는 결과를 가져와 사용하므로 실행 속도가 향상됨
- 실행 계획이 이미 최적화되어 있기 때문에, SQL문을 매번 구문 분석하고 최적화하는 오버헤드를 줄일 수 있음

2. 유지보수 및 재사용성 향상

- SP 사용시 복잡한 로직과 비즈니스 규칙을 DB내에 캡슐화 할 수 있음
- 여러 어플리케이션이나 스크립트에서 동일한 SP 호출할 수 있으므로 코드 중복을 줄이고 유지 관리성을 향상시킴
- 또한 로직을 중앙 집중화된 위치에 저장하기 때문에 SP를 수정하더라도 애플리케이션 코드를 변경할 필요 없음

3. 보안 강화

- 사용자별로 테이블에 권한을 주는것이 아닌 SP에만 접근 및 실행 권한을 줌으로써 테이블과 데이터를 보호할 수 있음
- 테이블과 데이터에 대한 직접적인 무단 접근을 방지하고 민감한 정보를 안전하게 보안 가능

문제점

1. 복잡성

- 특정 데이터베이스 시스템과 SP를 작성하기 위한 절차적 언어(T-SQL, PL/SQL 등)에 대한 추가적인 이해 필요
- 일반적인 SQL문을 작성하는 것보다 복잡할 수 있음

2. 벤더 종속성

- 각 데이터베이스 시스템마다 SP를 위한 문법과 기능이다름
- 다른 데이터베이스 시스템으로 전환시 SP를 다시 작성하거나 수정해야 함

3. 성능 제한

- SP는 첫 실행시에만 최적화 단계를 수행함
- 이 단계에서 인덱스 스캔을 할 것인지 등을 결정하는데, 매개 변수에 따라 적합한 스캔 방식이 다를 수 있음
- 하지만 최초 실행시 설정된 최적화 단계를 자동적으로 수행하기 때문에 성능이 더 나쁜 방법으로 실행될 수 있음
- 이를 방지하기 위해, SP 생성 시점에서 실행시마다 다시 컴파일이 되도록 설정하는 방법을 사용함

사용 예시

기본 문법

- 문법의 경우, 사용하는 데이터베이스 시스템에 따라 상이

-- 선언
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

-- 실행
EXECUTE procedure_name;

매개 변수가 1개 있을 때

-- 선언
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

-- 실행
EXECUTE SelectAllCustomers @City = 'London';

매개 변수가 여러 개 있을 때

-- 선언
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

-- 실행
EXECUTE SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';