Sample Code for implementing oracle materialized view using mysql triggers

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.
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
-> Insert into Order_mv
-> Select product_name, sum (price), sum (amount), avg (price), count (*) from Orders
-> Group by product_name;