Okay, I'm a huge fan of using Oracle's %ROWTYPE to define records structures. I love writing code like the following:
DECLARE
rec_contract CONTRACT%ROWTYPE := Null;
BEGIN
-- ... retrieval logic and business logic ...
-- "Set" logic
UPDATE CONTRACT
SET ROW = rec_contract
WHERE CONTRACT_ID = rec_contract.CONTRACT_ID;
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO CONTRACT
VALUES rec_contract;
END IF;
END;
Code like the above is really convenient for writing "setters". It can easily be extended to handle
DELETE logic too.
Now, recently I needed to copy a bunch of data
within the same table but with a different primary key. I started to write a bunch of code like the above, but I just needed some of code.
To cut to the chase, the code below is an example of how to do an "
UPSERT" (like a
MERGE) but
within the same table [which is impossible with the
MERGE command].
Source:
drop table test;
clear;
create table test(
id number(10),
name varchar2(50)
);
insert into test(id,name) values (1,'First row');
insert into test
using select 2,'Row two' from dual;
insert into test
using select 3,'Row three - unmodified' from dual;
commit;
select * from test;
update test set (id,name) = (select 3,'Row three - modified' from test where id = 3) where id = 3;
commit;
select * from test;
Results:
Table created
1 row inserted
1 row inserted
1 row inserted
Commit complete
ID NAME
----------- --------------------------------------------------
1 First row
2 Row two
3 Row three - unmodified
1 row updated
Commit complete
ID NAME
----------- --------------------------------------------------
1 First row
2 Row two
3 Row three - modified
Posts
