MySQL基础教程之存储过程

create procedure porcedureName () 
begin 
    select name from user; 
end;  

-- 调用过程 
call porcedureName ();  

DROP PROCEDURE IF EXISTS porcedureName; -- 没有括号() 

create procedure procedureName( 
    out min decimal(8,2), 
    out avg decimal(8,2), 
    out max decimal(8,2) 
) 
BEGIN 
    select MIN(price) INTO min from order; 
    select AVG(price) into avg from order; 
    select MAX(price) into max from order; 
END;  

-- 由于过程指定三个参数， 故调用必须要参数匹配 
call procedureName(@min, @avg, @max);  

select @min, @avg, @max; 

create procedure getTotalById ( 
    in userId int, 
    out total decimal(8,2) 
) 
BEGIN 
    select SUM(r.price) from order r 
    where r.u_id = userId 
    into total; 
END;  

call getTotalById(1, @total); 
select @total;  

create procedure getTotalByUser2( 
    in userId int, 
    in falg boolean, -- 是否加税标记 
    out total decimal(8,2) 
) 
begin 
    DECLARE tmptotal DECIMAL(8,2); 
    DECLARE taxrate int DEFAULT 6;-- 默认的加税的利率 
     
    select SUM(r.price) from order r 
    where r.u_id = userId 
    into tmptotal; 
     
    if flag then 
        select tmptotal + (tmptotal/1000*taxrate) into tmptotal; 
    end if; 
     
    select tmptotal into total; 
END;  

call getTotalByUser2(1, false, @total); -- 不加税 
call getTotalByUser2(1, true, @total);  -- 加税 
select @total;