[13주 4일차] SQL 고급 문법 : SQL 프로그래밍

 

04-3. SQL 프로그래밍

 

 

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 떄 사용하는 데이터베이스 개체이다.

sql에서 프로그래밍은 기본적으로 스ㅗ어드 프로시저 안에 만들어야한다.

 

delimiter $$
create procedure 스토어드_프로시저_이름()
begin
	코딩 작성
end $$
delimiter ;
call 스토어드_프로시저_이름();

 

delimiter $$ ~ end $$ 안에 작성한다. (delimiter 뒤에 공백 후 $$붙인다. 이때 특수문자는 뭐든 상관없다.)

call 로 호출한다.

 

 

 

 

 

if 문

if 문은 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나이다.

 

if <조건식> then 
	sql 문장들
end if;

 

* sql 문장들이 두문장 이상 처리되어야 할 때는 begin ~ end 로 묶어줘야한다.

 

 

 

 

if문의 활용

 

기존테이블과 if문을 활용하여 APN 회원의 데뷔 일자가 5년이 넘으면 축하 메시지를 출력하도록 쿼리문을 작성했다.

drop procedure if exists ifproc3;
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();

 

declare을 사용하여 변수를 선언해 생성했다.

 

APN의 데뷔 일자를 추출하기 위해 select문을 사용했는데 보통의 select문과 달리 into변수를 사용했다.

into변수를 사용할 경우 결과를 변수에 저장한다. (APN의 데뷔 일자가 into뒤의 debutdate 변수에 저장된다.)

 

current_date() 함수를 사용해 현재 날짜를 변수에 저장했다.

 

datediff() 함수를 사용하여 데뷔 일지부터 현재 날짜까지 일수를 변수 days에 저장했다.

 

저장된 days를 365로 나누어 5년이 넘을 경우 메세지를 출력하고 안넘으면 다른 메세지를 출력하도록 쿼리문을 작성했다.

 

 

 

 

 

 

* 날짜 관련 함수

CURRENT_DATE() : 오늘 날짜를 알려준다.

CURRENT_TIMESTAMP() : 오늘 날짜 및 시간을 함께 알려준다.

DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜1까지의 일수로 몇일인지 알려준다.

 

 

 

 

 

 

---

 

 

 

case 문 

case 문은 여러 가지 조건 중에 선택해야하는 경우 사용한다.

 

case
	when 조건1 then
    	sql문장1
	when 조건2 then
    	sql문장2
	when 조건3 then
    	sql문장3
   	else
    	sql문장4
end case;

 

 

 

아래는 case문을 사용하여 취득점수와 취득점수에 따른 학점을 출력해주는 쿼리문이다.

declare를 통해 변수를 선언하고 set을 사용하여 point에 값을 넣어주었다.

받은 데이터 값을 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 문의 활용

고객의 등급을 나누는 쿼리문을 작성했다.

쿼리문은 차례대로 작성해보았다.

 

 

총구매액을 구했다. 이때 id별 총구매액이므로 group by mem_id로 같은 id끼리 묶어주었다.

-- step1. 동일 id별 총구매액 합

select mem_id, sum(price*amount) "총구매액" from buy group by mem_id;

 

 

 

 

총 구매액을 구한 다음 구매액이 많은 순서로 정렬하기 위해 order by절을 사용해 총구매액을 내림차순으로 정렬해주었다.

그럼 데이터가 총구매액이 큰것부터 작은것 순서로 정렬되는 것을 볼 수 있다.

  -- step2. 동일 id별로 구한 총구매액 합을 내림차순으로 정렬

select mem_id, sum(price*amount) "총구매액" from buy group by mem_id order by sum(price*amount) desc;

 

 

 

 

 

 

회원의 id, 총구매액뿐만아니라 회원의 이름도 출력하기 위해 회원의 이름 등 정보를 가진 회원테이블(member)과 구매테이블(buy)와 조인시켰다.

 

 -- step3. 구매한 회원들의 정보를 추가적으로 보기 위해 inner 조인했다.

select B.mem_id, M.mem_name, sum(price*amount) "총구매액" from buy B inner join member M on B.mem_id = M.mem_id group by B.mem_id order by sum(price*amount) desc;

 

 

 

 

 

 

구매하지 않은 회원들의 정보도 보기 위해 outer 조인을 사용했다.

앞서 사용한 inner조인은 겹치는 데이터만 출력하기 때문에 회원을 다루는 데이터에는 outer 조인이 필요하다.

-- step4. 구매하지 않은 회원들의 정보도 보기 위해서는 outer 조인을 사용해야한다.

select M.mem_id, M.mem_name, sum(price*amount) "총구매액" 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;

 

 

 

 

 

마지막으로 id별 총구매액의 합계로 회원의 등급을 나누도록 쿼리문을 작성했다.

-- step5. 회원별 구매액합을 기준으로 case when 사용하여 등급나누기

select M.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;

 

 

 

 

 

 

---

 

 

 

 

While 문

while문은 필요한 만큼 계속 같은 내용을 반복할 수 있다.(참인 동안 계속 반복하게 만들 수 있음)

 

 

while <조건식> do
	sql문장들
end while;

 

 

 

while문의 응용

while문에는 iterate문과 leave문이 있다.

iterate문은 지정한 레이블로 가서 진행한다. (continue와 같음)

leave문은 지정한 레이블을 빠져나가며 while문을 종료한다. (break;와 같음)

 

특정 지점에 레이블을 작성해 돌아갈 지점을 지정해준 후

iterate 레이블명; leave 레이블명; 으로 지정해준다.

 

 

 

 

아래의 쿼리문은 1부터 100까지의 숫자 중 4의 배수를 제외하고 더한다. 조건으로 합이 1000이 넘으면 종료하도록 하는 쿼리문이다.

drop procedure if exists whileproc2;

delimiter $$
create procedure whileproc2()
begin
	declare i int;
    declare hap int;
    set i = 1;
    set hap = 0;
    
    myWhile:   -- iterate 지점 지정
    while (i <= 100) do    
		if (i%4 = 0) then     -- 4의 배수 제외시켜줌
			set i = i + 1;
            iterate myWhile;
        end if;
        set hap = hap+i;   -- hap변수 값에 i값을 더해주고 다시 hap에 넣어줌
        if (hap > 1000) then leave myWhile;  -- hap의 범위 설정
        end if;
        set i = i + 1;
    end while;
    
    select '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==> ', hap;
end $$
delimiter ;

call whileproc2();

 

 

 

 

 

 

 

 

---

 

 

 

 

 

동적sql

동적 sql은 상황에 따라 내용 변경을 할 수 있도록 해준다.

 

 

 

prepare와 execute

prepare는 sql문을 실행하지 않고 미리 준비만 해놓는다.

execute는 준비한 sql문을 실행한다.

그리고 실행 후 deallocate prepare로 문장을 해제해준다.

 

prepare myQuery from 'select member where mem_id = "BLK"';
execute myQuery;
dellocate prepare myQuery;

 

prepare문에서는 쿼리문을 실행하지 않고 myQuery에 입력 시켜놓는다.

실행이 필요한 시점에 execute myQuery 문을 실행한다.

이와 같이 sql을 준비한 후 나중에 실행하는 것을 동적 sql이라고 한다.

 

 

 

 

 

 

 

동적 sql의 활용

prepare 문에서는 ?로 입력될 값을 비워 놓고 execute에서 using으로 ?에 값을 전달할 수 있다.

 

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;