데이터베이스 스토어드 프로시저

데이터베이스에서 **스토어드 프로시저(Stored Procedure)**는 효율성과 생산성을 높이는 중요한 기능 중 하나입니다. 이 글에서는 스토어드 프로시저의 개념, 주요 역할, 사용법, 그리고 활용하기 좋은 상황과 실제 예시를 통해 자세히 알아보겠습니다.

1. 스토어드 프로시저란 무엇인가?

스토어드 프로시저는 데이터베이스에 저장된 SQL 코드의 집합으로, 특정 작업을 수행하기 위해 재사용 가능한 방식으로 작성됩니다. 간단히 말해, 자주 반복되는 SQL 쿼리나 복잡한 로직을 데이터베이스에 미리 저장해 두고 필요할 때 호출해서 실행하는 것입니다.

💡 비유를 통한 이해

스토어드 프로시저를 프로그래밍에서 함수나 메서드에 비유할 수 있습니다. 일련의 작업(예: 데이터 삽입, 조회, 업데이트 등)을 하나의 함수로 만들어 필요할 때 호출하면 코드를 반복 작성하지 않아도 됩니다.

2. 스토어드 프로시저의 주요 역할

2.1. 재사용성

• 한 번 작성하면 여러 곳에서 호출해 사용할 수 있습니다.
• 중복된 SQL 쿼리를 줄이고 관리 효율성을 높입니다.

2.2. 성능 향상

• SQL 코드가 데이터베이스에 미리 컴파일되어 저장되므로 실행 속도가 빠릅니다.

2.3. 보안 강화

• 사용자가 직접 SQL 문을 실행하는 대신 스토어드 프로시저를 호출하므로, 민감한 데이터와 로직을 보호할 수 있습니다.

2.4. 데이터베이스 작업 단순화

• 복잡한 로직(조건문, 반복문 등)을 데이터베이스에서 처리하여 클라이언트와 서버 간의 데이터 전송을 줄입니다.

3. 스토어드 프로시저 사용하는 방법

3.1. 스토어드 프로시저 작성

스토어드 프로시저는 CREATE PROCEDURE 문을 사용하여 작성합니다.

DELIMITER //

CREATE PROCEDURE procedure_name (IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    -- 작업 로직
    SELECT column_name INTO param2
    FROM table_name
    WHERE id = param1;
END //

DELIMITER ;

3.2. 스토어드 프로시저 실행

작성한 스토어드 프로시저는 CALL 문으로 실행합니다.

CALL procedure_name(1, @output_param);
SELECT @output_param;

4. IN, OUT, INOUT 파라미터의 차이점

스토어드 프로시저에서 사용하는 파라미터는 크게 IN, OUT, INOUT 세 가지로 나뉩니다. 각 파라미터는 데이터의 전달 방향에 따라 동작 방식이 달라지며, 특정 상황에서 적합하게 사용됩니다. 아래에서 각각의 특성과 차이점을 자세히 살펴보겠습니다.

4.1. IN 파라미터

• 특징:
  • 프로시저에 입력값을 전달하는 데 사용됩니다.
  • 프로시저 내부에서 값을 변경할 수 있지만, 호출한 쪽에 영향을 주지 않습니다.
  • 기본 파라미터 유형으로, 별도 선언 없이 사용 가능합니다.
• 사용 예시:
   

CREATE PROCEDURE SayHello(IN userName VARCHAR(50))
BEGIN
    SELECT CONCAT('Hello, ', userName) AS Greeting;
END;

-- 실행
CALL SayHello('Alice');

• 실행 결과:

Greeting
--------
Hello, Alice

4.2. OUT 파라미터

• 특징:
  • 프로시저 실행 후 호출자에게 출력값을 반환하는 데 사용됩니다.
  • 호출 시 초기값은 전달하지 않으며, 프로시저 내부에서 값을 설정해야 합니다.
  • 호출자가 결과를 확인하려면 출력 변수를 선언해야 합니다.
• 사용 예시:

CREATE PROCEDURE GetSquare(IN inputNumber INT, OUT result INT)
BEGIN
    SET result = inputNumber * inputNumber;
END;

-- 실행
SET @square = 0;
CALL GetSquare(4, @square);
SELECT @square AS SquareResult;

• 실행 결과:

SquareResult
------------
16

4.3. INOUT 파라미터

• 특징:
  • 입력과 출력을 모두 처리할 수 있는 파라미터입니다.
  • 호출 시 값을 전달하고, 프로시저 실행 후 수정된 값을 반환받습니다.
  • 초기값이 필요하며, 결과도 저장됩니다.
• 사용 예시:

CREATE PROCEDURE MultiplyByTwo(INOUT number INT)
BEGIN
    SET number = number * 2;
END;

-- 실행
SET @num = 5;
CALL MultiplyByTwo(@num);
SELECT @num AS Result;

• 실행 결과:
   

Result
------
10

4.4. 세 가지 파라미터의 차이점 정리

4.5. 활용 시나리오

• IN 파라미터: 입력값만 필요한 경우 (예: 특정 사용자 데이터 조회).
• OUT 파라미터: 결과를 반환해야 하는 경우 (예: 계산 결과, 상태 코드 반환).
• INOUT 파라미터: 입력값을 기반으로 결과를 다시 출력해야 하는 경우 (예: 값 변경 후 반환).

5. 스토어드 프로시저 사용하기 좋은 상황

5.1. 반복 작업 자동화

• 매일 정해진 시간에 특정 데이터를 업데이트하거나 리포트를 생성해야 할 때 유용합니다.
• 예: 일일 매출 데이터 집계

5.2. 복잡한 비즈니스 로직 처리

• 여러 쿼리를 결합해 실행해야 하는 경우(예: 조건문, 반복문 포함 로직).
• 예: 여러 테이블의 데이터를 기반으로 계산 작업 수행

5.3. 애플리케이션과 데이터베이스 간 트래픽 최소화

• 복잡한 쿼리를 클라이언트에서 여러 번 호출하는 대신, 데이터베이스에서 한 번에 처리.

5.4. 보안 강화

• 클라이언트가 직접 SQL을 실행하지 못하게 하고, 스토어드 프로시저만 호출하도록 제한.

6. 스토어드 프로시저 활용 예시

예시 1: 특정 조건에 맞는 사용자 데이터 조회

CREATE PROCEDURE GetUserById (IN userId INT)
BEGIN
    SELECT * 
    FROM Users 
    WHERE id = userId;
END;

-- 실행
CALL GetUserById(5);

예시 2: 여러 테이블에 걸친 데이터 삽입

CREATE PROCEDURE InsertOrderAndDetails (
    IN customerId INT, 
    IN productId INT, 
    IN quantity INT
)
BEGIN
    -- 1. 주문 테이블에 데이터 삽입
    INSERT INTO Orders (customer_id, order_date) 
    VALUES (customerId, NOW());
    
    -- 2. 마지막 주문 ID 가져오기
    SET @orderId = LAST_INSERT_ID();
    
    -- 3. 주문 상세 테이블에 데이터 삽입
    INSERT INTO OrderDetails (order_id, product_id, quantity) 
    VALUES (@orderId, productId, quantity);
END;

-- 실행
CALL InsertOrderAndDetails(1, 101, 2);

예시 3: 자동 데이터 정리 작업

CREATE PROCEDURE CleanOldLogs ()
BEGIN
    DELETE FROM Logs 
    WHERE log_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
END;

-- 실행
CALL CleanOldLogs();

7. 스토어드 프로시저 사용 시 주의사항

• 복잡한 로직 관리: 스토어드 프로시저가 지나치게 복잡하면 유지보수가 어려워질 수 있습니다.
• 데이터베이스 종속성: 프로시저는 특정 데이터베이스 플랫폼에 종속될 가능성이 있으므로, 이식성을 고려해야 합니다.
• 디버깅 어려움: 스토어드 프로시저는 디버깅이 쉽지 않으므로, 충분한 테스트가 필요합니다.

마무리

스토어드 프로시저는 데이터베이스 성능 최적화와 반복 작업 간소화에 탁월한 도구입니다. 특히, 보안과 효율성을 중요시하는 환경에서 필수적인 기능으로 활용됩니다. 작성과 활용이 익숙해지면 데이터베이스와 애플리케이션의 협업을 더 원활하게 만들 수 있습니다.