mysql遍历数组执行更新（存储过程）

CREATE PROCEDURE init_reportUrl()

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

---