基本概念
存儲(chǔ)過程就是將一條或多條MySQL語(yǔ)句保存起來系任，方便以后的使用。類似于自定義的函數(shù)墓赴。
創(chuàng)建存儲(chǔ)過程
create procedure 過程名()
begin
...
end
delimiter $$ //使用$$作為存儲(chǔ)過程的分隔符
create procedure ordertotal(
in onumber int,
out ototal decimal(8,2) //可以定義輸入和輸出
)
begin
select sum(item_price*quantity) from orderitems
where order_num=onumber
into ototal;
end$$
調(diào)用存儲(chǔ)過程
使用call關(guān)鍵字
call ordertotal(20005,@total);
select @total;//所有MySQL變量都必須以@開始
刪除存儲(chǔ)過程
drop procedure if exists ordertotal;//存在的時(shí)候刪除
顯示存儲(chǔ)過程信息
show procedure status like 'ordertotal';
show create procedure ordertotal;
建立智能存儲(chǔ)過程
- 需求:獲得訂單合計(jì),并且需要對(duì)某些顧客合計(jì)增加營(yíng)業(yè)稅
- 思路:
1.獲得合計(jì)
2.把營(yíng)業(yè)稅有條件的增加到合計(jì)
3.返回合計(jì) - 具體實(shí)現(xiàn):
Name:ordertotal
Parameters:
onumber=order number
taxable=0 if not taxable,1 if taxable
ototal=order total variable - 代碼
//創(chuàng)建存儲(chǔ)過程
delimiter $$
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)comment 'Obtain order total,optionally adding tax'
begin
declare total decimal(8,2);
declare taxrate int default 6; //聲明存儲(chǔ)過程中的變量
select sum(item_price*quantity) from orderitems
where order_num=onumber into total;
if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end;$$
//調(diào)用存儲(chǔ)過程
call ordertotal(20005,1,@total);$$
//顯示結(jié)果
select @total;$$
//刪除存儲(chǔ)過程
drop procedure ordertotal;
