파트 5-6. SQL 프로그래밍

1. sql 프로그래밍


sq에서도 다른 프로그래밍 언어와 비슷한 분기, 흐름 제어, 반복의 기능이 있다. 이러한 기능을 전에 소개했던 변수와 함께 잘 활용한다면 강력한 sql 프로그래밍이 가능하다. sql 프로그래밍을 진행하기 전에 우선 스토어드 프로시저를 만들고 사용하는 방법을 간단히 요약하고 넘어가자.

delimiter $$
create procedure 스토어드 프로시저이름()
begin

 부분에 sql 프로그램이 코딩

end $$
delimiter ;
call 스토어드 프로시저이름();

delimiter \(~ end\) 부분까지는 스토어드 프로시저의 코딩할 부분을 묶어준다고 보면 된다. mysql의 종료 문자는 ;인데 create procedure 안에서도 세미콜론이 종료 문자이므로 어디까지가 스토어드 프로시저인지 구별이 어렵다. 그래서 end $$ 가 나올 때까지를 스토어드 프로시저로 인식하게 하는 것이다. 그리고 다시 delimiter ;로 종료 문자를 ;으로 변경해 놓아야 한다. call 스토어드 프로시저이름(); 은 create procedure로 생성한 스토어드 프로시저를 호출한다. 결국 위 코드에서 스토어드 프로시저이름과 sql 프로그램 코딩 부분만 수정해서 사용하면 된다.


2. if else


조건에 따라 분기한다. 한 문장 이상이 처리되어야 할 때는 begine.. end와 함께 묶어줘야만 하며, 습관적으로 한 문장이더라고 묶어주는 것이 좋다.

-- 예시1
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();

-- 예시 2
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();


3. case문


일반적으로 여러 언어에서 사용되는 case문이랑 똑같다. case문은 혹시 조건에 맞는 when이 여러 개더라도 먼저 조건이 만족하는 when이 처리된다. 그리고 case를 종료한다. 학점을 예시로 작성해보자.

-- 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();

-- 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();


실습

그림1

case문을 활용하는 sql 프로그래밍을 실습해보자.
sqldb의 구매 테이블에 구매액이 1500원 이상인 고객은 최우수 고객, 1000원 이상인 고객은 우수고객, 1원 이상인 고객은 일반고객으로 출력하자. 전혀 실적이 없다면 유령고객이라고 출력하자. 출력결과는 아래와 같다.

그림2

use sqldb;
-- 사실 amount와 price는 buytbl에만 있어서 별칭을 안써줘도 된다.
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 ;

-- 프로시져에서 사용할때는 case가 끝날때 end case라고 작성했는데
-- 프로시저가 아닐때는 그냥 end 라고만 쓰면 되는 것 같다.


4. while과 iterate/leave


다른 프로그래밍 언어의 while문과 동일한 개념이다.

-- 형식
while <부울 > do
    sql 명령문
end 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();


그런데 만약 1에서 100까지 합계에서 7의 배수를 제외시키고, 도중에 합이 1000이 넘으면 그만두고 싶다면 어떻게 해야 할까? 그럴 경우에는 iterate문과 leave문을 사용할 수 있다.

  • iterate문 : 지정한 label문으로 가서 계속 진행한다.
  • leave문 : 지정한 label문을 떠난다.
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 whileProc2();


5. 오류처리


mysql은 오류가 발생할 경우 직접 오류를 처리하는 방법을 제공한다. 그 형식은 아래와 같다.

declare 액션 handler for 오류조건 처리할_문장;
  • 액션 : 오류 발생 시에 행동을 정의하는데 continue와 exit 둘 중 하나를 사용한다. continue가 나오면 제일 뒤의 ‘처리할_문장’ 부분이 처리된다.
  • 오류조건 : 어떤 오류를 처리할 것인지를 지정한다. 여기에는 mysql의 오류 코드 숫자가 오거나 sqlstate’상태코드’, sqlexception, sqlwarning, not found 등이 올 수 있다. sqlstate에서 상태코드는 5자리 문자열로 되어 있다. sqlexception은 대부분의 오류를, sqlwarning은 경고 메시지를, not found는 커서나 select…into에서 발생되는 오류를 의미한다.
  • 처리할_문장 : 처리할 문장이 하나라면 한 문장이 나오면 되며, 처리할 문장이 여러 개일 경우에는 begin..end로 묶어줄 수 있다.
-- 예시 1
-- 없는 테이블을 조회해서 '테이블이 없어요'가 출력된다.
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();


-- 예시 2
DROP PROCEDURE IF EXISTS errorProc2; 
DELIMITER $$
CREATE PROCEDURE errorProc2()
BEGIN
    -- sql 오류가 발생했을 때 여러 문장을 실행해야 할 경우 begin과 end로 묶는다.
    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();


6. 동적 sql


  • prepare문 : sql문을 실행하지는 않고 미리 준비만 해놓는다.
  • execute문 : 준비한 쿼리문을 실행한다.
  • deallocate prepare : 실행 후에는 deallocate prepare로 문장을 해제해 주는 것이 좋다.

미리 쿼리문을 준비한 후에 나중에 실행하는 것을 동적 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(); -- 현재 날짜와 시간
-- from 이후에 ''내용을 myquery에 저장
prepare myquery from 'insert into mytable values(null,?)';
EXECUTE myquery using @curdate; -- 실행
deallocate prepare myquery; -- myquery 삭제

select * from mytable;



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony