[MySQL] 프로시저 매개변수 값 입력

프로시저 실행 시 조건 값 변경
저장된 프로시저 수행 시 수행 질의문에 특정 값을 매개변수로 전달할 수 있도록 하여
다양한 조건을 하나의 질의문으로 수행할 수 있게 지원해줌

MySQL의 샘플 데이터베이스 employees를 활용한 예제

https://github.com/datacharmer/test_db

GitHub - datacharmer/test_db: A sample MySQL database with an integrated test suite, used to test your applications and database

 

employees 테이블 10001번 직원의 입사일이 5년 경과하였는지 여부를 확인

위의 자료에서 emp_no 로 조회하여 hire_date 와 오늘 날짜를 비교 후 판단

 

예제1) 매개변수를 입력받지 않는 프로시저

DELIMITER $$
CREATE PROCEDURE fiveOrNot()
BEGIN
    DECLARE empNum INT;
    DECLARE hireDate DATE;			# 입사일
    DECLARE todayDate DATE;			# 오늘날짜
    DECLARE hDays INT;				# 입사일부터 오늘날짜까지의 경과 일수 변수
    
    SET empNum = 10001;
    SELECT hire_date INTO hireDate FROM employees WHERE emp_no = empNum;
    SET todayDate = CURDATE();
    SET hDays = DATEDIFF(todayDate, hireDate);
    
    IF(hDays/365 >= 5) THEN
    	SELECT CONCAT(hDays, '일째 일하는 중으로 5년이 경과되었습니다.') AS 결과;
    ELSE
    	SELECT CONCAT(hDays, '일째 일하는 중으로 5년이 경과되지 않았습니다.') AS 결과;
    END IF;
END $$
DELIMITER ;

CURDATE(): 오늘날짜를 반환하는 함수

DAYDIFF(날짜1, 날짜2): 날짜1 - 날짜2

프로시저 내부에서 조회할 직원의 사번을 미리 입력해두기 때문에 다른 직원을 조회하려면 사번을 변경한 프로시저를 새로 생성해야함

 

호출

CALL fiveOrNot();

---

예제2) 매개변수를 입력받아 다양한 사번 조회가 가능한 프로시저

문법

DELIMITER $$
CREATE PROCEDURE 프로시저명(IN 파라미터명 데이터타입(크기))
BEGIN
	실행 코드
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE fiveOrNot2(IN empNum INT)
BEGIN
    DECLARE hireDate DATE;
    DECLARE todayDate DATE;
    DECLARE hDays INT;
    
    SELECT hire_date INTO hireDate FROM employees WHERE emp_no = empNum;
    SET todayDate = CURDATE();
    SET hDays = DAYDIFF(todayDate, hireDate);
    
    IF (hDays/365 >= 5) THEN
    	SELECT CONCAT(hDays, '일째 일하는 중으로 5년이 경과되었습니다.') AS 결과;
    ELSE
    	SELECT CONCAT(hDays, '일째 일하는 중으로 5년이 경과되지 않았습니다.') AS 결과;
    END IF;
END $$
DELIMITER ;

파라미터 내부에 입력된 값마다 다르게 처리하면서 활용도가 높아짐

 

호출

CALL fiveOrNot2(매개변수 값);

생성한 프로시저 호출시 파라미터 내부에 내용이 입력되지 않으면 에러가 발생