我写了个插入c1表数据的存储过程,但在运行之后发现c表中多了一条记录,请看代码:DROP TABLE IF EXISTS a1;
DROP TABLE IF EXISTS b1;
DROP TABLE IF EXISTS c1;
-- 创建a表
CREATE TABLE a1
(a1 INT NOT NULL AUTO_INCREMENT,
a2 BIGINT(20),
CONSTRAINT PRIMARY KEY(a1)
);
-- 创建b表
CREATE TABLE b1
(b1 int NOT NULL auto_increment,
b2 VARCHAR(20),
CONSTRAINT PRIMARY KEY(b1));
-- 创建c表
CREATE TABLE c1
(c1 INT NOT NULL,
c2 BIGINT(20)
);
-- 创建插入a1,b1表数据的存储过程
CREATE PROCEDURE spInsa1b1
BEGIN
DECLARE v_a2 BIGINT(20);
DECLARE v_b2 BIGINT(20);
SET v_a2=1001;
SET v_b2=2001;
-- 插入a1表数据
WHILE v_a2<=1060 DO
INSERT INTO a1 (a2) VALUES(v_a2);
SET v_a2=v_a2+1;
END WHILE;
-- 插入b1表数据
WHILE v_b2<=2060 DO
INSERT INTO b1(b2) VALUES(v_b2);
SET v_b2=v_b2+1;
END WHILE;
END;
CALL PROCEDURE spInsa1b1();然后写了个插入c1表数据的存储过程CREATE PROCEDURE spInsc1
BEGIN
DECLARE v_a2 BIGINT(20);
DECLARE v_b2 BIGINT(20);
SET v_a2=1001;
SET v_b2=2001;
WHILE v_a2<=1060 DO
INSERT INTO a1 (a2) VALUES(v_a2);
SET v_a2=v_a2+1;
END WHILE;
WHILE v_b2<=2060 DO
INSERT INTO b1(b2) VALUES(v_b2);
SET v_b2=v_b2+1;
END WHILE;
END;
CALL PROCEDURE spInsc1();结果发现c1表中最后多了记录:
mysql> select * from c1;
+----+----------+
| c1 | c2 |
+----+----------+
| 1 | 10012001 |
| 2 | 10022002 |
| 3 | 10032003 |
| 4 | 10042004 |
| 5 | 10052005 |
................
---中间数据省略--
............
| 57 | 10572057 |
| 58 | 10582058 |
| 59 | 10592059 |
| 60 | 10602060 |
| 60 | 10602060 |
+----+----------+
61 rows in set (0.00 sec)
就是多了条‘ 60 | 10602060’这个,不知道为什么?如果把c1列设为主键就会提示主键重复。请高手帮忙看看。
DROP TABLE IF EXISTS b1;
DROP TABLE IF EXISTS c1;
-- 创建a表
CREATE TABLE a1
(a1 INT NOT NULL AUTO_INCREMENT,
a2 BIGINT(20),
CONSTRAINT PRIMARY KEY(a1)
);
-- 创建b表
CREATE TABLE b1
(b1 int NOT NULL auto_increment,
b2 VARCHAR(20),
CONSTRAINT PRIMARY KEY(b1));
-- 创建c表
CREATE TABLE c1
(c1 INT NOT NULL,
c2 BIGINT(20)
);
-- 创建插入a1,b1表数据的存储过程
CREATE PROCEDURE spInsa1b1
BEGIN
DECLARE v_a2 BIGINT(20);
DECLARE v_b2 BIGINT(20);
SET v_a2=1001;
SET v_b2=2001;
-- 插入a1表数据
WHILE v_a2<=1060 DO
INSERT INTO a1 (a2) VALUES(v_a2);
SET v_a2=v_a2+1;
END WHILE;
-- 插入b1表数据
WHILE v_b2<=2060 DO
INSERT INTO b1(b2) VALUES(v_b2);
SET v_b2=v_b2+1;
END WHILE;
END;
CALL PROCEDURE spInsa1b1();然后写了个插入c1表数据的存储过程CREATE PROCEDURE spInsc1
BEGIN
DECLARE v_a2 BIGINT(20);
DECLARE v_b2 BIGINT(20);
SET v_a2=1001;
SET v_b2=2001;
WHILE v_a2<=1060 DO
INSERT INTO a1 (a2) VALUES(v_a2);
SET v_a2=v_a2+1;
END WHILE;
WHILE v_b2<=2060 DO
INSERT INTO b1(b2) VALUES(v_b2);
SET v_b2=v_b2+1;
END WHILE;
END;
CALL PROCEDURE spInsc1();结果发现c1表中最后多了记录:
mysql> select * from c1;
+----+----------+
| c1 | c2 |
+----+----------+
| 1 | 10012001 |
| 2 | 10022002 |
| 3 | 10032003 |
| 4 | 10042004 |
| 5 | 10052005 |
................
---中间数据省略--
............
| 57 | 10572057 |
| 58 | 10582058 |
| 59 | 10592059 |
| 60 | 10602060 |
| 60 | 10602060 |
+----+----------+
61 rows in set (0.00 sec)
就是多了条‘ 60 | 10602060’这个,不知道为什么?如果把c1列设为主键就会提示主键重复。请高手帮忙看看。
BEGIN
DECLARE v_a2 BIGINT(20);
DECLARE v_b2 BIGINT(20);
DECLARE v_c1 BIGINT(20);
DECLARE v_c2 BIGINT(20);
DECLARE v_fig INT;
DECLARE cur CURSOR FOR SELECT a1.a1,a1.a2,b1.b2 FROM a1 INNER JOIN b1 ON a1.a1=b1.b1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig=1;
OPEN cur;
REPEAT
FETCH cur INTO v_c1,v_a2,v_b2;
SET v_c2=CONCAT(v_a2,v_b2);
INSERT INTO c1 VALUES (v_c1,v_c2);
UNTIL v_fig=1
END REPEAT;
CLOSE cur;
END;
WHILE v_fig=0 DO
SET v_c2=CONCAT(v_a2,v_b2);
INSERT INTO c1 VALUES (v_c1,v_c2);
FETCH cur INTO v_c1,v_a2,v_b2;
END WHILE;
mysql> delimiter //mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
FETCH cur INTO v_c1,v_a2,v_b2;
SET v_c2=CONCAT(v_a2,v_b2);
INSERT INTO c1 VALUES (v_c1,v_c2);
UNTIL v_fig=1
END REPEAT;即使没有记录,好要执行INSERT,在UNTIL v_fig=1中判断
WHILE v_fig=0 DO
FETCH cur INTO v_c1,v_a2,v_b2;
SET v_c2=CONCAT(v_a2,v_b2);
INSERT INTO c1 VALUES (v_c1,v_c2);
END WHILE;
运行之后也多一行记录,这个为什么呢?
FETCH cur INTO v_c1,v_a2,v_b2;, 没有判断v_fig=0,如果没有记录,依然执行INSERT,多看看MYSQL HELP