프로시저 (Procedure)

데이터베이스 프로시저(Database Procedure)는 데이터베이스 내에 저장되고 실행되는 일련의 SQL 문들의 집합으로,
자주 사용하는 SQL 명령어들을 하나의 작은 프로그램으로 미리 작성해두고 필요할 때 호출하여 사용하는 것이다.

SQL Server에서의 프로시저 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- 주문 처리를 위한 저장 프로시저 생성
CREATE PROCEDURE ProcessOrder
    @OrderID int,
    @CustomerID int,
    @TotalAmount decimal(10,2)
AS
BEGIN
    -- 트랜잭션 시작
    BEGIN TRANSACTION
    
    TRY
        -- 주문 정보 입력
        INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
        VALUES (@OrderID, @CustomerID, GETDATE(), @TotalAmount)
        
        -- 재고 수량 업데이트
        UPDATE Inventory
        SET Quantity = Quantity - 1
        WHERE ProductID IN (
            SELECT ProductID 
            FROM OrderDetails 
            WHERE OrderID = @OrderID
        )
        
        -- 고객 포인트 업데이트
        UPDATE Customers
        SET Points = Points + (@TotalAmount * 0.01)
        WHERE CustomerID = @CustomerID
        
        -- 트랜잭션 완료
        COMMIT TRANSACTION
    CATCH
        -- 오류 발생 시 롤백
        ROLLBACK TRANSACTION
        
        -- 오류 정보 반환
        SELECT ERROR_MESSAGE() AS ErrorMessage
    END
END

-- 프로시저 사용 예시
EXEC ProcessOrder @OrderID = 1001, @CustomerID = 500, @TotalAmount = 150000

프로시저의 주요 특징과 장점

  1. 성능 최적화
    프로시저는 최초 실행 시 컴파일되어 캐시에 저장되므로, 반복 실행 시 더 빠른 성능을 제공한다:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

    -- 성능 최적화된 프로시저 예시
CREATE PROCEDURE GetCustomerOrders
    @CustomerID int,
    @StartDate date,
    @EndDate date
WITH RECOMPILE  -- 실행 계획을 매번 최적화
AS
BEGIN
    SET NOCOUNT ON;  -- 불필요한 메시지 제거로 성능 향상

    SELECT 
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        p.ProductName
    FROM Orders o
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
    WHERE 
        o.CustomerID = @CustomerID
        AND o.OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (OPTIMIZE FOR UNKNOWN)  -- 다양한 매개변수 값에 대해 최적화
END

  2. 보안 강화
    프로시저를 통해 데이터베이스 접근을 제어할 수 있다:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

    -- 보안이 강화된 프로시저 예시
CREATE PROCEDURE UpdateUserPassword
    @UserID int,
    @OldPassword varchar(100),
    @NewPassword varchar(100)
AS
BEGIN
    -- 비밀번호 유효성 검사
    IF NOT EXISTS (
        SELECT 1 FROM Users 
        WHERE UserID = @UserID 
        AND Password = HASHBYTES('SHA2_256', @OldPassword)
    )
    BEGIN
        RAISERROR ('Invalid old password', 16, 1)
        RETURN
    END

    -- 새 비밀번호 업데이트
    UPDATE Users
    SET 
        Password = HASHBYTES('SHA2_256', @NewPassword),
        LastPasswordChange = GETDATE()
    WHERE UserID = @UserID
END

  3. 비즈니스 로직 캡슐화
    복잡한 비즈니스 규칙을 프로시저 내에 캡슐화할 수 있다:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

    -- 비즈니스 로직이 포함된 프로시저
CREATE PROCEDURE CalculateOrderDiscount
    @OrderID int,
    @CustomerType varchar(20),
    @OrderAmount decimal(10,2),
    @FinalAmount decimal(10,2) OUTPUT
AS
BEGIN
    DECLARE @DiscountRate decimal(5,2)

    -- 고객 유형별 할인율 적용
    SET @DiscountRate = 
        CASE @CustomerType
            WHEN 'VIP' THEN 0.15
            WHEN 'Regular' THEN 0.10
            ELSE 0.05
        END

    -- 주문 금액별 추가 할인
    IF @OrderAmount > 100000
        SET @DiscountRate = @DiscountRate + 0.05

    -- 최종 금액 계산
    SET @FinalAmount = @OrderAmount * (1 - @DiscountRate)
END

  4. 유지보수성 향상
    프로시저를 통해 코드의 재사용성과 유지보수성을 높일 수 있다:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

    -- 모듈화된 프로시저 예시
CREATE PROCEDURE LogAction
    @UserID int,
    @ActionType varchar(50),
    @Description varchar(500)
AS
BEGIN
    INSERT INTO ActivityLog (
        UserID,
        ActionType,
        Description,
        LogDate
    )
    VALUES (
        @UserID,
        @ActionType,
        @Description,
        GETDATE()
    )
END

-- 다른 프로시저에서 재사용
CREATE PROCEDURE UpdateProduct
    @ProductID int,
    @NewPrice decimal(10,2),
    @UserID int
AS
BEGIN
    -- 제품 가격 업데이트
    UPDATE Products
    SET Price = @NewPrice
    WHERE ProductID = @ProductID

    -- 작업 로그 기록
    EXEC LogAction 
        @UserID = @UserID,
        @ActionType = 'PRODUCT_UPDATE',
        @Description = 'Updated product price'
END

프로시저 사용 시 주의사항

  1. 적절한 예외 처리가 필요하다.
  2. 성능 최적화를 고려해야 한다.
  3. 보안 측면을 고려한 설계가 필요하다.
  4. 버전 관리와 문서화가 중요하다.

프로시저는 데이터베이스 애플리케이션 개발에서 매우 중요한 도구이며, 특히 대규모 시스템에서 성능, 보안, 유지보수성을 향상시키는 데 크게 기여한다.

프로시저는 일반적으로 CREATE PROCEDURE 문을 사용하여 생성하며, EXEC 또는 CALL 문을 통해 실행할 수 있다.
프로시저는 데이터 조작, 트랜잭션 관리, 에러 처리 등 다양한 데이터베이스 작업에 활용된다.

참고 및 출처