ORACLE开发注意事项

1、代码自测应严格

2、若字段col1由源表若干字段依据公式计算而来，col2由col1计算而来，则计算col1的结果不应先保留小数位，否则会造成col2的计算有误差，应只在最后的结果中保留小数位。

3、取源表增量部分数据时，要取全，把所有受影响的、参与计算的数据都取出来。

3、关联提取，主键来自一张表TABLE1时，从附表TABLE2取出来的字段col2处理方式有两种(以录入转中心为例)：

WITH TMP AS

(

SELECT ID,COL1,COL2

FROM TABLE1 A

WHERE INPUTSTATE=1 AND UPDATETIME>V_NOWTIME AND UPDATETIME<=V_MAXTIME

OR EXISTS ( SELECT ID,COL3

                      FROM TABLE2

                      WHERE INPUTSTATE=1 AND UPDATETIME>V_NOWTIME1 AND UPDATETIME<=V_MAXTIME1

                     )

)

SELECT

A.ID,A.COL1,A.COL2,B.COL3

FROM TMP A

LEFT JOIN TABLE2 B

ON A.ID=B.ID AND B.INPUTSTATE=1 AND B.UPDATESTATE<=1 ;

 

 

 

WITH TMP AS

(

SELECT ID,COL1,COL2

FROM TABLE1 A

WHERE INPUTSTATE=1 AND UPDATETIME>V_NOWTIME AND UPDATETIME<=V_MAXTIME

OR EXISTS ( SELECT ID,COL3

                      FROM TABLE2

                      WHERE INPUTSTATE=1 AND UPDATETIME>V_NOWTIME1 AND UPDATETIME<=V_MAXTIME1

                     )

)

SELECT

A.ID,A.COL1,A.COL2,CASE WHEN B.UPDATESTATE<=1 THEN B.COL3 ELSE NULL END AS COL3

FROM TMP A

LEFT JOIN TABLE2 B

ON A.ID=B.ID AND B.INPUTSTATE=1