《MYSQL數(shù)據(jù)庫(kù)mysql觸發(fā)器實(shí)現(xiàn)oracle物化視圖示例代碼》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫(kù)mysql觸發(fā)器實(shí)現(xiàn)oracle物化視圖示例代碼，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
oracle數(shù)據(jù)庫(kù)支持物化視圖--不是基于基表的虛表,而是根據(jù)表實(shí)際存在的實(shí)表,即物化視圖的數(shù)據(jù)存儲(chǔ)在非易失的存儲(chǔ)設(shè)備上.
下面實(shí)驗(yàn)創(chuàng)建ON COMMIT 的FAST刷新模式,在mysql中用觸發(fā)器實(shí)現(xiàn)insert , update , delete 刷新操作
1、基礎(chǔ)表創(chuàng)建,Orders 表為基表,Order_mv為物化視圖表
MYSQL教程
代碼如下:
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ā)器有一個(gè)bug,就是在一種產(chǎn)品的最后一個(gè)訂單被刪除的時(shí)候,Order_mv表的更新不能實(shí)現(xiàn),不知道這算不算是mysql的一個(gè)bug.當(dāng)然,如果這個(gè)也可以直接用sql語(yǔ)句生成數(shù)據(jù),而導(dǎo)致的直接后果就是執(zhí)行效率低.
代碼如下:
-> insert into Order_mv
-> select product_name ,sum(price),sum(amount),avg(price),count(*) from Orders
-> group by product_name;
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/1132.html
