Stored Procedure (프로시저)

  프로시저

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

 

▶ 프로시저의 특징

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

내가 필요한 만큼 응용해서 사용할 수 있기 때문에 MySQL 운영에 편리하다.

 

▶ 프로시저의 단점

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

 

▶ 프로시저 형식의 정의

  - 프로시저 문법

   ☞ $$  $$ : 괄호 개념으로 내부 부분이 프로시저 이다.

   ☞ call 프로시저이름(변수); : 정의했던 프로시저의 일련작업들이 수행된다.

 

 

 - HeidiSQL 에서 프로시저 사용

  ☞ 프로시저 생성

HeidiSQL에서는 사용이 불가능 . (데이터베이스 우클릭 - 새로 생성 - 저장루틴) 클릭시 stored program 사용이 가능하다. 유형부분에서 프로시저를 선택하고, 루틴 본문의 BEGIN ~ END 사이에 작성하면 된다.

 

  ☞ 프로시저 변수 등록

매개변수를 추가 버튼클릭으로 생성하고, 루틴 본문에서 사용한다.

 

  ☞ 프로시저 호출

프로시저는 쿼리에서 CALL 프로시저이름(변수)값으로 호출하여 값을 넘겨준다.

 

---

  프로시저 실습

1. 회원테이블(tb_member)의 아이디(m_id)와 패스워드(m_pw)를 입력받아 일치하는 이메일(m_email)을 출력하는 프로시저(memberProc_test1)를생성하고 호출하세요.

BEGIN
	select
		m.m_email as '이메일' into mEmail
	from
		tb_member as m
	where
		m.m_id = memberId
	and
		m.m_pw = memberPw;
END

CALL memberProc_tes1('id001', 'pw001', @mEmail);
SELECT @mEmail;

 

프로시저에 값을 넣어 호출한다. 이 때 결과가 뜨지 않는다.선택된 tb_member 테이블의 m_email의 값이 mEmail의 변수에 입력되었을 뿐.

 쿼리에서 select하고 변수의 값을 가져오면 결과값을 얻을 수 있다.

 

2. 아이디 값을 받아와서(memberId) 받아온 아이디의 권한을 tb_member에서 조회하여 출력하여라.

BEGIN
   DECLARE memberLevel INT;

   SELECT
     m.m_level into memberLevel
   FROM
     tb_member as m
   WHERE
     m.m_id = memberId;
 -- if문으로 작성하는 방법.
   IF (memberLevel = 1) THEN
       SELECT '관리자' AS '권한’;
   ELSEIF(memberLevel = 2) THEN
       SELECT '판매자' AS '권한’;
   ELSE
       SELECT '구매자' AS '권한’;
   END IF;
-- case문으로 작성하는 방법.
   CASE
   WHEN (memberLevel = 1) THEN
       SELECT '관리자' AS '권한’;
   WHEN (memberLevel = 2) THEN
       SELECT '판매자' AS '권한’;
   ELSE
       SELECT '구매자' AS '권한’;
   END CASE;
END

매개변수 memberId를 등록.

 쿼리에서 프로시저 호출 및 결과창.

 

3. 반복문(while,loop)을 사용하여 프로시저에 변수를 입력하고 호출하였을 때 0부터 입력값 까지의 모든 값을 더하고, 결과를 출력하여라

BEGIN
  DECLARE i INT;
  DECLARE resultSum INT;
  SET i = 0;
  SET resultSum = 0;
--while문을 사용한 방법.
  WHILE (i < intValue) DO
     SET i = i + 1;
     SET resultSum = resultSum + i;
  END WHILE;
--loop 반복문을 사용한 방법.
	LOOP_sum: LOOP
            IF (i = intValue) THEN
               LEAVE  LOOP_sum;
            END IF;
            SET i = i + 1;
            SET resultSum = resultSum + i;
            END LOOP;
  SELECT resultSum AS '총합계';
END

프로시저 명 : totalSumProc

매개변수 타입 : int

매개변수 명 : intValue 

 

4. 반복문 while과 loop을 활용한 2를 제외한 총합 구하기

BEGIN
   DECLARE i INT;
   DECLARE resultSum INT;
   SET i = 0;
   SET resultSum = 0;
--while 사용한 방법
   WHILE_sum: WHILE (i < intValue) DO
                 SET i = i + 1;
                 IF (i = 2) THEN
                    ITERATE  WHILE_sum;
                 END IF;
                 SET resultSum = resultSum + i;
              END WHILE;
--loop 사용한 방법
	LOOP_sum: LOOP
            IF (i = intValue) THEN
               LEAVE  LOOP_sum;
            END IF;
            SET i = i + 1; 
	   		IF (i <> 2) THEN
	      		SET resultSum = resultSum + i;
	  		END IF;
   SELECT resultSum AS ＇2를 제외한 총합';
END

프로시저 명 : totalSumProc

매개변수 타입 : int

매개변수 명 : intValue 

 

5. 다음과 같이 약수와 약수의 합계를 구하는 프로시저를 만들고 250을 입력받아 호출하여 출력하시오.

BEGIN	
	declare i int;
	declare result int;
	declare resultStr varchar(50);
	set i = 0;
	set result = 0;
	set resultStr = '';
	Loop_sum : Loop
					if(i = num)then
						leave Loop_sum;
					end if;
					set i = i + 1;
					
					if((num%i) = 0 )then
						set result = result+i;
						if(i=1) then
							set resultStr = concat('',i);
						else
							set resultStr = concat(resultStr, ',' ,i);
						end if;						
					end if;
	end LOOP;
	select resultStr as '약수'
	, result as '약수합계';
END