[ SQL 스터디 - 2주차 ] SQL 프로그래밍
위 포스트는 "혼자 공부하는 SQL" 책 Chapter 04-3 내용을 정리한 글입니다.
한빛미디어 - 혼자 공부하는 SQL (우재남 저) : 무료 동영상 강의 제공
누구를 위한 책인가요? SQL을 처음 시작하려고 하는 학생과 취업 준비생, 데이터베이스 기초를 배우고자 하는 초보 개발자, SQL의 이론과 실습을 동시에 학습하고 싶은 입문자, SQL을 공부하다가
hongong.hanbit.co.kr
Chapter 04-3 . SQL 프로그래밍
CH7에서 학습하게 될 스토어드 프로시저는
MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다.
이는 DELIMITER $$ ~ END $$ 안에서 작성하고 CALL로 호출한다.
DELIMITER $$ -- 스토어드 프로시저 시작
CREATE PROCEDURE --스토어드_프로시저_이름()
BEGIN
-- SQL 프로그래밍 코딩
END $$ --스토어드 프로시저 종료
DELIMITER; -- 종료 문자를 다시 세미콜론으로 변경
CALL -- 스토어드 _프로시저_이름()
우리는 이번 포스트에서
이 스토어드 프로시저에서 BEGIN~END 사이에 존재하는
SQL 프로그래밍 코딩하는 방법에 대해 알아보겠다.
** IF문
01. IF문의 기본 형식
IF문은
조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나이다.
조건식이 참이라면 SQL문장을 실행하고, 그렇지 않으면 그냥 넘어간다.
만약 SQL 문장들이 한 문장이라면,
아래와 같은 기본 IF문의 형식을 사용한다.
IF < 조건식 > THEN
SQL 문장들
END IF;
다만, 두 문장 이상이 처리되어야 할 때는
다음과 같이 BEGIN ~ END로 묶어주어야 한다.
DELIMITER $$ -- 스토어드 프로시저 시작
CREATE PROCEDURE ifProc1() --스토어드_프로시저_이름()
BEGIN
IF 100 = 100 THEN
SELECT '100은 100과 같습니다.';
END IF;
END $$ --스토어드 프로시저 종료
DELIMITER; -- 종료 문자를 다시 세미콜론으로 변경
CALL -- 스토어드 _프로시저_이름()
02. IF ~ ELSE문
IF ~ ELSE문은
조건에 따라 다른 부분을 수행한다.
조건식이 참이라면 'SQL 문장1'을, 그렇지 않으면 'SQL 문장2'를 실행한다.
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(); -- 스토어드 _프로시저_이름()
03. IF문의 활용
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 = CURRRENT_DATE();
SET days= DATEDIFF(curDate,debutDate);
IF (days/365)>=5 THEN
SELECT CONCAT('데뷔한지',days,'일이나 지났습니다. 핑순이들 축하합니다!');
ELSE
SELECT '데뷔한지'+days+'일밖에 안되었네요. 핑순이들 화이팅 ~';
END IF;
END $$
DELIMITER;
CALL ifProc3();
** CASE문
01. CASE문의 기본 형식
CASE문은
여러 가지 조건 중에서 선택해야 하는 경우 사용하는 문장이다.
참 아니면 거짓 두 가지만 있기 때문에 '2중 분기'라는 용어를 사용하며,
2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 '다중 분리'라고 부른다.
CASE문의 형식은 다음과 같다.
CASE
WHEN 조건1 THEN
SQL 문장들1
WHEN 조건2 THEN
SQL 문장들2
WHEN 조건3 THEN
SQL 문장들3
ELSE
SQL 문장들4
END CASE;
CASE와 END CASE 사이에는 여러 조건을 넣을 수 있으며,
WHEN 다음에 나오는 조건이 여러 개라면 WHEN을 여러 번 반복한다.
그리고 모든 조건에 해당하지 않으면 마지막 ELSE 부분을 수행한다.
02. CASE문의 활용
SELECT mem_id, SUM(price*amount)"총구매액"
FROM buy
GROUP BY mem_id;
SELECT mem_id, SUM(price*amount)"총구매액"
FROM buy
GROUP BY mem_id
ORDER BY SUM(price*amount) DESC;
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;
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;
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문
01. WHILE문의 기본 형식
WHILE문은
조건식이 참인 동안에 필요한 만큼 계속 같은 내용을 반복하는 문장이다.
WHILE문의 형식은 다음과 같다.
WHILE <조건식> DO
SQL 문장들
END WHILE;
1에서 100까지의 값을 모두 더하는 WHILE문을 구현하면 다음과 같다.
DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
DECLARE i INT; -- 1에서 100까지 증가할 변수
DECLARE hap INT; -- 더한 값을 누적할 변수
SET i = 1;
SET hap = 0;
WHILE(i<=100) DO
SET hap = hap + i; -- hap의 원래 값에 i를 더해서 다시 hap에 넣으라는 의미
SET i = i + 1; -- i의 원래 값에 1을 더해서 다시 i에 넣으라는 의미
END WHILE;
SELECT '1부터 100까지의 합 ==>',hap;
END $$
DELIMITER;
CALL whileProc();
WHILE문은 조건식이 참인 동안에 계속 반복한다.
02. WHILE문의 응용
DROP PROCEDURE IF EXISTS whilePro2;
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 = 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 '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>',hap;
END $$
DELIMITER;
CALL whileProc2();
* ITERATE [레이블] : 지정한 레이블로 가서 계속 진행
* LEAVE [레이블] : 지정한 레이블을 빠져나감. 즉 WHILE문이 종료됨
** 동적 SQL
동적 SQL문이란
상황에 따라 SQL문 내용 변경이 필요할 때
변경되는 내용을 실시간으로 적용시켜 사용할 수 있는 문장이다.
SQL문은 내용이 고정되어 있는 경우가 대부분이긴 하지만,
동적 SQL문을 통해 내용 변경을 적용한다.
01. PREPARE와 EXECUTE
아래 예시 코드와 함께 PREPARE와 EXECUTE 개념에 대해 알아보자.
USE market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id ="BLK"';
EXECUTE myQUery;
DEALLOCATE PREPARE myQuery;
PREPARE는
SQL문을 실행하지는 않고 미리 준비만 해놓는 단계이다.
위 예시일 경우 SELECT * FROM member WHERE mem_id="BLK"를 바로 실행하지 않고
myQuery에 입력만 시켜놓는다.
EXECUTE는
준비한 SQL문을 실행하는 단계이다.
실행이 필요한 시점에서 EXECUTE myQuery문으로 실행한다.
이렇게 미리 SQL을 준비한 후에 나중에 실행하는 것을
동적 SQL이라고 부른다.
02. 동적 SQL의 활용