프로시저 예제.

1. sp_add_district_cd

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

BEGIN

DECLARE _done INT DEFAULT FALSE;

DECLARE prvnCd INT;

DECLARE distcNm VARCHAR(100);

DECLARE _distc_cursor_ CURSOR FOR

SELECT  p.prvn_cd,  o.distc_name

FROM org_data o 

LEFT OUTER JOIN province p ON p.prvn_name = o.prvn_name 

LEFT OUTER JOIN district d ON d.distc_name = o.distc_name AND d.prvn_cd =p.prvn_cd

WHERE d.distc_cd IS NULL

 GROUP BY p.prvn_cd, d.distc_cd, o.distc_name; 

 // 하나로 묶어주지않으면 여러개 들어간다.

 

 DECLARE CONTINUE HANDLER FOR NOT FOUND

 SET _done = TRUE;

 OPEN _distc_cursor_; 

 REPEAT

FETCH _distc_cursor_ INTO prvnCd, distcNm;

IF NOT _done THEN

INSERT INTO district(prvn_cd, distc_cd, distc_name,reg_date)

SELECT prvnCd, MAX(distc_cd)+1, distcNm, now()

FROM district // 맥스값만 필요하기에 조인필요없다.

WHERE prvn_cd = prvnCd;

//조건 줬기 때문에 그룹 필요없다. 

END IF;

    

 UNTIL _done END REPEAT;

 CLOSE _distc_cursor_;

 

END

2.sp_add_attration_cd

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

BEGIN

-- 변수선언

DECLARE _done INT DEFAULT FALSE; -- 반복위한 변수 done 

-- 읽어들여 받을 변수들 선언 

DECLARE prvnCd INT; 

DECLARE distcCd INT;

DECLARE attrcCd INT;

DECLARE attrcType VARCHAR(10);

DECLARE attrcNm VARCHAR(100);

-- 커서선언 

DECLARE _attrc_cursor_ CURSOR FOR

 SELECT  p.prvn_cd, d.distc_cd, a.attrc_cd, o.attrc_name, o.attrc_type

FROM org_data o 

LEFT OUTER JOIN province p ON p.prvn_name = o.prvn_name 

LEFT OUTER JOIN district d ON d.distc_name = o.distc_name AND d.prvn_cd =p.prvn_cd

LEFT OUTER JOIN attraction a ON a.attrc_name = o.attrc_name AND a.distc_cd = d.distc_cd AND a.prvn_cd = p.prvn_cd  AND a.attrc_type = IF(o.attrc_type= '유료',1,0) -- 유료무료 구분!

//같은 곳에 유료/무료가 따로 존재하는 경우를 구분하기 위함.

WHERE a.attrc_cd IS NULL

 GROUP BY p.prvn_cd, d.distc_cd,a.attrc_cd,o.attrc_name,o.attrc_type;

 

 -- 핸들러 선언 

 

 DECLARE CONTINUE HANDLER FOR NOT FOUND -- 더이상 찾을수 없으면 다음문장 실행 

 SET _done = TRUE; -- 더이상 값 없을때 반복 종료됨. 

 

 OPEN _attrc_cursor_;  -- 오픈커서 

 REPEAT

FETCH _attrc_cursor_ INTO prvnCd,distcCd,attrcCd,attrcNm,attrcType; -- 패치로 값데려오기 

IF NOT _done THEN -- 이프문 시작. 만약 던이 거짓이면 반복해라. 

        INSERT INTO attraction(prvn_cd, distc_cd,attrc_cd, attrc_name,attrc_type,reg_date)

SELECT prvnCd, 

distcCd, 

                            IF(MAX(attrc_cd) IS NULL, 1 , MAX(attrc_cd)+1) as attrc_cd, 

                            attrcNm ,

                            IF(attrcType = '유료' , 1, 0),

                            now() 

FROM attraction  // 맥스값만 필요하기에 조인필요없다.

WHERE prvn_cd = prvnCd AND distc_cd = distcCd;

//조건 줬기 때문에 그룹 필요없다. 

END IF; -- 이프 종료 

    

 UNTIL _done END REPEAT; -- 던이 참일때까지 반복

 CLOSE _attrc_cursor_; -- 클로즈 커서 

END

** IF( x IS NULL, 1, x) = IFNULL ( x, 1 )

3. sp_add_figure_dynamic

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_figure_dynamic`(IN _year VARCHAR(10))

BEGIN

DECLARE _basisDate VARCHAR(12); 

DECLARE _month VARCHAR(6); 

DECLARE _monthNum INT; 

DECLARE _sql VARCHAR(2000);

SET _monthNum = 1; 

REPEAT

SET _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 ,

                               @basis_date ,

                                now(),

                                p.prvn_cd, d.distc_cd, a.attrc_cd

FROM org_data o 

LEFT OUTER JOIN province p ON p.prvn_name = o.prvn_name 

LEFT OUTER JOIN district d ON d.distc_name = o.distc_name AND d.prvn_cd =p.prvn_cd

LEFT OUTER JOIN attraction a ON a.attrc_name = o.attrc_name AND a.distc_cd = d.distc_cd AND a.prvn_cd = p.prvn_cd  

                    AND a.attrc_type = if(o.attrc_type= '유료',1,0)

GROUP BY p.prvn_cd, d.distc_cd, a.attrc_cd; " ; 

SET _basisDate = CONCAT(_year,LPAD(_monthNum,2,'0'),'01');

SET _month = CASE _monthNum 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 '`dec`' // dec라는 예약어가 존재하기때문에 문자로 인식시키기 위해 반따옴표로 묶어준다. 

                                                          END;

                                                          

SET _sql = REPLACE(_sql, '@month', _month);  

SET _sql = REPLACE(_sql, '@basis_date', _basisDate);         

SET @sql = _sql ;

PREPARE stmt from @sql; // sql 실행하기 위한 준비장착  

EXECUTE stmt ;             // 실행 

DEALLOCATE PREPARE stmt;   // 준비해제(메모리에서 풀어주는것.)                                            

                                                     

SET _monthNum = _monthNum+1; 

UNTIL _monthNum > 12 END REPEAT;

END