저장 프로시저 만들기

저장 프로시저 는 비즈니스 논리를 캡슐화하고 애플리케이션 성능을 개선하기 위한 SQL Server의 가장 강력한 도구 중 하나입니다. 저장 프로시저를 만들 때 서버에서 실행되는 재사용 가능한 코드 블록을 빌드하여 네트워크 트래픽을 줄이고 데이터 액세스 논리를 중앙 집중화합니다.

저장 프로시저 이해

저장 프로시저는 SQL Server가 단일 단위로 저장하고 실행하는 T-SQL 문의 컴파일된 컬렉션입니다. 매번 서버에 보내는 계획되지 않은 쿼리와 달리 저장 프로시저는 미리 컴파일되고 최적화되므로 후속 실행 시 더 빠르게 실행됩니다.

저장 프로시저를 사용하여 복잡한 비즈니스 논리를 캡슐화하고, 데이터 유효성 검사 규칙을 적용하고, 애플리케이션이 데이터베이스와 상호 작용하는 방식을 제어합니다. 예를 들어 직접 테이블 액세스를 허용하는 대신 입력의 유효성을 검사하고, 비즈니스 규칙을 적용하고, 데이터를 수정하기 전에 변경 내용을 기록하는 저장 프로시저를 만들 수 있습니다.

성능 이점은 쿼리 계획 캐싱에서 비롯됩니다. 계획되지 않은 쿼리를 사용하는 경우 SQL Server는 매번 각 쿼리를 구문 분석하고 최적화해야 합니다. 저장 프로시저를 사용하면 첫 번째 실행 후에 실행 계획이 캐시되어 반복 작업에 대한 오버헤드가 줄어듭니다.

기본 저장 프로시저 만들기

저장 프로시저를 생성할 때는 CREATE PROCEDURE 문으로 시작한 후 T-SQL 로직을 작성합니다. 스키마 정규화된 식별자를 사용하여 프로시저 이름을 지정하면 명확성과 성능이 향상됩니다.

CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END

이 문은 SET NOCOUNT ON 영향을 받는 행 수에 대한 메시지가 클라이언트로 전송되지 않도록 합니다. 이렇게 하면 특히 프로시저가 여러 문을 실행하는 경우 네트워크 트래픽이 줄어들고 성능이 향상됩니다.

프로시저를 만들 때 BEGIN 및 END 키워드를 사용하여 프로시저 본문을 명확하게 정의합니다. 이렇게 하면 코드를 더 쉽게 읽을 수 있으며 나중에 논리를 추가하거나 수정할 때 오류를 방지할 수 있습니다.

매개 변수를 사용합니다.

매개 변수를 사용하면 저장 프로시저를 유연하고 재사용할 수 있습니다. 호출하는 애플리케이션의 값을 수락하도록 입력 매개 변수를 정의하고 값을 호출자에게 반환하는 출력 매개 변수를 정의합니다.

입력 매개 변수는 @ 기호와 매개 변수 이름 및 데이터 형식을 사용합니다. 기본값을 제공하여 매개 변수를 선택적으로 만들 수 있습니다.

CREATE PROCEDURE dbo.GetCustomerOrdersByDate
    @CustomerID int,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
        AND (@StartDate IS NULL OR OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR OrderDate <= @EndDate)
    ORDER BY OrderDate DESC;
END

출력 매개 변수를 사용하면 호출 애플리케이션에 값을 반환할 수 있습니다. 키워드 OUTPUT를 사용하여 그것들을 정의합니다.

CREATE PROCEDURE dbo.CalculateOrderTotal
    @OrderID int,
    @TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
    
    RETURN 0;
END

출력 매개 변수를 사용하는 프로시저를 호출할 때, 값을 받을 변수를 선언하고 그 변수를 OUTPUT 키워드를 사용하여 EXECUTE 문에 사용해야 합니다.

오류 처리 구현

강력한 저장 프로시저에는 예기치 않은 조건을 관리하고 데이터 무결성을 유지하기 위한 오류 처리가 포함됩니다. 다른 프로그래밍 언어의 예외 처리와 유사하게 작동하는 블록을 사용하여 TRY...CATCH 오류 처리를 구현합니다.

CREATE PROCEDURE dbo.InsertCustomerOrder
    @CustomerID int,
    @OrderDate datetime,
    @TotalAmount decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Customer does not exist.', 16, 1);
        END
        
        -- Insert order
        INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
        VALUES (@CustomerID, @OrderDate, @TotalAmount);
        
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END

블록 TRY는 주요 논리를 포함하고, 블록 CATCH는 발생하는 모든 오류를 처리합니다. 와 같은 ERROR_MESSAGE()ERROR_SEVERITY()ERROR_STATE() 시스템 함수를 사용하여 오류 세부 정보를 캡처하고 호출 애플리케이션에 전달할 수 있습니다.

항상 @@TRANCOUNT를 확인한 후 CATCH 블록에서 트랜잭션을 롤백하십시오. 이렇게 하면 트랜잭션이 이미 완료되었거나 시작되지 않은 경우 오류가 발생하지 않습니다.

모범 사례 적용

저장 프로시저를 만들 때 설정된 모범 사례를 따르면 유지 관리가 가능하고 안전하며 성능이 향상됩니다.

스키마로 정규화된 이름을 사용하세요

모든 개체에 스키마 정규화된 이름을 사용합니다. 이렇게 하면 모호성이 제거되고 스키마 확인 오버헤드를 방지하여 성능이 향상됩니다.

-- Good
SELECT * FROM dbo.Orders

-- Avoid
SELECT * FROM Orders

매개 변수 유효성 검사 구현

프로시저를 시작할 때 매개 변수 유효성 검사를 구현합니다. 잘못된 데이터를 처리하는 대신 입력이 유효하지 않은 경우 빠르게 실패합니다.

IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
    RAISERROR('CustomerID must be a positive integer.', 16, 1);
    RETURN -1;
END

SELECT * 사용하지 않음

프로덕션 코드에서는 사용하지 마세요 SELECT * . 테이블 구조가 변경될 때 문제를 방지하고 쿼리 성능을 향상시키기 위해 열을 명시적으로 나열합니다.

-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders

-- Avoid
SELECT * FROM dbo.Orders

의미 있는 이름 사용

프로시저가 수행하는 작업을 설명하는 의미 있는 이름을 사용합니다. 작업(가져오기, 삽입, 업데이트, 삭제, 계산)을 나타내는 동사를 포함합니다.

CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders

접두사를 피하십시오 sp_

저장 프로시저에 sp_ 접두사를 사용하지 마세요. SQL Server는 데이터베이스에 저장된 master 시스템 프로시저에 대해 이 접두사를 예약합니다. 프로시저에 sp_ 이름을 지정하면, SQL Server는 master을 먼저 검색한 다음, 현재 데이터베이스를 확인하여 불필요한 오버헤드를 추가합니다.

-- Good
CREATE PROCEDURE dbo.GetCustomerOrders

-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders

이러한 사례를 토대로 팀이 프로덕션 환경에서 안정적으로 수행할 수 있도록 이해하고 유지 관리하고 신뢰할 수 있는 저장 프로시저를 만들 수 있습니다.