/* 批量插入存储过程 */
CREATE PROCEDURE auto_insert(IN indexs INT,IN loops INT)
BEGIN
DECLARE v_sql LONGTEXT;
SET v_sql = "insert into t_info(name,time) values";
WHILE(indexs <= loops) DO
/* 合并SQL */
SET v_sql = concat(v_sql, "('", concat('anquan--',indexs), "',NOW());");
END IF;
SET indexs = indexs+1;
END WHILE;
set @v_sql=v_sql;/* 注意很重要,将连成成的字符串赋值给一个变量(可以之前没有定义,但要以@开头)*/
prepare stmt from @v_sql;/* 预处理需要执行的动态SQL,其中stmt是一个变量*/
EXECUTE stmt; /* 执行SQL语句*/
deallocate prepare stmt; /* 释放掉预处理段*/
/* 打印SQL */
SELECT v_sql;
END;
/* 调用 */
call auto_insert(1,100);
/* 删除 */
drop procedure auto_insert;
CREATE PROCEDURE auto_insert(IN indexs INT,IN loops INT)
BEGIN
DECLARE v_sql LONGTEXT;
SET v_sql = "insert into t_info(name,time) values";
WHILE(indexs <= loops) DO
/* 合并SQL */
IF(indexs != loops) THEN
SET v_sql = concat(v_sql, "('", concat('anquan--',indexs), "',NOW()),");
ELSESET v_sql = concat(v_sql, "('", concat('anquan--',indexs), "',NOW());");
END IF;
SET indexs = indexs+1;
END WHILE;
set @v_sql=v_sql;/* 注意很重要,将连成成的字符串赋值给一个变量(可以之前没有定义,但要以@开头)*/
prepare stmt from @v_sql;/* 预处理需要执行的动态SQL,其中stmt是一个变量*/
EXECUTE stmt; /* 执行SQL语句*/
deallocate prepare stmt; /* 释放掉预处理段*/
/* 打印SQL */
SELECT v_sql;
END;
/* 调用 */
call auto_insert(1,100);
/* 删除 */
drop procedure auto_insert;