[MySQL] CREATE PROCEDURE 완벽 가이드 사용법(프로시저 생성)

MySQL CREATE PROCEDURE는 MySQL 데이터베이스에서 저장 프로시저(Stored Procedure)를 생성하는 데 사용됩니다. 저장 프로시저는 재사용 가능한 SQL 코드 블록으로, 데이터베이스 작업을 자동화하거나 복잡한 작업을 단순화하는 데 매우 유용합니다.

CREATE PROCEDURE란?

MySQL의 CREATE PROCEDURE는 하나 이상의 SQL 문으로 구성된 이름이 지정된 프로시저를 생성하는 명령어입니다. 저장 프로시저를 사용하면 반복적인 작업을 줄이고, 복잡한 로직을 쉽게 관리할 수 있습니다. 이는 데이터 무결성 유지, 코드 재사용성 증가, 그리고 애플리케이션 성능 개선에 기여합니다.

CREATE PROCEDURE의 기본 문법

DELIMITER //
CREATE PROCEDURE 프로시저명 (매개변수)
BEGIN
    SQL문;
END //
DELIMITER ;

• DELIMITER: MySQL에서 기본 구분자인 ; 대신 다른 구분자를 설정합니다.
• CREATE PROCEDURE: 저장 프로시저를 정의하는 명령어입니다.
• 프로시저명: 생성할 저장 프로시저의 이름입니다.
• 매개변수(Parameter): 프로시저가 입력받거나 반환하는 값입니다.
• BEGIN ... END: SQL 문 블록을 정의하는 구문입니다.

매개변수의 유형

1. IN: 호출 시 값을 전달합니다(기본값).
2. OUT: 호출 후 값을 반환합니다.
3. INOUT: 값을 전달받고 반환할 수 있습니다.

반응형

예제: 간단한 저장 프로시저 생성

아래는 두 개의 숫자를 입력받아 합계를 출력하는 간단한 저장 프로시저입니다.

DELIMITER //
CREATE PROCEDURE AddNumbers(IN num1 INT, IN num2 INT, OUT sum INT)
BEGIN
    SET sum = num1 + num2;
END //
DELIMITER ;

호출 예제

CALL AddNumbers(10, 20, @result);
SELECT @result;

결과는 @result에 저장되어 30이 출력됩니다.

실제 비즈니스에서의 활용 예

1. 판매 데이터 분석

주어진 기간 동안의 총 판매액을 계산하는 프로시저를 생성할 수 있습니다.

DELIMITER //
CREATE PROCEDURE TotalSales(IN startDate DATE, IN endDate DATE, OUT total DECIMAL(10,2))
BEGIN
    SELECT SUM(amount) INTO total
    FROM sales
    WHERE sale_date BETWEEN startDate AND endDate;
END //
DELIMITER ;

2. 재고 업데이트

특정 상품의 재고 수량을 업데이트하는 프로시저입니다.

DELIMITER //
CREATE PROCEDURE UpdateStock(IN productId INT, IN newStock INT)
BEGIN
    UPDATE products
    SET stock = newStock
    WHERE id = productId;
END //
DELIMITER ;

반응형

CREATE PROCEDURE의 장점

• 반복 작업 자동화: 동일한 작업을 반복적으로 수행해야 할 때 코드 재사용성을 높여줍니다.
• 복잡한 로직 캡슐화: 여러 SQL 문을 단일 엔드포인트로 처리할 수 있어 유지보수성을 높입니다.
• 보안성 향상: 저장 프로시저 내부에서 데이터 접근을 제한하여 데이터 무결성을 보호할 수 있습니다.
• 성능 최적화: 클라이언트와 서버 간의 데이터 전송을 줄이고, 실행 계획을 캐싱하여 속도를 높입니다.

저장 프로시저 관리

• 프로시저 수정: 저장 프로시저는 CREATE PROCEDURE 명령어로 수정할 수 없습니다. 대신 삭제한 후 다시 생성해야 합니다.
• 삭제: 저장 프로시저를 삭제하려면 DROP PROCEDURE 프로시저명을 사용합니다.

예제: 저장 프로시저를 활용한 사용자 관리

아래는 새로운 사용자를 데이터베이스에 추가하는 저장 프로시저입니다.

DELIMITER //
CREATE PROCEDURE AddUser(IN username VARCHAR(50), IN email VARCHAR(100))
BEGIN
    INSERT INTO users (username, email, created_at)
    VALUES (username, email, NOW());
END //
DELIMITER ;

호출 방법

CALL AddUser('john_doe', 'john@example.com');

결론

MySQL의 CREATE PROCEDURE는 데이터베이스 관리에서 필수적인 도구입니다. 이를 활용하면 SQL 코드 재사용성, 자동화된 데이터 처리, 그리고 애플리케이션 성능을 개선할 수 있습니다. 다양한 매개변수와 SQL 문을 활용해 복잡한 비즈니스 로직을 단순화할 수 있으며, 이를 통해 데이터베이스 작업 효율을 극대화할 수 있습니다.

더 자세한 내용은 MySQL 공식 문서를 참고하세요: https://dev.mysql.com/doc/refman/8.0/en/