MySQL 自定义函数将罗马数字转为阿拉伯数字

e.g. XXCVI

Into its Arabic numbering equivalent. Its a long story as to why I need it, I just do.

Based on a PHP function that someone posted, I created the following MySQL Function, but it seems to be running endlessly and I'm not sure why. (I might just be too tired)

Anybody have any hints as to what's wrong with my function, or have a more efficient way to convert a roman numeral string into an Arabic number?

DROP FUNCTION IF EXISTS `romeToArabic`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `romeToArabic`(roman_in VARCHAR(64)) RETURNS int(11)
BEGIN
  DECLARE numeral VARCHAR(2);
  DECLARE int_val INT;
  DECLARE roman VARCHAR(64);
  DECLARE res INT;
  DECLARE no_more_rows BOOLEAN;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE roman_cur CURSOR FOR SELECT num, val FROM roman_numeral ORDER BY id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
  SET roman = UPPER(roman_in);
  SET res = 0;

  DROP TEMPORARY TABLE IF EXISTS roman_numeral;
  CREATE TEMPORARY TABLE roman_numeral (
      `id` INT(8) NOT NULL AUTO_INCREMENT,
      `num` varchar(2) DEFAULT NULL,
      `val` int(8) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;

  INSERT INTO roman_numeral (num, val) VALUES ('M', 1000), ('CM', 900), ('D', 500), ('CD', 400), ('C', 100), ('XC', 90), ('L', 50), ('XL', 40), ('X', 10), ('IX', 9), ('V', 5), ('IV', 4), ('I', 1);

  OPEN roman_cur;
  SELECT FOUND_ROWS() INTO num_rows;

 the_loop:
  LOOP
    FETCH  roman_cur INTO   numeral, int_val;
    IF no_more_rows THEN CLOSE roman_cur;
      LEAVE the_loop;
    END IF;

    WHILE INSTR(roman, numeral) = 1 DO
      SET res = res + int_val;
      SET roman = SUBSTRING(roman, LENGTH(numeral));
    END WHILE;

  END LOOP the_loop;
  IF res > 0 THEN
    RETURN res;
  ELSE
    RETURN -1;
  END IF;
END$$

Not sure why your isnt working but googling was fast and I came up with this link:

http://forge.mysql.com/tools/tool.php?id=107

CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC
BEGIN

    DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM';

    DECLARE digit TINYINT;
    DECLARE previous INT DEFAULT 0;
    DECLARE current INT;
    DECLARE sum INT DEFAULT 0;

    SET inRoman = UPPER(inRoman);

    WHILE LENGTH(inRoman) > 0 DO
        SET digit := LOCATE(RIGHT(inRoman, 1), numeral) - 1;
        SET current := POW(10, FLOOR(digit / 2)) * POW(5, MOD(digit, 2));
        SET sum := sum + POW(-1, current < previous) * current;
        SET previous := current;
        SET inRoman = LEFT(inRoman, LENGTH(inRoman) - 1);
    END WHILE;

    RETURN sum;
END

这篇关于MySQL 自定义函数将罗马数字转为阿拉伯数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！