mysql觸發(fā)器實現(xiàn)oracle物化視圖示例代碼

更新時間：2014年02月08日 15:27:01 作者：

mysql觸發(fā)器實現(xiàn)oracle物化視圖即不是基于基表的虛表，而是根據(jù)表實際存在的實表,需要的朋友可以參考下

oracle數(shù)據(jù)庫支持物化視圖--不是基于基表的虛表，而是根據(jù)表實際存在的實表，即物化視圖的數(shù)據(jù)存儲在非易失的存儲設備上。
下面實驗創(chuàng)建ON COMMIT 的FAST刷新模式，在mysql中用觸發(fā)器實現(xiàn)insert , update , delete 刷新操作
1、基礎表創(chuàng)建，Orders 表為基表，Order_mv為物化視圖表

復制代碼代碼如下:

 
mysql> create table Orders( 
-> order_id int not null auto_increment, 
-> product_name varchar(30)not null, 
-> price decimal(10,0) not null , 
-> amount smallint not null , 
-> primary key (order_id)); 
Query OK, 0 rows affected 
mysql> create table Order_mv( 
-> product_name varchar(30) not null, 
-> price_sum decimal(8.2) not null, 
-> amount_sum int not null, 
-> price_avg float not null, 
-> order_cnt int not null, 
-> unique index(product_name)); 
Query OK, 0 rows affected 

2、insert觸發(fā)器

復制代碼代碼如下:

 
delimiter $$ 
create trigger tgr_Orders_insert 
after insert on Orders 
for each row 
begin 
set @old_price_sum=0; 
set @old_amount_sum=0; 
set @old_price_avg=0; 
set @old_orders_cnt=0; 

select ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0) 
from Order_mv 
where product_name=new.product_name 
into @old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt; 

set @new_price_sum=@old_price_sum+new.price; 
set @new_amount_sum=@old_amount_sum+new.amount; 
set @new_orders_cnt=@old_orders_cnt+1; 
set @new_price_avg=@new_price_sum/@new_orders_cnt; 

replace into Order_mv 
values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt); 
end; 
$$ 
delimiter ; 

3、update觸發(fā)器

復制代碼代碼如下:

 
delimiter $$ 
create trigger tgr_Orders_update 
before update on Orders 
for each row 
begin 
set @old_price_sum=0; 
set @old_amount_sum=0; 
set @old_price_avg=0; 
set @old_orders_cnt=0; 

set @cur_price=0; 
set @cur_amount=0; 

select price,amount from Orders where order_id=new.order_id 
into @cur_price,@cur_amount; 

select ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0) 
from Order_mv 
where product_name=new.product_name 
into @old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt; 

set @new_price_sum=@old_price_sum-@cur_price+new.price; 
set @new_amount_sum=@old_amount_sum-@cur_amount+new.amount; 
set @new_orders_cnt=@old_orders_cnt; 
set @new_price_avg=@new_price_sum/@new_orders_cnt; 

replace into Order_mv 
values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt); 
end; 
$$ 
delimiter ; 

4、delete觸發(fā)器

復制代碼代碼如下:

 
delimiter $$ 
create trigger tgr_Orders_delete 
after delete on Orders 
for each row 
begin 
set @old_price_sum=0; 
set @old_amount_sum=0; 
set @old_price_avg=0; 
set @old_orders_cnt=0; 

set @cur_price=0; 
set @cur_amount=0; 

select price,amount from Orders where order_id=old.order_id 
into @cur_price,@cur_amount; 

select ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0) 
from Order_mv 
where product_name=old.product_name 
into @old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt; 

set @new_price_sum=@old_price_sum - old.price; 
set @new_amount_sum=@old_amount_sum - old.amount; 
set @new_orders_cnt=@old_orders_cnt - 1; 

if @new_orders_cnt>0 then 
set @new_price_avg=@new_price_sum/@new_orders_cnt; 
replace into Order_mv 
values(old.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt); 
else 
delete from Order_mv where product_name=@old.name; 
end if; 
end; 
$$ 
delimiter ; 

5、這里delete觸發(fā)器有一個bug，就是在一種產(chǎn)品的最后一個訂單被刪除的時候，Order_mv表的更新不能實現(xiàn)，不知道這算不算是mysql的一個bug。當然，如果這個也可以直接用sql語句生成數(shù)據(jù)，而導致的直接后果就是執(zhí)行效率低。

復制代碼代碼如下:

 
-> insert into Order_mv 
-> select product_name ,sum(price),sum(amount),avg(price),count(*) from Orders 
-> group by product_name; 

您可能感興趣的文章:

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

mysql觸發(fā)器實現(xiàn)oracle物化視圖示例代碼

相關文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具