mysql觸發(fā)器實現(xiàn)oracle物化視圖示例代碼
更新時間：2014年02月08日 15:27:01 作者：
mysql觸發(fā)器實現(xiàn)oracle物化視圖即不是基于基表的虛表，而是根據(jù)表實際存在的實表,需要的朋友可以參考下
oracle數(shù)據(jù)庫支持物化視圖--不是基于基表的虛表，而是根據(jù)表實際存在的實表，即物化視圖的數(shù)據(jù)存儲在非易失的存儲設(shè)備上。
下面實驗創(chuàng)建ON COMMIT 的FAST刷新模式，在mysql中用觸發(fā)器實現(xiàn)insert , update , delete 刷新操作
1、基礎(chǔ)表創(chuàng)建，Orders 表為基表，Order_mv為物化視圖表
復(fù)制代碼 代碼如下:
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ā)器
復(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ā)器
復(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ā)器
復(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。當(dāng)然，如果這個也可以直接用sql語句生成數(shù)據(jù)，而導(dǎo)致的直接后果就是執(zhí)行效率低。
復(fù)制代碼 代碼如下:
-> insert into Order_mv
-> select product_name ,sum(price),sum(amount),avg(price),count(*) from Orders
-> group by product_name;
相關(guān)文章
這篇文章主要介紹了MySQL中在查詢結(jié)果集中得到記錄行號的方法,本文解決方法是通過預(yù)定義用戶變量來實現(xiàn),需要的朋友可以參考下2015-01-01
MySQL中出現(xiàn)lock?wait?timeout?exceeded問題及解決
這篇文章主要介紹了MySQL中出現(xiàn)lock?wait?timeout?exceeded問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
SQL?ALTER?TABLE語句靈活修改表結(jié)構(gòu)和數(shù)據(jù)類型
這篇文章主要介紹了SQL?ALTER?TABLE語句靈活修改表結(jié)構(gòu)和數(shù)據(jù)類型,有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-12-12
本篇文章是對Mysql常用SQL語句進行了詳細的分析介紹，需要的朋友參考下2013-06-06
這篇文章主要給大家介紹了關(guān)于MySQL聯(lián)結(jié)表介紹及使用的相關(guān)資料,聯(lián)結(jié)SQL最強大的功能之一就是能在數(shù)據(jù)檢索查詢的執(zhí)行中聯(lián)結(jié)表,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了mysql增刪改查基礎(chǔ)語句,需要的朋友可以參考下2017-10-10
MySQL宕機是指MySQL數(shù)據(jù)庫服務(wù)突然停止運行,通?？赡苁怯捎谟布收稀④浖e誤、資源耗盡、網(wǎng)絡(luò)中斷、配置問題或是惡意攻擊等導(dǎo)致,當(dāng)MySQL發(fā)生宕機時,系統(tǒng)可能無法提供數(shù)據(jù)訪問,本文給大家介紹了MySQL異常宕機無法啟動的處理過程,需要的朋友可以參考下2024-08-08
mysql數(shù)據(jù)庫在我們的工作中經(jīng)常需要使用,經(jīng)常在表中需要使用時間,下面這篇文章主要給大家介紹了關(guān)于在MySQL中使用DateTime的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09
這篇文章主要介紹了MySQL 分頁查詢的優(yōu)化技巧，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-05-05
這篇文章主要介紹了 mysql獲取分組后每組的最大值實例詳解的相關(guān)資料,需要的朋友可以參考下2017-06-06