[SQL] MYSQL 스토어드 프로시저(IF, CASE, WHILE)와 동적 SQL

✍️ Study Note

 

 

 

---

 

 

1. 프로시저

프로시저(PROCEDURE) 생성

-- 스토어드 프로시저 생성

DELIMITER $$

CREATE PROCEDURE '스토어드 프로시저 이름'

BEGIN 

'~~ SQL 프로그래밍 코딩 ~~'

END $$

DELIMITER ;



-- 스토어드 프로시저 실행

CALL '스토어드 프로시저 이름'

- 프로시저를 ㅌ

 

프로시저(PROCEDURE)  + IF 조건

DELIMITER $$
CREATE PROCEDURE IFPROC()
BEGIN 
	DECLARE VAR1 INT; 			-- 변수 선언
    SET VAR1 = 100; 				-- 변수 값 대입
    
    IF VAR1 = 100 THEN 				-- IF 조건 삽입
		SELECT '100입니다.'; 		-- 참값 문장 끝에도 ';' 작성 필요
	ELSE 
		SELECT '100이 아닙니다.'; 	-- 거짓값 문장 끝에도 ';' 작성 필요
	END IF; -- IF 끝
END $$
DELIMITER ;
CALL IFPROC();


-- 사번 10001 직원의 입사일 5년 초과여부 확인
DELIMITER $$
CREATE PROCEDURE IFPRO2()
BEGIN 
	DECLARE HIREDATE DATE; 			-- 변수 선언
    DECLARE CURDATE DATE;
    DECLARE DAYS INT;
    
    SELECT HIRE_DATE INTO HIREDATE 		-- EMPLOYEES 테이블에 있는 HIRE_DATE를 변수 HIREDATE에 대입
    FROM EMPLOYEES.EMPLOYEES
    WHERE EMP_NO = 10001;
    
    SET CURDATE = CURRENT_DATE(); 		-- 변수 값 대입
    SET DAYS = DATEDIFF(CURDATE, HIREDATE); 	-- 변수 값 대입
    
    IF (DAYS/365)>5 THEN 
		SELECT CONCAT('입사한지', DAYS, '일이 지났습니다. 축하합니다!');
	ELSE
		SELECT CONCAT('입사한지', DAYS, '일이 지났습니다. 수고하셨습니다.');
	END IF;
END $$
DELIMITER ;
CALL IFPRO2();

 

 

프로시저(PROCEDURE) + IF & CASE 조건

-- #1
-- IF & ELSEIF 사용한 다중조건문
DELIMITER $$
CREATE PROCEDURE IFPROC3()
BEGIN
	DECLARE POINT INT; 	-- 변수선언
    DECLARE CREDIT CHAR(1); 	-- 변수선언
    
    SET POINT = 77; 		-- 변수 값 대입
    
    IF POINT >= 90 THEN 		-- 조건1
		SET CREDIT = 'A';
	ELSEIF POINT >= 80 THEN 	-- 조건2
		SET CREDIT = 'B';
	ELSEIF POINT >= 70 THEN 	-- 조건3
		SET CREDIT = 'C';
	ELSEIF POINT >=60 THEN 		-- 조건4
		SET CREDIT = 'D';
	ELSE
		SET CREDIT = 'F'; 	-- 모든 조건 아닐 때, 디폴트 값
	END IF;
	SELECT CONCAT('취득점수 ==>', POINT), CONCAT('학점 ==>', CREDIT);
END $$
DELIMITER ;
CALL IFPROC3();


-- #2
-- CASE 사용한 다중조건문
DELIMITER $$
CREATE PROCEDURE CASEPROC()
BEGIN 
	DECLARE POINT INT;
    DECLARE CREDIT CHAR(1);
    
    SET POINT = 77;
    
    CASE
		WHEN POINT >= 90 THEN 		-- 조건1
			SET CREDIT = 'A';
		WHEN POINT >= 80 THEN 		-- 조건2
			SET CREDIT = 'B';
		WHEN POINT >= 70 THEN 		-- 조건3
			SET CREDIT = 'C';
		WHEN POINT >= 60 THEN 		-- 조건4
			SET CREDIT = 'D';
		ELSE
			SET CREDIT = 'F'; 	-- 모든 조건 아닐 때, 디폴트 값
	END CASE;
	SELECT CONCAT('취득점수 ==>', POINT), CONCAT('학점 ==>', CREDIT);
END $$
DELIMITER ;
CALL CASEPROC();

- 동일한 결과값

 

 

REF. CASE 구문 

SELECT U.USERID, U.NAME, SUM(AMOUNT*PRICE) AS '총구매액', 
	CASE WHEN ('총구매액' > 1500) THEN '최우수고객'
		WHEN ('총구매액' > 1000) THEN '우수고객'
		WHEN ('총구매액' > 1) THEN '일반고객'
		ELSE '유령고객'
	END AS '고객등급'
FROM BUYTBL B RIGHT JOIN USERTBL U 
ON B.USERID = U.USERID
GROUP BY U.USERID
ORDER BY U.USERID;

 

 

프로시저(PROCEDURE) + WHILE, ITERATE, LEAVE

-- #1
-- 1~100까지 더한 값 구하기
DELIMITER $$
CREATE PROCEDURE WHILEPROC()
BEGIN 
	DECLARE I INT;
    DECLARE HAP INT;
    
    SET I = 1;
    SET HAP = 0;
    
    WHILE (I <=100) DO			-- I가 100이 될때까지 진행하라
		SET HAP = HAP + I;  	-- HAP에 I값을 더하여 HAP에 넣어라 (누적값)
        SET I = I + 1;			-- I는 1씩 증가
	END WHILE;
    
    SELECT HAP;
    
END $$
DELIMITER ;
CALL WHILEPROC();
        

-- #2
-- [조건1] 1~100까지 더하되, 7의 배수는 더하기 제외 
-- [조건2] 총 합계 >1000 이면 중단
DELIMITER $$
CREATE PROCEDURE WHILEPROC2()
BEGIN 
	DECLARE I INT; -- 변수 선언
    DECLARE HAP INT;
    
    SET I = 1; -- 변수 시작값 설정
    SET HAP = 0;
    
    MYWHILE : WHILE (I <=100) DO 	-- WHILE 문에 'MYWHILE' 이름 붙여줌
	IF(I%7 =0) THEN 			
		SET I = I + 1; 			
        ITERATE MYWHILE;		-- [조건1] I = 7의 배수일때, [I+1] 계산하고 'MYWHILE'로 돌아감
	END IF;
    
    SET HAP = HAP + I;			-- I = 7의 배수X 일때, HAP에 누적으로 더하기
    IF (HAP >1000) THEN 		
		LEAVE MYWHILE;		-- [조건2] HAP > 1000일 경우 중단
	END IF;
		SET I = I + 1;		-- I = 7의 배수X 일때, HAP에 더하기까지 완료했으면 +1 진행. 
	END WHILE;
    
    SELECT HAP;
END $$
DELIMITER ;
CALL WHILEPROC2();

 

-- 3과 8의 배수 합계 더하기 (~1000까지)

DELIMITER $$
CREATE PROCEDURE WHILEPROC3()
BEGIN
	DECLARE I INT;
    DECLARE HAP INT;
    
    SET I = 1;
    SET HAP = 0;
    
    MYWHILE : WHILE (I <=1000) DO
    IF (I%3 OR I%8 = 0) THEN 
		SET HAP = HAP + I;
        SET I = I +1;
        ITERATE MYWHILE;
	END IF;
    
    SET I = I +1;
    IF (I >1000) THEN 
		LEAVE MYWHILE;
    END IF;
    END WHILE;
    
    SELECT HAP;
END $$
DELIMITER ;
CALL WHILEPROC3();

 

프로시저(PROCEDURE) 삭제

• DROP PROCEDURE 프로시저이름 
• 이때 프로시저이름 뒤에 '()'는 미기재.

DROP PROCEDURE IF EXISTS CASEPROC;

 

 

---

 

 

2. 동적 SQL & 오류처리

오류 처리

DECLARE CONTINUE HANDLER FOR 오류조건 처리할_문장;

오류조건에 들어갈 수 있는 항목 

1. 오류 코드 숫자 (NO TABLE : 1146)
2. SQLSTATE '상태코드' : 5자리 문자열 (NO TABLE : 42S02)
3. SQLEXCEPTION : 대부분의 오류
4. SQLWARNING : 경고메시지
5. NOT FOUND : SELECT -- INTO에서 발생하는 오류

 

-- #1
DELIMITER $$
CREATE PROCEDURE ERRORPROC()
BEGIN 
    DECLARE CONTINUE HANDLER FOR 1146 SELECT '테이블이 없어요ㅠㅠ' AS '메시지'; -- 오류처리 선언
    SELECT * FROM NOTABLE; 						-- 오류 넣어보기 (테스트)
END $$
DELIMITER ;
CALL ERRORPROC();

-- #2
DELIMITER $$
CREATE PROCEDURE ERRORPROC2()
BEGIN 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION				-- 오류처리 선언
    BEGIN 
	SHOW ERRORS; 							-- 에러 보여주기
        SELECT '오류가 발생했네요. 작업은 취소합니다.' AS '메시지'; 	-- 에러 안내
        ROLLBACK;							-- 에러 발생한 작업 취소
	END;
    INSERT INTO USERTBL VALUES 
    ('LSG', '이상구', 1988, 'TJDNF', NULL, NULL, 170, CURRENT_DATE()); 	-- 오류 넣어보기 (테스트)
    
END $$
DELIMITER ;
CALL ERRORPROC2();

에러 메시지 보여주는 명령어

• SHOW ERRORS : 에러 코드, 메시지 출력
• SHOW COUNT(*) ERRORS : 오류 개수 출력
• SHOW WARNINGS : 경고에 대한 코드, 메시지 출력

 

 

 

동적 SQL (PREPARE, EXECUTE, DEALLOCATE)과 변수 사용

-- #1
PREPARE MYQUERY FROM 'SELECT * FROM USERTBL WHERE USERID = "EJW"'; 	-- 문장 설정 
EXECUTE MYQUERY;							-- 문장 실행
DEALLOCATE PREPARE MYQUERY; 						-- 문장 해제


-- #2
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; 					-- 문장 실행. USING으로 변수 이용
DEALLOCATE PREPARE MYQUERY; 						-- 문장 해제
SELECT * FROM MYTABLE;

• PREPARE : 문장 설정. '?'를 사용하여 변수 설정 가능
• EXECUTE : 문장 실행. USING을 사용하여 '?'에 들어갈 변수 지정 가능
• DEALLOCATE : 문장 해제.