MySQL 带有while循环SELECT的MySQL存储功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16803156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL store function with while loop SELECT
提问by TX T
This is my first time writing a MySQL store function but I am having problem see what did I do wrong:
这是我第一次编写 MySQL 存储函数，但我遇到了问题，看看我做错了什么：
Here is my table and the function:
这是我的表和函数：
mysql> select * from PathNames;
+-----+--------+-----+
| NID | Name | PID |
+-----+--------+-----+
| 1 | / | 1 |
| 6 | US | 1 |
| 7 | TX | 6 |
| 8 | Dallas | 7 |
| 9 | Plano | 7 |
| 10 | Frisco | 7 |
+-----+--------+-----+
DROP FUNCTION IF EXISTS NID2PathName;
DELIMITER //
CREATE FUNCTION NID2PathName(nid INT UNSIGNED)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE rtn TEXT;
DECLARE str TEXT;
DECLARE tmp TEXT;
DECLARE id INT UNSIGNED;
DECLARE pid INT UNSIGNED;
SET id = nid;
SET rtn = "";
SET str = "";
SET tmp = "";
WHILE id > 1 DO
SELECT Name, PID into str, pid FROM PathNames WHERE NID=id LIMIT 1;
SET rtn = CONCAT('/', str, rtn);
set id = pid;
END WHILE;
RETURN rtn;
END//
DELIMITER ;
SELECT NID2PathName(10);
I expect the SELECT NID2PathName(10)to return "/US/TX/Frisco", but I only get "//". Any help will greatly appreciated.
我期望SELECT NID2PathName(10)返回"/US/TX/Frisco"，但我只得到"//". 任何帮助将不胜感激。
回答by Ike Walker
You need to disambiguate the column names from the parameter and variable names.
您需要从参数和变量名称中消除列名称的歧义。
For example, in this line you use the same name "pid" to reference a variable and a table column, and MySQL is not behaving the way you want it to:
例如，在这一行中，您使用相同的名称“pid”来引用一个变量和一个表列，而 MySQL 没有按照您希望的方式运行：
SELECT Name, PID into str, pid FROM PathNames WHERE NID=id LIMIT 1;
I like to use the prefix p_for parameter names and v_for variable names in order to guarantee that the variable names and parameter names will not conflict with column names.
我喜欢p_为参数名和v_变量名使用前缀，以保证变量名和参数名不会与列名冲突。
Here's your function rewritten with that convention. It should work for you:
这是用该约定重写的函数。它应该适合你：
DROP FUNCTION IF EXISTS NID2PathName;
DELIMITER //
CREATE FUNCTION NID2PathName(p_nid INT UNSIGNED)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE v_rtn TEXT;
DECLARE v_str TEXT;
DECLARE v_tmp TEXT;
DECLARE v_id INT UNSIGNED;
DECLARE v_pid INT UNSIGNED;
SET v_id := p_nid;
SET v_rtn := "";
SET v_str := "";
SET v_tmp := "";
WHILE v_id > 1 DO
SELECT Name, PID into v_str, v_pid FROM PathNames WHERE NID=v_id LIMIT 1;
SET v_rtn := CONCAT('/', v_str, v_rtn);
set v_id := v_pid;
END WHILE;
RETURN v_rtn;
END//
DELIMITER ;
