Using %ROWTYPE for INSERTs and UPDATEs
The below shows how to use %ROWTYPE for INSERTs and UPDATEs. I have not seen UPDATE table SET ROW syntax very often.
————————————–
SQL> create table cds(id number, artist_name varchar(255), album_name varchar2(255));
Table created.
SQL> create sequence cds_seq;
Sequence created.
SQL> DECLARE
2 l_new_cd cds%ROWTYPE;
3 BEGIN
4 l_new_cd.id := cds_seq.nextval;
5 l_new_cd.album_name := ‘The Future’;
6 l_new_cd.artist_name := ‘Leonard Cohen’;
7 INSERT INTO cds
8 VALUES l_new_cd;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> col artist_name format a20
SQL> col album_name format a20
SQL> select * from cds;
ID ARTIST_NAME ALBUM_NAME
———- ——————– ——————–
1 Leonard Cohen The Future
SQL> DECLARE
2 l_updated_cd cds%ROWTYPE;
3 BEGIN
4 l_updated_cd.id := cds_seq.nextval;
5 l_updated_cd.album_name := ‘Oracular Spectacular’;
6 l_updated_cd.artist_name := ‘MGMT’;
7 UPDATE cds
8 SET ROW = l_updated_cd
9 WHERE album_name = ‘The Future’;
10* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from cds;
ID ARTIST_NAME ALBUM_NAME
———- ——————– ——————–
1 MGMT Oracular Spectacular
————————————–
Do you think MGMT’s album was named after a particularly impressive OUG?
