SQL 저장 프로시저: 쿼리 자동화 및 최적화

튜토리얼
SQL

SQL 저장 프로시저는 데이터베이스에 저장된 SQL 문 집합입니다. 데이터 조작 및 검증 작업을 수행하기 위해 필요에 따라 실행할 수 있으며, 일반적인 작업을 위해 반복적인 SQL 코드를 작성할 필요성을 줄여줍니다. 저장 프로시저는 효율성과 재사용성을 촉진하여 데이터베이스 관리에 도움을 줍니다. 또한, 향상된 데이터베이스 보안 및 유지 관리성을 지원합니다. 이 기사에서는 SQL 저장 프로시저를 생성하고 실행하는 방법, 일반적인 사용 사례, 그리고 모범 사례에 대해 논의할 것입니다.

시작하면서, 데이터캠프의 SQL 입문 및 SQL 배우기 코스를 수강하여 SQL을 사용하여 데이터를 추출하고 분석하는 기초 지식을 배우는 것을 강력히 추천합니다. 또한 다운로드할 수 있는 SQL 기초 요약표는 가장 일반적인 SQL 함수가 모두 포함되어 있어 유용한 참고 자료입니다.

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

SQL에서 저장 프로시저는 데이터베이스 내에 저장된 SQL 문장의 모음입니다. SQL 저장 프로시저의 목적은 데이터베이스에서 데이터 쿼리, 삽입, 업데이트 또는 삭제와 같은 일련의 작업을 수행하는 것입니다.

일반 SQL 쿼리와 달리 별도의 명령으로 실행되는 저장 프로시저는 SQL 문 집합을 캡슐화하여 SQL 명령을 반복적으로 작성하지 않고도 코드를 쉽게 재사용할 수 있습니다.

SQL 저장 프로시저의 이점은 다음과 같습니다:

  • 코드 재사용성: 저장 프로시저가 한 번 생성되면 필요할 때마다 호출할 수 있어 SQL 코드의 중복을 없앨 수 있습니다.
  • 성능 향상: 저장 프로시저는 미리 컴파일되어 데이터베이스 서버에 저장되므로 일반적으로 더 빠르게 실행되며, 네트워크 대기 시간과 컴파일 시간을 줄입니다.
  • 보안: 저장 프로시저는 사용자가 테이블에 직접 접근하지 않고도 저장 프로시저를 실행할 수 있는 권한을 부여함으로써 데이터 보안 및 민감한 데이터 접근에 대한 통제를 개선할 수 있습니다.

기본 구문 및 구조

저장 프로시저를 생성하는 구문은 데이터베이스 시스템(예: MySQL, SQL Server, Oracle)에 따라 약간 다를 수 있습니다. 아래는 SQL Server 구문을 사용한 일반적인 예입니다:

-- ProcedureName이라는 이름의 저장 프로시저 생성
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL 문이 여기에 들어갑니다
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

위의 구문에서;

  • 프로시저 생성: 이 명령은 새로운 저장 프로시저를 정의하는 데 사용됩니다.

  • 프로시저 이름: 저장 프로시저에 부여된 이름입니다. 데이터베이스 내에서 고유해야 합니다.

  • @매개변수1, @매개변수2: 매개변수는 선택 사항이며, 프로시저가 데이터 입력을 받을 수 있도록 합니다. 각 매개변수는 @ 기호와 데이터 유형(예: INT, VARCHAR(50))으로 정의됩니다.

  • AS BEGIN…END: BEGIN과 END 사이의 SQL 문은 프로시저의 본체를 형성하며, 여기서 주요 로직이 실행됩니다. 이 예제에서는 특정 조건에 따라 테이블에서 레코드를 검색하는 프로시저입니다.

입력 및 출력 매개변수

입력 및 출력 매개변수를 통해 저장 프로시저에 값을 전달하고 반환할 수 있습니다.

예를 들어, @Parameter1이 입력 매개변수로 정의되면 프로시저가 호출될 때 어떤 값이든 할당될 수 있으며, 이는 SQL 로직이나 출력에 영향을 미칩니다. 아래 예제에서 @UserID 매개변수는 제공된 UserID에 특정한 데이터를 검색합니다.

-- UserID로 특정 사용자의 데이터를 검색하는 프로시저 생성
CREATE PROCEDURE GetUserData 
 -- 입력 매개변수: 검색할 사용자의 ID
    @UserID INT 
AS
BEGIN
    -- UserID가 입력 매개변수와 일치하는 Users의 모든 열 선택
    SELECT * FROM Users WHERE UserID = @UserID;
END;

OUTPUT 키워드로 정의된 출력 매개변수는 저장 프로시저가 호출 환경으로 값을 반환할 수 있게 합니다. 예를 들어, 프로시저가 할인을 계산하는 경우, 출력 매개변수를 사용하여 호출 프로그램에 값을 전달할 수 있습니다.

-- 할인된 가격을 계산하는 프로시저 생성
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- 출력: 할인 후 최종 가격
AS
BEGIN
    -- 원래 가격에 할인율을 적용하여 최종 가격 계산
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

이 절차를 호출하려면 다음과 같이 사용합니다;

-- 할인 후 최종 가격을 저장할 변수를 선언합니다
DECLARE @FinalPrice DECIMAL(10, 2);
-- 가격 100과 10% 할인으로 CalculateDiscount 절차를 실행합니다
-- 출력 결과를 @FinalPrice 변수에 저장합니다
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- 최종 할인된 가격을 선택하고 표시합니다
SELECT @FinalPrice AS FinalPrice;

MySQL의 저장 프로시저

앞서 언급했듯이, SQL에서 저장 프로시저를 실행하는 방법은 데이터베이스 시스템과 사용되는 도구에 따라 다를 수 있습니다.

MySQL에서 저장 프로시저 생성하기

MySQL에서 저장 프로시저를 생성하려면 프로시저의 이름, 매개변수 및 본문을 구성하는 SQL 문을 정의해야 합니다. 다음 예제는 EmployeeID를 입력 매개변수로 받아 해당 직원의 세부 정보를 검색하는 GetEmployeeDetails라는 프로시저를 생성합니다.

DELIMITER $
-- 특정 EmployeeID에 대한 세부 정보를 검색하는 프로시저 생성
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- 입력 매개변수와 일치하는 EmployeeID를 가진 Employees에서 모든 열 선택
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

MySQL에서 저장 프로시저 실행

저장 프로시저를 실행하는 가장 일반적인 방법은 SQL 명령을 사용하는 것입니다. MySQL에서는 CALL 명령을 사용하여 저장 프로시저를 실행합니다.

CALL ProcedureName();

정의된 GetEmployeeDetails 프로시저를 사용하여 실행 쿼리는 다음과 같습니다:

-- EmployeeID 101에 대한 세부 정보를 검색하기 위해 저장 프로시저 실행
CALL GetEmployeeDetails(101);

SQL Server의 저장 프로시저

SQL Server는 저장 프로시저를 생성, 실행 및 관리하기 위한 특정 구문과 명령을 제공합니다. 이 접근 방식은 복잡한 작업을 최소한의 반복으로 처리할 수 있는 효율적이고 재사용 가능한 SQL 루틴을 구축하는 데 더 쉽게 만들어줍니다.

SQL Server에서 저장 프로시저 생성

SQL Server에서 저장 프로시저를 생성하는 것은 프로시저의 이름, 매개변수 및 본체를 구성하는 SQL 문을 정의하는 것을 포함합니다. 다음 예제는 GetEmployeeDetails라는 프로시저를 생성하며, 이 프로시저는 @EmployeeID를 입력 매개변수로 받아 해당 특정 직원의 세부 정보를 검색합니다.

-- EmployeeID로 특정 직원의 세부 정보를 검색하는 프로시저 생성
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- 입력 매개변수: 검색할 직원의 ID
AS
BEGIN
    -- 입력 매개변수와 일치하는 EmployeeID에서 Employees의 모든 열 선택
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

SQL Server에서 저장 프로시저 실행

SQL Server에서 EXEC 또는 EXECUTE 명령어는 저장 프로시저를 호출합니다. 다음 예제는 특정 입력 매개변수를 사용하여 저장 프로시저 GetEmployeeDetails를 실행하는 방법을 보여줍니다.

-- EmployeeID를 102로 설정하여 GetEmployeeDetails 프로시저 실행
EXEC GetEmployeeDetails @EmployeeID = 102;

명령에서 변수를 선언하여 출력 매개변수를 실행할 수도 있습니다. 다음 예제에서 @TotalSales는 CalculateTotalSales의 출력을 받기 위해 선언된 변수입니다.

-- 총 판매 금액을 저장할 변수를 선언합니다
DECLARE @TotalSales DECIMAL(10, 2);
-- SalespersonID 5에 대해 CalculateTotalSales를 실행하고 결과를 @TotalSales에 저장합니다
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- 총 판매 금액을 표시합니다
SELECT @TotalSales AS TotalSales;

SQL Server의 다양한 데이터 쿼리 기능을 이해하기 위해 SQL Server 소개 과정을 수강하는 것을 추천합니다. 또한, 저장 프로시저를 생성, 업데이트 및 실행하는 기술뿐만 아니라 집계 함수, 조인, 삽입 및 삭제 테이블 등을 도와줄 전체 SQL Server 개발자 경력 코스도 고려해 보세요.

저장 프로시저의 일반적인 사용법

SQL 저장 프로시저는 반복적인 복잡한 작업이 필요한 시나리오에서 유용합니다. 다음은 데이터 관리 및 비즈니스 운영에서 저장 프로시저의 실제 적용 사례입니다.

데이터 검증 및 무결성 시행

저장 프로시저는 업데이트나 삽입 전에 데이터를 검증하는 데 사용될 수 있습니다. 아래의 예제에서는 저장 프로시저가 고객의 이메일이 고유한지 확인한 후 새로운 레코드를 Customers 테이블에 삽입하여 데이터 일관성을 보장합니다. 이는 데이터베이스 내에서 검증 로직을 중앙 집중화하여 중복을 줄이고 다양한 애플리케이션에서 균일한 시행을 보장합니다.

-- 중복 이메일을 확인하여 새로운 고객을 추가하는 프로시저 생성
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- 이메일이 이미 Customers 테이블에 존재하는지 확인
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- 이메일이 이미 사용 중인 경우 오류 발생
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- 이메일이 고유한 경우 새로운 고객 정보를 삽입
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

자동화된 데이터 처리 및 보고

저장 프로시저를 사용하여 정기 보고서를 생성하거나 대량의 데이터 세트를 처리할 수도 있습니다. 예를 들어, 저장 프로시저는 전자 상거래 플랫폼에서 일일 판매 데이터를 집계하고 이를 보고 테이블에 저장하여 팀이 복잡한 쿼리를 실행하지 않고도 판매 통찰력을 쉽게 접근할 수 있도록 합니다.

-- 일일 판매 보고서를 생성하는 프로시저 생성
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- 오늘 날짜와 총 판매액을 SalesReport 테이블에 삽입
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Sales 테이블에서 오늘의 현재 날짜와 판매 합계 선택
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

트랜잭션 관리

저장 프로시저를 사용하면 여러 작업이 단일 트랜잭션으로 실행되도록 보장할 수 있습니다. 예를 들어, 은행 시스템에서는 저장 프로시저가 자금 이체에서 직불 및 신용 작업을 모두 처리하여 두 작업이 함께 성공하거나 실패하도록 할 수 있습니다.

-- 계좌 간 자금을 이체하는 절차를 만듭니다
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- 원자성을 보장하기 위해 트랜잭션을 시작합니다
    -- 발신자의 계좌 잔액에서 지정된 금액을 차감합니다
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- 수신자의 계좌 잔액에 지정된 금액을 추가합니다
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- 오류를 확인하고 발생한 경우 롤백하며, 그렇지 않으면 트랜잭션을 커밋합니다
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- 오류가 발생한 경우 모든 변경 사항을 취소합니다
    ELSE
        COMMIT TRANSACTION;    -- 오류가 없으면 변경 사항을 확인합니다
END;

액세스 제어 및 데이터 보안

SQL 저장 프로시저를 사용하여 민감한 정보에 대한 데이터 접근을 제어할 수 있습니다. 예를 들어, 저장 프로시저는 사용자가 트랜잭션 세부정보 없이 계좌 잔액과 같은 관련 필드만 검색하는 프로시저를 호출하도록 허용하여 직접 테이블 접근을 제한할 수 있습니다.

-- 인증 확인과 함께 계좌 잔액을 검색하는 프로시저를 만듭니다
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- 계좌가 존재하고 지정된 사용자가 소유하고 있는지 확인합니다
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- 인증되면 계좌 잔액을 선택하고 반환합니다
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- 인증되지 않은 경우 오류를 발생시킵니다
        THROW 50000, 'Unauthorized access.', 1;
END;

데이터 마이그레이션 및 ETL 프로세스

저장 프로시저는 시스템 간에 데이터를 로드, 변환 및 마이그레이션하는 데에도 사용됩니다. 저장 프로시저는 소스 데이터베이스에서 데이터를 자동으로 추출하고 필요에 따라 변환한 후, 대상 테이블에 삽입하여 보고서 또는 분석을 위한 데이터 통합을 간소화합니다.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- 추출
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- 변환
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- 로드
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

저장 프로시저에 대한 모범 사례

효율적이고 유지 관리가 용이한 저장 프로시저를 작성하면 데이터베이스 성능을 최적화할 수 있습니다. 다음은 SQL 데이터베이스를 위한 저장 프로시저 작성 팁입니다.

  • 일관된 명명 규칙 사용: 저장 프로시저를 쉽게 식별하고 이해할 수 있도록 일관되고 설명적인 명명 형식을 사용하십시오. 또한, 잠재적인 충돌 및 성능 문제를 방지하기 위해 시스템 프로시저에 예약된 SQL Server의 sp_ 접두사는 피하십시오.

  • 오류 처리 구현: SQL 문을 TRY...CATCH 블록으로 감싸서 오류를 포착하고 처리하며 데이터 무결성을 유지합니다. 

  • 성능 최적화: 커서 사용을 최소화하세요. 커서는 느리고 자원을 많이 소모할 수 있습니다. 대신 일반적으로 더 효율적인 집합 기반 작업을 사용해 보세요. 자주 사용하는 열에 인덱스를 생성하고 대규모 테이블에서 복잡한 조인을 피하여 메모리 오버헤드를 줄이고 효율성을 향상시키세요.

  • 저장 프로시저 매개변수화: 하드코딩된 값 대신 매개변수를 사용하여 절차에 동적 값을 전달할 수 있도록 하여 더 유연하고 재사용 가능하게 만드세요.

집합 함수와 조인을 사용하여 데이터를 필터링하는 방법에 대해 더 배우려면 중급 SQL 과정을 확인해 보세요. 또한, 테이블 조인 및 데이터 분석 기술을 향상시키기 위해 SQL 서버 기초 및 SQL 기초 스킬 트랙을 시도해 보세요.

결론

SQL 저장 프로시저는 데이터베이스 관리에서 코드 재사용성과 성능 최적화를 향상시킵니다. 저장 프로시저는 제어된 접근을 통해 데이터베이스 보안을 강화하고 데이터 무결성을 보장합니다. 데이터 실무자로서, 최상의 데이터베이스 관리 관행을 익히기 위해 저장 프로시저를 생성하고 실행하는 연습을 하기를 권장합니다.

데이터 분석가로서 능숙해지고 싶다면, 필요한 기술을 배우기 위해 우리의 SQL의 준 데이터 분석가 경력 트랙을 확인해 보세요. SQL을 사용하여 전문적인 대시보드를 만드는 방법을 배우고 싶다면 SQL에서의 보고서 작성 과정도 적합합니다. 마지막으로, 데이터 분석을 위한 SQL 사용 능력을 입증하고 다른 데이터 전문가들 사이에서 돋보이기 위해 SQL 준 인증을 취득할 것을 추천합니다.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure