oracle 甲骨文合并。我怎样才能使用它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11570527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Merge. How can I use it?
提问by Corwin01
I have this function:
我有这个功能：
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;
Right now as I wrote it, it just does an INSERT. However, I need it to UPSERT. I've looked around at documentation on MERGE, but mainly it's just left me confused on how to apply the syntax to my specific case.
现在，正如我写的那样，它只是做一个 INSERT。但是，我需要它来 UPSERT。我已经查看了有关 MERGE 的文档，但主要是让我对如何将语法应用于我的特定情况感到困惑。
I've looked hereand hereand I get the jist of it, but syntax fails me.
Anyone care to help an Oracle newbie out?
有人愿意帮助 Oracle 新手吗？
回答by DCookie
Assuming that cal_year and metric_name define the join criteria, this should get you close (untested):
假设 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_name, metric_val)
VALUES (v.YY, v.POS, v.WA);
