데모 요청
데모 요청
데이터베이스 관리에서의 저장 프로시저

저장 프로시저: 작동 원리 및 효과적인 사용 방법

인컴 2026 년 7 월 30 일 ,

데이터베이스 기반 애플리케이션은 결국 애플리케이션 코드 전체에 흩어져 있는 SQL 쿼리가 유지 관리 문제를 야기하는 시점에 도달합니다. 동일한 복잡한 조인 쿼리가 세 개의 서로 다른 서비스에서 나타나고, 데이터베이스 계층에 있어야 할 비즈니스 로직이 애플리케이션으로 유출되며, 데이터 계층에서 시행되어야 할 보안 정책이 우회될 수 있는 애플리케이션 코드에 의해 일관성 없이 적용되는 경우가 발생합니다. 저장 프로시저는 재사용 가능하고 보안에 민감하며 성능에 중요한 SQL 로직을 데이터베이스로 이동시켜 애플리케이션과 독립적으로 관리, 버전 관리, 보안 및 최적화할 수 있도록 함으로써 이러한 문제를 해결합니다.

저장 프로시저는 데이터베이스에 저장되고 하나의 단위로 실행되는 이름이 지정된 사전 컴파일된 SQL 문 집합입니다. 매개변수를 받아들이고, 논리를 포함하며, 결과를 반환하거나, 매개변수를 출력하거나, 상태 코드를 반환할 수 있습니다. 애플리케이션 코드에서 전송하는 임시 쿼리와 달리, 저장 프로시저는 한 번만 구문 분석 및 컴파일되고, 실행 계획은 캐시되어 이후 호출 시마다 재사용되므로, 반복적인 동적 SQL에서 발생하는 컴파일 오버헤드를 제거합니다. 이 가이드에서는 저장 프로시저가 무엇인지, 언제 사용해야 하는지, 보안을 어떻게 강화하는지, 성능에 어떤 영향을 미치는지, 그리고 데이터베이스 전체에 걸쳐 종속성을 갖게 될 때 어떻게 관리해야 하는지에 대해 다룹니다.

데이터베이스 변경 사항을 실행하기 전에 모든 변경 사항에 대한 범위를 지정합니다.

SMART TS XL SQL, COBOL, Java 및 Python에서 저장 프로시저의 종속성을 동시에 매핑합니다.

더 많은 정보

저장 프로시저란 무엇인가요?

저장 프로시저는 관계형 데이터베이스에 저장되고 이름으로 호출되며 선택적 매개변수를 사용할 수 있는 미리 컴파일된 루틴입니다. 데이터베이스 엔진은 저장 프로시저를 한 번 컴파일하고 실행 계획을 캐시한 다음, 이후 호출 시마다 해당 계획을 재사용합니다. 따라서 임시 SQL 쿼리가 실행될 때마다 필요한 구문 분석-컴파일-최적화 과정을 거치지 않아도 됩니다.

SQL

-- SQL Server: basic stored procedure
CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
END;
GO

-- Calling the procedure
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

해당 애플리케이션은 SQL 쿼리를 직접 실행하지 않고 호출합니다. GetCustomerOrders 매개변수를 사용합니다. 나머지는 데이터베이스가 처리합니다.

저장 프로시저 vs. 뷰 vs. 함수

자주 혼동되는 데이터베이스 객체가 세 가지 있습니다. 아래 표는 이들을 구분합니다.

목적반품매개변수를 허용합니다데이터를 수정할 수 있습니다실행 계획이 캐시되었습니다지원 기기
저장 프로 시저결과 집합, 출력 매개변수, 반환 코드가능가능가능복잡한 논리, DML 작업, 보안 강화
관측단일 결과 집합(예: 표)아니아니요 (일반적으로)일부의SELECT 쿼리 간소화, 열 수준 보안
스칼라 함수단일 값가능아니아니SELECT 목록에서 재사용되는 계산
테이블 값 함수결과 집합가능아니일부의매개변수화된 뷰, 집합 반환 연산

핵심 구분: 재사용 가능한 SELECT 추상화가 필요할 때는 뷰를 사용합니다. 매개변수, 조건부 논리, 데이터 수정 또는 보안 적용이 필요할 때는 저장 프로시저를 사용합니다. 값을 반환하거나 테이블을 반환하는 계산이 필요하고 다른 SQL 문과 결합해야 할 때는 함수를 사용합니다.

네 가지 핵심 이점

성능: 사전 컴파일 및 실행 계획 캐싱

SQL Server, PostgreSQL 또는 Oracle은 저장 프로시저 호출을 받으면 해당 프로시저에 대한 캐시된 실행 계획이 있는지 확인합니다. 캐시된 실행 계획이 있으면 즉시 실행하고, 없으면 프로시저를 컴파일하고 실행 계획을 생성하여 캐시한 후 실행합니다. 이후의 모든 호출에서는 캐시된 계획이 재사용됩니다.

애플리케이션 코드에서 전송되는 문자열 연결 쿼리와 같은 임시 SQL 쿼리는 데이터베이스 및 쿼리 구조에 따라 호출될 때마다 다시 컴파일될 수 있습니다. 초당 수천 번 호출되는 고빈도 쿼리의 경우 이러한 컴파일 오버헤드가 상당합니다.

SQL

-- Demonstrating plan reuse in SQL Server
-- Check if a plan exists for a procedure
SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, 1, 100) AS procedure_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.execution_count DESC;

두 번째 성능 향상 이점은 네트워크 트래픽 감소 입니다. 매번 호출할 때마다 애플리케이션에서 데이터베이스로 복잡한 30줄짜리 SQL 쿼리를 보내는 대신, 애플리케이션은 간단한 프로시저 호출만 보냅니다. 네트워크 페이로드가 최소화됩니다. 데이터베이스는 서버 측에서 복잡한 계산을 수행하고 결과 집합만 반환합니다.

보안: 테이블 직접 접근 제한

SC 데이터가 구체적으로 찾고자 하는 이점은 바로 이것입니다. "저장 프로시저를 사용하여 데이터에 대한 직접 접근을 제한하고 데이터베이스 보안을 강화하는 방법"입니다. 이 메커니즘은 간단하면서도 강력합니다.

애플리케이션 사용자에게 저장 프로시저에 대한 실행 권한을 부여합니다. 기본 테이블에 대한 직접 접근은 차단합니다. 사용자는 프로시저를 호출할 수는 있지만, 테이블에서 직접 조회, 삽입, 업데이트 또는 삭제 작업을 수행할 수는 없습니다.

SQL

-- Create a role for application users
CREATE ROLE AppReadRole;

-- Grant execute on the procedure
GRANT EXECUTE ON GetCustomerOrders TO AppReadRole;

-- Deny direct table access
DENY SELECT ON Orders TO AppReadRole;
DENY SELECT ON Customers TO AppReadRole;

-- The user can now:
--   EXEC GetCustomerOrders @CustomerId=123, ... -> works
--   SELECT * FROM Orders WHERE ... -> ACCESS DENIED

두 번째 보안 이점은 SQL 인젝션 방지 입니다. 동적 SQL 구문 대신 매개변수화된 입력을 사용하는 저장 프로시저는 본질적으로 SQL 인젝션으로부터 보호됩니다. 매개변수 값은 SQL 코드가 아닌 리터럴 값으로 처리됩니다.

SQL

-- VULNERABLE: dynamic SQL built from user input
DECLARE @sql NVARCHAR(500);
SET @sql = 'SELECT * FROM Customers WHERE Name = ''' + @UserInput + '''';
EXEC(@sql);
-- An attacker can inject: '; DROP TABLE Customers; --

-- SAFE: parameterized stored procedure
CREATE PROCEDURE GetCustomerByName
    @CustomerName NVARCHAR(100)
AS
BEGIN
    SELECT CustomerId, Name, Email
    FROM Customers
    WHERE Name = @CustomerName;  -- @CustomerName is a literal, not SQL
END;

조심해: 내부적으로 동적 SQL을 생성하는 저장 프로시저 EXEC() or sp_executesql 사용자 입력값을 문자열로 연결하는 방식은 애플리케이션 수준의 동적 SQL만큼 취약합니다. 프로시저 내부에서 생성되는 모든 동적 SQL에도 매개변수화가 적용되어야 합니다.

유지보수성: 한 번의 변경으로 모든 애플리케이션이 업데이트됩니다.

비즈니스 로직, 세금 계산 규칙, 할인 등급, 규정 준수에 필요한 데이터 변환 등이 변경될 때, 저장 프로시저는 이러한 로직을 한 곳에 집중시킵니다. 해당 프로시저를 호출하는 모든 애플리케이션은 재배포 없이 자동으로 업데이트된 동작을 적용받습니다.

SQL

-- Before: discount logic duplicated in three application services
-- After: centralized in one stored procedure
CREATE PROCEDURE CalculateOrderTotal
    @OrderId INT,
    @DiscountedTotal DECIMAL(10,2) OUTPUT
AS
BEGIN
    DECLARE @Subtotal DECIMAL(10,2);
    DECLARE @CustomerTier NCHAR(1);

    SELECT @Subtotal     = SUM(li.Quantity * li.UnitPrice),
           @CustomerTier = c.Tier
    FROM OrderLineItems li
    JOIN Orders o ON o.OrderId = li.OrderId
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE li.OrderId = @OrderId
    GROUP BY c.Tier;

    -- Business rule: Gold customers get 15%, Silver get 8%, Standard get 0%
    SET @DiscountedTotal = @Subtotal * CASE @CustomerTier
        WHEN 'G' THEN 0.85
        WHEN 'S' THEN 0.92
        ELSE 1.00
    END;
END;

할인율을 한 곳에서 변경하세요. 호출하는 세 가지 애플리케이션 서비스 모두에서 적용됩니다. CalculateOrderTotal 새로운 요금을 즉시 반영하십시오.

출력 매개변수 및 오류 처리를 포함한 캡슐화

저장 프로시저는 출력 매개변수를 통해 여러 값을 반환하고 반환 코드를 통해 처리 상태를 전달하므로 단순한 SELECT 문보다 더 풍부한 상호 작용 패턴을 가능하게 합니다.

SQL

CREATE PROCEDURE InsertOrder
    @CustomerId  INT,
    @OrderDate   DATE,
    @NewOrderId  INT OUTPUT,
    @StatusCode  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, @OrderDate, 'PENDING');

        SET @NewOrderId = SCOPE_IDENTITY();
        SET @StatusCode = 0;  -- success

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @StatusCode = ERROR_NUMBER();
        SET @NewOrderId = -1;
    END CATCH;
END;

-- Usage
DECLARE @OrderId INT, @Status INT;
EXEC InsertOrder
    @CustomerId = 5001,
    @OrderDate  = '2026-07-15',
    @NewOrderId = @OrderId OUTPUT,
    @StatusCode = @Status OUTPUT;

IF @Status <> 0
    PRINT 'Insert failed with error: ' + CAST(@Status AS VARCHAR);
ELSE
    PRINT 'Created order: ' + CAST(@OrderId AS VARCHAR);

성능 최적화: 실행 계획이 알려주는 것

실행 계획은 데이터베이스 엔진이 쿼리를 실행하는 방법, 사용한 인덱스, 선택한 조인 알고리즘, 각 단계에서 예상한 행 수 등을 기록한 문서입니다. 하루에 수천 번 호출되는 저장 프로시저의 경우, 실행 계획은 성능 문제를 진단하는 데 가장 중요한 도구입니다.

SQL

-- Enable actual execution plan in SQL Server
-- Then run:
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

-- Check for parameter sniffing issues
-- (cached plan was optimized for a different parameter distribution)
EXEC GetCustomerOrders
    @CustomerId = 99999,  -- rare customer with very few orders
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30'
WITH RECOMPILE;  -- forces fresh plan for this call

매개변수 스니핑 은 저장 프로시저 성능 저하의 가장 흔한 원인입니다. SQL Server는 프로시저가 처음 호출될 때 사용된 매개변수 집합에 대해 생성된 실행 계획을 캐시합니다. 이후 호출에서 매개변수 값이 매우 다를 경우(예: 주문 건수가 50,000만 건인 고객과 2건인 고객), 캐시된 실행 계획은 해당 값에 대해 최적화되지 않은 경우가 많습니다.

완화 전략: OPTIMIZE FOR 대표 매개변수 값을 최적화하기 위한 힌트; WITH RECOMPILE 프로시저 수준에서 매 호출마다 새로운 실행 계획을 생성합니다(비용이 많이 들지만 매개변수 분포가 크게 다를 때 효과적입니다). 스니핑을 방지하기 위해 프로시저 시작 시 로컬 변수를 할당합니다.

SQL

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    -- Local variable trick: prevents parameter sniffing
    DECLARE @LocalCustomerId INT = @CustomerId;
    DECLARE @LocalStart      DATE = @StartDate;
    DECLARE @LocalEnd        DATE = @EndDate;

    SELECT o.OrderId, o.OrderDate, o.TotalAmount
    FROM Orders o
    WHERE o.CustomerId = @LocalCustomerId
      AND o.OrderDate BETWEEN @LocalStart AND @LocalEnd;
END;

모범 사례: 실무 체크리스트

일반적인 원칙이라기보다는, 대규모 환경에서 저장 프로시저를 유지 관리하기 쉽게 만드는 실천적인 방법들입니다.

명명 및 구성

  • 일관된 명명 규칙을 사용하세요: usp_ 사용자 저장 프로시저의 접두사, sp_ 시스템 절차를 위해 예약됨
  • 동사 + 명사 형식으로 절차의 이름을 지정하세요: GetCustomerOrders, InsertPaymentRecord, UpdateInventoryCount
  • 스키마에서 그룹 관련 절차: Sales.GetCustomerOrders, Inventory.UpdateStock

코드 구조

  • 모든 절차는 다음으로 시작하십시오. SET NOCOUNT ON 클라이언트가 오해할 수 있는 행 수 메시지를 억제하기 위해
  • BEGIN TRY / BEGIN CATCH 명시적인 블록 BEGIN TRANSACTION / COMMIT / ROLLBACK
  • 집합 연산에는 커서 사용을 피하고, 가능한 경우 집합 기반 SQL로 다시 작성하십시오.
  • 사용하지 마세요 SELECT *프로시저가 반환하는 모든 열의 이름을 지정합니다.

보안

  • 프로시저에 대한 실행 권한을 부여하고, 애플리케이션 역할에 대한 테이블 직접 접근은 거부합니다.
  • 프로시저 내에서 사용자 입력으로 생성되는 동적 SQL을 사용하지 마십시오.
  • sp_executesql 동적 SQL이 불가피한 경우 매개변수화된 쿼리를 사용합니다.

성능

  • 대용량 테이블의 테이블 스캔 실행 계획을 확인하고 필요한 경우 인덱스를 추가하세요.
  • 도청에 취약한 절차에 대해 대표적인 매개변수 값으로 테스트하십시오.
  • 모니터 sys.dm_exec_procedure_stats 실행 빈도가 높거나 시간이 오래 걸리는 절차의 경우

문서

  • 모든 프로시저에 목적, 매개변수, 반환 값, 작성자, 최종 수정일을 명시한 헤더 주석을 추가하세요.
  • SQL이 무엇을 하는지뿐만 아니라 왜 그렇게 하는지까지 프로시저 로직에 비즈니스 규칙을 명시적으로 문서화하십시오.

저장 프로시저 종속성 관리

저장 프로시저는 독립적으로 존재하지 않습니다. 다섯 개의 테이블에서 데이터를 읽고, 다른 두 개의 프로시저를 호출하며, 수십 개의 애플리케이션 서비스에서 호출되는 프로시저는 자신이 의존하는 대상, 자신에게 의존하는 대상, 그리고 다른 프로시저와 공유하는 부분 등 세 방향 모두에서 복잡한 종속성을 가진 구성 요소입니다.

테이블 열의 데이터 유형이 변경되면 해당 열을 참조하는 모든 프로시저를 테스트해야 합니다. 프로시저의 출력 형식이 변경되면 모든 호출자를 검증해야 합니다. 프로시저 수정이 고려될 때는 모든 호출자를 종합하여 변경 범위와 필요한 회귀 테스트 범위를 결정합니다.

중요한 의존성 유형:

  • 객체 종속성: 프로시저가 참조하는 테이블, 뷰, 함수 및 기타 프로시저
  • 호출자 종속성: 이 프로시저를 호출하는 애플리케이션 코드, 다른 저장 프로시저 및 예약된 작업
  • 스키마 종속성: 프로시저의 매개변수 유형과 SELECT 목록이 일치해야 하는 테이블 및 열 정의
  • 거래 종속성: 호출자 또는 다른 프로시저와 거래 범위를 공유하는 프로시저

저장 프로시저가 10개 정도 있는 소규모 데이터베이스에서는 이러한 종속성을 수동으로 추적할 수 있습니다. 하지만 저장 프로시저가 수년간의 비즈니스 로직을 캡슐화하는 엔터프라이즈 환경에서 흔히 볼 수 있는 수백 개의 저장 프로시저가 있는 데이터베이스 환경에서는 수동 종속성 추적으로 인해 불완전한 맵이 생성되고 변경 관련 문제가 발생합니다.

방법 SMART TS XL 엔터프라이즈 규모에서 저장 프로시저 종속성을 관리합니다.

SMART TS XL의 정적 코드 분석 SQL 저장 프로시저뿐만 아니라 동일한 데이터베이스와 상호 작용하는 COBOL 프로그램, Java 서비스, Python 파이프라인 및 기타 구성 요소도 분석합니다. 통합 분석을 통해 데이터베이스 내의 SQL 간 종속성뿐만 아니라 애플리케이션 코드에서 저장 프로시저를 거쳐 기본 테이블로 이어지는 전체 연결 고리를 보여주는 언어 간 구조 모델을 생성합니다.

애플리케이션 종속성 매핑 기능은 전체 호출자 그래프를 구축합니다. 즉, 저장 프로시저가 소유한 테이블에서 데이터를 읽는 내장 SQL을 사용하는 COBOL 프로그램, JDBC를 통해 저장 프로시저를 호출하는 Java 서비스, 저장 프로시저를 실행하는 데이터베이스 유틸리티를 호출하는 JCL 배치 작업 등을 파악합니다. 저장 프로시저의 시그니처나 동작이 변경되면 종속성 맵은 모든 언어의 모든 호출자를 보여주므로, 변경 사항을 프로덕션 환경에 배포하기 전에 테스트해야 할 전체 범위를 파악할 수 있습니다.

The 영향 분석 이 기능을 통해 변경 계획을 위한 실행 가능한 종속성 맵을 만들 수 있습니다. 변경 사항을 제안하세요. CalculateOrderTotal 이를 호출하는 모든 구성 요소, 읽고 쓰는 모든 테이블, 호출하는 모든 하위 프로시저의 열거된 목록을 받게 됩니다. 이렇게 하면 "이것이 어떤 문제를 일으킬까?"라는 질문이 암묵적인 지식에 의존하는 것이 아니라 구조화되고 증거에 기반한 범위 보고서로 전환됩니다.

The 기업 검색 이 기능을 통해 전체 종속성 모델을 쿼리할 수 있습니다. 예를 들어, 특정 위치에서 읽는 모든 저장 프로시저를 찾을 수 있습니다. Orders모든 발신자 GetCustomerOrders데이터베이스 규모에 관계없이 특정 열을 수정하는 모든 프로시저를 초 단위로 처리할 수 있습니다.

진행하는 팀의 경우 레거시 현대화 저장 프로시저에 수십 년간 축적된 비즈니스 로직이 인코딩되어 있어 마이그레이션 과정에서 이를 보존해야 하는 프로그램 SMART TS XL이 분석은 논리를 추출하고 마이그레이션 순서를 계획할 수 있도록 하는 구조적 문서를 제공합니다.

제 역할을 톡톡히 해내는 데이터베이스 계층

저장 프로시저는 과거 데이터베이스 시대의 유물이 아닙니다. 오히려 데이터베이스에 있어야 할 로직을 배치하기에 적합한 곳입니다. 예를 들어 어떤 애플리케이션이 데이터에 접근하든 일관성을 유지해야 하는 보안 강화 로직, 캐시된 실행 계획을 통해 성능 향상을 기대할 수 있는 고성능 쿼리, 그리고 한 번만 업데이트하면 모든 곳에 적용되어야 하는 비즈니스 규칙 등이 있습니다.

문제는 저장 프로시저를 사용하는 것 자체가 아니라, 문서화되지 않고 매핑되지 않은 의존성 네트워크로 발전하도록 내버려 두는 것입니다. 이렇게 되면 아무도 그 의미를 제대로 이해하지 못하게 됩니다. 중요한 비즈니스 계산을 수행하는 저장 프로시저라도 호출자가 문서화되어 있지 않고, 용도를 설명하는 헤더 주석도 없으며, 수정 전 영향 분석도 이루어지지 않았다면, 아무리 잘 작성되었더라도 위험 요소가 될 수 있습니다. 의존성 그래프를 관리하는 것은 SQL 자체를 관리하는 것만큼 중요합니다. 둘 다 암묵적인 지식보다는 체계적인 분석이 필요합니다.

JBS AUSSIE BEEF에 문의하기

©2026 IN-COM Data Systems, Inc. 모든 권리 보유. SMART TS XL®, 소프트웨어 인텔리전스®,

®