Stored Procedure (프로시저)

 프로시저

어떠한 동작을 일괄 처리하기 위한 용도이다. 쿼리문 처리를 할 때 바로 처리하지 않고 값을 받아와 프로시저 내부에서 일괄적으로 처리한 후 결과를 내보내 주기 위한 루틴이다.

 

 

 

 

프로시저의 특징

자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때만 호출하면 편리하다. 
내가 필요한 만큼 응용해서 사용할 수 있기 때문에 MySQL 운영에 편리하다.

 

 

 

 

프로시저의 단점

 · 유지 보수 복잡성 증가
 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산되어 관리하기 때문에 애플리케이션의 설치나 배포가 더 복잡해진다. 사용자가 감당할 수 있는 부분에 관하여 적절하게 사용해야 한다.

 

 

 

 

프로시저 형식의 정의

 · 프로시저 문법
$$  $$  : 괄호 개념으로 내부 부분이 프로시저 이다.
call 프로시저이름(변수);  : 정의했던 프로시저의 일련작업들이 수행된다.

 

 

 

 

 

프로시저 실습

프로시저

이름 : sp_search_member

코멘트 : 회원의 이름을 입력받아 회원의 정보를 조회하는 프로시저

 

BEGIN
	select 
		m.*
	from
		tb_member as m
	where
		m.m_name like concat('%', mName, '%');
END

CALL sp_search_member('홍1');

 

 

실습1

 

 

실습2

 

 

실습3

BEGIN
	/* 변수를 선언 declare변수명 데이터타입(길이)*/
	/* private int memberLevel */
	declare memberLevel int;
	/* memberLevel =0; */
	set memberLevel := 0;
	/* int memberLevel =0; 선언과 동시에 초기화 */
	/* declare memberLevel int defaut 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 (memberid = 2) then 
		select '판매자' as '권한';
	elseif (memberLevel =3) then
		select '구매자' as '권한';
	else
		select '일반회원' as '권한';
	end if;
END

 

 

실습4

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

 

 

실습5

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

 

 

실습6

 

 

실습7. loop

BEGIN
	/* 변수 선언 및 초기화 */
	declare totalSum int default 0;
	/* 반복문 사용할 변수 증가값 */
	declare i int default 0;
	
loop_sum	: loop 
					/* for break */
					if(i = intValue) then 
						leave loop_sum;
					end if;
					set i := i+1;
					set totalSum := totalSum + i; 
				end loop;
	select totalSum as '총합계';	
END

 

 

실습8. loop2

 

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

 

 

실습9

BEGIN
	/* 구구단 식*/
	DECLARE timesVar VARCHAR(10);
	/* 증가변수 */
	DECLARE i INT DEFAULT 0;
	/* 구구단의 결과 값 */
	DECLARE result_times INT;
	
	/* 테이블이 있다면 테이블을 삭제 */
	DROP TABLE IF EXISTS tb_times;
	
	/* 테이블 생성*/
	CREATE TABLE IF NOT EXISTS tb_times(
		 times VARCHAR(10) NOT NULL COMMENT '단'
		,result INT COMMENT '결과값'
	);
	
	/* 구구단*/
loop_times	:  LOOP
						IF (i = 9) THEN
							LEAVE loop_times;
						END IF;
						SET i := i + 1;
						SET timesVar := CONCAT(intValue, ' X ', i);
						SET result_times := intValue * i;
						
						INSERT INTO tb_times 
						(times, result)
						VALUES 
						(timesVar, result_times);
					END LOOP;
	SELECT * FROM tb_times;
END

 

 

loop에서 무한루프 걸릴 경우, 윈도우 -> 서비스 -> MYSQL -> 중지 누르고 다시 시도!

무한루프시

 

 

 

1. 프로시저 실습

BEGIN
	declare resultTimes text;
	declare i int;
	declare k int;
	if (intValue < 2 ) then
		select '2이상의 값을 입력해주세요';
	else 
		drop table if exists tb_gugu;
		create table if not exists tb_gugu(
			result TEXT COMMENT '구구단 결과'
		);
		
		set i := 2;
		while ( i <= intValue ) do			
			set resultTimes :='';
			set k := 1;
			
			while ( k < 10 ) do
			
				if ( (i*k) <10 ) then 
					set resultTimes := concat(resultTimes, '   ', i, 'X', k, '=0', (i*k));
				else 
					set resultTimes := concat(resultTimes, '   ', i, 'X', k, '=', (i*k));
				end if;
				
				set k := k + 1;
			end while;
			
			INSERT INTO tb_gugu(result) values (resultTimes);
						
			set i := i + 1;			
		end while;
		
		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