56,679
社区成员
发帖
与我相关
我的任务
分享
树形结构的表如下:
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 结果 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 002
7结果是正常的。
但是,我用JAVA JPA 连接MYSQL,调用该存储过程{call treeRecurisiveTest(?)}.
返回的结果集,却是 1结果。没有取到最终正确的结果。
请问:是否可以可以一次性返回最终结果呢?谢谢!
mysql> TRUNCATE TABLE treeRecurisiveTest_tmp;
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>
感觉在MYSQL 存储过程中,
begin
..........
insert into tab(...) values(...);
(进行INSERT操作)
end ;
begin
...........
select * from tab;
(返回前面insert进的记录)
end;
1、存储过程直接返回的结果集记录 少了
2、存储过程调用后,写SQL语句 select * from tab 查出的结果集 又是正常的。