Copy and modify the Oracle record using% ROWTYPE
I don't have access right now to check this, but this is the next - or something like that, since my code is not at the top of my head may not be perfect! - possible in Oracle:
declare
myRecord myTable%ROWTYPE;
begin
select * into myRecord from myTable where key = 123;
myRecord.key := 456;
myRecord.value := myRecord.value + 50;
insert into myTable select * from table(myRecord);
end;
those. we copy a record from myTable, which can have, say, 100 fields, with key 123 to a variable with the same schema, and then update some of the copied fields of the records (like new key here and updated value
) before inserting it back into original table: In fact, duplicating the original record if necessary with some modifications?
I know there are other ways to do this, but this seems pretty neat compared to what I've seen so far ... if it worked, of course!
source to share
INSERT INTO myTable VALUES myRecord;
Some good examples on this site: http://psoug.org/reference/insert.html I guess you don't want to do something like this because you have a lot of columns?
INSERT INTO myTable
SELECT 456, value+50, colx, coly
FROM myTable
WHERE key = 123;
source to share
This is actually even simpler than the suggested syntax
Customize your table and data
CREATE TABLE foo(
col1 NUMBER,
col2 VARCHAR2(100)
);
INSERT INTO foo( col1, col2 )
VALUES( 1, 'Justin' );
PL / SQL block to select, modify, and reinstall a record
declare
l_foo_rec foo%rowtype;
begin
select *
into l_foo_rec
from foo
where col1 = 1;
l_foo_rec.col2 := 'Michael';
l_foo_rec.col1 := l_foo_rec.col1 + 1;
insert into foo
values l_foo_rec;
end;
That when you run it, it will produce this data in FOO
1* select * from foo
SQL> /
COL1 COL2
---------- --------------------
1 Justin
2 Michael
Of course, as Glenn points out, it is generally more efficient to write an SQL statement that inserts a new row by fetching data from an existing row without having to use PL / SQL at all. However, depending on how complex your logic is, the PL / SQL approach might be easier to debug and maintain.
source to share