Mysql 动态插入数据,储存过程

xiaoxiao2021-03-01  8

方法一 -- 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 $ -- ---------------------------------------------- --

 

转载请注明原文地址: https://www.6miu.com/read-3350047.html

最新回复(0)