問題描述
好的，我需要一個 MySQL 函數來轉換羅馬數字字符串:
例如XXCVI
轉換成它的阿拉伯數字等價物.至于我為什么需要它，這是一個很長的故事，我只是需要.
基于某人發布的 PHP 函數，我創建了以下 MySQL 函數，但它似乎無休止地運行，我不知道為什么.(我可能只是太累了)
有人對我的函數有什么問題有任何提示，或者有更有效的方法將羅馬數字字符串轉換為阿拉伯數字嗎?
DROP 函數如果存在`romeToArabic`$$CREATE DEFINER=`root`@`localhost` FUNCTION `romeToArabic`(roman_in VARCHAR(64)) 返回 int(11)開始聲明數字 VARCHAR(2);聲明 int_val INT;聲明羅馬 VARCHAR(64);DECLARE res INT;聲明 no_more_rows 布爾值；聲明 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;設置羅馬 = UPPER(roman_in);設置資源= 0;如果存在 roman_numeral，則刪除臨時表；創建臨時表 roman_numeral (`id` INT(8) NOT NULL AUTO_INCREMENT,`num` varchar(2) 默認為空，`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), ('我', 1);打開 roman_cur;SELECT FOUND_ROWS() INTO num_rows;the_loop:環形FETCH roman_cur INTO 數字，int_val；IF no_more_rows THEN CLOSE roman_cur;離開 the_loop;萬一;WHILE INSTR(羅馬，數字)= 1 DOSET res = res + int_val;設置羅馬= SUBSTRING(羅馬，長度(數字))；結束時；結束循環 the_loop;IF res＞0 那么返回資源；別的返回-1；萬一;完$$不知道為什么你不工作，但谷歌搜索很快，我想出了這個鏈接:
http://forge.mysql.com/tools/tool.php?id=107
CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC開始DECLARE 數字 CHAR(7) DEFAULT 'IVXLCDM';聲明數字 TINYINT;DECLARE 前一個 INT DEFAULT 0;聲明當前的INT；DECLARE sum INT DEFAULT 0;SET inRoman = UPPER(inRoman);雖然長度(羅馬)>0 做SET digit := LOCATE(RIGHT(inRoman, 1), numeric) - 1;SET current := POW(10, FLOOR(digit/2)) * POW(5, MOD(digit, 2));SET sum := sum + POW(-1, current Ok, I need a MySQL Function that will convert a Roman Numeral String:
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模板網！