曾经做过的mysql的存储过程

scvmm

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