BEGIN
DECLARE s INT DEFAULT 0
DECLARE r_id bigint(10)
DECLARE report CURSOR FOR select distinct id as r_id FROM ReportHotLine
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1
-- 打开游标
open report
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into r_id
-- 当s不等于1,也就是未遍历完时,会一直循环
while s<>1 do
-- 执行业务逻辑
UPDATE wh_csyx . dy_pres30207800013_001_local a
JOIN ReportHotLine b ON a.gdId = b.ID
AND b.createtime = ( SELECT max( createtime ) FROM ReportHotLine WHERE ID = r_id )
SET blms = b.banliInfo where a.gdId =r_id
-- 将游标中的值再赋值给变量,供下次循环使用
fetch report into r_id
-- 当s等于1时表明遍历以完成,退出循环
end while
-- 关闭游标
close report
END
call init_reportUrl()
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() //调用执行
create procedure batchUpdate(in n int)begin
declare inParam int default n
declare i int default 1
declare id int
declare username varchar(255)
declare integral varchar(255)
declare ranking int
declare _resultSet cursor for select a.id, a.username,a.integral,a.ranking from user a order by a.integral desc limit inParam
open _resultSet
while i <= inParam do
fetch _resultSet into id, username, integral,ranking
update user a set a.ranking = i where a.id = id
set i = i +1
end while
close _resultSet
end
mysql> select * from user
+----+----------+----------+---------+
| id | username | integral | ranking |
+----+----------+----------+---------+
| 1 | abc | 1 | 0 |
| 2 | abc | 2 | 0 |
| 3 | abc | 3 | 0 |
| 4 | abc | 4 | 0 |
| 5 | abc | 5 | 0 |
| 6 | abc | 6 | 0 |
| 7 | abc | 7 | 0 |
| 8 | abc | 8 | 0 |
| 9 | abc | 9 | 0 |
| 10 | abc | 10 | 0 |
| 11 | abc | 11 | 0 |
| 12 | abc | 12 | 0 |
| 13 | abc | 13 | 0 |
| 14 | abc | 14 | 0 |
+----+----------+----------+---------+
14 rows in set
mysql> call batchUpdate(14)
Query OK, 1 row affected
mysql> select * from user
+----+----------+----------+---------+
| id | username | integral | ranking |
+----+----------+----------+---------+
| 1 | abc | 1 | 14 |
| 2 | abc | 2 | 13 |
| 3 | abc | 3 | 12 |
| 4 | abc | 4 | 11 |
| 5 | abc | 5 | 10 |
| 6 | abc | 6 | 9 |
| 7 | abc | 7 | 8 |
| 8 | abc | 8 | 7 |
| 9 | abc | 9 | 6 |
| 10 | abc | 10 | 5 |
| 11 | abc | 11 | 4 |
| 12 | abc | 12 | 3 |
| 13 | abc | 13 | 2 |
| 14 | abc | 14 | 1 |
+----+----------+----------+---------+
14 rows in set
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)