Re: PL/SLQ question

In article <3427AA2F.5354D5A8_at_ifad.dk>, Anders Blaagaard <blaa_at_ifad.dk> wrote:

> I have a table with more than 100 columns, and a want to make a copy of
> a row, change a few fields, a put it back into the table (without
> changing the original). The idea is this:
>
> declare
> tmp_row table_name%rowtype;
> begin
> select * into tmp_row from table_name where ...
> tmp_row.field37 := ..
> tmp_row.field98 := ..
> insert into table_name values (tmp_row); -- ILLEGAL
> end;
>
> Can anyone think of an easy way to do this?
>
> Anders Blaagaard
> blaa_at_ifad.dk

If there are just two fields of many to change, use UPDATE.

I frequently have similar conceptual problems with PL/SQL. Because PL/SQL contains embedded SQL statements I often expect PL/SQL specific constructs, such as rowtype variables, to be usable directly in SQL statements. SQL statements have their own rules.

To solve this problem for myself, I organize my PL/SQL as follows:

1. I generally think in OO terms. To define a class, map the attributes to table columns, add another attribute to provide object identity, and map the methods to a package.
2. In the package provide access to the table through a standard set of private methods: GET, PUT, NEW, and FREE. The methods are implemented using SELECT, UPDATE, INSERT and DELETE respectively. GET returns a rowtype; PUT and NEW have a single rowtype parameter; and FREE has a single object id paramter.
3. Creating these methods from the table definition is a mechanical process. On my next project I think I might write a script to do it for me.
4. All other methods in the package use the GET, PUT, NEW, and FREE methods.

To paraphrase your example for a hypothetical guest object where I want to change just the guest's name and phone number attributes:

declare

    guest T_GUEST%rowtype ;
begin

    guest := Get( OID_GUEST ) ;

    guest.NAME := < new name > ;
    guest.PHONE := < new phone > ;

    Put( guest ) ;
end
;

The other benefit of this approach is that I can implement extended forms of data integrity in these methods. For example, accept several formats for phone number strings but store them in some canonical format as part of the PUT and UPDATE methods. Received on Sat Sep 27 1997 - 00:00:00 CDT