MySQL - 프로시저

DBMS

MySQL - 프로시저

전주천둥새 2023. 5. 15. 15:45

1. 프로시저(Procedure)

1) 프로시저의 특징

- 어떠한 동작을 일괄 처리하기 위한 용도

- 자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때만 호출

- MySQL 운영에 편리

2) 프로시저의 단점

- 유지 보수 복잡성 증가

- 애플리케이션의 설치나 배포가 더 복잡해짐 (각 기능을 담당하는 프로그램 코드가

자바와 MySQL Stored 프로그램으로 분산되어 관리하기 때문에)

3) 프로시저 생성 및 실행

1. 생성할 db를 선택하고 우클릭하여 저장루틴 새로생성

2. 프로시저명과 추가할 매개변수, 데이터 유형, 문맥 등을 설정하기

3. 루틴 본문에 쿼리구문 작성

>>> 프로시저 루틴 본문의 형태는 'BEGIN 쿼리구문 END' 형태로 작성된다.

4. CALL 프로시저명(매개변수 값); 형태로 프로시저 호출

*** 프로시저 실습 ***

1. 제어문 IF 실습

▼ 프로시저 생성

▼ 프로시저 루틴 본문 작성

BEGIN
	/* 변수를 선언 (DECLARE 변수명 데이터타입(길이)) */
	/* private int memberLevel과 동일 */
	DECLARE memberLevel INT;
	/* memberLevel = 0 과 동일 */
	SET memberLevel := 0;
	/* int memberLevel = 0 과 동일(초기선언부터 초기값 설정) */
	/* > DECLARE memberLevel INT DEFAULT 0; */
	SELECT
		m.m_level INTO memberLevel
	FROM
		tb_member AS m
	WHERE
		m.m_id = memberId;
	/* PL/SQL IF 조건문 */	
	IF (memberLevel = 1) THEN
		SELECT '관리자' AS '권한';
	ELSEIF (memberLevel = 2) THEN
		SELECT '판매자' AS '권한';
	ELSEIF (memberLevel = 3) THEN	
		SELECT '구매자' AS '권한';
	ELSE
		SELECT '일반회원' AS '권한';
	END IF;
END

▼ 호출 후 결과 확인

2. 제어문 CASE 실습

▼ 프로시저 생성

▼ 프로시저 루틴 본문 작성

BEGIN
	/* 변수를 선언 (DECLARE 변수명 데이터타입(길이)) */
	/* private int memberLevel과 동일 */
	/* > DECLARE memberLevel INT; */
	/* memberLevel = 0 과 동일 */
	/* SET memberLevel := 0; */
	/* int memberLevel = 0 과 동일(변수 선언과 동시에 초기화) */
	DECLARE memberLevel INT DEFAULT 0;
	SELECT
		m.m_level INTO memberLevel
	FROM
		tb_member AS m
	WHERE
		m.m_id = memberId;
	/* PL/SQL CASE 조건문 */	
	CASE 
	WHEN (memberLevel = 1) THEN
		SELECT '관리자' AS '권한';
	WHEN (memberLevel = 2) THEN
		SELECT '판매자' AS '권한';
	WHEN (memberLevel = 3) THEN	
		SELECT '구매자' AS '권한';
	ELSE
		SELECT '일반회원' AS '권한';
	END CASE;
END

▼ 호출 후 결과 확인

3. 반복문 WHILE 실습

▼ 프로시저 생성

▼ 프로시저 루틴 본문 작성

BEGIN
	/* 변수 선언 초기화 */
	DECLARE totalSum INT DEFAULT 0;
	/* 증가하는 변수 선언 */
	DECLARE i INT DEFAULT 0;
	
	/* PL/SQL WHILE 구문 */
	WHILE (i<intValue) DO
		SET i := i + 1;
		SET totalSum := totalSum + i;
	END WHILE;
	
	SELECT totalSum AS '총합계';
END

▼ 호출 후 결과 확인

3-1. IF조건문을 포함한 반복문 WHILE 실습(ITERATE)

▼ 프로시저 루틴 본문 작성

BEGIN
	DECLARE totalSum INT DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	
	while_sum : WHILE (i < intValue) DO
						SET i:= i+1;
						/* Java for문의 continue와 동일 */
						IF(i=2) THEN
							ITERATE while_sum;
						END IF;
						SET totalSum := totalSum+i;
					END WHILE;
	SELECT totalSum AS '2를 제외한 총합';				
END

▼ 호출 후 결과 확인

4. 반복문 LOOP 실습

▼ 프로시저 생성

▼ 프로시저 루틴 본문 작성

BEGIN
	/* 변수 선언 및 초기화 */
	DECLARE totalSum INT DEFAULT 0;
	/* 반복문 사용할 변수 증가 값 */
	DECLARE i INT DEFAULT 0;
	
loop_sum : LOOP
					/* 빠져나갈 조건식 설정(미설정시 무한반복 발생) => break문과 같다 */
					IF(i = intValue) THEN
						LEAVE loop_sum;
					END IF;	
					SET i:= i + 1;
					SET totalSum := totalSum + i;
				END LOOP;
	SELECT totalSum AS '총합계';			
END

▼ 호출 후 결과 확인

4-1. IF 조건문을 포함한 반복문 LOOP 실습

▼ 프로시저 루틴 본문 작성

BEGIN
	/* 변수 선언 */
	DECLARE totalSum INT;
	/* 반복문 증가 변수 선언과 동시에 초기화 */
	DECLARE i INT DEFAULT 0;
	/* 변수 초기화 및 할당 */
	SET totalSum := 0;
	
	loop_sum : LOOP
						IF(i = intValue) THEN
							LEAVE loop_sum;
						END IF;
						SET i := i + 1;
						IF (i <> 2) THEN
							SET totalSum := totalSum + i;
						END IF;	
					END LOOP;
	SELECT totalSum AS '2를 제외한 총 합계';				
END

▼ 호출 후 결과 확인

5. 예외처리(Overflow)

▼ 해당 예외처리 구문만 익혀두자

***유의사항***

>>> IF, CASE 등 구문 사용시 END IF, END CASE 를 꼭 기입하고 변수 선언에 유의하며 작성할 것

*** 프로시저 실습문제풀이 ***

문제1. 사용자에게 숫자를 입력 받아 구구단의 결과를 tb_gugu에 삽입하는 프로시저를 정의하고 호출하시오

BEGIN
	DECLARE resultgugu TEXT;
	DECLARE i INT;
	DECLARE j INT;
	IF (intValue < 2) THEN 
		SELECT '2이상의 값을 입력해주세요';
	ELSE
		/* 테이블이 존재하면 DROP 하고 존재하지 않으면 CREATE 한다. */
		DROP TABLE IF EXISTS tb_gugu;
		CREATE TABLE IF NOT EXISTS tb_gugu(
			result TEXT COMMENT '구구단 결과'
		);
		/* 증가하는 변수i의 초기값 설정후 WHILE 반복문 실행 */
		SET i := 2;
		WHILE ( i <= intValue ) DO
			/* WHILE문이 한번 반복할 때 마다 변수 resultgugu와 j값을 초기화 */
			SET resultgugu := '';
			SET j := 1;
			/* 조건에 부합할 때까지 중첩 WHILE 반복문 실행 */
			WHILE ( j < 10) DO
				/* 증가하는 변수 i와 j의 계산 결과를 문자열로 변환하여 resultgugu변수에 대입 */
				IF ( (i*j) < 10 )	THEN
					SET resultgugu := CONCAT(resultgugu, '     ', i, 'X', j, '=0', (i*j)); 
				ELSE
					SET resultgugu := CONCAT(resultgugu, '     ', i, 'X', j, '=', (i*j));
				END IF;
				/* 증가하는 변수 j의 증가값 설정 */
				SET j := j + 1;
			END WHILE;
			/* INSERT 쿼리 실행 */
			INSERT INTO tb_gugu (result) VALUES (resultgugu);
			/* 증가하는 변수 i의 증가값 설정(초기값을 2로설정하였기 때문에 반복문이 끝날때 증가) */
			SET i := i + 1;	
		END WHILE;
		/* SELECT 쿼리 실행하여 tb_gugu에 저장된 값 출력 */
		SELECT * FROM tb_gugu; 
	END IF;
END

문제2. 사용자에게 정수를 입력받아 해당 정수의 약수를 구하고 약수의 합계를 출력하는 프로시저를 작성해라.

BEGIN
	DECLARE divisors TEXT DEFAULT '';
	DECLARE totalDivisor INT DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	
	WHILE ( i <= intValue) DO
		IF ((intValue % i) = 0) THEN
			IF(i=1) THEN
				SET divisors := '1'; 
			ELSE
				SET divisors := CONCAT(divisors, ', ', i);
			END IF;
			SET totalDivisor := totalDivisor + i;
		END IF;
		SET i := i + 1;
	END WHILE;
	
	SELECT divisors AS '약수', totalDivisor AS '약수들의 합';
END