我有这个功能:
Procedure UpdateDefaultWeight ( vYear Number,costWeight Number,qualityWeight Number,serviceWeight Number ) AS type weight_table is table of Number(5,2) index by varchar2(50); weightArray weight_table; currentPosition varchar2(50); Begin weightArray('Cost Weighting') := costWeight; weightArray('Quality Weighting') := qualityWeight; weightArray('Service Weighting') := serviceWeight; currentPosition := weightArray.first; Loop Exit When currentPosition is null; Insert Into GVS.GVSSD16_DFLT_WEIGHT ( cal_year,metric_name,metric_val ) Values ( vYear,currentPosition,weightArray(currentPosition) ); currentPosition := weightArray.next(currentPosition); End Loop; END;
就在我写这篇文章的时候,它只是一个INSERT.但是,我需要UPSERT.我查看了有关MERGE的文档,但主要是让我对如何将语法应用于我的特定情况感到困惑.
我看了here和here,我得到了它的主旨,但语法让我失望.
有人关心帮助Oracle新手吗?
解决方法
假设cal_year和metric_name定义了连接条件,这应该让你关闭(未经测试):
MERGE INTO GVS.GVSSD16_DFLT_WEIGHT d USING (SELECT vYear AS YY,currentPosition AS POS,weightArray (currentPosition) AS WA FROM DUAL) v ON (d.cal_year = v.YY AND d.metric_name = v.pos) WHEN MATCHED THEN UPDATE SET metric_val = v.WA WHEN NOT MATCHED THEN INSERT (cal_year,metric_val) VALUES (v.YY,v.POS,v.WA);