Oracle Merge.我怎么用呢?

前端之家收集整理的这篇文章主要介绍了Oracle Merge.我怎么用呢?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有这个功能

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

我看了herehere,我得到了它的主旨,但语法让我失望.

有人关心帮助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);

猜你在找的Oracle相关文章