数据库脚本如下:
use test;
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`account` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`money` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`t3_account` int(11) DEFAULT NULL,
`t3_money` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO t1 values(1,10),(2,11);INSERT INTO t2 values(1,10),(2,11);mysql> select * from t1;
+----+---------+
| id | account |
+----+---------+
| 1 | 10 |
| 2 | 11 |
+----+---------+mysql> select * from t2;
+----+-------+
| id | money |
+----+-------+
| 1 | 10 |
| 2 | 11 |
+----+-------+
下面是存储过程的代码,这个是用来测试的,为了解决工作中的存储过程的问题。。
DELIMITER //
DROP PROCEDURE IF EXISTS test.cursor_nest//
CREATE PROCEDURE test.cursor_nest()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sp_id int DEFAULT 0;
DECLARE sp_account int DEFAULT 0;
DECLARE sp_money int DEFAULT 0;
DECLARE sp_t3_account int DEFAULT 0;
DECLARE sp_t3_money int DEFAULT 0;
DECLARE sp_t3_id int DEFAULT 0;DECLARE cursor_t1 CURSOR FOR SELECT
`id`,
`account`
FROM `t1`;DECLARE cursor_t2 CURSOR FOR SELECT
`id`,
`money`
FROM `t2`;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cursor_t1;
loop_t1:LOOP
FETCH cursor_t1 INTO sp_id,sp_account;
IF done THEN
LEAVE loop_t1;
END IF;
SET sp_t3_id=sp_id;
SET sp_t3_account=sp_t3_account+1;
INSERT INTO t3(id,t3_account) VALUES(
sp_t3_id,
sp_t3_account)
ON DUPLICATE KEY UPDATE
t3_account=sp_t3_account;
END LOOP loop_t1;
CLOSE cursor_t1;
OPEN cursor_t2;
loop_t2:LOOP
FETCH cursor_t2 INTO sp_id,sp_money;
IF done THEN
LEAVE loop_t2;
END IF;
SET sp_t3_id=sp_id;
SET sp_t3_money=sp_money+1;
INSERT INTO t3(id,t3_money) VALUES(
sp_t3_id,
sp_t3_money)
ON DUPLICATE KEY UPDATE
t3_money=sp_t3_money;
END LOOP loop_t2;
CLOSE cursor_t2;
END//
DELIMITER ;
调用上述存储过程后,t3表数据不正常,为什么t3_money没有值呢?如果想实现让它值是11,12 这个存储过程代码该是什么样子?mysql> select * from t3;
+----+------------+----------+
| id | t3_account | t3_money |
+----+------------+----------+
| 1 | 11 | NULL |
| 2 | 12 | NULL |
+----+------------+----------+原谅我是程序娃娃,学了一点SP的皮毛而已。大家抽空帮忙看看呀!3Q!!
use test;
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`account` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`money` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`t3_account` int(11) DEFAULT NULL,
`t3_money` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO t1 values(1,10),(2,11);INSERT INTO t2 values(1,10),(2,11);mysql> select * from t1;
+----+---------+
| id | account |
+----+---------+
| 1 | 10 |
| 2 | 11 |
+----+---------+mysql> select * from t2;
+----+-------+
| id | money |
+----+-------+
| 1 | 10 |
| 2 | 11 |
+----+-------+
下面是存储过程的代码,这个是用来测试的,为了解决工作中的存储过程的问题。。
DELIMITER //
DROP PROCEDURE IF EXISTS test.cursor_nest//
CREATE PROCEDURE test.cursor_nest()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sp_id int DEFAULT 0;
DECLARE sp_account int DEFAULT 0;
DECLARE sp_money int DEFAULT 0;
DECLARE sp_t3_account int DEFAULT 0;
DECLARE sp_t3_money int DEFAULT 0;
DECLARE sp_t3_id int DEFAULT 0;DECLARE cursor_t1 CURSOR FOR SELECT
`id`,
`account`
FROM `t1`;DECLARE cursor_t2 CURSOR FOR SELECT
`id`,
`money`
FROM `t2`;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cursor_t1;
loop_t1:LOOP
FETCH cursor_t1 INTO sp_id,sp_account;
IF done THEN
LEAVE loop_t1;
END IF;
SET sp_t3_id=sp_id;
SET sp_t3_account=sp_t3_account+1;
INSERT INTO t3(id,t3_account) VALUES(
sp_t3_id,
sp_t3_account)
ON DUPLICATE KEY UPDATE
t3_account=sp_t3_account;
END LOOP loop_t1;
CLOSE cursor_t1;
OPEN cursor_t2;
loop_t2:LOOP
FETCH cursor_t2 INTO sp_id,sp_money;
IF done THEN
LEAVE loop_t2;
END IF;
SET sp_t3_id=sp_id;
SET sp_t3_money=sp_money+1;
INSERT INTO t3(id,t3_money) VALUES(
sp_t3_id,
sp_t3_money)
ON DUPLICATE KEY UPDATE
t3_money=sp_t3_money;
END LOOP loop_t2;
CLOSE cursor_t2;
END//
DELIMITER ;
调用上述存储过程后,t3表数据不正常,为什么t3_money没有值呢?如果想实现让它值是11,12 这个存储过程代码该是什么样子?mysql> select * from t3;
+----+------------+----------+
| id | t3_account | t3_money |
+----+------------+----------+
| 1 | 11 | NULL |
| 2 | 12 | NULL |
+----+------------+----------+原谅我是程序娃娃,学了一点SP的皮毛而已。大家抽空帮忙看看呀!3Q!!
DROP PROCEDURE IF EXISTS test.cursor_nest//
CREATE PROCEDURE test.cursor_nest()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sp_id int DEFAULT 0;
DECLARE sp_account int DEFAULT 0;
DECLARE sp_money int DEFAULT 0;
DECLARE sp_t3_account int DEFAULT 0;
DECLARE sp_t3_money int DEFAULT 0;
DECLARE sp_t3_id int DEFAULT 0;DECLARE cursor_t1 CURSOR FOR SELECT
`id`,
`account`
FROM `t1`;DECLARE cursor_t2 CURSOR FOR SELECT
`id`,
`money`
FROM `t2`;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cursor_t1;
loop_t1:LOOP
FETCH cursor_t1 INTO sp_id,sp_account;
IF done THEN
LEAVE loop_t1;
END IF;
SET sp_t3_id=sp_id;
SET sp_t3_account=sp_t3_account+1;
INSERT INTO t3(id,t3_account) VALUES(
sp_t3_id,
sp_t3_account)
ON DUPLICATE KEY UPDATE
t3_account=sp_t3_account;
END LOOP loop_t1;
CLOSE cursor_t1;
SET done=0;
OPEN cursor_t2;
loop_t2:LOOP
FETCH cursor_t2 INTO sp_id,sp_money;
IF done THEN
LEAVE loop_t2;
END IF;
SET sp_t3_id=sp_id;
SET sp_t3_money=sp_money+1;
INSERT INTO t3(id,t3_money) VALUES(
sp_t3_id,
sp_t3_money)
ON DUPLICATE KEY UPDATE
t3_money=sp_t3_money;
END LOOP loop_t2;
CLOSE cursor_t2;
END//
DELIMITER ;
+----+---------+
| id | account |
+----+---------+
| 1 | 10 |
| 2 | 11 |
+----+---------+
2 rows in set (0.00 sec)mysql> select * from t2;
+----+-------+
| id | money |
+----+-------+
| 1 | 10 |
| 2 | 11 |
+----+-------+
2 rows in set (0.00 sec)mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS cursor_nest//
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE PROCEDURE cursor_nest()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE sp_id int DEFAULT 0;
-> DECLARE sp_account int DEFAULT 0;
-> DECLARE sp_money int DEFAULT 0;
-> DECLARE sp_t3_account int DEFAULT 0;
-> DECLARE sp_t3_money int DEFAULT 0;
-> DECLARE sp_t3_id int DEFAULT 0;
->
-> DECLARE cursor_t1 CURSOR FOR SELECT
-> `id`,
-> `account`
-> FROM `t1`;
->
-> DECLARE cursor_t2 CURSOR FOR SELECT
-> `id`,
-> `money`
-> FROM `t2`;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
->
-> OPEN cursor_t1;
-> loop_t1:LOOP
-> FETCH cursor_t1 INTO sp_id,sp_account;
-> IF done THEN
-> LEAVE loop_t1;
-> END IF;
-> SET sp_t3_id=sp_id;
-> SET sp_t3_account=sp_t3_account+1;
-> INSERT INTO t3(id,t3_account) VALUES(
-> sp_t3_id,
-> sp_t3_account)
-> ON DUPLICATE KEY UPDATE
-> t3_account=sp_t3_account;
-> END LOOP loop_t1;
-> CLOSE cursor_t1;
->
-> SET done=0;
-> OPEN cursor_t2;
-> loop_t2:LOOP
-> FETCH cursor_t2 INTO sp_id,sp_money;
-> IF done THEN
-> LEAVE loop_t2;
-> END IF;
-> SET sp_t3_id=sp_id;
-> SET sp_t3_money=sp_money+1;
-> INSERT INTO t3(id,t3_money) VALUES(
-> sp_t3_id,
-> sp_t3_money)
-> ON DUPLICATE KEY UPDATE
-> t3_money=sp_t3_money;
->
-> END LOOP loop_t2;
-> CLOSE cursor_t2;
->
-> END//
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql> call cursor_nest();
Query OK, 2 rows affected (0.03 sec)mysql> select * from t3;
+----+------------+----------+
| id | t3_account | t3_money |
+----+------------+----------+
| 1 | 1 | 11 |
| 2 | 2 | 12 |
+----+------------+----------+
2 rows in set (0.00 sec)mysql>
SET sp_t3_account=sp_t3_account+1;这儿应该是
SET sp_t3_account=sp_account+1;这样正确结果是
mysql> select * from t3;
+----+------------+----------+
| id | t3_account | t3_money |
+----+------------+----------+
| 1 | 11 | 11 |
| 2 | 12 | 12 |
+----+------------+----------+多谢ACMAIN_CHM 君啊!
结贴了。
happy!!