SQL6

drop table member;
create table member(
   mem_id varchar(8) not null primary key,
    mem_name varchar(10) not null,
    mem_number int not null,
    addr varchar(2) not null,
    phone1 varchar(3),
    phone2 varchar(8),
    height int,
    debut_date date
);
ALTER TABLE member CHARSET=UTF8;
show create table member;

insert into member values('TWC','트와이스',9,'서울','02','11111111',167,'2015.10.19');
insert into member values('BLK','블랙핑크',4,'경남','055','22222222',163,'2016.08.08');
insert into member values('WMN','여자친구',6,'경기','031','33333333',166,'2015.01.15');
insert into member values('OMY','오마이걸',7,'서울','','',160,'2015.04.21');
insert into member values('GRL','소녀시대',8,'서울','02','44444444',168,'2007.08.02');
insert into member values('ITZ','잇지',5,'경남','','',167,'2019.02.12');
insert into member values('RED','레드벨벳',4,'경북','054','55555555',161,'2014.08.01');
insert into member values('APN','에이핑크',6,'경기','031','77777777',164,'2011.02.10');
insert into member values('SPC','우주소녀',13,'서울','02','88888888',162,'2016.02.25');
insert into member values('MMU','마마무',4,'전남','061','99999999',165,'2014.06.19');


-- 스토어드 프로시저 
-- 입력 매개변수의 활용
use market_db;
drop procedure if exists user_proc1;
delimiter $$
create procedure user_proc1(in username varchar(10))
begin
	select * from member where mem_name = username;
end $$
delimiter ; 

call user_proc1('에이핑크');



drop procedure if exists user_proc2;
delimiter $$
create procedure user_proc2(
	in usernumber int,
    in userheight int )
begin
	select * from member
    where mem_number > usernumber and height > userheight;
end $$
delimiter ;

call user_proc2(6,165);

-- 출력 매개변수의 활용
drop procedure if exists user_proc3;
delimiter $$
create procedure user_proc3
	(in txtvalue char(10),
    out outvalue int)
begin
	insert into notable values(null, txtvalue);
	select max(id) into outvalue from notable;
end $$
delimiter ; 


desc notable;

create table if not exists notable
(
	id int auto_increment primary key,
    txt char(10)
);

desc notable;

call user_proc3 ('테스트1', @myvalue);
select concat('입력된 id 값 ==> ', @myvalue); 

-- SQL 프로그래밍 활용
drop procedure if exists ifelse_proc;
delimiter $$
create procedure ifelse_proc
	(in memname char(10))
begin
	declare debutyear int; -- 변수 선언
	select year(debut_date) into debutyear from member
		where mem_name = memname;
	if(debutyear >= 2015) then
		select '신인 가수네요. 화이팅하세요.' as '메세지';
	else
		select '고참 가수네요. 그동안 수고하셨어요.' as '메세지';
	end if;
end $$
delimiter ; 

call ifelse_proc('오마이걸');