mysql存储过程修改数据

直接上代码

DROP PROCEDURE IF EXISTS `update_tender_bid_details`;
CREATE DEFINER=`b2b_test`@`%` PROCEDURE `update_tender_bid_details`()
BEGIN
	DECLARE d_id BIGINT;-- 定义变量
	DECLARE d_parent_id BIGINT;
  DECLARE done INT DEFAULT FALSE;-- 创建游标，并存储数据
	DECLARE cur_test CURSOR FOR SELECT id,parent_id FROM zc_tender_bid_details WHERE goods IS NULL  ORDER BY id;
	-- 游标中的内容执行完后将done设置为true
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
	
	-- 打开游标
	OPEN cur_test;
	-- 执行循环
	posLoop :LOOP-- 判断是否结束循环
	select done;
		FETCH cur_test INTO d_id,d_parent_id;-- 取游标中的值
		IF done 
		THEN
				SELECT '跳出posLoop';
				LEAVE posLoop;
		END IF;
		
		SET @ordernum= NULL;
    SET @tenderId= NULL;
		set @tenderType=NULL;
		set @goodsId=null;
		select d_id,d_parent_id;
		select d.i into @ordernum from (select (@i:=@i+1) as i,id,parent_id from zc_tender_bid_details,(select   @i:=0) as it where goods is null and parent_id=d_parent_id order by id) d where d.id=d_id;
		select tender into  @tenderId  from zc_tender_bid where id=d_parent_id;
		if @tenderId is not null
		THEN
				SELECT '进入查询tender';
				select type into @tenderType from zc_tendering where id=@tenderId;
				select @tenderType;
				
				if @tenderType is null
				THEN
					set done=FALSE;
					SELECT concat('投标表的',@tenderId,'不存在');
				ELSE
					select @ordernum;
					select @tenderId;
					set @g_num=@ordernum-1;
					
					select  @g_num;
					case @tenderType  
										when 1 then
												SET @sql = CONCAT('select id from zc_tender_machinery_goods where parent_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 2 then
												SET @sql = CONCAT('select id from zc_tender_labour_goods where parent_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 3 then
												SET @sql = CONCAT('select id from zc_tender_material_goods where tender_id = @tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 4 then
												SET @sql = CONCAT('select id from zc_tender_custom_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 5 then
												SET @sql = CONCAT('select id from zc_tender_equipment_rental_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 14 then
												SET @sql = CONCAT('select id from zc_tender_land_transfer_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 15 then
												SET @sql = CONCAT('select id from zc_tender_land_transfer_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 11 then
												SET @sql = CONCAT('select id from zc_tender_property_transfer_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
												
										when 12 then
												SET @sql = CONCAT('select id from zc_tender_car_rental_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 16 then
												SET @sql = CONCAT('select id from zc_tender_transfer_machinery_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 17 then
												SET @sql = CONCAT('select id from zc_tender_equipment_transfer_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 18 then
												SET @sql = CONCAT('select id from zc_tender_vehicle_transfer_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										when 19 then
												SET @sql = CONCAT('select id from zc_tender_logistics_freight_goods where tender_id=@tenderId limit ', @g_num,1);
												PREPARE stmt FROM @sql;
												EXECUTE stmt;
												deallocate prepare stmt;
												select FOUND_ROWS() into @goodsId;
										ELSE select '无查询的物资id';
						end case;  
						IF @goodsId is null
							THEN
								set done=FALSE;
								select 'goodsId未找到';
							ELSE
									select '进入修改';
									UPDATE zc_tender_bid_details SET goods=@goodsId where id=d_id;
									COMMIT;
						END IF;
				
				end if;
		END IF;									
	END LOOP posLoop;-- 释放游标
	CLOSE cur_test;

END

说明

此处用游标循环修改（CURSOR ）

在limit查询中不能使用变量，因此需要使用以下查询。

set @goodsId=null;
SET @sql = CONCAT('select id from zc_tender_machinery_goods where parent_id=@tenderId limit ', @g_num,1);
									PREPARE stmt FROM @sql;
									EXECUTE stmt;
									deallocate prepare stmt;
									select FOUND_ROWS() into @goodsId;

如果游标中使用了查询，查询无结果会自动跳出循环，需要将set done=FALSE;

if @tenderType is null
				THEN
					set done=FALSE;
					SELECT concat('投标表的',@tenderId,'不存在');
end if;