Heim > Datenbank > MySQL-Tutorial > mysql触发器实现oracle物化视图示例代码_MySQL

mysql触发器实现oracle物化视图示例代码_MySQL

PHP中文网
Freigeben: 2016-05-27 14:12:05
Original
1138 Leute haben es durchsucht

bitsCN.com

oracle数据库支持物化视图--不是基于基表的虚表,而是根据表实际存在的实表,即物化视图的数据存储在非易失的存储设备上。
下面实验创建ON COMMIT 的FAST刷新模式,在mysql中用触发器实现insert , update , delete 刷新操作
1、基础表创建,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
Nach dem Login kopieren


2、insert触发器

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 ;
Nach dem Login kopieren


3、update触发器

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; 
$$
Nach dem Login kopieren

delimiter ;

4、delete触发器

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 ;
Nach dem Login kopieren


5、这里delete触发器有一个bug,就是在一种产品的最后一个订单被删除的时候,Order_mv表的更新不能实现,不知道这算不算是mysql的一个bug。当然,如果这个也可以直接用sql语句生成数据,而导致的直接后果就是执行效率低。

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

以上就是mysql触发器实现oracle物化视图示例代码_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!


Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage