create table CALCULATE_PARA 
(
   REPORT_CODE          VARCHAR2(50),--报表
   ITEM_ID              VARCHAR2(20),--项目
   CALCULATE_FORMULA    VARCHAR2(2000),--计算公式
   EFFECT_REPORT_CODE   VARCHAR2(500)--受项目影响的表
);INSERT INTO CALCULATE_PARA SELECT 'A001','A001_01','A001_02+A001_03','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A002','A002_03','A001_01+A002_01','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A002','A002_05','A002_03+A002_04','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A002','A002_09','A002_05-A002_07','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A003','A003_08','A002_06+A002_07','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A004','A004_06','A001_01+A004_03','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A005','A005_07','A003_08+A004_06','' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A006','A006_02','A002_05+A006_02','' FROM DUAL;求受ITEM影响的报表,用逗号分隔,存在EFFECT_REPORT_CODE(报表不重复)。
例如:A001_01这个项目,
一、因为A002_03=A001_01+A002_01,A002_05=A002_03+A002_04,A006_02=A002_05+A006_02,A002_09=A002_05-A002_07 ,所以受A001_01这个项目的报表:A001,A002,A006
二、因为A004_06=A001_01+A004_03,A005_07=A003_08+A004_06,所以受A001_01这个项目的报表:A004,A005
所以综合以上两点,受A001_01这个项目影响的报表有A001,A002,A004,A005,A006。
最后结果如下:
INSERT INTO CALCULATE_PARA SELECT 'A001','A001_01','A001_02+A001_03','A001,A002,A004,A005,A006' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A002','A002_03','A001_01+A002_01','A002,A004' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A002','A002_05','A002_03+A002_04','A002,A004' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A002','A002_09','A002_05-A002_07','A002' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A003','A003_08','A002_06+A002_07','A003' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A004','A004_06','A001_01+A004_03','A004,A005' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A005','A005_07','A003_08+A004_06','A005' FROM DUAL;
INSERT INTO CALCULATE_PARA SELECT 'A006','A006_02','A002_05+A006_02','A006' FROM DUAL;