分100张表执行
#drop PROCEDURE idata; delimiter ;; create procedure iData() begin declare i int; declare s varchar(2); set i = 0; set s = ''; while (i <= 100) do set s = LPAD(i, 2, 0); ##每个表新增指定的num记录数 set @sqlStr = concat('update user_card_0', s, ' set activity_limit_type=''10'',activity_id=''T13234612'' WHERE sale_order in (''00020200427001000075'',''XSD20200116001000352''); '); PREPARE stmt from @sqlStr; EXECUTE stmt; ##每个表新增指定的num记录数 set @sqlStr2 = concat('update user_card_0', s, ' set activity_limit_type=''10'',activity_id=''T13234613'' WHERE sale_order in (''00020200708001000092''); '); PREPARE stmt from @sqlStr2; EXECUTE stmt; set i = i + 1; end while; end;; delimiter ; call iData();查询
drop PROCEDURE idata1; delimiter ;; create procedure iData1(OUT data_t VARCHAR(100)) begin declare i int; declare s varchar(2); set i = 0; set s = ''; while (i <= 100) do set s = LPAD(i, 2, 0); ##每个表新增指定的num记录数 set @sqlStr = concat( 'select activity_id,user_id,sale_order into @card_no_result, @user_id_result,@sale_order_result FROM user_card_0', s, ' WHERE sale_order in (''00020200427001000075'',''XSD20200116001000352'',''00020200708001000092'') limit 1; '); PREPARE stmt from @sqlStr; EXECUTE stmt; IF @card_no_result is not null THEN select @card_no_result, @user_id_result, @sale_order_result; END IF; SET data_t := @card_no_result; set i = i + 1; end while; end;; delimiter ; call iData1(@data_t); SELECT @data_t;