<스토어드 프로시저 (stored procedure)>
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
'SQL 프로그래밍 코딩'
END -- 책에는 END $$ 이지만 나의 MySQL 8.0.0 버전에서 에러 발생
DELIMTER ;
CALL 스토어드_프로시저_이름(); -- 호출
IF 문
DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
IF 100 = 100 THEN
SELECT '100은 100과 같습니다.'; -- 문자 출력
END IF;
END
DELIMITER ;
CALL ifProc1();
▶ IF ~ ELSE
DROP PROCEDURE IF EXISTS ifProc2;
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();
▶ IF 문 활용
DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
-- 변수 3개
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 -- 5년 지났다면
SELECT CONCATE('데뷔한 지', days, '일이나 지났습니다. APN 축하합니다!');
ELSE
SELECT '데뷔한 지' + days + '일빡에 안되었네요. APN 화이팅~';
END IF;
END
DELIMITER ;
CALL ifProc3();
< 날짜 관련 함수>
- CURRNET_DATE() : 오늘 날짜를 알려주는 함수
- CURRENT_TIMESTAMP() : 오늘 날짜 와 시간을 알려주는 함수
- DATEDIFF(날짜1, 날짜2) : 날짜2 부터 날짜1 까지 몇 일 인지 알려주는 함수
CASE 문
DROP PROCEDUER IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
DECLARE point INT ;
DECLARE credit CHAR(1);
SET point = 88 ;
CASE
WHEN point >= 90 THEN
SET credict = 'A';
WHEN point >= 80 THEN
SET credict = 'B';
WHEN point >= 70 THEN
SET credict = 'C';
WHEN point >= 60 THEN
SET credict = 'D';
ELSE
SET credit = 'F';
END CASE;
SELECT CONCAT ('취득점수 => ', point), CONCAT('학점 => ', credit);
END
DELIMITER ;
CALL caseProce();
▶ case 문 활용
SELECT m.mem_id, m.mem_name, SUM(b.price * b.amount) '총구매액'
CASE
WHEN (SUM(b.price * b.amount) >= 1500) THEN '최우수고객'
WHEN (SUM(b.price * b.amount) >= 1000) THEN '우수고객'
WHEN (SUM(b.price * b.amount) >= 1) THEN '일반고객'
ELSE '유령고객'
END '회원등급'
FROM buy b
RIGHT OUTER JOIN members m
ON b.mem_id = m.mem_id
GROUP BY m.mem_id
ORDER BY SUM(price* amount) DESC ;
WHILE 문
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 '1 부터 100까지의 합 => ' , hap;
END
DELIMITER ;
CALL whileProc();
▶ while 문 활용
DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
DECLARE i INT;
DECLARE hap INT;
SET i = 1;
SET hap = 0;
myWhile: -- WHILE 문을 myWhile이라는 레이블로 지정
WHILE (i <= 100) DO
IF (i%4 = 0) THEN
SET i = i + 1;
ITERATE myWhile; -- 지정한 레이블(label)인 myWhile로 가서 계속 진행
END IF;
SET hap = hap + i;
IF (hap > 1000) THEN
LEAVE myWhile; -- 지정한 레이이블 myWhile을 빠져나간다. WHILE 종료
END IF;
SET i = i + 1;
END WHILE;
SELECT '1 부터 100 까지의 합(4의 배수 제외), 1000 넘으면 종료 => ' , hap;
END
DELIMITER ;
CALL whileProc2();
동적 SQL
-- 출입용 테이블 생성, id 는 자동으로 증가
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, ?)'; -- 실행할 SQL문 준비
EXECUTE myQUery USING @curDate -- @curDate에 있는 값을 ?에 전달(입력)
DEALLOCATE PREPARE myQuery; -- ? 에 입렵된 값 지우고 같은 sql 문으로 준비
SELECT * FROM gate_table;
