用MySQL执行批量插入数据,格式如下:
declare
i integer ; -- 定义变量
begin
i := 0000;
loop
/* 插入数据 */
insert into `表名` (字段1,字段2,... )
values
(字段1值,字段2值,...);
/* 参数递增 */
i := i + 1;
/* 停止条件 */
exit when i > 000111;
end loop;
commit;
end;
一直报错1064
错误原因:这个语法是Oracle的,MySQL的语法跟Oracle不一致,导致错误
MySQL一个完整的例子:(这是一个自定义分页的存储过程)
DELIMITER $$
DROP PROCEDURE IF EXISTS `dbcall`.`get_page`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
/**//*Table name*/
tableName varchar(100),
/**//*Fileds to display*/
fieldsNames varchar(100),
/**//*Page index*/
pageIndex int,
/**//*Page Size*/
pageSize int,
/**//*Field to sort*/
sortName varchar(500),
/**//*Condition*/
strWhere varchar(500)
)
BEGIN
DECLARE fieldlist varchar(200);
if fieldsNames=''||fieldsNames=null THEN
set fieldlist='*';
else
set fieldlist=fieldsNames;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;