Skip to content

Stored Procedure

SQL

1. Stored Procedure란?

Stored Procedure(저장 프로시저)는 미리 작성된 SQL 코드를 저장하여 계속해서 재사용할 수 있는 코드입니다. 만약 반복적으로 같은 SQL 쿼리를 작성해야 한다면, 그 쿼리를 저장 프로시저로 저장해두고 필요할 때마다 호출하여 실행할 수 있습니다. 또한, 저장 프로시저에는 매개변수를 전달할 수 있어서, 전달된 매개변수 값에 따라 저장 프로시저가 다르게 동작하도록 할 수 있습니다.

2. 동작 방식

저장 프로시저를 사용하는 주된 이점은 코드의 재사용성, 중앙 집중화된 관리, 보안 강화, 실행 성능 향상 등입니다. 또한, 복잡한 비즈니스 로직을 데이터베이스 서버 내에서 처리함으로써 어플리케이션과 데이터베이스 사이의 트래픽을 줄일 수 있습니다.

2-1. 작성 및 저장

CREATE PROCEDURE 명령을 사용하여 작성되며, 이후 데이터베이스 시스템 내에 저장됩니다. 이 과정에서 SQL 코드는 컴파일되어 실행을 위한 바이너리 형태로 변환되며, 이는 실행 속도를 높이는 데 기여합니다.

2-2. 호출

EXECUTE 또는 EXEC 명령과 함께 호출됩니다. 사용자는 필요에 따라 매개변수를 전달할 수 있으며, 이 매개변수는 프로시저 내에서 정의된 변수와 연동되어 다양한 작업을 수행합니다.

2-3. 실행

프로시저가 호출되면, 데이터베이스 서버는 이미 컴파일된 코드를 메모리에서 찾아 실행합니다. 이 과정은 일반적인 SQL 쿼리를 매번 해석하고 실행하는 것보다 훨씬 빠르며, 자원을 적게 사용합니다.

2-4. 결과 처리

프로시저 실행 후, 결과는 호출한 프로그램 또는 사용자에게 반환됩니다. 이 결과는 단일 값일 수도 있고, 테이블 형태의 결과 집합일 수도 있습니다.

2-5. 종료

프로시저 작업이 완료되면, 모든 로컬 변수와 사용한 리소스는 정리되고 프로시저 실행은 종료됩니다.

3. 작성 예시

예를 들어, 고객 데이터베이스에서 특정 고객의 정보를 반복적으로 조회해야 하는 상황을 생각해볼 수 있습니다. 이 경우, 고객 ID를 매개변수로 받아 해당 고객의 정보를 조회하는 저장 프로시저를 생성할 수 있습니다.

CREATE PROCEDURE GetCustomerInfo
    @CustomerID INT
AS
BEGIN
    SELECT Name, Address, Phone
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;

이 저장 프로시저는 GetCustomerInfo라는 이름으로 생성되었으며, @CustomerID라는 매개변수를 사용합니다. 이 매개변수를 통해 특정 고객의 ID를 입력받아, Customers 테이블에서 해당 고객의 이름(Name), 주소(Address), 전화번호(Phone)를 조회합니다.

저장 프로시저는 다음과 같이 호출합니다.

EXEC GetCustomerInfo @CustomerID = 123;

위 저장 프로시저를 호출함으로써 고객 ID가 123인 고객의 정보를 데이터베이스에서 빠르게 조회할 수 있습니다.

4. 저장 프로시저와 일반 쿼리의 차이

  1. 성능 : 저장 프로시저는 사전에 컴파일되어 데이터베이스 내에 저장됩니다. 이는 매번 SQL 쿼리를 컴파일하는 것이 아니라, 이미 컴파일된 코드를 실행하기 때문에 처리 속도가 빨라집니다. 반면, 일반 쿼리는 매번 실행할 때마다 파싱, 컴파일, 실행의 과정을 거쳐야 하므로 상대적으로 느릴 수 있습니다.
  2. 재사용성과 유지 관리: 저장 프로시저는 한 번 작성하면 여러 곳에서 재사용할 수 있습니다. 이는 코드 중복을 줄이고, 유지 관리를 용이하게 합니다. 어떤 변경이 필요할 경우, 저장 프로시저만 수정하면 모든 관련 기능이 업데이트되므로 효율적입니다. 반면, 일반 쿼리는 각 사용 사례마다 복사 및 붙여넣기를 반복하며, 수정이 필요할 때마다 모든 인스턴스를 찾아 개별적으로 수정해야 할 수도 있습니다.
  3. 보안: 저장 프로시저를 사용하면 데이터베이스의 세부 구현을 숨기면서 사용자에게 필요한 데이터만을 제공할 수 있습니다. 사용자가 프로시저를 통해서만 데이터에 접근할 수 있게 하여 데이터베이스 보안을 강화할 수 있습니다. 또한, SQL 주입 공격과 같은 보안 위협으로부터 보호하는 데에도 도움을 줍니다.
  4. 트랜잭션 관리: 저장 프로시저는 여러 SQL 명령을 하나의 트랜잭션으로 묶어 실행할 수 있습니다. 이는 복잡한 비즈니스 로직을 처리할 때 데이터의 일관성과 무결성을 유지하는 데 필수적입니다. 일반적인 쿼리 실행 방식으로는 각 쿼리가 독립적으로 실행되기 때문에, 트랜잭션 관리가 복잡해질 수 있습니다.