方法一
-- mysql动态批量插入数据，储存过程
-- 如果存在此存储过程则删掉
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE id INT DEFAULT 68000;
DECLARE orderno BIGINT DEFAULT 100020001012;
WHILE i<=10000 DO
SET id = id+1;
SET orderno = orderno+1;
INSERT INTO `delivery_warning_detail` VALUES(id , '2018-8-10', NULL, orderno, 'US', 'CN06', 'USSFOY', 'LAX', 'LAX03S', 'LAX03S01D', 'EE', 1, 3.000, 3.000, '92705', 0, NULL, NULL, NULL, NULL, '2018-7-23 13:50:00', NULL, NULL, NULL, NULL, NULL, NULL, '2018-7-23 02:10:00', NULL, NULL, '82h38m', 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 0, '10.202.72.89', NULL, '2018-7-26 17:48:54', '2018-7-23 11:26:38');
SET i = i+1;
SELECT concat('运单号', orderno);/*输出执行的运单号*/
END WHILE;
END $
CALL proc_initData();
方法二
DELIMITER $
CREATE PROCEDURE usertest( fieldstr VARCHAR(200), tablename VARCHAR(200), orderstr VARCHAR(100), ordersort INT)
BEGIN
DECLARE sort VARCHAR(20);
DECLARE tmpsql VARCHAR(500);
SET sort = '';
IF ordersort=0 THEN
SET sort='asc';
ELSE
SET sort='desc';
END IF;
SET @sqlcmd = CONCAT('select ' , fieldstr , ' from ' , tablename , ' order by ' , orderstr , ' ' , sort);
PREPARE tmpsql FROM @sqlcmd;
SELECT @sqlcmd; /*输出执行的sql语句*/
EXECUTE tmpsql;
DEALLOCATE PREPARE tmpsql;
END $
-- ---------------------------------------------- --
