Mysql trigger implementation oracle Materialized view example code _ MySQL

Mysql trigger Implements oracle Materialized view sample code bitsCN.com

Oracle Database supports materialized views-instead of a virtual table based on the base table, the data of Materialized views is stored on non-easy storage devices based on the actual real table existing in the table.
The following experiment creates the on commit fast refresh mode. in mysql, insert, update, and delete refresh operations are performed using triggers.
1. create a basic table. the Orders table is the base table, and Order_mv is the Materialized view chart.

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 trigger

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 trigger

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 trigger

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. here, the delete trigger has a bug that the Order_mv table cannot be updated when the last order of a product is deleted, I don't know. this is not a mysql bug. Of course, if this problem occurs, you can directly use SQL statements to generate data, resulting in low execution efficiency.

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

The above is the sample code for implementing the oracle Materialized view of the mysql trigger _ MySQL content. For more information, see The PHP Chinese network (www.php1.cn )!