11g里面的虚拟列

--我以前使用 trigger(行级别和块级别)来实现复杂的数据一致性，修改一个表的同时同步另外/自己表的数据 --20200619 这个技术GENERATED ALWAYS AS (f_count_yaorh(id)) VIRTUAL可以实现触发器的功能 --调用函数来作为虚拟列表达式是  -----------多表同步，我以前使用的是 drop table t_main_yaorh purge; drop table t_sub_yaorh purge; create table t_sub_yaorh(id number,name varchar2(20)); insert into t_sub_yaorh values(1,'thomas'); insert into t_sub_yaorh values(1,'jerry'); insert into t_sub_yaorh values(1,'kkk'); insert into t_sub_yaorh values(2,'kkk'); insert into t_sub_yaorh values(2,'John'); insert into t_sub_yaorh values(2,'John'); create or replace function f_count_yaorh(p_id number) return number deterministic  as li_rtn number; begin   select count(*) into li_rtn from t_sub_yaorh where id = p_id;   return li_rtn; exception when others then     dbms_output.put_line(sqlerrm); end; / create table t_main_yaorh(id number,cnt number GENERATED ALWAYS AS (f_count_yaorh(id)) VIRTUAL); insert into t_main_yaorh(id) values(1); insert into t_main_yaorh(id) values(2); commit; select * from t_main_yaorh; insert into t_sub_yaorh values(1,'bbb'); -----------单表多列 drop table t_main_yaorh purge; create or replace function f_total_yaorh(p_id number) return number deterministic as ld_total number; begin   execute immediate 'select price * num from t_main_yaorh where id = :p_id' into ld_total using p_id;   return ld_total; exception when others then     dbms_output.put_line(sqlerrm); end; / create table t_main_yaorh(id number,price number,num number,total number GENERATED ALWAYS AS (f_total_yaorh(id)) VIRTUAL); insert into t_main_yaorh(id,price,num) values(1,2.34,3); insert into t_main_yaorh(id,price,num) values(2,1.34,2);