[MySQL] 프로시저 (Procedure)

---

프로시저

 

● 프로시저 생성 및 호출

# 프로시저 생성
DELIMITER $$
CREATE PROCEDURE 프로시저명()
BEGIN
	SELECT
END $$
DELIMITER ;

# 프로시저 호출
CALL 프로시저명();

 

● DELIMITER 사용 이유

프로시저 안에는 세미콜론이 여러개 있기 때문에 세미콜론 단위로 명령을 읽어들임 → 에러발생!

프로시저를 통으로 명령문이라 인식할수 있도록 구분하기 위함

 

● 매개변수 모드

IN	프로시저로 전달 하는 값
OUT	프로시저에서 반환 하는 값
INOUT	IN과 OUT 기능을 둘다 함

 

● 변수 선언 및 값 할당

# 변수 선언
DECLARE 변수명 데이터타입;

# 값 할당
SET 변수명 = 값;

 

● 조건문

① IF 문

# IF문
IF 조건 THEN
  -- A;
END IF;

# IF ~ ELSE문
IF 조건 THEN
  -- A;
ELSE
  -- B;
END IF;

# IF ~ ELSE IF문
IF 조건 THEN
  -- A;
ELSEIF 조건 THEN
  -- B;
...
ELSE
  -- N;
END IF;

예제) 점수를 입력받아 학점을 출력하시오

DELIMITER $$
CREATE PROCEDURE getGrade(IN score INT, OUT grade VARCHAR(10))
BEGIN
	IF(score BETWEEN 90 AND 100) THEN
		SET grade = 'A';
	ELSEIF(score >= 80) THEN
		SET grade = 'B';
	ELSEIF(score >= 70) THEN
		SET grade = 'C';
	ELSEIF(score >= 60) THEN
		SET grade = 'D';
	ELSE
		SET grade = 'F';
	END IF;
END $$

CALL getGrade(95, @grade);
SELECT @grade;

 

② CASE 문

# 값만 비교할 경우
CASE 변수
	WHEN 값1 THEN
	   -- A;
	WHEN 값2 THEN
	   -- B;
	...
	ELSE
	   -- N;
END CASE;

# 조건을 비교 할 경우
CASE
	WHEN 조건1 THEN
	   -- A;
	WHEN 조건2 THEN
	   -- B;
	...
	ELSE
	   -- N;
END CASE;

예제) 수량, 단가를 입력받아 금액(수량*단가), 세금(10%), 총액(금액+세금) 계산하고, 총액의 구간별 등급을 출력하시오

DELIMITER $$
CREATE PROCEDURE getCustomerLevel(IN quantity int, IN unitprice int)
BEGIN
	DECLARE amount int;
	DECLARE tax int;
	DECLARE total int;

	SET amount = quantity * unitprice;
	SET tax = amount * 0.1;
	SET total = amount + tax;
	
	CASE 
		WHEN total >= 1000000 THEN SELECT '최우수 고객'; 
		WHEN total >= 100000 THEN SELECT '우수 고객'; 
		WHEN total >= 10000 THEN SELECT '고객';
		ELSE SELECT '자주 이용해주세요';
	END CASE;
END $$

CALL getCustomerLevel(99, 9800);  -- 최우수 고객

 

● 반복문

# 1.변수선언
DECLARE finished INT DEFAULT 0;
DECLARE _col1 데이터타입;
DECLARE _col2 데이터타입;

# 2.커서 선언
DECLARE 커서명 CURSOR FOR SELECT col1, col2 FROM 테이블명;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

# 3.커서 오픈
OPEN 커서명;
	
	result : LOOP
		FETCH 커서명 INTO _col1, _col2;
    
		IF finished = 1 THEN 
			LEAVE result;
		END IF;
    
		SELECT _col1, _col2;
	END LOOP;
    
CLOSE 커서명;

예제) 직원들의 이메일 정보를 세미콜론( ; )으로 구분지어 출력하시오

DELIMITER $$
CREATE PROCEDURE getEmpEmailList(
	INOUT emailList VARCHAR(4000)
)
BEGIN		
	DECLARE finished INT DEFAULT 0;
	DECLARE emailAddress VARCHAR(100) DEFAULT '';
	
	DECLARE curEmail CURSOR FOR 
		SELECT email FROM classicmodels.employees;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

	OPEN curEmail;
		rs : LOOP
			FETCH curEmail INTO emailAddress;
			IF finished = 1 THEN
				LEAVE rs;
			END IF;
			# 문자열 결합시 한개라도 NULL이 있다면 NULL을 반환 → IFNULL(값, '') 처리
			SET emailList = CONCAT(emailAddress, ';', IFNULL(emailList, ''));
		END LOOP rs;
	CLOSE curEmail;
END $$

CALL getEmpEmailList(@elist);
SELECT @elist;