Geek asks and answers

Oracle Merge How can i use it?

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;

Now that I wrote it, it just does an INSERT. However, I need this for UPSERT. I looked at the MERGE documentation, but basically it just confused me how to apply the syntax to my specific case.

I looked here and here , and I get this, but the syntax does not allow me.

Anyone want to help Oracle newbies?

+5
source share
1 answer

Assuming cal_year and metric_name define the connection criteria, this should close you (unchecked):

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);
+6
source

More articles:


All Articles