一:如何实现某两列的相同数不大于5.
1、conn / as sysdba
2、grant create materialized view to scott ;--授予创建物化视图的权限给scott用户
3、create table t(a varchar2(10),b number,c number,constraint t_pk primary key(a,b,c));--建立测试表
4、createt materialized view log on t with rowid(a,b) including new values ;--创建物化视图日志
5、创建物化视图mv,是将t表提取出a,b的值。按a,b进行分组,然后求总数。这个时候求得的总数cnt就是相同的a,b的个数。
1 create materialized view mv
2 refresh fast
3 on commit
4 as
5 select a,b,count(*) cnt
6 from t
7 group by a,b
8 /
6、alter table mv add constraint check_t check(cnt<=5) ;--为视图添加约束,即相同的a,b的个数不大于5.
7、insert into t(a,b,c) values('A',1,1);
insert into t(a,b,c) values('A',1,4);
insert into t(a,b,c) values('A',1,99) ;
insert into t(a,b,c) values('A',1,10000);
insert into t(a,b,c) values('A',1,3);
insert into t(a,b,c) values('A',1,15);
8、commit ;
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CHECK_T) violated
9、select * from t ;
no rows selected --注意此处,事务具有原子性,即插入要么全部成功,要么全部失败。不可能部分成功。
二、利用物化视图,实现某类的数值总和必须是100。
1、drop table t ;
2、drop materialized view mv ;
3、drop sequence s ;
4、create table t(t_id number,group_id number,pct number) ;
5、alter table t add constraint cp_t primary key(t_id) ;
6、create materialized view log on t with rowid(t_id,group_id,pct) including new values ;
7、
1 create materialized view mv
2 refresh fast
3 on commit
4 as select group_id,sum(pct) pct from t group by group_id ;
8、alter table mv add constraint check_mv check(pct=100) ;
9、insert into t values(s.nextval,1,10);
insert into t values(s.nextval,1,20);
insert into t values(s.nextval,1,30);
insert into t values(s.nextval,1,39);
insert into t values(s.nextval,2,10);
insert into t values(s.nextval,2,20);
insert into t values(s.nextval,2,30);
insert into t values(s.nextval,2,40) ;
10、commit ;
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CHECK_MV) violated
1、conn / as sysdba
2、grant create materialized view to scott ;--授予创建物化视图的权限给scott用户
3、create table t(a varchar2(10),b number,c number,constraint t_pk primary key(a,b,c));--建立测试表
4、createt materialized view log on t with rowid(a,b) including new values ;--创建物化视图日志
5、创建物化视图mv,是将t表提取出a,b的值。按a,b进行分组,然后求总数。这个时候求得的总数cnt就是相同的a,b的个数。
1 create materialized view mv
2 refresh fast
3 on commit
4 as
5 select a,b,count(*) cnt
6 from t
7 group by a,b
8 /
6、alter table mv add constraint check_t check(cnt<=5) ;--为视图添加约束,即相同的a,b的个数不大于5.
7、insert into t(a,b,c) values('A',1,1);
insert into t(a,b,c) values('A',1,4);
insert into t(a,b,c) values('A',1,99) ;
insert into t(a,b,c) values('A',1,10000);
insert into t(a,b,c) values('A',1,3);
insert into t(a,b,c) values('A',1,15);
8、commit ;
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CHECK_T) violated
9、select * from t ;
no rows selected --注意此处,事务具有原子性,即插入要么全部成功,要么全部失败。不可能部分成功。
二、利用物化视图,实现某类的数值总和必须是100。
1、drop table t ;
2、drop materialized view mv ;
3、drop sequence s ;
4、create table t(t_id number,group_id number,pct number) ;
5、alter table t add constraint cp_t primary key(t_id) ;
6、create materialized view log on t with rowid(t_id,group_id,pct) including new values ;
7、
1 create materialized view mv
2 refresh fast
3 on commit
4 as select group_id,sum(pct) pct from t group by group_id ;
8、alter table mv add constraint check_mv check(pct=100) ;
9、insert into t values(s.nextval,1,10);
insert into t values(s.nextval,1,20);
insert into t values(s.nextval,1,30);
insert into t values(s.nextval,1,39);
insert into t values(s.nextval,2,10);
insert into t values(s.nextval,2,20);
insert into t values(s.nextval,2,30);
insert into t values(s.nextval,2,40) ;
10、commit ;
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CHECK_MV) violated