Mysql, MariaDB 프로시저 만드는 방법

MySQL에서 프로시저를 만드는 방법을 단계별로 설명하겠습니다. 저장 프로시저는 데이터베이스에서 자주 사용하는 쿼리나 로직을 미리 작성해두고, 필요할 때마다 호출해서 사용할 수 있도록 하는 유용한 기능입니다. 여기서는 간단한 예제를 통해 저장 프로시저를 만드는 방법을 설명하겠습니다.

1. 기본 저장 프로시저 만들기

우선, 가장 기본적인 형태의 저장 프로시저를 만들어보겠습니다. 이 예제에서는 employees 테이블의 모든 데이터를 가져오는 간단한 저장 프로시저를 만들 것입니다.

테이블 생성 및 데이터 삽입

먼저, 예제로 사용할 employees 테이블을 생성하고 데이터를 삽입합니다.

sql코드 복사-- employees 테이블 생성
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

-- 데이터 삽입
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES
(1, 'John', 'Doe', 1),
(2, 'Jane', 'Smith', 2),
(3, 'Jim', 'Beam', 1);

기본 저장 프로시저 생성

이제, employees 테이블의 모든 데이터를 선택하는 저장 프로시저를 만들어보겠습니다.

sql코드 복사DELIMITER //

CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //

DELIMITER ;

이 예제에서 중요한 부분은 다음과 같습니다:

• DELIMITER //: 기본 구분자인 ; 대신 //를 사용하여 저장 프로시저의 끝을 구분합니다.

• CREATE PROCEDURE GetAllEmployees(): GetAllEmployees라는 이름의 저장 프로시저를 만듭니다.

• BEGIN ... END: 저장 프로시저의 본문을 정의합니다.

저장 프로시저 실행

저장 프로시저를 호출하여 결과를 확인합니다.

sql코드 복사CALL GetAllEmployees();

2. 파라미터가 있는 저장 프로시저 만들기

이제, 입력 파라미터를 사용하여 특정 부서의 직원만 선택하는 저장 프로시저를 만들어보겠습니다.

sql코드 복사DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT 
        employee_id,
        first_name,
        last_name
    FROM 
        employees
    WHERE 
        department_id = dept_id;
END //

DELIMITER ;

이 예제에서 중요한 부분은 다음과 같습니다:

• IN dept_id INT: dept_id라는 이름의 정수형 입력 파라미터를 정의합니다.

• WHERE department_id = dept_id: 입력 파라미터를 사용하여 조건을 설정합니다.

저장 프로시저 실행

저장 프로시저를 호출하여 특정 부서의 직원을 선택합니다.

sql코드 복사CALL GetEmployeesByDepartment(1);

3. 복잡한 로직이 포함된 저장 프로시저 만들기

이제, 여러 개의 쿼리와 제어문이 포함된 좀 더 복잡한 저장 프로시저를 만들어보겠습니다. 예를 들어, 특정 부서의 직원 수를 계산하고, 직원 목록을 출력하는 프로시저를 만들어보겠습니다.

sql코드 복사DELIMITER //

CREATE PROCEDURE GetDepartmentSummary(IN dept_id INT)
BEGIN
    DECLARE emp_count INT;

    -- 특정 부서의 직원 수 계산
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = dept_id;

    -- 직원 수 출력
    SELECT CONCAT('Department ', dept_id, ' has ', emp_count, ' employees.') AS summary;

    -- 직원 목록 출력
    SELECT 
        employee_id,
        first_name,
        last_name
    FROM 
        employees
    WHERE 
        department_id = dept_id;
END //

DELIMITER ;

이 예제에서 중요한 부분은 다음과 같습니다:

• DECLARE emp_count INT: emp_count라는 이름의 정수형 변수를 선언합니다.

• SELECT COUNT(*) INTO emp_count: 특정 부서의 직원 수를 계산하여 변수에 저장합니다.

• SELECT CONCAT(...) AS summary: 직원 수를 요약하여 출력합니다.

• 여러 개의 쿼리를 사용하여 로직을 구현합니다.

저장 프로시저 실행

저장 프로시저를 호출하여 특정 부서의 직원 수와 목록을 확인합니다.

sql코드 복사CALL GetDepartmentSummary(1);

요약

• 기본 저장 프로시저: 데이터를 선택하는 간단한 프로시저

• 파라미터가 있는 저장 프로시저: 입력 파라미터를 사용하여 조건을 설정하는 프로시저

• 복잡한 로직이 포함된 저장 프로시저: 변수 선언, 제어문, 여러 쿼리를 포함한 프로시저