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;
선생님 풀이
/*연도변수*/
DECLARE resultYear varchar(20) default '';
/*성별 체크 변수*/
DECLARE genderCheck int DEFAULT 0;
/*결과 변수*/
DECLARE resultAge varchar(20) default '';
IF(CHAR_LENGTH(jumin)=14) THEN
SET genderCheck := CAST(SUBSTRING(jumin,8,1) AS UNSIGNED);
IF(genderCheck<3) THEN
SET resultYear := CONCAT(19, substring(jumin,1,6));
ELSE
SET resultYear := CONCAT(20, substring(jumin,1,6));
END IF;
set resultAge := concat('만 ', truncate((to_days(now())-(to_days(resultYear)))/365, 0), '세입니다');
ELSE
SET resultAge := '입력한 데이터 유효하지 않습니다.';
END IF;
return resultAge;
'{ "Hello World!" }; > DataBase' 카테고리의 다른 글
mysql/ 소수점 표시 (0) | 2023.09.22 |
---|---|
(DB) 트리거(Trigger) (0) | 2022.02.14 |
(DB) Transction(트랜잭션) (0) | 2022.02.14 |
(DB) eXERD 사용 방법 (0) | 2022.01.26 |
(DB) 뷰(VIEW) (0) | 2022.01.26 |