ORACLENERD: %ROWTYPE

%ROWTYPE

Tuesday, August 4, 2009

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable...

I have never been fond of using %ROWTYPE, for me, it feels to much like SELECT *.

The last 2 years or so I've worked on legacy systems though, and that's forced me to reconsider %ROWTYPE.

In particular, you have various INSERT/UPDATE statements on a given table all over the place. I have typically written stored procedures to handle discrete, logical, business requirements. For example, you need to update a person's name. There are a couple of ways to do this.

1. Create a discrete procedure like update_person_name that accepts the appropriate input.
2. Create a generic procedure like update_person which will take all the possible inputs and possibly change them all (the data is UPDATEd, even if the data doesn't change).
3. Create a super generic (fancy term) procedure that accepts a row from a table, PERSON%ROWTYPE.

One advantage of using %ROWTYPE, is that your input list has shrunk considerably. Instead of

PROCEDURE update_person_name
  ( p_personnameid IN NUMBER,
    p_namecodeid IN NUMBER,
    p_titleid IN NUMBER,
    p_firstname IN VARCHAR2,
    p_middlename IN VARCHAR2,
    p_lastname IN VARCHAR2,
    p_suffixid IN NUMBER )
IS
BEGIN
...

You could have

PROCEDURE update_person_name( p_personname_row IN PERSON_NAME%ROWTYPE )
IS
BEGIN
...

Ultimately, it doesn't really save on typing because you have to assign the variables somewhere (it's just shifted from one location to another). Here's how you would call the second example:

DECLARE
  l_row PERSON_NAME%ROWTYPE;
BEGIN
  l_row.personnameid := 22;
  l_row.namecodeid := 2;
  l_row.titleid := 4;
  l_row.firstname := 'Jake';
  l_row.middlename := NULL;
  l_row.lastname := 'Kuramoto';
  l_row.suffxid := 'VI';

  package_name.update_person_name( l_row );
  --OR (BETTER)
  package_name.update_person_name( p_personname_row => l_row );
END;

As opposed to the other way

BEGIN
  package_name.update_person_name
    ( p_personnameid => 22,
      p_namecodeid => 2,
      p_titleid => 4,
      p_firstname => 'Jake',
      p_middlename => NULL,
      p_lastname => 'Kuramoto',
      p_suffixid => 'VI' );
END;

Here's where I get a little scared though. Inside the procedure which accepts the PERSON_NAME%ROWTYPE, my UPDATE looks like this:

UPDATE person_name
  SET namecodeid = NVL( p_personname_row.namecodeid, namecodeid ),
      titleid = NVL( p_personname_row.titleid, titleid ),
      firstname = NVL( p_personname_row.firstname, firstname ),
      middlename = NVL( p_personname_row.middlename, middlename ),
      lastname = NVL( p_personname_row.lastname, lastname ),
      suffixid = NVL( p_personname_row.suffixid, suffixid )
WHERE personnameid = p_personname_row.personnameid;

All the NVL make me feel dirty for some reason. I'm not saying it makes sense, I'm just saying.

However, it does serve a purpose, in a legacy system anyway. If you need to quickly and easily get a handle on things, without knowing all the rules of a given system, it works well...as a first step.

Thoughts? Opinions? Does anyone else out there use this? For similar reasons or completely different? Should I feel guilty about this? Or is this an acceptable method you have (and do) use?

Labels: %ROWTYPE, oradb, plsql

¶ 8/04/2009 08:31:00 PM

Comments:

"All the NVL make me feel dirty for some reason"
Because they stop you setting a value to null once it has been non-null ?

You can do an
UPDATE person_name
SET ROW = p_personname_row
WHERE...

http://www.oreillynet.com/pub/a/network/2003/04/22/feuerstein.html?page=2

To get rid of that uncomfortable 'SELECT *' feeling, you can go this route.

DECLARE
cursor c_1 is select first_name, middle_name, surname
from names
where id = 1;
c_rec c_1%rowtype;
BEGIN
UPDATE
(select first_name, middle_name, surname
from names
WHERE id = 1)
SET ROW = c_rec
;
END;

# posted by

Gary Myers : August 5, 2009 at 12:19 AM

@gary

I'm not sure why it makes me feel dirty. (I really need to stop saying that too. ;)

SET ROW? Awesome!

I did not know about that. I'm going to have to give that a whirl.

Thanks!

# posted by

oraclenerd : August 5, 2009 at 12:34 AM

How do you distinguish between wanting to set a value to NULL and not wanting to update a value?

Some might argue that on initialising the row variable you should populate the existing values and then overwrite with the ones you want to change. But then some will be introducing multi-user / concurrency issues if something else has updated the row in the meantime.

Some people might argue that you could use magic values to indicate that you don't want to update a particular column or a magic value to indicate NULL. Nasty.

# posted by

DomBrooks : August 5, 2009 at 4:48 AM

Dom,

That's it! That's why I feel dirty. It's been so long though I had forgotten about it.

I think that's why I prefer stored procedures to perform logical transactions.

Great, now I have to ponder that too...

# posted by

oraclenerd : August 5, 2009 at 9:21 AM

Dom,

I think one reason it didn't occur to me recently is that I usually have NOT NULL constraints on all the columns.

But you are absolutely right, how do you change a value from 'chet' to NULL? You can't.

Back to drawing board...

# posted by

oraclenerd : August 5, 2009 at 9:25 AM

I'm with you about the NVL...I'm not saying there's a better way to do it, I just don't like it.

# posted by

moleboy : August 5, 2009 at 9:38 AM

Wow, I'm test data now? Just please no SELECT INTO, unless it's pre-approved content :)

# posted by

Jake : August 5, 2009 at 11:43 AM

This comment has been removed by the author.

# posted by

Mike : August 6, 2009 at 12:42 AM

If you really want to get freaky-deaky with it (a little Austin Powers lingo for you there...no charge, of course) you have to introduce some DSQL and synchronized nested tables (or assoc arrays ... in this case nested tables 'cause I wanted to declare and initialize simultaneously).

This way, I believe, you can control what table you're updating and what columns inside the table you're updating, whether few or many. Also, you can set columns to null by name on demand (constraints notwithstanding).

The WHERE part would have to be dealt with in a serious way, as opposed to the "1=1" here.

The types (ty_list_of_columns & ty_list_of_values) would have to be declared either in a package spec or at the schema level ... or somewhere ... when implementing in a named-procedure environment (i.e., not an anonymous block).

Various languages (I've heard) have compatibilities with Oracle's collections, varying by language on detail. So getting the column names and values over from the GUI to the required type (in parameter for "please_update_my_table" proc) to Oracle might be a challenge.

/* ***** */
create table t1
  (f1 varchar2(1)
  ,f2 varchar2(1)
  ,f3 varchar2(1)
  ,f4 varchar2(1));
insert into t1 values ('z','z','z','z');
commit;
/* ***** */
declare
  type ty_list_of_columns is table of varchar2(32);
  type ty_list_of_values is table of varchar2(32);
  vr_list_of_columns ty_list_of_columns
    := ty_list_of_columns('f1','f2','f3','f4');
  vr_list_of_values ty_list_of_values
    := ty_list_of_values('a','b','c','NULL');
  procedure please_update_my_table
    (table_name in varchar2
    ,list_of_columns in ty_list_of_columns
    ,list_of_values in ty_list_of_values) is
    stmt varchar2(32767);
  begin
    stmt := 'update '||chr(10)||' '||table_name;
    stmt := stmt||chr(10)||'set ';
    for i in vr_list_of_columns.first..
      vr_list_of_columns.last loop
      if vr_list_of_values(i) = 'NULL' then
        stmt := stmt||chr(10);
        stmt := stmt||' '||vr_list_of_columns(i);
        stmt := stmt||' = NULL,';
      else
        stmt := stmt||chr(10)||' ';
        stmt := stmt||vr_list_of_columns(i);
        stmt := stmt||'='''||vr_list_of_values(i)||''',';
      end if;
    end loop;
    stmt := substr(stmt,1,length(stmt)-1);
    stmt := stmt||chr(10);
    stmt := stmt||'where'||chr(10);
    stmt := stmt||' 1 = 1';
    dbms_output.put_line(chr(10)||'*****'||chr(10)||stmt);
    execute immediate stmt;
  end;
begin
  please_update_my_table
    ('t1',vr_list_of_columns,vr_list_of_values);
end;

# posted by

Mike : August 6, 2009 at 1:39 AM