记录一条纠结了SQL语句

在使用BAM监控SOA接口实例运行情况时，需要设计一个视图整合SOA实例信息，包括实例ID，创建时间，完成时间，还有实例状态等信息。

SOA中分别有三张比较重要的表保存实例信息：

COMPOSITE_INSTANCE:记录实例基本信息，包括ID，状态，创建时间，但没有完成时间，比较麻烦的是SOA状态监控需在Oracle EM里面开启，否则对于已完成的实例一律标记为状态32，也就是Unknow状态。系统出于性能的考虑默认是关闭的，而且客户一般也不允许开启，这就给状态监控带来问题，因此需要以下两张表的支持，这里为了讲解方便假设假设表只存在ID,STATE两个字段。

CUBE_INSTANCE:记录BPEL流程信息，包括该BPEL创建时间和结束时间，由于一次SOA接口的调用，大部分时间是花在BPEL的执行，因此BPEL执行时间可以近似看做SOA接口的运行时间。CUBE_INSTANCE通过字段CMPST_ID和COMPOSITE_INSTANCE相关联，而且COMPOSITE_INSTANCE和CUBE_INSTANCE的关系是一对多的关系。为了讲解方便假设CUBE_INSTANCE表只存在CMPST_ID，CIKEY，RUNTIME三个字段。

WI_FAULT:记录BPEL错误信息，如果SOA接口的BPEL流程执行出现问题，系统会将错误信息记录在该表中，也就是说，在该表中出现的BPEL流程对应的SOA实例一定是错误的调用，可以通过查询该表判断实例是否出错。WI_FAULT通过CIKEY字段与CUBE_INSTANCE相关联，为了讲解方便假设WI_FAULT只有CIKEY字段。

现在整理一下思路，表COMPOSITE_INSTANCE的ID字段和CUBE_INSTANCE的CMPST_ID字段相关联，关系为1对多，CUBE_INSTANCE和WI_FAULT通过CIKEY字段相关联，关系为1对1。

视图除了获取实例的一些基本信息最重要的是判断实例的BPEL流程是否有错误，如果有错误将状态STATE修改成80如果没有错误就保留原状态，以这种方式来判断实例的状态。下面这个Oracle SQL语句是经过多次修改后的版本，由于SQL和ORACLE都入门尚浅因此在该SQL上花了不少功夫。

CREATE OR REPLACE VIEW INSTANCE_REPORT_V AS
  FROM (SELECT A.ID CMPST_ID, DECODE(C.ERROR_ID, NULL, A.STATE, 80) STATE
          FROM COMPOSITE_INSTANCE A,
               (SELECT BB.CMPST_ID CMPST_ID, MAX(BB.RUNTIME) RUNTIME
                  FROM CUBE_INSTANCE BB
                 GROUP BY BB.CMPST_ID) B,
               (SELECT DISTINCT CUBE.CMPST_ID ERROR_ID
                  FROM CUBE_INSTANCE CUBE, WI_FAULT WI
                 WHERE CUBE.CIKEY = WI.CIKEY) C
         WHERE A.ID = B.CMPST_ID(+)
           AND A.ID = C.ERROR_ID(+);
           

基本思路是先select出错误实例ID，也就是表C，再select出用时最长的BPEL流程，也就是表B，最后将信息整合，注意表C的distinct如果没有表C可能会出现多个相同的实例ID，最后的结果数会多于表COMPOSITE_INSTANCE记录数。