利用mysql存储过程循环插入新数据并更新

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() //调用执行

--先在数据库中创建test表,表中有列名为name,类型为varchar(50)

然后先执行

create

procedure

proc_insert

@name

varchar(50)

as

begin

insert

into

test

values(@name)--插入数据

end

go

create

procedure

proc_update

@newname

varchar(50),@oldname

varchar(50)

as

begin

update

test

set

name=@newname

where

name=@oldname--更新数据

end

go

--其中proc_insert为存储过程名,可自定义

procedure可使用简写proc

上面执行完成后调用存储过程

exec

proc_insert

'晓华'--将"晓华"添加到test表中

exec

proc_update

'小明','晓华'

--将表中'晓华'

改为'小明',必须与存储过程变量顺序相同

exec

proc_update

@oldname='小明',@newname='晓华'--与存储过程变量顺序可以不同

drop

procedure

proc_insert

删除存储过程proc_insert

添加更新数据跟一般的sql一样的写法啊，唯一差别是变量、传参的写法，比如

select

count(1)

into

v_count

from

table

a

where

a.clct_day

=

p_clct_day

这里v_count就是定义的一个变量，p_clct_day就是过程传入的时间参数

---