MySQL 프로시저(Procedure) 단계별로 작성해보기

프로시저란?

데이터베이스 관련 작업을 수행하는 일련의 SQL 명령어들을 하나의 단위로 묶은 것

 

1) 장점

- 코드의 재사용성을 향상한다.

- 코드의 복잡성을 낮춘다.

 

실습해 보기

공통사항

- DELIMITER //는 MySQL에서 프로시저를 생성할 때 사용하는 구문이다.
이는 기본적인 SQL 문장 구분자인 세미콜론(;)이 프로시저 내부에서도 사용되기 때문에, 프로시저의 시작과 끝을 명확히 구분하기 위해 일시적으로 문장 구분자를 //로 사용한다.
- CREATE PROCEDURE HelloWorld()는 HelloWorld라는 이름의 새로운 프로시저를 만든다.
괄호 내부에는 매개변수가 들어갈 수도 있다.
- BEGIN과 END 사이에 있는 부분에 프로시저가 수행할 SQL 문장들을 넣는다.
- DELIMITER ;는 문장 구분자를 원래의 세미콜론(;)을 사용해서 마친다.

 

간단한 HelloWorld 프로시저

DELIMITER //
CREATE PROCEDURE HelloWorld()
BEGIN
  SELECT 'Hello, World!';
END//
DELIMITER ;

# 프로시저 실행
CALL HelloWorld();

 

입력 매개변수를 가진 프로시저

DELIMITER //
CREATE PROCEDURE GetUser(IN userId INT)
BEGIN
  SELECT name 
  FROM Users 
  WHERE id = userId;
END //
DELIMITER ;

- userId라는 매개변수를 받아 Users 테이블에서 해당 userId를 가진 사용자의 name을 조회하고 출력한다.

# 프로시저 실행
CALL GreetUser(1);

 

출력 매개변수를 가진 프로시저

DELIMITER //
CREATE PROCEDURE GetTotalEmployees(OUT total INT)
BEGIN
  SELECT COUNT(*)
  INTO total
  FROM employees;
END //
DELIMITER ;

 - employees 테이블의 직원 수를 계산해서 total이라는 출력 매개변수에 저장한다.

# 프로시저 실행
CALL GetTotalEmployees(@total); 
SELECT @total;

 

입력과 출력을 매개변수로 가진 프로시저

DELIMITER //
CREATE PROCEDURE GetUserEmail(IN userId INT, OUT userEmail VARCHAR(255))
BEGIN
  SELECT email
  INTO userEmail
  FROM users
  WHERE id = userId;
END //
DELIMITER ;

- userId라는 입력 매개변수를 받아서 해당 사용자의 이메일 주소를 userEmail이라는 출력 매개변수에 저장한다.

# 프로시저 실행
CALL GetTotalEmployees(1, @total); 
SELECT @total;

 

조건문을 사용하는 프로시저

DELIMITER //
CREATE PROCEDURE UpdateUserStatus(IN userId INT, IN newStatus VARCHAR(20))
BEGIN
  IF newStatus IN ('Active', 'Inactive') THEN
    UPDATE users
    SET status = newStatus
    WHERE id = userId;
  ELSE
    SELECT 'Invalid status!';
  END IF;
END //
DELIMITER ;

- userId와 newStatus를 입력 매개변수로 받는다.

- newStatus가 'Active' 또는 'Inactive'인 경우에만 사용자의 상태를 업데이트하고, 그렇지 않으면 오류 메시지를 출력

 

반복문을 사용하는 프로시저

DELIMITER //
CREATE PROCEDURE PrintNumbers(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= n DO
    SELECT i;
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

- DECLARE: 변수를 선언한다. => i의 기본값은 1이다.

- i가 입력받은 n보다 작거나 같을 때까지 무한 반복한다.

- 현재 i의 값을 출력한다.

- i의 값을 1 증가시킨다.

 

커서를 사용하는 프로시저

DELIMITER //
CREATE PROCEDURE GetAllUserEmails()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE userEmail VARCHAR(255);
  DECLARE cur CURSOR FOR SELECT email FROM users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO userEmail;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT userEmail;
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;

- DECLARE
done이라는 이름의 변수를 선언하고, 기본값을 FALSE로 설정
userEmail이라는 이름의 문자열 변수를 선언
CURSOR를 선언하고, 이를 users 테이블의 모든 email을 선택하는 쿼리에 연결
CONTINUE HANDLER는 특정 조건에서 발생하는 예외를 처리하는 구문으로, NOT FOUND 조건, 즉 커서에서 더 이상 읽을 데이터가 없을 때 done 변수를 TRUE로 설정

- OPEN
선언된 커서를 연다.

- read_loop
LOOP 키워드로 반복문을 시작한다. 이 반복문은 read_loop: 라벨로 표시된다.

- FETCH
커서에서 다음 행을 가져와 userEmail 변수에 저장

- IF
done 변수가 TRUE라면 LEAVE 명령어로 read_loop 반복문을 빠져나온다.

 

예외처리를 사용하는 프로시저

DELIMITER //
CREATE PROCEDURE DivideNumbers(IN num1 FLOAT, IN num2 FLOAT, OUT result FLOAT)
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '22012' -- division by zero
  BEGIN
    SET result = NULL;
    SELECT 'Error: Division by zero is not allowed.';
  END;

  SET result = num1 / num2;
END //
DELIMITER ;

- 두 개의 입력 매개변수 num1과 num2와 하나의 출력 매개변수 result를 가진다.

- SQLSTATE '22012'은 0으로 나누는 경우에 발생하는 예외 코드이다. 이 예외가 발생하면 이 핸들러가 동작하도록 설정한다.

 - 예외가 발생하면 result 변수를 NULL로 설정하고, 에러 메시지를 출력한다.

- num1을 num2로 나눈 값을 result 변수에 저장한다.

CALL DivideNumbers(10, 2, @result); 쿼리를 실행하면,
SELECT @result; 쿼리를 실행하면 5가 출력된다.

CALL DivideNumbers(10, 0, @result); 쿼리를 실행하면,
'Error: Division by zero is not allowed.' 메시지가 출력되고, SELECT @result;의 결과는 NULL이다.