24.07.19 Day15

스토어드 프로시저

프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체

IF문

drop procedure if exists ifproc1;
delimiter $$
create procedure ifproc1()
begin
   if 100 = 100 then
      select '100은 100과 같습니다.';
   end if;
end $$
delimiter ;
call ifproc1();

IF~ELSE문

drop procedure if exists ifProc2;
delimiter $$
create procedure ifProc2()
begin
	declare myNum INT;
    set myNum = 200;
    if myNum = 100 then
		select '100입니다.';
	else
		select '100이 아닙니다.';
	end if;
end $$
delimiter ;
call ifProc2();

IF문의 활용

drop procedure if exists ifPro3;
delimiter $$
create procedure ifProc3()
begin
	declare debutDate DATE;
    declare curDate DATE;
    declare days INT;
select debut_date INTO debutDate
	from market_db.member
	where mem_id = 'APN';
	set curDate = current_date();
	set days = datediff(curDate, debutDate);

	if(days/365) >= 5 then
		select concat('데뷔한지', days,'일이나 지났습니다. 핑순이들 축하합니다!');
	else
		select '데뷔한지' + days + '일밖에 안되었네요. 핑순이들 화이팅~';
	end if;
end $$
delimiter ;
call ifProc3();

CASE문 = 다중분기

여러가지 조건 중에서 선택해야할 경우

drop procedure if exists caseProc;
delimiter $$
create procedure caseProc()
begin
	declare point int;
    declare credit char(1);
    set point = 88;

    CASE
		when point >= 90 then
			set credit = 'A';
		when point >= 80 then
			set credit = 'B';
		when point >= 70 then
			set credit = 'C';
		when point >= 60 then
			set credit = 'D';
		else
			set credit = 'F';
	END CASE;
    select concat('취득점수 ==>',point), concat('학점 ==> ', credit);
end $$
delimiter ;
call caseProc();

CASE문의 활용

select B.mem_id, M.mem_name ,sum(price*amount) "총구매액",
	case
		when (sum(price*amount) >= 1500) then "최우수고객"
		when (sum(price*amount) >= 1000) then "우수고객"
		when (sum(price*amount) >= 1) then "일반고객"
        else '유령고객'
	end '회원등급'

	from buy B
        -- 구매한 적이 없어도 회원 테이블에 있는 회원 모두 출력
		right outer join member M
        on B.mem_id = m.mem_id
    group by M.mem_id
    order by sum(price*amount) DESC;

where문

drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
begin
	declare i int;
    declare hap int;
    set i = 1;
    set hap = 0;

    while (i <= 100) DO
		set hap = hap + i;
        set i = i + 1;
	end while;
		select '1부터 100까지의 합 ==> ', hap;
	end $$
    delimiter ;
    call whileProc();

while응용

drop procedure if exists whileProc2;
delimiter $$
create procedure whileProc2()
begin
	declare i int;
    declare hap int;
    set i = 1;
    set hap = 0;

    myWhile:
    while (i<=100) DO
		if(i%4=0) then
			set i = 1 + i;
            iterate myWhile;
		end if;
        set hap = hap + i;
        if(hap > 1000) then
			leave mywhile;
		end if;
        set i = i + 1;
	end while;

    select '1부터 100까지의 합(4의 배수 제외),1000 넘으면 종료 ==> ', hap;
end $$
delimiter ;
call whileProc2();

동적 SQL

prepare myQuery from 'SELECT * FROM member WHERE mem_id = "BLK"';
execute myQuery;
deallocate prepare myQuery;

동적 SQL 활용

drop table if exists gate_table;
create table gate_table (id int auto_increment primary key, entry_time datetime);

set @curDate = current_timestamp();

prepare myQuery from 'INSERT INTO gate_table VALUES(NULL,?)';
execute myQuery using @curDate;
deallocate prepare myQuery;

select * from gate_table;

구구단 계산

delimiter $$
create PROCEDURE gugudan()
BEGIN
	drop table if exists num1;
	create table num1 (num1_1 int);
    insert into num1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);

    drop table if exists num2;
    create table num2 (num2_1 int);
    insert into num2 values (1),(2),(3),(4),(5),(6),(7),(8),(9);

    select num1_1, num2_1, num1_1 * num2_1
		from num1
        cross join num2
        where num1 between 2 and 9
        order by num2_1, num1_1;
end $$
delimiter ;

call gugudan();

VIEW

view를 만든 후에는 테이블과 동일하게 사용

use market_db;
create view v_member
as
	select mem_id,mem_name,addr
		from member;

select mem_name, addr
	from v_member
	where addr in('서울','경기');

# 복잡한 SQL 단순하게
create view v_memberbuy
as
	select B.mem_id, M.mem_name, B.prod_name, M.addr, concat(M.phone1, M.phone2) '연락처'
		from buy B
			inner join mamber M
            on B.mem_id = M.mem_id;

select * from v_memberbuy where mem_name = '블랙핑크';

뷰의 실제 생성, 수정, 삭제

create view v_viewtest1
as
	select B.mem_id 'Member ID', M.mem_name as 'Member Name',
		B.prod_name "Product Name",
			concat(M.phone1, M.phone2) as 'Office Phone'
		from buy B
			inner join member M
            on B.mem_id = M.mem_id;

select distinct 'Member ID', 'Member Name' from v_viewtest1;

alter view

alter view v_viewtest1
as
	select B.mem_id '회원 아이디', M.mem_name AS '회원 이름',
		B.prod_name "제품 이름",
			concat(M.phone1, M.phone2) as "연락처"
		from buy B
			inner join member M
            on B.mem_id = M.mem_id;
select distinct '회원 아이디', '회원 이름' from v_viewtest1;