MYSQL 음력 변환 함수

MYSQL 양력 달력 음력 달력으로 변경 할때 함수 

CREATE DEFINER=`db이름`@`%` FUNCTION `함수이름`(
`AS_DATE` VARCHAR(50)
)
RETURNS double
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '생일음력->양력변환 '
BEGIN

DECLARE ll_convert_date         VARCHAR(100)  DEFAULT  '';
      DECLARE LunerY  INT  DEFAULT  0 ;
      DECLARE LunerM  INT  DEFAULT  0 ;
      DECLARE LunerD  INT  DEFAULT  0 ;
      DECLARE i INT(5); 
      DECLARE j INT(5);
	  DECLARE p INT(5);

      DECLARE ll_FromYear  BIGINT  DEFAULT  0 ;
      DECLARE ls_YunMon    VARCHAR(500);     
      DECLARE ls_YunLen    VARCHAR(500);    

      DECLARE ls_MonLen    VARCHAR(500) ;
      DECLARE ls_lYearDay  VARCHAR(500);   
      DECLARE ls_SolarMon  VARCHAR(39) ;

      DECLARE ll_DaySum        BIGINT;

      DECLARE li_M INT  DEFAULT  0 ;
      DECLARE MM INT  DEFAULT  0 ; 
      DECLARE MK INT DEFAULT 0 ;

      DECLARE v_loop INT(5);
      DECLARE li_PrevYear INT(5);
      DECLARE li_OneYearDay  INT(5);
      DECLARE NA INT DEFAULT 0;
      DECLARE YD INT DEFAULT 0;
      DECLARE KA INT DEFAULT 0;
      DECLARE SolarY INT DEFAULT 0 ;
      DECLARE SolarM INT DEFAULT 0 ;
      DECLARE SolarD INT DEFAULT 0;

    SET LunerY = CAST(SUBSTR( as_date,1,4) AS UNSIGNED) + 2333;
    SET LunerM = CAST(SUBSTR( as_date,5,2) AS UNSIGNED);
    SET LunerD = CAST(SUBSTR( as_date,7,2) AS UNSIGNED);

   SET ls_YunMon = Concat(' 0 5 0 0 4 0 0 2 0 6', 
' 0 0 5 0 0 3 0 7 0 0',
' 6 0 0 4 0 0 2 0 7 0',
' 0 5 0 0 3 0 8 0 0 6',
' 0 0 4 0 0 3 0 7 0 0',
' 5 0 0 4 0 8 0 0 6 0',
' 0 4 010 0 0 6 0 0 5',
' 0 0 3 0 8 0 0 5 0 0',
' 4 0 0 2 0 7 0 0 5 0',
' 0 3 0 9 0 0 5 0 0 4',
' 0 0 2 0 6 0 0 5 0 0');

 SET ls_YunLen = Concat(' 029 0 029 0 029 029',
' 0 030 0 030 030 0 0',
'30 0 030 0 029 029 0',
' 030 0 030 029 0 029',
' 0 029 0 029 029 0 0',
'29 0 029 029 0 030 0',
' 029 029 0 029 0 029',
' 0 029 029 0 029 0 0',
'29 0 029 029 0 029 0',
' 030 029 0 029 0 029',
' 0 029 029 0 029 0 0');

   SET ls_lYearDay =
Concat(' 354 384 354 354 385 354 355 384 354 383',
' 354 355 384 355 354 384 354 384 354 354',
                ' 384 355 355 384 354 354 384 354 384 354',
' 355 384 355 354 384 354 384 354 354 384',
                ' 355 354 384 355 353 384 355 384 354 355',
' 384 354 354 384 354 384 354 355 384 355',
                ' 354 384 354 384 354 354 385 354 355 384',
' 354 354 383 355 384 355 354 384 354 354',
' 384 354 355 384 355 384 354 354 384 354',
' 354 384 355 384 355 354 384 354 354 384',
' 354 355 384 354 384 355 354 383 355 354');

   SET ls_MonLen = 
Concat('26352891170527722997 6942395133511751622',
'3658374917051461 69422222350321332213402',
                '346629211389 603 60523493371270934132890',
'290113651243 603213513232715168517062794',
              '2741120627342647131838783477171713862477',
'1245119826383405336534132900343423942395',
              '1179271526352855170117482901 69423951207',
'117516111866374917531453 694241423503222',
'37333402349318771389 699 605234932432709',
'28902890290113731211 6032391132327092965',
'1706277317171206267026471319170234751450');
   
   SET ll_DaySum = 701303  ; 
	SET ll_FromYear = LunerY - 4254  ;

    SET i = 1;
    WHILE i <= ll_FromYear DO
        SET ll_DaySum = ll_DaySum + CAST( SubStr( ls_lYearDay, i*4-3, 4 ) AS UNSIGNED);
        SET i = i + 1;
    END WHILE;
   
   	IF LunerM != 1  THEN 
         SET li_M = 2048;
         SET ll_FromYear = ll_FromYear + 1;
		 SET j = 1;
         SET MM = CAST( SUBSTR( ls_MonLen, ll_FromYear*4-3,4 ) AS UNSIGNED);
		WHILE j <= LunerM - 1 DO
            SET ll_DaySum = ll_DaySum + 29 + TRUNCATE(MM / li_M, 0);
            SET MM = MM - TRUNCATE(MM / li_M, 0) * li_M;
            SET li_M = TRUNCATE(li_M / 2, 0);
            IF  j = CAST( SUBSTR( ls_YunMon,ll_FromYear*2-1,2 )AS UNSIGNED )  THEN  
                SET ll_DaySum = ll_DaySum + 
                      CAST( SUBSTR( ls_YunLen, ll_FromYear*2-1,2 ) AS UNSIGNED);
            END IF;
            SET j = j + 1;
		END WHILE;
   END IF;
   
   SET ll_DaySum = ll_DaySum + LunerD;
   
   SET li_PrevYear = TRUNCATE(ll_DaySum/365, 0) - 1;
   SET NA = TRUNCATE(ll_DaySum - li_PrevYear*365, 0);
   SET YD = TRUNCATE(li_PrevYear/4, 0) 
         - TRUNCATE(li_PrevYear/100, 0) 
         + TRUNCATE(li_PrevYear/400, 0);
   SET KA = NA - YD;

   IF KA < 0 THEN
        SET li_PrevYear = li_PrevYear - 1;
        SET NA = ll_DaySum - TRUNCATE(li_PrevYear*365, 0);
        SET YD = TRUNCATE(li_PrevYear/4, 0) - TRUNCATE(li_PrevYear/100, 0 )
              + TRUNCATE(li_PrevYear/400, 0);
        SET NA = NA - YD;
   ELSE
        SET NA = KA;
   END IF;
   
   SET SolarY = li_PrevYear + 1;
   IF   SolarY = TRUNCATE(SolarY/4, 0)*4  AND  SolarY != TRUNCATE(SolarY/100, 0)*100 THEN
        SET ls_SolarMon =   ' 0 31 29 31 30 31 30 31 31 30 31 30 31';
        SET li_OneYearDay = 366;
   ELSEIF  SolarY = TRUNCATE(SolarY/400, 0)*400 THEN
        SET ls_SolarMon =   ' 0 31 29 31 30 31 30 31 31 30 31 30 31';
        SET li_OneYearDay = 366;
   ELSE
        SET ls_SolarMon =   ' 0 31 28 31 30 31 30 31 31 30 31 30 31';
        SET li_OneYearDay = 365;
   END IF;

   IF NA = 0 THEN
        SET NA = li_OneYearDay;
        SET SolarY = SolarY - 1;
   END IF;
   
    SET p = 1;
    loop_label:LOOP
    	IF ( p <= 13 ) THEN
    		SET v_loop = p;
            IF  NA > CAST( SUBSTR( ls_SolarMon,p*3-2,3 ) AS UNSIGNED) THEN
                SET NA = NA - CAST( SUBSTR( ls_SolarMon,p*3-2,3 ) AS UNSIGNED);
            ELSE 
            	LEAVE loop_label;
            END IF;
            	SET p = p + 1;
        END IF;
   	END LOOP;

    SET SolarM = v_loop - 1;
    SET SolarD = NA;
    SET ll_convert_date = CONCAT(IFNULL(LPAD(SolarY,4,0), '') , 
                IFNULL(LPAD(SolarM,2,0), '') , 
                IFNULL(LPAD(SolarD,2,0), ''));
   Return ll_convert_date;
END