[MariaDB] Procedure 생성, 호출, 활용

Stored Procedure란?

💡 Stored procedure는 일련의 SQL 문장을 저장하고 실행할 수 있도록 하는 기능이다. 이를 통해 반복적인 작업을 자동화하고, 데이터베이스 서버에서 직접 실행되므로 클라이언트-서버 간의 네트워크 트래픽을 줄일 수 있다.

Stored procedure는 프로그래밍 언어와 유사한 구조를 가지며, 변수 선언, 조건문, 반복문 등을 사용할 수 있다.

Procedure의 사용 목적

• Stored procedure는 보안성과 성능을 향상시키는 데 기여한다. 이를 통해 데이터베이스 접근 권한을 세밀하게 제어할 수 있다.
  • 특정 사용자에게 특정 stored procedure만 실행할 수 있는 권한을 부여하는 것이 가능하다.
• 여러 SQL 문을 하나의 단위로 묶어 실행함으로써, 데이터베이스 서버의 작업 부하를 줄이고 성능을 최적화할 수 있다.

Procedure 생성

• BEGIN과 END 사이에 수행해야 할 일련의 구문을 작성한다.
• DELIMITER는 SQL 명령어를 구분하기 위해 사용한다.
• 끝났다는 표현으로 //를 사용한다.

DELIMITER //

CREATE PROCEDURE getAllEployee()
BEGIN
		SELECT emp_id, emp_name, salary
 			FROM employee;
END //

DELIMITER;

Procedure 호출

CALL getAllEmployee();

IN 매개변수

• IN 매개변수를 사용하는 프로시저는 호출 시 전달된 값을 사용한다.

예제 : 특정 부서의 직원 정보 조회 프로시저

-- 프로시저 생성
DELIMITER //

CREATE PROCEDURE getAllEmployeesByDepartment(
	IN dept CHAR(2)
)
BEGIN
		SELECT emp_id, emp_name, salary
 			FROM employee
		 WHERE dept_code = dept;
END //

DELIMITER ;

-- 프로시저 호출
CALL getAllEmployeesByDepartment('D9');

OUT 매개변수

• OUT 매개변수는 결과 값을 호출한 곳으로 반환한다.
• 예제 : 특정 직원의 급여를 반환하는 프로시저

-- 프로시저 생성
DELIMITER //

CREATE PROCEDURE getEmployeeSalary(
	IN id VARCHAR(3),
	OUT sal DECIMAL(10, 2)
)
BEGIN
		SELECT salary INTO sal
			FROM employee
		 WHERE emp_id = id;
END //

DELIMITER ;

-- 프로시저 호출
SET @sal = 0;
CALL getEmployeeSalary('201');
SELECT @sal;

• @ 기호는 사용자 변수(user variable)를 나타낸다. 사용자 변수는 SQL 문장에서 동적으로 값을 저장하고 사용할 수 있는 변수이다.
  • 동적 할당: 사용자 변수는 동적으로 생성되며, 특정 데이터 유형을 선언할 필요 없이 값을 할당할 수 있다.
  • 세션 범위: 사용자 변수는 세션 범위로 정의된다. 즉, 해당 변수를 설정한 세션 내에서만 유효하며, 세션이 종료되면 변수도 사라진다.
  • 기본값 없음: 초기화되지 않은 사용자 변수는 NULL 값을 가진다.
  • 접두사 @ 사용: 사용자 변수를 정의하고 사용할 때는 항상 @ 기호를 사용한다.

INOUT 매개변수

• 예제 : 특정 직원의 급여를 증가시키고 증가된 급여(보너스 포함) 반환`

-- 프로시저 생성
DELIMITER //

CREATE PROCEDURE updateAndReturnSalary(
	IN id VARCHAR(3),
	INOUT sal DECIMAL(10, 2)
)
BEGIN
		UPDATE employee
			SET salary = sal
		 WHERE emp_id = id;
		 
		SELECT salary + (salary + IFNULL(bonus, 0)) INTO sal
			FROM employee
		 WHERE emp_id = id;
END //

DELIMITER ;

-- 프로시저 호출
SET @new_sal = 9000000;
CALL updateAndReturnSalary('200', @new_sal);
SELECT @new_sal;

IF-ELSE 활용

• 기본 형식

IF 조건 THEN
    -- 조건이 참일 때 실행될 문장
ELSE
    -- 조건이 거짓일 때 실행될 문장
END IF;

• 예제 : 특정 직원의 급여가 특정 값보다 높은지 여부를 확인

-- 프로시저 생성
DELIMITER //

CREATE PROCEDURE checkEmployeeSalary(
	IN id VARCHAR(3),
	IN threshold DECIMAL(10, 2),
	OUT result VARCHAR(50)
)
BEGIN
		-- 내부에서 사용할 별도의 변수 선언
		DECLARE sal DECIMAL(10, 2);
		 
		SELECT salary INTO sal
			FROM employee
		 WHERE emp_id = id;
		
		IF sal > threshold THEN
			SET result = '기준치를 넘는 급여입니다.';
		ELSE 
			SET result = '기준치와 같거나 기준치 이하의 급여입니다.';
		END IF;
END //

DELIMITER ;

-- 프로시저 호출(1)
SET @result = '';
CALL checkEmployeeSalary('200', 3000000, @result);
SELECT @result;

-- 프로시저 호출(2)
SET @result = '';
CALL checkEmployeeSalary('200', 10000000, @result);
SELECT @result;

CASE 활용

• 기본 형식

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ...
    ELSE 결과N
END CASE;

• 예제 : 특정 직원의 부서를 전달받아 메시지 확인

-- 프로시저 생성
DELIMITER //

CREATE PROCEDURE getDepartmentMessage(
	IN id VARCHAR(3),
	OUT message VARCHAR(50)
)
BEGIN
		-- 내부에서 사용할 별도의 변수 선언
		DECLARE dept VARCHAR(50);
		 
		SELECT dept_code INTO dept
			FROM employee
		 WHERE emp_id = id;
		
		CASE
			WHEN dept = 'D1' THEN
				SET message = '인사관리부 직원이시군요!';
			WHEN dept = 'D2' THEN
				SET message = '회계관리부 직원이시군요!';
			WHEN dept = 'D3' THEN
				SET message = '마케팅부 직원이시군요!';
			ELSE
				SET message = '어떤 부서 직원인지 모르겠어요!';
		END CASE;	
END //

DELIMITER ;

-- 프로시저 호출
SET @message = '';
CALL getDepartmentMessage('221', @message);
SELECT @message;

WHILE 활용

• 기본 형식

WHILE 조건 DO
    -- 실행할 SQL 문들
END WHILE;

• 예제 : 특정 직원의 부서를 전달받아 메시지 확인`

-- 프로시저 생성
DELIMITER //

CREATE PROCEDURE calculateSumUpTo(
	IN max_num INT,
	OUT sum_result INT
)
BEGIN
		DECLARE current_num INT DEFAULT 1;
		DECLARE total_sum INT DEFAULT 0;
		 
		WHILE current_num <= max_num DO
			SET total_sum = total_sum + current_num;
			SET current_num = current_num + 1;
		END WHILE;
		
		SET sum_result = total_sum;
		
END //

DELIMITER ;

-- 프로시저 호출
SET @sum_result = 0;
CALL calculateSumUpTo(100, @sum_result);
SELECT @sum_result;

예외처리

• 기본 형식

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- 예외 처리 코드
END;

• 예제

DELIMITER //

CREATE PROCEDURE divideNumbers(
	IN numerator DOUBLE,
	IN denominator DOUBLE,
	OUT result DOUBLE 
)
BEGIN
	DECLARE division_by_zero CONDITION FOR SQLSTATE '45000';

	DECLARE EXIT HANDLER FOR division_by_zero
	BEGIN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '0으로 나눌 수 없습니다.';
	END;
	
	IF denominator = 0 THEN
		SIGNAL division_by_zero;
	ELSE
		SET result = numerator / denominator;
	END IF;
END //

DELIMITER ;

-- 프로시저 호출 (예외발생 X)
CALL divideNumbers(10, 2, @result);

-- 프로시저 호출 (예외발생 O)
SELECT @result;