DROP PROCEDURE IF EXISTS test
-- 创建存储过程
CREATE PROCEDURE test()
BEGIN
-- 声明循环变量int型 i
DECLARE i INT DEFAULT 1
-- 设置i=0
set i=0
-- 循环100次
while i<100 do
-- if判断i为偶数
if i%2 = 0 THEN
-- 要实现的 *** 作 CONCAT(str1,str2,...) concat函数用于拼接子串和数字类型
INSERT into user(name,addr) VALUES(CONCAT('test',i,'ss'),'addr')
end if
-- i自洞拦增含颤基
set i=i+1
END WHILE
END
-- 调用存储过程
CALL test()
DROP PROCEDURE IF EXISTS excute_job_v340
create procedure excute_job_v340()
begin
declare rdevid int//声明参数
declare rech_id int
declare slot int
declare new_rech_id int
declare new_price DOUBLE//声明参数
declare done INT DEFAULT FALSE////声明结束标识参数
-- 声明游标
DECLARE rdevrech_id CURSOR FOR
select r.id as rdevid,r.rechargeconfig_id as rech_id,r.slot_no as slot from b_device_tbl dev
LEFT JOIN r_device_rechargeconfig_tbl r on dev.id= r.device_id
告裤where dev.dev_typedef_id =7 and dev.masterid is not NULL and r.rechargeconfig_type=4 and r.is_deleted=0 and r.slot_no is not NULL
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE
-- 打开游标
OPEN rdevrech_id
-- 遍历
read_loop: LOOP
-- 取值
FETCH NEXT from rdevrech_id INTO rdevid,rech_id,slot
IF done THEN
LEAVE read_loop
END IF// 结束判断
猛并 select price into new_price from c_device_rechargeconfig_item_tbl where rechargeconfig_id =rech_id limit 1
INSERT INTO `c_device_rechargeconfig_tbl` ( `type`, `style_id`, `is_default`, `dev_type_code`, `is_deleted`, `create_time`, `slot_no`)
VALUES ( '4', NULL, '0', '0', '0', '2019-08-19 15:59:24',slot )
select max(id) into new_rech_id from c_device_rechargeconfig_tbl
INSERT INTO `c_device_rechargeconfig_item_tbl` ( `price`, `goods`, `description`, `is_deleted`, `create_time`, 袜知简 `rechargeconfig_id` )
VALUES ( new_price,new_price, '0.00', '0', '2019-08-19 15:59:24', new_rech_id)
update r_device_rechargeconfig_tbl set rechargeconfig_id=new_rech_id where id=rdevid
END LOOP
CLOSE rdevrech_id
end;
call excute_job_v340() //调用执行
用存储过程衫或搜,写或历个循团旦环给你个我以前写的看看begin
declare i int
declare b int
declare c int
set @i=2
set @b=6
set @c=0
set @stmt = concat('insert into t_j_goods_name (id,`code`,`name`)
values(?,?,(select distinct `a` from sheet1 limit ?, 1))')
while @i<442 do
prepare s1 from @stmt
execute s1 using @b,@i,@c
deallocate prepare s1
set @i=@i+1,@b=@b+1,@c=@c+1
end while
end
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)