甲骨文upsert问题 [英] oracle upsert question

问题描述

 开始

插入 插入 I_CONTEXT(商品名称，指示，材料)>值(:Trade_name，:Indication，:Material);


如果(:材料存在)然后

更新 I_CONTEXT 设置 Trade_name =:Trade_name，Indication =:Indication 其中 material =:材料；

 结束 如果
结束；
提交; 

材料不是PK
我收到以下错误

 ORA-06550:第7行，第15列:
PLS-00103:预期以下情况之一时遇到符号"EXISTS":

   . ()，* @％& =-+ <    /   >  in in是mod余数，不是rem =>
   <   an      (**) <  <   =  <  > 或类似like2
   like4在||之间指标多集成员
   子多集
符号."被替换为"EXISTS"以继续.

解决方案

问题是if语句.如果:material是绑定变量，并且您要检查值是否为null ，则可以使用:

if :Material IS NULL then

但是，您的代码将始终首先插入该行，并在材料具有值的情况下对其进行更新.如果要进行实际的更新，请参阅合并 [ ^ ]

由于您提到Material并非PK，尽管该列上可能存在UNIQUE约束，所以请注意，您的UPDATE可以更改多个行，而不仅仅是更改刚刚插入的行.

begin

Insert into  I_CONTEXT (Trade_name , Indication, Material) values ( :Trade_name,:Indication, :Material);


if (:Material exists) then

Update  I_CONTEXT set Trade_name = :Trade_name , Indication = :Indication where material = :Material;

 end if
end;
commit;

Material is not a PK
I am getting the following error

ORA-06550: line 7, column 15:
PLS-00103: Encountered the symbol "EXISTS" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec as between || indicator multiset member
   submultiset
The symbol "." was substituted for "EXISTS" to continue.

解决方案

The problem is the if statement. If :material is a bind variable and you want to check if the value is null then you could use:

if :Material IS NULL then

However, your code will always first insert the row and the update it if the material has a value. If you want to do an actual upsert, see Merge[^]

Since you mention that Material is not a PK, though there could be a UNIQUE constraint on the column, be aware that your UPDATE could change multiple rows, not just the one that was just inserted.

这篇关于甲骨文upsert问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！