select count(*) into 变量 from a,b where a.1=b.1 if 变量>0 then delete from a where exists (select 1 from b where a.1=b.1) else insert end
建议楼主看看merge函数,这是针对的需求的
先插入再删除? 如果主键不变,直接用merge into 更省事,像select count等内容oracle都帮你做了
oracle 能直接用if else?
MERGE INTO PRODUCTS P USING NEWPRODUCTS NP ON (P.PRODUCT_ID = NP.PRODUCT_ID) WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY WHERE P.CATEGORY = 'DVD' WHEN NOT MATCHED THEN INSERT VALUES (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS'
if 变量>0 then
delete from a where exists (select 1 from b where a.1=b.1)
else
insert
end
MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (P.PRODUCT_ID = NP.PRODUCT_ID)
WHEN MATCHED THEN
UPDATE
SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY
WHERE P.CATEGORY = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES
(NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS'