--我以前使用 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);