ORACLE: INSERT SELECT FROM 2 views and value from param

Question

我试图从视图MYVIEW1和MYVIEW2插入一些字段到MYTABLE，然后从参数(这是存储过程的一部分)为UPDATED_BY添加值，为UPDATED_ON添加SYSDATE。我怎么能正确地做到这一点与INSERT SELECT或其他方式完全?

MYVIEW1    
+------+----+-----+-----------+---------+
|   YR | MO | QTR | USER_CODE | MO_PERF |
+------+----+-----+-----------+---------+
| 2012 |  1 |   1 |      1099 |      89 | 
| 2012 |  2 |   1 |      1099 |      86 |
| 2012 |  3 |   1 |      1099 |      95 |
+------+----+-----+-----------+---------+
MYVIEW2    
+------+-----+-----------+----------+
|   YR | QTR | USER_CODE | QTR_PERF |
+------+-----+-----------+----------+
| 2012 |   1 |      1099 |       90 | 
+------+-----+-----------+----------+
MYTABLE
+------+-----+-----------+---------+---------+---------+---------+-------------+------------+
|   YR | QTR | USER_CODE | MO1_PCT | MO2_PCT | MO3_PCT |     INC |  UPDATED_BY | UPDATED_ON |
+------+-----+-----------+---------+---------+---------+---------+-------------+------------+
| 2012 |   1 |      1099 |      89 |      86 |      95 |    7000 | SAMPLE NAME | 01/16/2013 |
+------+-----+-----------+---------+---------+---------+---------+-------------+------------+
INSERT INTO MYTABLE
 (YR,QTR,USER_CODE,MO1_PCT,MO2_PCT,MO3_PCT,INC,UPDATED_BY,UPDATED_ON)
    SELECT b.YR,b.QTR,b.USER_CODE,b.MO1_PCT,b.MO2_PCT,b.MO3_PCT,c.INC
    FROM MYVIEW1 b,
         MYVIEW2 c

如何将(QTR的MO_PERF的第一个月)插入值为MO1_PCT， (QTR的MO_PERF的第二个月)插入值为MO2_PCT， (QTR的MO_PERF的最后一个月)插入值为MO3_PCT，确保我在正确的季度和年份中插入了正确的月份。然后检查每个月的MO_PERF值是否至少达到85，否则将INC设置为NULL。

,CASE WHEN MO1_PCT>=85 AND MO2_PCT>=85 AND MO3_PCT>=85 THEN 7000
ELSE NULL 
END INC

Answer 1

如果你使用的是oracle 11g，那么你可以这样使用PIVOT:

select YR, QTR, USER_CODE, "1_MO_PCT" MO1_PCT, "2_MO_PCT" MO2_PCT, "3_MO_PCT" MO3_PCT ,
case when "1_MO_PCT" >= 85 and "2_MO_PCT" >= 85 and "2_MO_PCT" >= 85 then 7000 end INC,
user updated_by, sysdate  updated_on
from (
select m1.yr, m1.mo, m1.qtr, m1.user_code, m1.mo_perf, m2.qtr_perf
from myview1 m1 join myview2 m2 on m1.yr=m2.yr 
and m1.qtr = m2.qtr and m1.user_code = m2.user_code )t
pivot(
  max(mo_perf) MO_PCT  for mo in (1,2,3)
  )

下面是一个sqlfiddle演示