-- ONE DDL TEST CASE FOR FUNCTION

CREATE DEFINER=`root`@`localhost` FUNCTION `ROUND_OFF`(IN_VAL DOUBLE,TECHNIQUE INT,PRECESSION_VAL INT) RETURNS double
    DETERMINISTIC
BEGIN

   DECLARE ROUND_VAL DOUBLE;

   DECLARE ADJUST_VAL DOUBLE;

   DECLARE LOOP_COUNT INT;

   DECLARE TEMP_VAL DOUBLE;

      SET ADJUST_VAL=1;

SET LOOP_COUNT=0;

    ADJUST_LOOP: LOOP

         SET ADJUST_VAL=ADJUST_VAL*10;

         

                 IF LOOP_COUNT=PRECESSION_VAL THEN

                      LEAVE ADJUST_LOOP;

                 END IF;

                 

                

                SET LOOP_COUNT=LOOP_COUNT+1;

     END LOOP ADJUST_LOOP;

          SET ADJUST_VAL = 1/ADJUST_VAL;

          

   IF TECHNIQUE=0 THEN

   

IF IN_VAL>0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5) THEN

SET IN_VAL=IN_VAL+ADJUST_VAL;

ELSEIF IN_VAL<0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5) THEN

SET IN_VAL=IN_VAL-ADJUST_VAL;

END IF;

   

SET ROUND_VAL=ROUND(IN_VAL,PRECESSION_VAL);

   END IF;

 IF TECHNIQUE=1 THEN

 

IF IN_VAL<0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5) THEN

SET IN_VAL=IN_VAL+ADJUST_VAL;

ELSEIF IN_VAL>0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5) THEN

SET IN_VAL=IN_VAL-ADJUST_VAL;

END IF;



SET ROUND_VAL=ROUND(IN_VAL,PRECESSION_VAL);

   END IF;

 IF TECHNIQUE=2 THEN

SET ROUND_VAL=CEIL(IN_VAL);

 END IF;

 IF TECHNIQUE=3 THEN

SET ROUND_VAL=FLOOR(IN_VAL);

 END IF;

 

 IF TECHNIQUE=4 THEN

 IF IN_VAL>0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5)  THEN

SET TEMP_VAL=IN_VAL+ADJUST_VAL;

ELSEIF IN_VAL<0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5)  THEN 

SET TEMP_VAL=IN_VAL-ADJUST_VAL; 

ELSE

SET TEMP_VAL=IN_VAL;

  END IF;

SET ROUND_VAL=IF(ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5 

         AND NOT CONVERT(TRUNCATE(ABS(IN_VAL) * POWER(10, PRECESSION_VAL), 0), UNSIGNED) % 2 = 1,

             TRUNCATE(IN_VAL, PRECESSION_VAL), 

              ROUND(TEMP_VAL, PRECESSION_VAL));

 END IF;

 

 IF TECHNIQUE=5 THEN

 IF IN_VAL>0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5)  THEN

SET TEMP_VAL=IN_VAL+ADJUST_VAL;

ELSEIF IN_VAL<0 AND (ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5)  THEN

SET TEMP_VAL=IN_VAL-ADJUST_VAL; 

ELSE

SET TEMP_VAL=IN_VAL;

  END IF;

SET ROUND_VAL=IF(ABS(IN_VAL - TRUNCATE(IN_VAL, PRECESSION_VAL)) * POWER(10, PRECESSION_VAL + 1) = 5 

         AND NOT CONVERT(TRUNCATE(ABS(IN_VAL) * POWER(10, PRECESSION_VAL), 0), UNSIGNED) % 2 = 0,

             TRUNCATE(IN_VAL, PRECESSION_VAL), 

              ROUND(TEMP_VAL, PRECESSION_VAL));

 END IF;

 

RETURN ROUND_VAL;
END
