Thanks for your re-post, it's much better now.
Below please find a solution using materialized view. It will fulfill your request with low DML frequency to table work_order.
(1) Set up the test env:
create table work_order(dev_no varchar2(5) , ticket_no varchar2(10), ticket_type varchar2(1));
insert into work_order values ('A0001','WO-0001','U');
insert into work_order values ('A0002','WO-0002','U');
insert into work_order values ('A0001','WO-0003','P');
insert into work_order values ('A0003','WO-0004','U');
insert into work_order values ('A0005','WO-0005','P');
insert into work_order values ('A0004','WO-0006','P');
insert into work_order values ('A0001','WO-0007','U');
commit;
alter table work_order add constraint work_order_pk primary key( dev_no, ticket_no );
create table Maintenance_object ( dev_no varchar2(5), dev_status varchar2(1) default 'W' );
-- 'W'working status
-- 'M'Maitaining status , stopped
复制代码
(2) Create the Materialized view:
create materialized view maintenance_object
on prebuilt table
refreshon demand
as
select dev_no
, decode( sum( decode(ticket_type, 'U',1,0)), 0, 'W','M') dev_status
from work_order
group by dev_no ;
复制代码
(3) Any time you want to refresh the data in maintenance_object, just issue the following command:
SQL>exec dbms_mview.refresh('maintenance_object', 'c');
PL/SQL procedure successfully completed.
SQL>select * from maintenance_object;
DEV_N D
----- -
A0001 M
A0002 M
A0003 M
A0004 W
A0005 W
SQL>
复制代码
Please be noticed that it's not a good solution if the table work_order has a high volume with frequency DML. |
