表category
字段 category_id name parent_id
1 *** 0
2 *** 1
3 *** 2
4 *** 1
5 *** 4
写了一个函数 如下
DELIMITER $$DROP FUNCTION IF EXISTS `treerole`.`f_cid` $$
CREATE FUNCTION `treerole`.`f_cid`(@Node_id VARCHAR(45)) RETURNS @Child table(category_id VARCHAR(20) primary key,name VARCHAR(45),parent_id VARCHAR(45))
BEGIN
Insert @Child Select * From category Where category_id=@Node_id
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join category B On A.category_id=B.parent_id Where B.parent_id Not In (Select Distinct parent_id From @Child)
Return @Child table(category_id VARCHAR(20) primary key,name VARCHAR(45),parent_id VARCHAR(45))
END $$DELIMITER ;报错如下Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@Node_id VARCHAR(45)) RETURN @Child table(category_id VARCHAR(20) primary key,n' at line 1
求高手解决下 给发个完整的谢谢
字段 category_id name parent_id
1 *** 0
2 *** 1
3 *** 2
4 *** 1
5 *** 4
写了一个函数 如下
DELIMITER $$DROP FUNCTION IF EXISTS `treerole`.`f_cid` $$
CREATE FUNCTION `treerole`.`f_cid`(@Node_id VARCHAR(45)) RETURNS @Child table(category_id VARCHAR(20) primary key,name VARCHAR(45),parent_id VARCHAR(45))
BEGIN
Insert @Child Select * From category Where category_id=@Node_id
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join category B On A.category_id=B.parent_id Where B.parent_id Not In (Select Distinct parent_id From @Child)
Return @Child table(category_id VARCHAR(20) primary key,name VARCHAR(45),parent_id VARCHAR(45))
END $$DELIMITER ;报错如下Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@Node_id VARCHAR(45)) RETURN @Child table(category_id VARCHAR(20) primary key,n' at line 1
求高手解决下 给发个完整的谢谢
我不知道 他为什么说我语法错误 ?
是不是mysql不给返回表? 我主要想用mysql写一个(查询指定节点及其所有子节点的函数)
网上都是sql server
我的mysql版本是5.0的!
帮个忙 解决下给个实例 在此谢过!