Home > PL/SQL, SQL*Plus > Using %ROWTYPE for INSERTs and UPDATEs

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?

About these ads
Categories: PL/SQL, SQL*Plus
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: