提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

mysqlOracle代码 实现 示例 视图 触发器 物化
2023-06-13 09:15:17 时间

oracle数据库支持物化视图--不是基于基表的虚表，而是根据表实际存在的实表，即物化视图的数据存储在非易失的存储设备上。
下面实验创建ONCOMMIT的FAST刷新模式，在mysql中用触发器实现insert,update,delete刷新操作
1、基础表创建，Orders表为基表，Order_mv为物化视图表

复制代码代码如下:

mysql>createtableOrders(
->order_idintnotnullauto_increment,
->product_namevarchar(30)notnull,
->pricedecimal(10,0)notnull,
->amountsmallintnotnull,
->primarykey(order_id));
QueryOK,0rowsaffected
mysql>createtableOrder_mv(
->product_namevarchar(30)notnull,
->price_sumdecimal(8.2)notnull,
->amount_sumintnotnull,
->price_avgfloatnotnull,
->order_cntintnotnull,
->uniqueindex(product_name));
QueryOK,0rowsaffected

2、insert触发器
复制代码代码如下:

delimiter$$
createtriggertgr_Orders_insert
afterinsertonOrders
foreachrow
begin
set@old_price_sum=0;
set@old_amount_sum=0;
set@old_price_avg=0;
set@old_orders_cnt=0;

selectifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0)
fromOrder_mv
whereproduct_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;

replaceintoOrder_mv
values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);
end;
$$
delimiter;

3、update触发器
复制代码代码如下:
delimiter$$
createtriggertgr_Orders_update
beforeupdateonOrders
foreachrow
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;

selectprice,amountfromOrderswhereorder_id=new.order_id
into@cur_price,@cur_amount;

selectifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0)
fromOrder_mv
whereproduct_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;

replaceintoOrder_mv
values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);
end;
$$
delimiter;

4、delete触发器
复制代码代码如下:
delimiter$$
createtriggertgr_Orders_delete
afterdeleteonOrders
foreachrow
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;

selectprice,amountfromOrderswhereorder_id=old.order_id
into@cur_price,@cur_amount;

selectifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0)
fromOrder_mv
whereproduct_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>0then
set@new_price_avg=@new_price_sum/@new_orders_cnt;
replaceintoOrder_mv
values(old.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);
else
deletefromOrder_mvwhereproduct_name=@old.name;
endif;
end;
$$
delimiter;

5、这里delete触发器有一个bug，就是在一种产品的最后一个订单被删除的时候，Order_mv表的更新不能实现，不知道这算不算是mysql的一个bug。当然，如果这个也可以直接用sql语句生成数据，而导致的直接后果就是执行效率低。
复制代码代码如下:
->insertintoOrder_mv
->selectproduct_name,sum(price),sum(amount),avg(price),count(*)fromOrders
->groupbyproduct_name;

相关文章