SQL 프로그래밍

서론

스토어드 프로시저

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

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

-- 스토어드 프로시저 코딩 시작
DELIMITER $$ 
CREATE PROCEDURE 스토어드 프로시저 이름()
BEGIN

-- 이 부분에 sql 프로그래밍 코딩

END $$
-- 스토어드 프로시저 코딩 끝
DELIMITER; -- 종료문자를 다시 세머콜론으로 변경
CALL 스토어드 프로시저 이름(); -- 스토어드 프로시저 실행

IF문

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

참 혹은 거짓만 있기 때문에 2중 분기 입니다.

기본 형식

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

SQL의 문장들이 두문장 이상이라면 begin ~ end로 묶어야합니다. 현재는 한문장이더라도 나중에 추가될 수 있으니 꼭 이렇게 묶는 습관을 들여야합니다.

drop procedure if exists ifProc1; -- 1
delimiter $$ -- 2
create procedure ifProc1() -- 3
begin
	if 100 = 100 then -- 4
		select '100은 100과 같습니다.';
	end if; -- 4
end $$ -- 2
delimiter ;
call ifProc1(); -- 5

1. 만약 기존에 ifProc1() 을 만든적이 있다면 삭제합니다.
2. 세미콜론으로는 SQL의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$을 사용합니다.
3. 스토어드 프로시저의 이름을 ifProc1()으로 지정합니다
4. 조건식으로 100과 100이 같은지 비교합니다. 참이라면 다음 행이 실행됩니다.
5. call로호출하면 ifProc1()이 실행됩니다.

IF ~ ELSE 문

drop procedure if exists ifPorc2;
delimiter $$
create procedure ifProc2()
begin
	declare myNum int; -- 1
	set myNum == 200; -- 2
	if myNum = 100 then --3
		select '100입니다.';
	else
		select '100이 아닙니다.';
	end if; -- 3
end $$
delimeter ;
call ifProc2();

1. declare 예약어를 사용해서 myNum 변수를 신언했습니다. 제일 뒤에는 변수의 데이터 형식을 int로 지정했습니다.
2. set 예약어로 myNum 변수에 200을 대입했습니다.
3. myNum이 100인지 아닌지 구분합니다.

IF 문의 활용

아이디가 APN인 회원의 데뷔일자가 5년이 넘었는지 확인해보고 5년이 넘었으면 축하 메시지를 출력해보자

drop procedure if exists ifProc3;
delimiter $$
create procedure ifProc3()
begin
	-- 1. 변수 생성
	declare debutDate DATE; -- 데뷔일자
	declare curDate DATE; -- 오늘
	declare days INT; -- 활동한 일수
	-- 2. 변수 값 할당
	select debute_date into debutDate
		from market_db.member
		where mem_id = 'APN';
	-- 3. 날짜 저장
	set curDate = current_date(); -- 현재날짜
	-- 4. 날짜 차이 저장
	set days = datediff(curDate, devbutDate); -- 날짜의 차이, 일 단위

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

1. 변수를 세개 만들었습니다.
2. APN이라는 커럶의 debute_date 라는 컬럼의 값들을 debutDate에 할당했습니다.
3. 현재 날짜를 curDate에 저장했습니다.
4. 현재 날짜에서 데뷔 날짜를 뺀 값을 datediff 함수를 사용하여 days에 저장했습니다.

CASE 문

여러 조건 중에서 선택해야 하는 경우 사용합니다.

여러가지 조건이 있기 때문에 다중 분기라고 부릅니다.

기본형식

case
	when 조건1 then
	--
	when 조건2 then
	--
	when 조건3 then
	--
	Else

end case;

활용

시험 성적 90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 이상은 D, 60점 미만은 F

drop procedure if exists caseProc;
delimiter $$
create procedure caseProc()
begin
	declare point int;
	declare credit chaar(1);
	declare 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();

활용2(중요함)

총 구매액	회원등급
1500 이상	최우수고객
1000 ~ 1499	우수고객
1 ~ 999	일반고객
0 이하(구매한 적 없음)	유령고객

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.member_id = m.member_id
	group by m.mem_id
	order by sum(price*amount) desc;

WHILE 문

필요한 만큼 계속 같은 내용을 반복가능

기본 형식

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

활용

1에서 100까지 모두 더하는 기능

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 hap;
end $$
delimiter ;
call whileProc();

응용

1에서 100까지의 합계에서 4의 배수를 제외하려면? 추가로 숫자를 더하는 중간에 합계가 1000이 넘으면 더하는 것을 그만두고 1000이 넘는 순간의 숫자를 출력한 후 프로그램을 종료하고 싶을때

이럴때는 iterate문과 leave문을 활용할 수 있다.

• iterate : 지정한 레이블로 가서 계속 진행
• leave : 지정한 레이블을 빠져나감 즉 while문이 종료됨

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

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

	select hap;
end $$
delimiter ;
call whileProc2();

동적 SQL

상황에 따라서 내용의 변경이 필요할때 사용한다.

prepare 와 excute

쿼리문을 실행하지는 않고 미리 준비만 해놓은 상태에서 excute로 실행한다. 실행한 후에는 deallocate prepare로 문장을 해제해주는 것이 바람직하다.

예제

use market_db;
prepare myQuery from 'select * from member where mem_id = "BLK";
execute myQuery;
deallocate prepare myQuery;

동적 SQL의 활용

gate_table에 entry_time을 비워놓고 나중에 넣기

drop table if exists gate_table;
create table gate_table (id int auto_increament 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