SQL정리(StoredProcedure)

/*
1. 스토어드 프로시저
: MySQL에서 프로그래밍 기능이 필요로 할 때 사용하는 데이터베이스 개체
2. 스토어드 프로시저의 구조
-- 구분 문자: delimiter
: 스토어드 프로시저의 코딩 부분을 일반 SQL문 종료와 구분하기 위해 스토어드 프로시저의 구분문자를 변경

--구분 문자 변경의 형태(권장)
delimiter $$
delimiter ;

스토어드 프로시저의 기본형태

delimiter $$

create procedure `절차명`
begin SQL 프로그래밍 코딩
end $$
delimiter;
call `스토어드 프로시저 절차명`;

SQL 프로그래밍 종류
1. if문

기본 형식
if 조건문 then
sql문장들;
end if
*/
delimiter $$
create procedure if1()
begin
if 100= 100 then
select '100은 100과 같습니다.';
end if;
end $$

delimiter ;
call if1();

# if else문
delimiter $$
create procedure if2()
begin
declare myNum int;
set myNum=200;
if myNum=100 then select '100입니다.';
else select '100이 아닙니다.';
end if;
end $$
delimiter ;
call if2();

/*
case문
    : 여러 조건 중에서 선택해야 하는 경우
    case
    when 조건1 then
# sql문장 1
when 조건2 then
# sql 문장 2
...
    else
# 모든 조건에 일치하지 않을 경우
end case;
*/
# 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();

drop procedure if exists whileProc;
delimiter ^^
create procedure whileProc()
begin
declare i int;
    declare result int;
    set i= 1;
    set result= 0;
    
    while(i<= 100) do
set result= result+ i;
        set i= i+1;
end while;
    select result;
end ^^
delimiter ^^
call whileProc;

예제

/*
1. case문 활용
*/
use market_db;
select * from buy;

select M.mem_id, M.mem_name, sum(price * amount) "총 구매액",
case
when "총 구매액" >= 1500 then '최우수 고객'
when "총 구매액" >= 1000 then '우수 고객'
when "총 구매액" >= 1 then '일반 고객'
        else '일반 회원'
end '회원 등급'
from member M
left join buy B
    on B.mem_id= M.mem_id
group by M.mem_id
order by '총 구매액' desc;