大家好!存储过程中递归调用,将中间结果插入到临时表中。
树形结构的表如下:DROP TABLE IF EXISTS `survey`.`tree_test`;
CREATE TABLE `survey`.`tree_test` (
`form_id` varchar(32) CHARACTER SET utf8 NOT NULL,
`parent_form_id` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO tree_test(form_id,parent_form_id) VALUES('001','000');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('002','001');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('003','001');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('004','003');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('005','002');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('006','002');保存中间结果的临时表如下:
CREATE TEMPORARY TABLE IF NOT EXISTS treeRecurisiveTest_tmp(
form_id VARCHAR(32),
parent_form_id VARCHAR(32)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
存储过程如下:
DELIMITER $$USE `survey`$$DROP PROCEDURE IF EXISTS `treeRecurisiveTest`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `treeRecurisiveTest`(IN pid VARCHAR(32))
BEGIN
DECLARE v_done1 INT DEFAULT 0;
DECLARE v_form_id VARCHAR(32);
DECLARE v_parent_form_id VARCHAR(32);
DECLARE cur1 CURSOR FOR
SELECT t.form_id,t.parent_form_id
FROM tree_test AS t WHERE t.parent_form_id = pid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done1 = 1;
SET max_sp_recursion_depth = 12;
OPEN cur1;
FETCH cur1 INTO v_form_id,v_parent_form_id;
WHILE v_done1 <> 1 DO
INSERT INTO treeRecurisiveTest_tmp(form_id,parent_form_id) VALUES(v_form_id,v_parent_form_id);
CALL `survey`.`treeRecurisiveTest`(v_form_id);
FETCH cur1 INTO v_form_id,v_parent_form_id;
END WHILE;
CLOSE cur1;
SELECT * FROM treeRecurisiveTest_tmp;
END$$DELIMITER ;1、调用存储过程,传入顶级节点的FORM_ID TRUNCATE TABLE treeRecurisiveTest_tmp;
CALL treeRecurisiveTest('001');2、查看存储过程的返回结果
form_id form_name
002 001
005 0023、存储过程调用后,直接用SELECT * FROM treeRecurisiveTest_tmp; 返回的结果。
form_id form_name
002 001
005 002
006 002
003 001
004 003请教大家:为什么第2步中,存储过程直接返回的结果只有2条记录呢?
谢谢帮忙!
树形结构的表如下:DROP TABLE IF EXISTS `survey`.`tree_test`;
CREATE TABLE `survey`.`tree_test` (
`form_id` varchar(32) CHARACTER SET utf8 NOT NULL,
`parent_form_id` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO tree_test(form_id,parent_form_id) VALUES('001','000');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('002','001');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('003','001');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('004','003');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('005','002');
INSERT INTO tree_test(form_id,parent_form_id) VALUES('006','002');保存中间结果的临时表如下:
CREATE TEMPORARY TABLE IF NOT EXISTS treeRecurisiveTest_tmp(
form_id VARCHAR(32),
parent_form_id VARCHAR(32)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
存储过程如下:
DELIMITER $$USE `survey`$$DROP PROCEDURE IF EXISTS `treeRecurisiveTest`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `treeRecurisiveTest`(IN pid VARCHAR(32))
BEGIN
DECLARE v_done1 INT DEFAULT 0;
DECLARE v_form_id VARCHAR(32);
DECLARE v_parent_form_id VARCHAR(32);
DECLARE cur1 CURSOR FOR
SELECT t.form_id,t.parent_form_id
FROM tree_test AS t WHERE t.parent_form_id = pid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done1 = 1;
SET max_sp_recursion_depth = 12;
OPEN cur1;
FETCH cur1 INTO v_form_id,v_parent_form_id;
WHILE v_done1 <> 1 DO
INSERT INTO treeRecurisiveTest_tmp(form_id,parent_form_id) VALUES(v_form_id,v_parent_form_id);
CALL `survey`.`treeRecurisiveTest`(v_form_id);
FETCH cur1 INTO v_form_id,v_parent_form_id;
END WHILE;
CLOSE cur1;
SELECT * FROM treeRecurisiveTest_tmp;
END$$DELIMITER ;1、调用存储过程,传入顶级节点的FORM_ID TRUNCATE TABLE treeRecurisiveTest_tmp;
CALL treeRecurisiveTest('001');2、查看存储过程的返回结果
form_id form_name
002 001
005 0023、存储过程调用后,直接用SELECT * FROM treeRecurisiveTest_tmp; 返回的结果。
form_id form_name
002 001
005 002
006 002
003 001
004 003请教大家:为什么第2步中,存储过程直接返回的结果只有2条记录呢?
谢谢帮忙!
感觉在MYSQL 存储过程中,
begin
..........
insert into tab(...) values(...);
(进行INSERT操作)
end ;begin
...........
select * from tab;
(返回前面insert进的记录)
end;1、存储过程直接返回的结果集记录 少了
2、存储过程调用后,写SQL语句 select * from tab 查出的结果集 又是正常的。
Query OK, 0 rows affected (0.01 sec)mysql> CALL treeRecurisiveTest('001');
+---------+----------------+
| form_id | parent_form_id |
+---------+----------------+
| 002 | 001 |
| 005 | 002 |
+---------+----------------+
2 rows in set (0.08 sec)+---------+----------------+
| form_id | parent_form_id |
+---------+----------------+
| 002 | 001 |
| 005 | 002 |
| 006 | 002 |
+---------+----------------+
3 rows in set (0.09 sec)+---------+----------------+
| form_id | parent_form_id |
+---------+----------------+
| 002 | 001 |
| 005 | 002 |
| 006 | 002 |
+---------+----------------+
3 rows in set (0.09 sec)+---------+----------------+
| form_id | parent_form_id |
+---------+----------------+
| 002 | 001 |
| 005 | 002 |
| 006 | 002 |
| 003 | 001 |
| 004 | 003 |
+---------+----------------+
5 rows in set (0.11 sec)+---------+----------------+
| form_id | parent_form_id |
+---------+----------------+
| 002 | 001 |
| 005 | 002 |
| 006 | 002 |
| 003 | 001 |
| 004 | 003 |
+---------+----------------+
5 rows in set (0.11 sec)+---------+----------------+
| form_id | parent_form_id |
+---------+----------------+
| 002 | 001 |
| 005 | 002 |
| 006 | 002 |
| 003 | 001 |
| 004 | 003 |
+---------+----------------+
5 rows in set (0.13 sec)Query OK, 0 rows affected (0.13 sec)mysql>
我用SQLyog客户端工具,运行存储过程,返回结果是分页显示出来的。1 结果 2 结果 ........ 7结果
form_id form_name form_id form_name form_id form_name
001 000 001 000
002 001 002 001
005 002 005 002
006 0027结果是正常的。但是,我用JAVA JPA 连接MYSQL,调用该存储过程{call treeRecurisiveTest(?)}.
返回的结果集,却是 1结果。没有取到最终正确的结果。
请问:是否可以可以一次性返回最终结果呢?谢谢!
不过建议你还是把结果放到临时表中去比较方便。
是否因为在存储过程的最后select出了结果集。
然后过程又自身递归调用,导致最后显示时,select出了多个结果。
导致最后显示时,select出了多个结果。谢谢
ACMAIN_CHM的帮忙!