Mysql trigger implementation oracle Materialized view example code _ MySQL

Source: Internet
Author: User
Tags oracle materialized view
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 )!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.