[DB]프로시저를 이용한 데이터 입력 및 검증

데이터 입출력 구현

프로시저를 이용한 데이터입력 및 검증 실습 연습문제 / 쿼리

1. 누락된 관광지의 정보(attrc_cd)를 테이블 attraction 에 입력.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_missed_attrc_cd`() 
BEGIN
--// 변수선언(begin 아래 일괄선언.)
DECLARE _done INT DEFAULT FALSE; 
DECLARE _prvnCd INT;
DECLARE _distcCd INT;
DECLARE _attrcName VARCHAR(100);

-- // 데이터베이스에서 가지고 와야할 정보가 필요한 경우. 커서-패치를 이용해 데이터베이스에서 필요한 정보를 가지고 온다! 
      여기서는 기존 관광지 데이터를 참조하여 arrtc_cd를 입력해야 하기때문에 커서 필요.

DECLARE _attrc_cursor_ CURSOR FOR //커서선언 데이터 가져와서 사용하기 위한. 

SELECT p.prvn_cd, d.distc_cd, o.attrc_name // 가져와서 다시 써야할 것들만 셀렉해오면 된다. 즉, 사용할 데이터의 범위.. 
FROM org_data o LEFT OUTER JOIN province p ON o.prvn_name = p.prvn_name
LEFT OUTER JOIN district d ON o.distc_name = d.distc_name
LEFT OUTER JOIN attraction a ON o.attrc_name = a.attrc_name
WHERE a.attrc_cd IS NULL // null 값이 포함된 데이터를 새로 insert해주어야 하므로 대상이 되는 데이터들만 불러옴, 
GROUP BY p.prvn_cd, d.distc_cd, o.attrc_name, a.attrc_cd ; 

-- //핸들러 선언 
DECLARE CONTINUE HANDLER FOR NOT FOUND //커서 상태 조절 위함. NOT FOUND:더이상 없을때.
SET _done = TRUE; //다음 실행: _done을 참으로 만들어라. 

-- ///커서 오픈
OPEN _attrc_cursor_;
REPEAT //반복 
FETCH _attrc_cursor_ INTO _prvnCd,_distcCd,_attrcName; //패치로 앞에서 셀렉해온 디비 데이터들을 차례로 변수에 담는다.  셀렉컬럼수와 변수의 순서와 수가 일치해야한다 

IF NOT _done THEN // 만약 done 이 거짓일때 (false일때 ) 
INSERT INTO attraction (prvn_cd, distc_cd, attrc_name, attrc_cd) // 빈칸있는 정보들을 빼와서, 기존정보는 다시 똑같이 덮어쓰기해주고, 빈칸에는 보정값을 넣어주는것. 
SELECT //셀렉해온 값 자체를 인서트 해준다. 
_prvnCd,  // 패치가 가져온데이터 그대로 넣고
_distcCd, // 패치가 가져온데이터 그대로 넣고
_attrcName, // 패치가 가져온데이터 그대로 넣고
MAX(attrc_cd) +1 as attrc_cd // null값인 곳에다 컬럼에서 가져온 최대값 +1 해준 값을 넣는것임!!!!!!!! 
FROM attraction //맥스값은 조인필요없이 그냥 어트렉션 테이블에서만 구할수 있는 것이었음...(조건문으로.)  
WHERE prvn_cd =_prvnCd AND distc_cd =_distcCd ; // 최대값을 구할때 15-1  / 15-2 경우로 나누어지는데, 데이터들을 그대로 가지고 오면 알아서 바뀜..  


END IF ;

UNTIL _done END REPEAT; //반복종료
CLOSE _attrc_cursor_; //커서 닫기 

END

2. org_data에 적재된 시계열 데이터를 해제하여 테이블(figure)에 입력. *매개변수로 기준일 또는 기준일을 입력받으면 편함.

 

 a)1월을 기준으로 작성

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_figure_without_cursor`()

// 아무것도 없는 디비에 데이터를 가져와 넣는 것이기 때문에 굳이 커서를 사용하지 않아도 된다.

     데이터를 참조한 변수에 참조데이터를 담을 경우에만 커서-패치를 씀. . 
BEGIN
// 변수 선언 필요없이, 바로 인서트.
INSERT INTO figure(native_cnt, fore_cnt, basis_date, reg_date, prvn_cd, distc_cd, attrc_cd)
SELECT MAX(IF(o.visitor_type='내국인',o.jan,0)) as native_cnt , // 내국인

             MAX(IF(o.visitor_type='외국인',o.jan,0)) as fore_cnt , //외국인. 나누어져있던 row를 하나로만드는 작업.
            CONCAT(year,'01','01') as basis_date, // basis_date

             now(),
                a.prvn_cd, a.distc_cd, a.attrc_cd
FROM org_data o JOIN attraction a ON o.attrc_name = a.attrc_name
GROUP BY year,o.attrc_name,a.prvn_cd, a.distc_cd,a.attrc_cd;
END

 b) 1-12월 컬럼(jan-dec)을 동적으로 정할 수 있게끔 변경 

SELECT     MAX(IF(o.visitor_type='내국인', @month, 0)) as native_cnt , 
                     MAX(IF(o.visitor_type='외국인', @month, 0)) as fore_cnt ,
                    @basisDate ,
                    now(),

                     a.prvn_cd, a.distc_cd, a.attrc_cd
FROM org_data o LEFT OUTER JOIN attraction a ON o.attrc_name = a.attrc_name
GROUP BY year,o.attrc_name,a.prvn_cd, a.distc_cd,a.attrc_cd ,o.attrc_type

    

  c) b를 수행하는 select 쿼리가 작성되면 INSERT 할것

INSERT INTO figure(native_cnt, fore_cnt, basis_date, reg_date, prvn_cd, distc_cd, attrc_cd)
SELECT     MAX(IF(o.visitor_type='내국인', @month, 0)) as native_cnt , 
                     MAX(IF(o.visitor_type='외국인', @month, 0)) as fore_cnt ,
                    @basisDate ,
                    now(),

                     a.prvn_cd, a.distc_cd, a.attrc_cd
FROM org_data o LEFT OUTER JOIN attraction a ON o.attrc_name = a.attrc_name
GROUP BY year,o.attrc_name,a.prvn_cd, a.distc_cd,a.attrc_cd ,o.attrc_type

(e) b가 된다면 년도만 입력했을때 1-12 월까지 수행하게끔 변경 

CREATE DEFINER=`root`@`localhost` PROCEDURE `_sp_dynamic_add_figure`( IN year VARCHAR(10))

//커서로 가져오는 작업이 없으므로 년도는 매개변수로 받아온다. 프로시저 콜 할때 입력해주어야함.

BEGIN
DECLARE _basisDate VARCHAR(12); //파라미터 변수 (@basisDate를 담을 변수선언)
DECLARE _month VARCHAR(6); //파라미터 변수 (@month를 담을 변수선언)
DECLARE _monthValue INT; // 1~12월까지 동적으로 변경하며 데이터 입력하기 위한 변수
DECLARE _sql VARCHAR(2000); //쿼리 입력할 변수 sql

SET _monthValue = 1; // 시작값이 1월이므로 1로 초기화.
REPEAT //반복시작 

SET _sql =  // sql이라는 변수에 sql쿼리문(앞에서 만들어준 @파라미터변수 포함한 쿼리문.)을 대입한다. 
" INSERT INTO figure(native_cnt, fore_cnt, basis_date, reg_date, prvn_cd, distc_cd, attrc_cd)
                SELECT  MAX(IF(o.visitor_type='내국인', @month, 0)) as native_cnt , 
                                MAX(IF(o.visitor_type='외국인', @month, 0)) as fore_cnt ,
                               @basisDate ,
                                now(),

                                a.prvn_cd, a.distc_cd, a.attrc_cd
FROM org_data o LEFT OUTER JOIN attraction a ON o.attrc_name = a.attrc_name
GROUP BY year,o.attrc_name,a.prvn_cd, a.distc_cd,a.attrc_cd " ; //쿼리문 자체를 한 문자열로 하여 변수에 대입.


SET _basisDate = CONCAT(year,LPAD(_monthValue,2,'0'),'01');

//basisDate: 매개변수로받은 년도, 반복문 이용하여 동적으로 변하게 할 월값, 무조건 유효한 1일값으로. ex)19910315
SET _month = CASE _monthValue WHEN 1 THEN 'jan' 
                                                          WHEN 2 THEN 'feb'
                                                          WHEN 3 THEN 'mar'
                                                          WHEN 4 THEN 'apr'
                                                          WHEN 5 THEN 'may
                                                          WHEN 6 THEN 'june'
                                                          WHEN 7 THEN 'july'
                                                          WHEN 8 THEN 'aug'
                                                          WHEN 9 THEN 'sept'
                                                          WHEN 10 THEN 'oct'
                                                          WHEN 11 THEN 'nov'
                                                          ELSE 'o.dec'
                                                          END; // org_date의 칼럼이름은 문자열의 월값이므로,  monthValue에따라 각 month 값을 문자열로 알맞게 대입.

// 여기서 replace함수을 통해 변수의 값들이 파라미터변수로 대채된다. 

이작업을 해주지않으면 쿼리가 각 변수에 담긴 값이 아닌 @변수이름 그대로 들어가기 때문에 에러가 남.
SET _sql = REPLACE(_sql, '@month', _month); // 반복문이 돌아가면서 월을 하나씩 늘여가며 1~12까지의 값이 @파라미터 변수로 대체된다. 
SET _sql = REPLACE(_sql, '@basisDate', _basisDate); // 동일한 작업. 
SET @sql = _sql; //쿼리문을 대입한 변수 _sql을 파라미터변수 @sql로 다시 대입한다. 

PREPARE stmt from @sql; //@sql로 부터 (쿼리문 담겨있는 ) statement(stmt)준비. 
EXECUTE stmt;  // 실행: sql문이 실행되면서 데이터베이스로 데이터값(row) 하나가 insert된다. 첫번째는 1월데이터 삽입.
SET _monthValue = _monthValue + 1 ;  // 이제 2~12월까지 반복하기 위해 월값을 1씩 증가시켜준다. 

UNTIL _monthValue > 12 END REPEAT; // until (조건 ) end repeat. // 12보다 커지면 반복 종료 .. 
END

3. 2010년부터 2017년까지의 데이터를 org_data 에 적재하여 프로시저 test

  TEST 완료.