mysql> delimiter $$ mysql> create procedure ptest() -> begin -> declare pid int ; -> set pid = 1000000; -> while pid>0 do -> insert into ...... --插入语句 -> set pid = pid-1; -> end while; -> end $$
-- 修改了一部分字段名 -- 同一天内调用此函数,可能出现相同的code值DROP TABLE IF EIXSTS `customer_order`; CREATE TABLE IF NOT EXISTS `customer_order` ( `id` INT(20) COMMENT '主键', `code` VARCHAR(20) COMMENT '订单号 格式:YYYYMMDD流水号', `quantity` INT(11) COMMENT '订单商品数量 默认值为1', `amount` DECIMAL(20,3) COMMENT '订单总额', `creation_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间', `payment_time` TIMESTAMP COMMENT '支付时间', `operation_time` TIMESTAMP COMMENT '操作时间', `status` INT(11) COMMENT '订单状态', `source` INT(11) COMMENT '订单来源', `re` TEXT COMMENT '备注', `machine` VARCHAR(50) COMMENT '机器ID IS IT FK?', `cart` VARCHAR(20) COMMENT '卡号 What is this?', `defined1` VARCHAR(20) COMMENT ' undefined1?' ); ALTER TABLE `customer_order` ADD CONSTRAINT `PK_CUSTOMER_ORDER` PRIMARY KEY(`id`);DELIMITER // DROP FUNCTION IF EXISTS `generate_order_with_given_number`// CREATE FUNCTION `generate_order_with_given_number`(p_quantity INT) RETURNS INT BEGIN DECLARE maxid INT DEFAULT 0; DECLARE curid INT DEFAULT 0; DECLARE precode VARCHAR(8); DECLARE i INT DEFAULT 0; DECLARE j INT DEFAULT 0; DECLARE len INT DEFAULT 7; DECLARE code VARCHAR(20); SELECT MAX(id) INTO maxid FROM `customer_order`; IF (maxid is null) then SET maxid =0; END IF;
SET precode = date_format(now(),'%Y%m%d');
while i< p_quantity do SET code = precode; SET i=i+1; SET curid = maxid+i; SET len = LENGTH(CONCAT(precode,i)); SET j=0; WHILE j <20 -len do SET j= j+1; SET code=concat(code,'0'); END WHILE; SET code = concat(code,i);
mysql> create procedure ptest()
-> begin
-> declare pid int ;
-> set pid = 1000000;
-> while pid>0 do
-> insert into ...... --插入语句
-> set pid = pid-1;
-> end while;
-> end $$
你按照 楼上的说法 创建了 存储过程
然后
你要调用 存储过程
即 “call 存储过程的名字” 如 ptest
你只是执行了创建命令,但是没执行运行命令
-- 同一天内调用此函数,可能出现相同的code值DROP TABLE IF EIXSTS `customer_order`;
CREATE TABLE IF NOT EXISTS `customer_order`
(
`id` INT(20) COMMENT '主键',
`code` VARCHAR(20) COMMENT '订单号 格式:YYYYMMDD流水号',
`quantity` INT(11) COMMENT '订单商品数量 默认值为1',
`amount` DECIMAL(20,3) COMMENT '订单总额',
`creation_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`payment_time` TIMESTAMP COMMENT '支付时间',
`operation_time` TIMESTAMP COMMENT '操作时间',
`status` INT(11) COMMENT '订单状态',
`source` INT(11) COMMENT '订单来源',
`re` TEXT COMMENT '备注',
`machine` VARCHAR(50) COMMENT '机器ID IS IT FK?',
`cart` VARCHAR(20) COMMENT '卡号 What is this?',
`defined1` VARCHAR(20) COMMENT ' undefined1?'
);
ALTER TABLE `customer_order` ADD CONSTRAINT `PK_CUSTOMER_ORDER` PRIMARY KEY(`id`);DELIMITER //
DROP FUNCTION IF EXISTS `generate_order_with_given_number`//
CREATE FUNCTION `generate_order_with_given_number`(p_quantity INT) RETURNS INT
BEGIN
DECLARE maxid INT DEFAULT 0;
DECLARE curid INT DEFAULT 0;
DECLARE precode VARCHAR(8);
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
DECLARE len INT DEFAULT 7;
DECLARE code VARCHAR(20);
SELECT MAX(id) INTO maxid FROM `customer_order`;
IF (maxid is null) then
SET maxid =0;
END IF;
SET precode = date_format(now(),'%Y%m%d');
while i< p_quantity do
SET code = precode;
SET i=i+1;
SET curid = maxid+i;
SET len = LENGTH(CONCAT(precode,i));
SET j=0;
WHILE j <20 -len do
SET j= j+1;
SET code=concat(code,'0');
END WHILE;
SET code = concat(code,i);
INSERT INTO `customer_order`(`id`,`code`,`quantity`,`amount`,`creation_time`,`payment_time`,`operation_time`,`status`,`source`,`re`,`machine`,`cart`) VALUES
(curid,code,1,1.00,NOW(),DATE_ADD(NOW(),INTERVAL 20 MINUTE),DATE_ADD(NOW(),INTERVAL 50 MINUTE),5,1,'test','xxx','???');
end while;
RETURN 0;
END//
SELECT generate_order_with_given_number(30)//
SELECT * FROM `customer_order`//
DELIMITER ;======================================================================mysql> SELECT generate_order_with_given_number(30)//
+--------------------------------------+
| generate_order_with_given_number(30) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.01 sec)mysql> select * from customer_order;
-> //
+----+----------------------+----------+--------+---------------------+---------------------+---------------------+--------+-------
-+--------+---------+------+----------+
| id | code | quantity | amount | creation_time | payment_time | operation_time | status | source
| re | machine | cart | defined1 |
+----+----------------------+----------+--------+---------------------+---------------------+---------------------+--------+-------
-+--------+---------+------+----------+
| 1 | 20130923000000000001 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 2 | 20130923000000000002 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 3 | 20130923000000000003 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 4 | 20130923000000000004 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 5 | 20130923000000000005 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 6 | 20130923000000000006 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 7 | 20130923000000000007 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 8 | 20130923000000000008 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 9 | 20130923000000000009 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 10 | 20130923000000000010 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 11 | 20130923000000000011 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 12 | 20130923000000000012 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 13 | 20130923000000000013 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 14 | 20130923000000000014 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 15 | 20130923000000000015 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 16 | 20130923000000000016 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 17 | 20130923000000000017 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 18 | 20130923000000000018 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 19 | 20130923000000000019 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 20 | 20130923000000000020 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 21 | 20130923000000000021 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 22 | 20130923000000000022 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 23 | 20130923000000000023 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 24 | 20130923000000000024 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 25 | 20130923000000000025 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 26 | 20130923000000000026 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 27 | 20130923000000000027 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 28 | 20130923000000000028 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 29 | 20130923000000000029 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
| 30 | 20130923000000000030 | 1 | 1.000 | 2013-09-23 18:12:12 | 2013-09-23 18:32:12 | 2013-09-23 19:02:12 | 5 | 1
| test | xxx | ??? | NULL |
+----+----------------------+----------+--------+---------------------+---------------------+---------------------+--------+-------
-+--------+---------+------+----------+
30 rows in set (0.00 sec)