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:

  myRecord myTable%ROWTYPE;
  select * into myRecord from myTable where key = 123;
  myRecord.key   := 456;
  myRecord.value := myRecord.value + 50;
  insert into myTable select * from table(myRecord);


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

2 answers

INSERT INTO myTable VALUES myRecord;


Some good examples on this site: I guess you don't want to do something like this because you have a lot of columns?

  SELECT 456, value+50, colx, coly
     FROM myTable
     WHERE key = 123;




This is actually even simpler than the suggested syntax

Customize your table and data

  col1 NUMBER,
  col2 VARCHAR2(100)

INSERT INTO foo( col1, col2 )
  VALUES( 1, 'Justin' );


PL / SQL block to select, modify, and reinstall a record

  l_foo_rec foo%rowtype;
  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;


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.



All Articles