지금 익히는 것은 특히 나중에 배우는 스토어드 프로시저, 스토어드 함수, 커서, 트리거 부분의 기본이 되므로 잘 알아두자. SQL에서도 다른 프로그래밍 언어와 비슷한 분기, 흐름 제어, 반복의 기능이 있다. 이러한 기능을 전에 소개했던 변수와 함께 잘 활용한다면 강력한 SQL 프로그래밍이 가능하다.
SQL 프로그래밍을 진행하기 전에 우선 스토어드 프로시저를 만들고 사용하는 방법을 간단히 요약하고 넘어가자.
DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저이름()
BEGIN
이 부분에 SQL 프로그래밍 코딩..
END $$
DELIMITER ;
CALL 스토어드 프로시저이름();DELIMITER $$ ~ END $$ 부분까지는 스토어드 프로시저의 코딩할 부분을 묶어준다고 보면 된다. MySQL의 종료 문자는 세미콜론(;)인데 CREATE PROCEDURE 안에서도 세미콜론이 종료 문자이므로 어디까지가 스토어드 프로시저인지 구별이 어렵다. 그래서 END $$가 나올 때까지를 스토어드 프로시저로 인식하게 하는 것이다. 그리고 다시 DELIMITER ;로 종료 문자를 세미콜론(;)으로 변경해 놓아야 한다. CALL 스토어드 프로시저이름(); 은 CREATE PROCEDURE로 생성한 스토어드 프로시저를 호출(=실행)한다.
'스토어드 프로시저이름()'과 '이 부분에 SQL프로그래밍 코딩..' 부분만 수정해서 사용.
IF...ELSE
조건에 따라 분기한다. 한 문장 이상이 처리되어야 할 때는 BEGIN...END와 함께 묶어줘야만 하며, 습관적으로 실행할 문장이 한 문장이라도 BEGIN...END로 묶어주는 것이 좋다.
형식:
IF <부울 표현식> THEN
SQL문장들1..
ELSE
SQL문장들2..
END IF;DROP PROCEDURE IF EXISTS ifProc; -- 기존에 만든 적이 있다면 삭제
DELIMITER $$
CREATE PROCEDURE ifProc()
BEGIN
**DECLARE var1 INT; -- var1 변수 선언**
**SET var1 = 100; -- 변수에 값 대입**
**IF var1 = 100 THEN -- 만약 @var1이 100이라면,**
**SELECT '100입니다.';**
**ELSE**
**SELECT '100이 아닙니다.'**
**END IF;**
END $$
DELIMITER;
CALL ifProc();이번에는 employees DB의 employees테이블을 사용해 보자. 열에는 입사일(hire_date)열이 있는데, 직원번호에 10001번에 해당하는 직원의 입사일이 5년이 넘었는지를 확인해 보자.
DROP PROCEDURE IF EXISTS ifProc2;
USE employees;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
DECLARE hireDATE DATE; -- 입사일
DECLARE curDATE DATE; -- 오늘
DECLARE days INT; -- 근무한 일수
SELECT hire_date INTO hireDATE -- hire_date열의 결과를 hireDATE에 대입
FROM employees.employees
WHERE emp_no = 10001;
SET curDATE = CURRENT_DATE(); -- 현재 날짜
SET days = DATEDIFF(curDATE, hireDATE); -- 날짜의 차이, 일 단위
IF (days/365) >= 5 THEN -- 5년이 지났다면
SELECT CONCAT('입사한지 ', days, '일이나 지났습니다. 축하합니다!');
ELSE
SELECT '입사한지 ' + days + '일밖에 안되었네요. 열심히 일하세요.';
END IF;
END $$
DELIMITER;
CALL ifProc2();결과 값:
'입사한지 00000일이나 지났습니다. 축하합니다!'
CASE
IF 구문은 2중 분기라는 용어를 종종 사용한다. 즉, 참 아니면 거짓 두 가지만 있기 때문이다. 점수와 학점을 생각해 보자. 90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 이상은 D, 60점 미만은 F로 분할할 수 있다. 이때 5가지의 경우에 따라 경우가 달라지므로 '다중 분기'라는 용어를 사용한다. IF문으로 작성해 보자.
DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
DECLARE point INT;
DECLARE credit CHAR(1);
SET point = 77;
IF point >= 90 THEN
SET credit = 'A';
ELSEIF point >= 80 THEN
SET credit = 'B';
ELSEIF point >= 70 THEN
SET credit = 'C';
ELSEIF point >= 60 THEN
SET credit = 'D';
ELSE
SET credit = 'F';
END IF;
SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER;
CALL ifProc3();결과 값:
취득점수==>77 학점==>C
이것을 CASE문으로 작성
DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
DECLARE point INT;
DECLARE credit CHAR(1);
SET point = 77;
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문은 혹시 조건에 맞는 WHEN이 여러 개더라도 먼저 조건이 만족하는 WHEN이 처리된다. 그리고 CASE를 종료한다. CASE문의 활용은 SELECT문에서 더 많이 사용된다.
<실습>
sqldb의 구매 테이블(buytbl)에 구매액(price*amount)이 1500원 이상인 고객은 '최우수 고객', 1000원 이상인 고객은 '우수고객', 1원 이상인 고객은 '일반고객'으로 출력하자. 또, 전혀 구매 실적이 없는 고객은 '유령고객'이라고 출력하자. 이번 실습의 최종 결과를 먼저 보면 다음과 같다.
sqldb초기화 후 진행하기
USE sqldb;
SELECT userID, SUM(price*amount) AS '총구매액'
FROM buytbl
GROUP BY userID
ORDER BY SUM(price*amount) DESC;
SELECT B.userID, U.name, SUM(price*amount) AS '총구매액'
FROM buytbl B
INNER JOIN usertbl U
ON B.userID = U.userID
GROUP BY B.userID, U.name
ORDER BY SUM(price*amount) DESC;
SELECT B.userID, U.name, SUM(price*amount) AS '총구매액'
FROM buytbl B
RIGHT OUTER JOIN usertbl U
ON B.userID = U.userID
GROUP BY B.userID, U.name
ORDER BY SUM(price*amount) DESC;
SELECT U.userID, U.name, SUM(price*amount) AS '총구매액'
FROM buytbl B
RIGHT OUTER JOIN usertbl U
ON B.userID = U.userID
GROUP BY U.userID, U.name
ORDER BY SUM(price*amount) DESC;
-->아이디가 모두 안나옴
이제 여기에 CASE문만 추가하면 됨
SELECT U.userID, U.name, SUM(price*amount) AS '총구매액'
CASE
WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
WHEN (SUM(price*amount) >= 1) THEN '일반고객'
ELSE '유령고객'
END AS '고객 등급'
FROM buytbl B
RIGHT OUTER JOIN usertbl U
ON B.userID = U.userID
GROUP BY U.userID, U.name
ORDER BY SUM(price*amount) DESC;
WHILE과 ITERATE/LEAVE
WHILE문은 다른 프로그래밍 언어의 WHILE과 동일한 개념이다. 해당 <부울 식>이 참인 동안에는 계속 반복되는 반복문이다.
1에서 100까지의 값을 모두 더하는 간단한 기능을 구현해 보자.
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 hap;
END $$
DELIMITER;
CALL whileProc();결과 값:
5050
그런데, 1에서 100까지 합계에서 7의 배수를 제외시키려면 어떻게 해야 할까? 즉 1+2+3+4+5+6+8+9+...100의 합계를 구하고 싶다. 또, 더하는 중간에 합계가 1000이 넘으면 더하는 것을 그만두고, 출력을 하고 싶다면? 그럴 경우에는 ITERATE문과 LEAVE문을 사용할 수 있다.
*ITERATE문은 다른 프로그래밍 언어의 CONTINUE와 LEAVE문은 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 -- While문에 label을 지정
IF (i%7 = 0) THEN
SET i = i + 1;
ITERATE myWhile; -- 지정한 label문으로 가서 계속 진행
END IF;
SET hap = hap + i;
IF (hap>1000) THEN
LEAVE myWhile; -- 지정한 label문을 떠남. 즉, While 종료
END IF;
SET i = i + 1;
END WHILE;
SELECT hap;
END $$
DELIMITER;
CALL whileProc();결과 값:
1029
오류 처리
MySQL은 오류가 발생할 경우 직접 오류를 처리하는 방법을 제공한다. 우선 그 형식을 살펴보자.
DECLARE 액션 HANDLER FOR 오류조건 처리할_문장;액션: 오류 발생 시에 행동을 정의하는데 CONTINUE와 EXIT 둘 중 하나를 사용한다. CONTINUE가 나오면 제일 뒤의 '처리할_문장'부분이 처리된다.
오류조건: 어떤 오류를 처리할 것인지를 지정한다. 여기에는 MySQL의 오류 코드 숫자가 오거나 SQLSTATE '상태코드', SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 올 수 있다. SQLSTATE에서 상태코드는 5자리 문자열로 되어 있다. SQLEXCEPTION은 대부분의 오류를, SQLWARNING은 경고 메시지를, NOT FOUND는 커서나 SELECT...INTO에서 발생되는 오류를 의미한다.
*처리할_문장: 처리할 문장이 하나라면 한 문장이 나오면 되며, 처리할 문장이 여러 개일 경우에는 BEGIN...END로 묶어줄 수 있다.
!!MySQL 8.0의 오류 코드(Error Code)는 서버 오류가 1000~1906, 3000~3186까지 정의되어 있으며, 클라이언트 오류는 2000~2062까지 정의되어 있다. 예로 SELECT * FROM noTable;문을 실행할 때 noTable이 없을 경우에는 오류코드는 1146이, 상태코드는 '42S02'가 발생된다. 각 오류코드 상태코드에 대한 상세한 설명은 https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html을 참조하자.
다음의 예는 테이블이 없을 경우에 오류를 직접 처리하는 코드다. DECLARE행이 없다면 MySQL이 직접 오류 메시지를 발생시키지만, DECLARE 부분이 있어서 사용자가 지정한 메시지가 출력된다.
DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
DECLARE CONTINUE HANDLER FOR 1146 SELECT '테이블이 없어요ㅠㅠ' AS '메시지';
SELECT * FROM noTable; -- noTable은 없음.
END $$
DELIMITER;
CALL errorProc();결과 값:
테이블이 없어요ㅠㅠ
위 코드에서 1146대신에 SQLSTATE '42S02'로 써줘도 된다. 둘다 테이블이 없을 경우를 의미한다. sqldb의 usertbl에 이미 존재하는 'LSG'라는 아이디를 생성시켜 보도록 하자. userID열은 기본 키로 지정되어 있으므로, 같은 ID를 입력할 수 없으므로 오류가 발생할 것이다.
DROP PROCEDURE IF EXISTS errorProc2;
DELIMITER $$
CREATE PROCEDURE errorProc2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS; -- 오류 메시지를 보여 준다.
SELECT '오류가 발생했네요. 작업은 취소시켰습니다.' AS '메시지';
ROLLBACK; -- 오류 발생 시 작업을 롤백시킨다.
END;
INSERT INTO usertbl VALUES('LSG', '이상구', 1988, '서울', NULL,
NULL, 170, CURRENT_DATE()); -- 중복되는 아이디이므로 오류 발생
END $$
DELIMITER;
CALL errorProc2();
별로 설명할 것도 없이 구문과 결과만 봐도 이해가 되었을 것이다. SHOW ERRORS문은 오류에 대한 코드와 메시지를 출력한다. ROLLBACK은 진행중인 작업을 취소시키며, COMMIT은 작업을 완전히 확정시키는 구문이다.
!SHOW COUNT(*) ERRORS문은 발생된 오류의 개수를 출력해 주며, SHOW WARNINGS문은 경고에 대한 코드와 메시지를 출력한다.
동적 SQL
PREPARE는 SQL문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE문은 준비한 쿼리문을 실행한다. 그리고 실행 후에는 DEALLOCATE PREFARE로 문장을 해제해 주는 것이 바람직하다.
use sqldb;
PREPARE myQuery FROM 'SELECT * FROM usertbl WHERE userID = "EJW"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;즉, SELECT * FROM usertbl WHERE userID="EJW" 문장을 바로 실행하지 않고, myQuery에 입력시켜 놓는다. 그리고 EXECUTE문으로 실행할 수 있다.
이렇게 미리 쿼리문을 준비한 후에 나중에 실행하는 것을 '동적 SQL'이라고도 부른다. 이 동적 SQL은 종종 유용하게 사용될 수 있다.
또한, PREPARE문에서 ?으로 향후에 입력될 값을 비워놓고, EXECUTE에서는 USING을 이용해서 값을 전달해서 사용할 수 있다. 이를 활용해 보자. 다음은 쿼리를 실행하는 순간의 날짜와 시간이 입력되는 기능을 한다.
USE sqldb;
DROP TABLE IF EXISTS myTable;
CREATE TABLE myTable (id INT AUTO_INCREMENT PRIMARY KEY, mDate DATETIME);
SET @curDATE = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간
PREPARE myQuery FROM 'INSERT INTO myTable VALUES(NULL, ?)';
EXECUTE myQuery USING @curDATE;
DEALLOCATE PREPARE myQuery;
SELECT * FROM myTable;지금까지 학습한 내용은 나중에 스토어드 프로시저, 스토어드 함수, 커서, 트리거에서 적극적으로 활용될 내용이므로 잘 기억해 놓자.
