Oracle合并.我该如何使用? [英] Oracle Merge. How can I use it?

问题描述

我有这个功能:

      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的文档，但主要是让我对如何将语法应用于我的特定情况感到困惑.

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.

我在此处和此处，我得到了它的精髓，但语法使我失败.

I've looked here and here and I get the jist of it, but syntax fails me.

有人在乎帮助Oracle新手吗?

Anyone care to help an Oracle newbie out?

推荐答案

假设cal_year和metric_name定义了连接条件，这应该使您结识(未测试):

Assuming that cal_year and metric_name define the join criteria, this should get you close (untested):

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);

这篇关于Oracle合并.我该如何使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！