| 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
![]() |
![]() |