Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> what do I know Oracle 9i MERGE statement is done via insert or update
After the completion of oracle 9i MERGE statement, I would like to do
some processing depend on whethere the data is insert or updated.
What is the best way to do it?
The value of SQL%ROWCOUNT seems to be always greater than 0 if the merge finished succesfully.
Any help is greatly appriciated.
MERGE INTO core_shipment a USING ( SELECT v_shipment.PICKUP_DATE pickupDate FROM dual ) B ON ( a.PICKUP_DATE = B.pickupDate) WHEN NOT MATCHED THEN INSERT ( SHIPMENT_SEQUENCE_NUMBER, SHIPPER_SEQUENCE_ID, RECORD_CREATE_DATE) VALUES( SHIPMENT_SEQUENCE_NUMBER.nextval, v_shipment.SHIPPER_SEQUENCE_ID, sysdate) WHEN MATCHED THEN UPDATE SET a.SHIPPER_SEQUENCE_ID = v_shipment.SHIPPER_SEQUENCE_ID, a.CONSIGNEE_SEQUENCE_ID = v_shipment.consignee_sequence_ID, a.RECORD_CREATE_DATE = SYSDATE; IF SQL%ROWCOUNT = 0 THEN n_new_record_cnt := n_new_record_cnt + 1; ELSE n_mod_record_cnt := n_mod_record_cnt + 1; END IF;Received on Sat Feb 21 2004 - 16:27:52 CST
![]() |
![]() |