%ROWTYPE, Part II

In the last entry on the subject, Gary Meyers pointed me to this link. Not having used %ROWTYPE that often I was not aware of some of the features.

The 2 that interested me the most were the INSERT and UPDATE:

DECLARE
  l_table MY_TABLE%ROWTYPE;
BEGIN
  INSERT INTO my_table
  VALUES l_table;
END;

DECLARE
  l_table MY_TABLE%ROWTYPE;
BEGIN
  UPDATE my_table
    SET ROW = l_table
  WHERE my_table_id = l_table.my_table_id;
END;

So I began to use them (despite the bad feeling I get...).

I started to receive errors however, NOT NULL constraint violations in fact. How could that be? Here's the definition of MY_TABLE

CREATE TABLE my_table
(
  my_table_id NUMBER(10)
    CONSTRAINT pk_mytableid PRIMARY KEY,
  status VARCHAR2(1) DEFAULT 'A'
    CONSTRAINT nn_status_mytable NOT NULL
    CONSTRAINT ck_aori_status_mytable CHECK ( status IN ( 'I', 'A' ) ),
  start_date DATE DEFAULT SYSDATE
    CONSTRAINT nn_startdate_mytable NOT NULL
);

See the column DEFAULTs?

I would imagine these are functioning as expected, but I don't like it. One benefit I see to this method is that it saves a little bit of typing (despite the SELECT * feel), or so I thought. If you don't explicitly set the value like this:

  l_table.status := 'A';
  l_table.start_date := SYSDATE;

you are hosed. Plus, what if the calling application had already set those, now you have this:

IF l_table.status IS NULL THEN
  l_table.status := 'A';
END IF;

IF l_table.start_date IS NULL THEN
  l_table.start_date := SYSDATE;
END IF;

So where is the savings in that?

%ROWTYPE

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?