delimiter //
CREATE PROCEDURE test_insert28()
BEGIN
DECLARE mnm VARCHAR(30);
DECLARE flag INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE random_num INT;
#定义游标,title表一共7条数据
#查出articles表的title字段值作为插入test表中的字段值
DECLARE a_id CURSOR FOR SELECT title AS 'mnm' from articles;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
#外层的while控制游标整体循环的次数,此条控制会增加7*2=14条数据
WHILE i<2 DO
SET flag=0;
OPEN a_id;
FETCH a_id INTO mnm;
#由于主键冲突的问题所以用了random_num
WHILE flag <>1 DO
SELECT FLOOR(RAND() * 10000)AS random_num FROM test where "random_num" not in(SELECT id FROM test) LIMIT 1 INTO random_num;
INSERT INTO test VALUES (random_num,200,mnm,"aaa");
FETCH a_id INTO mnm;
END WHILE;
CLOSE a_id;
SET i=i+1;
END WHILE;
END
//
delimiter;
mysql存储过程游标读取一个表的数据字段插入另外一个表
最新推荐文章于 2023-06-28 11:21:49 发布