[MySQL] SQL 프로그래밍 (IF, CASE, WHILE, 동적 SQL)
* 스토어드 프로시저: MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체
* 스토어드 프로시저의 기본 구조
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
이 부분에 SQL 프로그래밍 코딩
END $$
DELIMITER;
CALL 스토어드_프로시저_이름();- 스토어드 프로시저는 DELIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출
- 구분 문자로 $$를 많이 사용하지만, /, &, @ 등을 사용해도 상관없음. 다만, 다른 기호와 중복될 수 있으므로 기호 2개를 연속으로 사용하는 것이 좋음.
1. IF 문
1) IF문
IF <조건식> THEN
SQL 문장들
END IF;* SQL문이 두 문장 이상 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 하므로, 한 문장일 때도 써버릇 하자.
* 같다는 의미는 SQL에서는 =를 사용한다. 그리고 SELECT 뒤에 문자는 다른 언어의 print() 처럼 그냥 출력됨.
-- 예시
DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
IF 100 = 100 THEN
SELECT '100은 100과 같습니다.';
END IF;
END $$
DELIMITER ;
CALL ifProc1();
2) IF ~ ELSE 문: 조건에 따라 다른 부분을 수행함
-- 예시
DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
DECLARE myNum INT;
SET myNum = 200;
IF myNum = 100 THEN
SELECT '100은 100과 같습니다.';
ELSE
SELECT '100SMS DKSLQSLEK.';
END IF;
END $$
DELIMITER ;
CALL ifProc2();
3) 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 = CURRENT_DATE(); -- 현재 날짜
SET days = DATEDIFF(curDate, debutDate); -- 날짜의 차이, 일 단위
IF (days/365) >= 5 THEN
SELECT CONCAT('데뷔한 지', days, '일이나 지났습니다. 핑순이들 축하합니다!');
ELSE
SELECT '데뷔한 지' + days + '일밖에 안되었네요. 핑순이들 화이팅~';
END IF;
END $$
DELIMITER ;
CALL ifProc3();- INTO 변수가 붙으면 결과를 변수에 저장함
- CURRENT_DATE() 함수로 현재 날짜를 curDATE에 저장
- DATEDIFF() 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장
※ 날짜 관련 함수 ※
| CURRENT_DATE() | 오늘 날짜를 알려줌 |
| CURRENT_TIMESTAMP() | 오늘 날짜 및 시간을 함께 알려줌 |
| DATEDIFF(날짜1, 날짜2) | 날짜2부터 날짜1까지 일수로 몇일인지 알려줌 |
2. CASE 문
- CASE 문은 다중 분기로, 여러 조건에 따라 다른 SQL을 실행시킬 수 있음.
- CASE 문의 기본 형식
CASE
WHEN 조건1 THEN
SQL 문장들1
WHEN 조건2 THEN
SQL 문장들2
WHEN 조건3 THEN
SQL 문장들3
ELSE
SQL 문장들4
END CASE;* GROUP BY를 이용해 회원별로 총 구매액을 구할 수 있음
★ ★ ★ ★ ★ 이거 코테에 많이 나오는 스타일 ★ ★ ★ ★ ★
-- 예시
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;
3. WHILE 문
- 조건식이 참인 동안에 'SQL 문장들'을 계속 반복한다
- WHILE 문의 형식
WHILE <조건식> DO
SQL 문장들
END WHILE;
- 특정 조건에서 종료하고 싶다면 아래의 문을 활용할 수 있음
* ITERATE[레이블] : 지정한 레이블로 가서 계속 진행 ( = CONTINUE)
* LEAVE[레이블] : 지정한 레이블을 빠져나감. WHILE 문이 종료 ( = BREAK)
DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
DECLARE i INT; -- 1에서 100까지 증가할 변수
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();
4. 동적 SQL
PREPARE는 SQL 문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE는 준비한 SQL 문을 실행함.
실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직함
=> 이렇게 미리 SQL을 준비한 후 나중에 실행하는 것을 동적 SQL이라고 부른다 !!
use market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
- PREPARE 문에서 ?로 향후에 입력될 값을 비워놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있음
- 일반 SQL에서 변수는 @변수명으로 지정하는데 별도의 선언은 따로 없어도 됨. 다만, 스토어드 프로시저에서는 변수는 DECLARE로 선언한 후에 사용해야 함.
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;