(DB) 프로시저, 함수

카테고리

{ "Hello World!" };/DataBase

작성일

2022. 2. 14. 16:56

작성자

갱수터

728x90

프로시저(Stored Procedure)

MySQL 프로시저 생성 및 호출

IN 매개변수

CREATE PROCEDURE `sp_memberInfo`(
	IN `mName` VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '사용자의 이름을 입력받아 사용자의 정보를 조회하는 프로시저'
BEGIN
	SELECT 
		*
	FROM
		tb_member AS m
	WHERE
		m.m_name = mName;
END

OUT 매개변수

CALL sp_memberEmail_out('id001', 'pw001', @result);
SELECT @result;

제어문

조건문

IF문

BEGIN
	/*변수 선언*/ 
	DECLARE MemberLevel int;
	/*변수 선언 초기화
	DECLARE MemberLevel int DEFAULT 0;*/ 
	/*변수 초기화
	SET memberLevel := 1;*/
	SELECT
		m_level into MemberLevel
	FROM
		tb_member
	WHERE	
		m_id = memberId;
	/*제어문*/
	IF(memberLevel = 1) THEN
		SELECT '관리자' AS '권한';
	ELSEIF (memberLevel = 2) THEN
		SELECT '판매자' AS '권한';
	ELSEIF (memberLevel = 3) THEN
		SELECT '구매자' AS '권한';
	ELSE
		SELECT '회원' AS '권한';
	END IF;
END

호출

CASE문

BEGIN
	/*변수 선언, 초기화*/
	DECLARE memberLevel int DEFAULT 0;
	
	SELECT
		m.m_level INTO memberLevel
	FROM
		tb_member as m
	WHERE
		m.m_id = memberId;
	
	/*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

호출

반복문

while문

BEGIN
	DECLARE i int;	/*증가 변수*/
	DECLARE resultSum int;	/*결과 변수*/
	SET i := 0;	/*변수 초기화*/
	SET resultSum := 0;	/*변수 초기화*/
	
	/*while반복문*/
	WHILE (i < intValue) DO
		SET i := i + 1;	/*i증가*/
		SET resultSum := resultSum + i; 
	END WHILE;
	
	SELECT resultSum AS '결과';
END

호출

ITERATE

- continue

- 반복문의 이름을 정해줘야함

BEGIN
	DECLARE i int;	/*증가 변수*/
	DECLARE resultSum int;	/*결과 변수*/
	SET i := 0;	/*변수 초기화*/
	SET resultSum := 0;	/*변수 초기화*/
	
	/*while반복문*/
	while_sum : WHILE (i < intValue) DO
						SET i := i + 1;	/*i증가*/						
						IF(i = 2) THEN
							ITERATE while_sum;
						END IF;						
						SET resultSum := resultSum + i; 
					END WHILE;
	
	SELECT resultSum AS '2를 제외한 총합';
END

호출

LEAVE

- break

- 반복문의 이름을 정해줘야함

BEGIN
	DECLARE i int;	/*증가 변수*/
	DECLARE resultSum int;	/*결과 변수*/
	SET i := 0;	/*변수 초기화*/
	SET resultSum := 0;	/*변수 초기화*/
	
	/*while반복문*/
	while_sum : WHILE (i < intValue) DO
						SET i := i + 1;	/*i증가*/						
						IF(i = 2) THEN
							LEAVE while_sum;
						END IF;						
						SET resultSum := resultSum + i; 
					END WHILE;
	
	SELECT resultSum AS '결과';
END

LOOP문

- 무한반복 <= LEAVE 절 필요

BEGIN
	DECLARE i int;	/*증가 변수*/
	DECLARE resultSum int;	/*결과 변수*/
	SET i := 0;	/*변수 초기화*/
	SET resultSum := 0;	/*변수 초기화*/
	
	/*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

호출

실습

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

DROP TABLE IF EXISTS tb_gugu;
CREATE TABLE tb_gugu (result VARCHAR(150));

DECLARE i int default 2;
DECLARE j int;
DECLARE resultGugu VARCHAR(150);

loop_gugu :	LOOP
			IF(i > intValue) THEN
				LEAVE loop_gugu;
			END IF;			
            
			SET j := 1;
			WHILE(j<10) Do
				set resultGugu := CONCAT(resultGugu,'	',i,'x',j,'=',i*j);
			END WHILE;
                    
			SET i := i + 1;
			insert into tb_gugu (result) values (resultGugu);                    
		END LOOP;
                
SELECT * from tb_gugu;

1. 변수 초기화 default로 안됨
2. resultGugu 변수 초기화 안해서 문자 결합 안됨
3. j 증감식 안씀
4. 테이블 생성,삭제 구문 조건식 틀림

정답

BEGIN	
	DECLARE i int;
	DECLARE j int;
	DECLARE resultGugu VARCHAR(150);
	SET i := 2;
	
	IF(intValue < 2) THEN
		SELECT	'2 이상의 수를 입력해주세요' AS result;
		
	ELSE
		/*gugu 테이블생성*/
		CREATE TABLE IF NOT EXISTS tb_gugu (
			result varchar(150) NOT NULL COMMENT '구구단결과' 
		);
		/*gugu 테이블 비우기*/
		TRUNCATE TABLE tb_gugu;
		
		WHILE (i <= intValue) DO 
			SET j := 1;
			SET resultGugu := '';
			WHILE(j<10) Do
				set resultGugu := CONCAT(resultGugu,' ',i,'x',j,'=');
				IF(i*j <10) THEN
					SET resultGugu := CONCAT(resultGugu,'0',i*j);
				ELSE
					SET resultGugu := CONCAT(resultGugu,i*j);
				END IF;
				SET j := j + 1;
			END WHILE;
			
			SET i := i + 1;
			insert into tb_gugu(result) values (resultGugu);
		END WHILE;
					
		SELECT * from tb_gugu;
	END IF;
	
END

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

BEGIN
	DECLARE i int DEFAULT 0;
	DECLARE resultSum int DEFAULT 0;
	DECLARE resultDivisor VARCHAR(150) DEFAULT '';
	
	divisor_loop : LOOP
							IF(i =intValue) THEN
								LEAVE divisor_loop;
							END IF;
							SET i := i + 1;
							IF((intValue % i)=0) THEN
								SET resultSum := resultSum + i;
								IF(i=1) THEN
									SET resultDivisor := CONCAT(i);
								ELSE
									SET resultDivisor := CONCAT(resultDivisor, ', ',i);
								END IF;
							END IF;
						END LOOP;
	SELECT resultDivisor AS '약수', resultSum AS '약수들의 합계';					
END

함수(Stored Function)

특징

- 내장함수가 사용자를 만족하는 모든 함수를 제공하지 않아 필요에 의해 사용자가 직접 함수를 만들어서 사용
- 형태와 사용 용도에 프로시저와 차이가 있음

BEGIN
	DECLARE resultSum VARCHAR(100) DEFAULT '';
	SET resultSum := CONCAT('두수의 합은 ',intValue1+intValue2);
	RETURN resultSum;
END

집합결과 반환 안됨

BEGIN
	DECLARE levelName VARCHAR(50);
	SET levelName := '';
	
	SELECT
		l.level_name INTO levelName
	FROM
		tb_member AS m
		INNER JOIN
		tb_member_level AS l
		ON
		m.m_level = l.level_num
	WHERE
		m.m_id = memberId;
		
	RETURN levelName;
END

매개변수 1개

select 갯수 1개

실습

실습1. 상품코드 구하기 ex) g_001

BEGIN
	DECLARE newgoodsCode varchar(50) DEFAULT '';
	DECLARE num int DEFAULT 0;
	
	SELECT
		SUBSTRING_INDEX(g_code,g,count) into newgoodsCode
	FROM
		tb_goods
	order by g_code desc
	limit 1;
	
	if(SUBSTR(newgoodsCode,2,1)='0') then
		set num := cast(substr(newgoodsCode,3) as DECIMAL);
		set newgoodsCode := CONCAT('g0', num+1);
	else
		SET num := cast(substr(newgoodsCode,2) as DECIMAL);
		set newgoodsCode := CONCAT('g', num+1);
	end if;
	
	
	
	return newgoodsCode;
END

LPAD() 함수 사용

SELECT
	CASE
	WHEN COUNT(1)=0 THEN 'g001'
	ELSE
		CONCAT('g', LPAD(MAX(CAST(SUBSTRING_INDEX(g.g_code, 'g', -1) AS UNSIGNED))+1, 3, 0))
	END AS 'newGoodsCode'
FROM
	tb_goods AS g

-사용자 정의 함수로 만들 때 case 문 끝나는 곳에 INTO 리턴변수 지정

실습2. 주민등록번호 입력받아서 성별 반환

DECLARE a int;	
	declare b varchar(10);
	declare result varchar(50);
	set a := instr(member, '-')+1;
	set b := substr(member,a,1);
	
	if( b = 1 or b = 3) then
		set result := '남자입니다';
	elseif(b=2 or b=4) then
		set result := '여자입니다';
	else
		set result := '주민등록번호를 확인해주세요';
	end if;
	
	return result;

선생님 풀이

	/*결과(성별)변수*/
	DECLARE resultGender varchar(20) default '';
	/*성별 체크 변수*/
	DECLARE genderCheck int DEFAULT 0;
		
	IF(CHAR_LENGTH(member)=14) THEN
		SET genderCheck := CAST(SUBSTRING(member,8,1) AS UNSIGNED);
		IF(genderCheck%2=1) THEN
			SET resultGender := '남성입니다.';
		ELSE
			SET resultGender := '여성입니다.';
		END IF;
	ELSE
		SET resultGender := '입력한 데이터 유효하지 않습니다.';
	END IF;
	return resultGender;

실습3. 주민등록번호로 만나이 구하기

DECLARE result varchar(50);
DECLARE myear int;
DECLARE age int;
	
IF(CHAR_LENGTH(jumin)=14) THEN
	if(substr(jumin,1,2) then
		
	end if;
	set age := year(now())- myear;
ELSE
	SET result := '입력한 데이터 유효하지 않습니다.';
END IF;
	
return result;

처음에 나이 구하는게 현재 년도-태어난 년도+1 이니깐 현재 년도-태어난 년도만 일단 구하고 +1을 조건문으로 줄려고 했는데 현재 년도-태어난 년도 조건문 처리를 해야됨

만나이 구하는 식을 알려주셧음 -> truncate((to_days(now())-(to_days(ymd)))/365, 0)

DECLARE result varchar(50);
DECLARE ymd int default 0; -> int로 안써도됨 to_days에 문자열 넣어도 가능
	
IF(CHAR_LENGTH(jumin)=14) THEN
	if(substr(jumin,8,1)<3) then
		set ymd := cast(concat(19, substr(jumin,1,6)) as UNSIGNED);
	else
		set ymd := cast(concat(20, substr(jumin,1,6)) as UNSIGNED);
	end if;
	set result := concat('만 ',truncate((to_days(now())-(to_days(ymd)))/365, 0), '세입니다.');
ELSE
	SET result := '입력한 데이터 유효하지 않습니다.';
END IF;
	
return result;