曾经做过的mysql的存储过程-优快云博客

本文介绍了一种报价更新逻辑，包括从数据库中获取最新的报价记录，并基于这些记录更新临时报价表及最佳报价表。通过比较不同公司的报价金额和价格，确保数据库中保存的是最优报价。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


begin
	declare bb, product, id, company1, company2, amount1, amount2 int(11);
	declare price1, price2 double;
	declare time1, time2 timestamp;
	select  quote_type as bb, -- buy or sell
		company_id as compnay2,
		quote_id as id,
		quote_amount as amount2 ,
		quote_price as price2,
		product_id as product,
		quote_time as time2
	from quote
	order by quote_id desc
	limit 1
	into @bb, @company2, @id, @amount2, @price2, @product, @time2;

	set @price1:=null;
	select  quote_amount as amount1,
		quote_price as price1
	from temp_quote
	where product_id = @product
		and quote_type = @bb
	into @amount1, @price1;

	if @price1 is null then
		insert into temp_quote(company_id, 
			product_id, 
			quote_type, 
			quote_price, 
			quote_amount )
		values(	@company2, 
			@product, 
			@bb, 
			@price2, 
			@amount2);

		insert into best_quote( quote_id, 
			product_id, 
			quote_type, 
			quote_price, 
			quote_amount, 
			quote_time, 
			company_id )
		values(@id,
			@product,
			@bb, 
			@price2, 
			@amount2, 
			@time2, 
			@company2 );


		set @price1 = 0;
	end if;

	if @price2 < @price1 then
		update temp_quote
			set quote_amount = @amount2,
			quote_price =@price2,
			company_id=@company2
		where product_id=@product and
				quote_type=@bb;
		insert into best_quote( quote_id, 
			product_id, 
			quote_type, 
			quote_price, 
			quote_amount, 
			quote_time, 
			company_id )
		values(@id,
			@product,
			@bb, 
			@price2, 
			@amount2, 
			@time2, 
			@company2 );
	end if;

	if @price2 = @price1 and @amount2 > @amount1 then
		update temp_quote
			set quote_amount = @amount2,
			quote_price =@price2,
			company_id=@company2
		where product_id=@product and
				quote_type=@bb;
		insert into best_quote( quote_id, 
			product_id, 
			quote_type, 
			quote_price, 
			quote_amount, 
			quote_time, 
			company_id )
		values(@id,
			@product,
			@bb, 
			@price2, 
			@amount2, 
			@time2, 
			@company2 );
	end if;
end