RE: A quick pl/sql datatypes question

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 28 Aug 2001 11:21:34 -0700
Message-ID: <F001.0037A0E6.20010828111623@fatcity.com>

> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> 
> If you declare a variable to be of
>     table_name.column_name%type
> there are implicit constraints on the
> variable and any pl/sql assignments
> (in particular arithmetic operations)
> have to check that the result matches
> those constraints. At one time I believe
> this meant using a temporary variable
> to hold a result, then copying the result
> if the constraint was matched.

Could anyone explain this statement more fully? Why would there be "implicit constraints" when using table_name.column_name%rowtype vs. datatype? A simple test below showed no time difference.

SQL> create table t (d date not null primary key, n number) ; Table created.

SQL> set timing on
SQL> -- using "datatype" declaration
SQL> declare


  2     i pls_integer ;

  3     dd date ;

  4  begin

  5     for i in 1..100000

  6     loop

  7        dd := sysdate ;

  8     end loop ;

  9  end ;
 10  /

PL/SQL procedure successfully completed. Elapsed: 00:00:09.54

SQL> -- using "table.column%type" declaration SQL> declare
  2     i pls_integer ;
  3     dd t.d%type ;
  4  begin
  5     for i in 1..100000
  6     loop
  7        dd := sysdate ;
  8     end loop ;
  9  end ;
 10  /

PL/SQL procedure successfully completed. Elapsed: 00:00:09.34 Received on Tue Aug 28 2001 - 13:21:34 CDT