在学习游标的使用,弄了两个表,一个girls(_girlId_,name,tel),另一个Tels(tel),其中一个girls表tel字段都是空的,想建立存储过程利用游标逐一地将Tels表内数据搬到girls的tel属性上,但是发现自己建的循环体内总是会把tel第二行起的数据给游标:
DROP TABLE IF EXISTS girls;
CREATE TABLE girls(
_girlId_ int auto_increment primary key,
_girlName_ varchar(20)
);
INSERT INTO girls(_girlName_) VALUES ('Maria');
INSERT INTO girls(_girlName_) VALUES ('Ringo');
INSERT INTO girls(_girlName_) VALUES ('Messa');
INSERT INTO girls(_girlName_) VALUES ('Alice');
ALTER TABLE girls add tel varchar(20);
DROP TABLE IF EXISTS TELS;
CREATE TABLE TELS (
tel varchar(20)
);
INSERT INTO TELS VALUES ('11111111');
INSERT INTO TELS VALUES ('22222222');
INSERT INTO TELS VALUES ('33333333');
INSERT INTO TELS VALUES ('44444444');
DROP PROCEDURE IF EXISTS TravarseTEL;
CREATE PROCEDURE TravarseTEL()
BEGIN
DECLARE telvalue VARCHAR(20);
DECLARE i INT DEFAULT 0;
DECLARE len INT DEFAULT 0;
DECLARE FOUND BOOLEAN DEFAULT TRUE;
DECLARE telcur CURSOR FOR SELECT tel FROM TELS;
Declare continue handler for not found set FOUND = false;
SELECT COUNT(*) FROM girls INTO len;
OPEN telcur;
FETCH telcur into telvalue;
WHILE i <= len and found DO
SELECT telvalue;
UPDATE girls SET tel = telvalue WHERE _girlId_ = i;
FETCH telcur into telvalue;
set i = i+1;
end while;
close telcur;
end;
call TravarseTEL();