drop database recur;
create database recur;
use recur;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');CREATE PROCEDURE showChildList (IN rootId INT,IN)BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,
id int,
depth int
);
DELETE FROM tmpLst;
CALL createChildLst(rootId,0); select tmpLst.*,treeNodes.*
from tmpLst,treeNodes
where tmpLst.id = treeNodes.id
order by tmpLst.sno;END;
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
call showChildList(1);
call showChildList(3);
call showChildList(5);
直接保存成recur.sql文件,在mysql控制台上运行source命令出错:
ERROR 1064 (42000): 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 ')BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key ' at line 1
ERROR 1146 (42S02): Table 'recur.tmplst' doesn't exist
ERROR 1305 (42000): PROCEDURE recur.createChildLst does not exist
ERROR 1146 (42S02): Table 'recur.tmplst' doesn't exist
ERROR 1064 (42000): 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 'END'
at line 1
ERROR 1064 (42000): 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 '' at
line 3
ERROR 1064 (42000): 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 'DECLA
RE b INT' at line 1
ERROR 1064 (42000): 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 'DECLA
RE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId' at line 1
ERROR 1064 (42000): 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 'DECLA
RE CONTINUE HANDLER FOR NOT FOUND SET done = 1' at line 1
ERROR 1146 (42S02): Table 'recur.tmplst' doesn't exist
ERROR 1064 (42000): 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 'OPEN
cur1' at line 1
ERROR 1064 (42000): 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 'FETCH
cur1 INTO b' at line 1
ERROR 1064 (42000): 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 'WHILE
done=0 DO
CALL createChildLst(b,nDepth+1)' at line 1
ERROR 1064 (42000): 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 'FETCH
cur1 INTO b' at line 1
ERROR 1064 (42000): 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 'END W
HILE' at line 1
我是sql语言的新手,麻烦各位高手指点一下,谢谢。
create database recur;
use recur;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');CREATE PROCEDURE showChildList (IN rootId INT,IN)BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,
id int,
depth int
);
DELETE FROM tmpLst;
CALL createChildLst(rootId,0); select tmpLst.*,treeNodes.*
from tmpLst,treeNodes
where tmpLst.id = treeNodes.id
order by tmpLst.sno;END;
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
call showChildList(1);
call showChildList(3);
call showChildList(5);
直接保存成recur.sql文件,在mysql控制台上运行source命令出错:
ERROR 1064 (42000): 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 ')BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key ' at line 1
ERROR 1146 (42S02): Table 'recur.tmplst' doesn't exist
ERROR 1305 (42000): PROCEDURE recur.createChildLst does not exist
ERROR 1146 (42S02): Table 'recur.tmplst' doesn't exist
ERROR 1064 (42000): 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 'END'
at line 1
ERROR 1064 (42000): 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 '' at
line 3
ERROR 1064 (42000): 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 'DECLA
RE b INT' at line 1
ERROR 1064 (42000): 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 'DECLA
RE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId' at line 1
ERROR 1064 (42000): 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 'DECLA
RE CONTINUE HANDLER FOR NOT FOUND SET done = 1' at line 1
ERROR 1146 (42S02): Table 'recur.tmplst' doesn't exist
ERROR 1064 (42000): 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 'OPEN
cur1' at line 1
ERROR 1064 (42000): 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 'FETCH
cur1 INTO b' at line 1
ERROR 1064 (42000): 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 'WHILE
done=0 DO
CALL createChildLst(b,nDepth+1)' at line 1
ERROR 1064 (42000): 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 'FETCH
cur1 INTO b' at line 1
ERROR 1064 (42000): 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 'END W
HILE' at line 1
我是sql语言的新手,麻烦各位高手指点一下,谢谢。
解决方案 »
- mysql存储过程获得插入数据的自增长ID
- 请教sql语句,如何用like关联两个表中的字段
- 未找到匹配的结果,能否让查询字段返回0?
- Order By 能按实例的子变量排序吗
- mysql的锁,不同表之间怎么会相互影响?
- 开源数据库就是免费数据库么?
- mysql登录的问题!!
- 请问mysql几种版本的区别在什么地方?
- mysql问题,如何将一块数据插入到blob字段中。300分
- 一个网站资金流水记录表的设计
- MySQL root密码重置报错:mysqladmin: connect to server at 'localhost' failed的解决方案!
- 在Linux上安了MySQL5.6,可是远程访问不了,怎么办?
create database recur;
use recur;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
delimiter //
CREATE PROCEDURE showChildList (IN rootId INT,IN)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,
id int,
depth int
);
DELETE FROM tmpLst;
CALL createChildLst(rootId,0);
select tmpLst.*,treeNodes.*
from tmpLst,treeNodes
where tmpLst.id = treeNodes.id
order by tmpLst.sno;
END;
//
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
//
delimiter ;
call showChildList(1);
call showChildList(3);
call showChildList(5);
ERROR 1064 (42000): 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 ')BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key' at line 1
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth vari
able) was exceeded for routine createChildLst
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth vari
able) was exceeded for routine createChildLst