[DB 지식] 저장 프로시저(Stored PROCEDURE)

이번 포스팅에서는 데이터베이스 관리 시스템(DBMS)에서 사용되는 프로그래밍 개체인 저장 프로시저(Stored Procedure)에 대하여 공부를 해보고자 한다.

 

---

저장 프로시저란

저장 프로시저(Stored Procedure)는 데이터베이스 관리 시스템(DBMS)에서 사용되는 프로그래밍 개체로, 일련의 SQL 문을 하나의 논리적인 단위로 묶어서 실행하는 저장된 프로그램을 말한다.
저장 프로시저는 데이터베이스 내에서 정의되며, 데이터베이스 서버에 저장되어 실행된다.

여러 개의 SQL 문을 포함할 수 있으며, 조건부 논리, 반복문, 예외 처리 등과 같은 프로그래밍 구조를 지원한다.

 

저장 프로시저의 작동 방식

1. 저장 프로시저 정의
   • CREATE PROCEDURE 문을 사용하여 저장 프로시저를 데이터베이스에 정의한다.
   • 매개변수를 정의하고, 필요한 SQL 문을 포함시킨다.
     
     
2. 저장 프로시저 컴파일
   • 저장 프로시저는 데이터베이스 서버에 의해 컴파일되어 실행 계획을 생성한다.
   • 이는 저장 프로시저의 문법 및 논리적 유효성을 검사하고 최적화하는 단계다.
     
     
3. 저장 프로시저 실행
   • 저장 프로시저는 EXECUTE 문이나 저장 프로시저의 이름을 사용하여 호출된다.
   • 매개변수를 전달하고, 저장 프로시저의 내부 SQL 문이 실행된다.
     
     
4. 결과 반환
   • 저장 프로시저가 결과를 반환해야 할 경우, 결과 집합이나 출력 매개변수를 통해 클라이언트로 전달된다.

저장 프로시저는 특정 데이터베이스 시스템에 종속적일 수 있으며, 각 데이터베이스 시스템은 저장 프로시저를 정의하고 사용하는 방식에 차이가 있을 수 있다.
주로 사용되는 데이터베이스 시스템인 Oracle, SQL Server, MySQL, PostgreSQL 등은 모두 저장 프로시저를 지원한다.

저장 프로시저를 사용하면 데이터베이스 작업을 효율적으로 수행할 수 있으며, 복잡한 비즈니스 로직을 처리하거나 데이터베이스의 일관성을 유지하기 위해 사용될 수 있다.

 

---

저장 프로시저와 일반적인 SQL 문의 주요 차이점

일반적인 SQL 문

 

일반적인 SQL 쿼리문이 처음 실행 될 때의 과정

• 문법을 검사하여 불필요한 구문을 제거해서 표준화를 만들고 SQL 쿼리문을 실행할 수 있는지 권한 확인을 하여 최적의 실행 계획을 만들어서 컴파일한 후 캐시에 등록된 것을 사용하고 있다.
• 다음에 똑같은 쿼리문을 사용하면 캐시에 등록된 것을 사용하기 때문에 즉시 수행 되지만 만약 쿼리문이 조금이라도 변경된다면 방금 전 작업을 똑같이 수행하므로 CPU와 메모리 비용 부담이 커진다.

 

SELECT * FROM Customers WHERE Country = 'USA';

• 위의 SQL 문은 단일 SQL 문으로 데이터베이스에 직접 전송되고 실행된다.
• 결과 집합이 클라이언트로 반환되며, 클라이언트가 결과를 처리하고 출력한다.

 

저장 프로시저

프로시저를 만드는 과정

• 문법을 검사한 후 해당 개체의 이름이 존재하는지 확인을 안 한다는 말이 프로시저 내부 로직에서 참조하는 개체 이름이 정말 존재하지 않아도 지연된 이름 확인으로 무시된다. 
• 그리고 만들 수 있는지 권한 확인을 한 후 시스템 테이블에 개체의 정보를 저장한다.

 

만들었던 프로시저를 사용할때의 과정

• 처음에 사용할 땐 개체가 존재하는지 확인하고 저장 프로시저 실행 권한을 확인한 후 최적화된 실행 계획을 컴파일하여 캐시에 등록한 후 사용한다.
• 이후에 다시 사용할 땐 캐시에 등록되어 있으면 즉시 실행하고 없으면 처음 단계부터 다시 시작한다.

 

CREATE PROCEDURE GetCustomersByCountry
    @Country varchar(50)
AS
BEGIN
    SELECT * FROM Customers WHERE Country = @Country;
END;

• 위의 코드는 GetCustomersByCountry라는 저장 프로시저를 정의하는 예제이다.
• 저장 프로시저는 CREATE PROCEDURE 문을 사용하여 정의되며, @Country라는 매개변수를 받는다.
• 저장 프로시저 내부에는 SQL 문이 포함되어 있으며, 이 경우 SELECT 문을 사용하여 특정 국가의 고객을 조회한다.

 

저장 프로시저와 일반적인 SQL 문의 주요 차이점

1. 컴파일 및 최적화
   • 저장 프로시저는 데이터베이스 서버에 컴파일되고 최적화되어 성능을 향상한다.
   • 일반적인 SQL 문은 매번 실행될 때마다 컴파일 및 최적화 과정을 거쳐야 한다.
     
     
2. 재사용성
   • 저장 프로시저는 여러 번 호출될 수 있으며, 중복 코드를 피하고 코드를 재사용할 수 있다.
   • 일반적인 SQL 문은 매번 작성되고 실행되어야 한다.
     
     
3. 보안
   • 저장 프로시저는 데이터베이스 서버에서 실행되므로 클라이언트는 직접적인 데이터베이스 액세스를 할 필요가 없다.
   • 데이터베이스 관리자는 저장 프로시저에 대한 액세스 권한을 관리하여 보안을 강화할 수 있다.
     
     
4. 네트워크 트래픽 감소
   • 저장 프로시저는 클라이언트와 데이터베이스 서버 간의 네트워크 트래픽을 감소시킨다.
   • SQL 문을 클라이언트에서 서버로 전송하는 대신, 클라이언트는 저장 프로시저를 호출하고 결과를 받아온다.
     
     
5. 트랜잭션 관리
   • 저장 프로시저 내부에는 트랜잭션 관리 로직을 포함시킬 수 있다.
   • 이로 인해 데이터의 일관성과 안전성을 유지하기 위한 복잡한 작업을 수행할 수 있다.

저장 프로시저는 데이터베이스 시스템에 따라 문법과 기능의 차이가 있을 수 있으며, 각 데이터베이스 시스템의 문서를 참조하여 사용 방법을 확인하는 것이 좋다.

 

---