甲骨文upsert问题 [英] oracle upsert question
本文介绍了甲骨文upsert问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
开始
插入 插入 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 isnull
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屋!
查看全文