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_nameYou could have
( 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 )
PROCEDURE update_person_name( p_personname_row IN PERSON_NAME%ROWTYPE )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:
DECLAREAs opposed to the other way
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 );
package_name.update_person_name( p_personname_row => l_row );
BEGINHere's where I get a little scared though. Inside the procedure which accepts the PERSON_NAME%ROWTYPE, my UPDATE looks like this:
( p_personnameid => 22,
p_namecodeid => 2,
p_titleid => 4,
p_firstname => 'Jake',
p_middlename => NULL,
p_lastname => 'Kuramoto',
p_suffixid => 'VI' );
UPDATE person_nameAll the NVL make me feel dirty for some reason. I'm not saying it makes sense, I'm just saying.
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;
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?