Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: table constraint

Re: table constraint

From: <rwijk72_at_gmail.com>
Date: Tue, 26 Jun 2007 12:57:34 -0000
Message-ID: <1182862654.451560.217130@n60g2000hse.googlegroups.com>


In this scenario you definitely don't want to use database triggers.

It would be best if all inserts were done by the same session, so you could bulk insert them. However, I suspect that it must be doing all inserts separately from several user sessions. In this case your performance requirement does not seem to be a problem.

See:

SQL> create table mytable
  2 as
  3 select 1 colour, 1 direction,
to_date('25062007120000','ddmmyyyyhh24miss') mydate from dual   4 /

Tabel is aangemaakt.

SQL> alter table mytable add constraint mytable_pk primary key (colour,direction,mydate)
  2 /

Tabel is gewijzigd.

SQL> create package mytable_api
  2 as
  3 procedure ins

  4    ( p_colour in mytable.colour%type
  5    , p_direction in mytable.direction%type
  6    , p_mydate in mytable.mydate%type
  7 );
  8 end mytable_api;
  9 /

Package is aangemaakt.

SQL> create package body mytable_api
  2 as
  3 procedure lck
  4 ( p_colour in mytable.colour%type   5 , p_direction in mytable.direction%type   6 )
  7 is

  8      cursor c_lock
  9      is
 10      select 'dummy'
 11        from mytable
 12       where colour = p_colour
 13         and direction = p_direction
 14      for update of mydate
 15      ;
 16    begin
 17      open c_lock;
 18      close c_lock;

 19 end lck
 20 ;
 21 procedure ins
 22    ( p_colour in mytable.colour%type
 23    , p_direction in mytable.direction%type
 24    , p_mydate in mytable.mydate%type
 25 )
 26 is
 27 l_dummy varchar2(5);
 28 begin
 29      lck(p_colour,p_direction)
 30      ;
 31      select 'dummy'
 32        into l_dummy
 33        from mytable
 34       where colour = p_colour
 35         and direction = p_direction
 36         and mydate between p_mydate - 3/24/60 and p_mydate +
3/24/60
 37      ;

 38 exception
 39 when no_data_found then
 40      insert into mytable
 41      ( colour
 42      , direction
 43      , mydate
 44      )
 45      values
 46      ( p_colour
 47      , p_direction
 48      , p_mydate
 49      );
 50    when too_many_rows then
 51      null;

 52 end ins
 53 ;
 54 end mytable_api;
 55 /

Package-body is aangemaakt.

SQL> declare
  2 l_start_time number := dbms_utility.get_time;   3 begin
  4 for i in 1..200
  5 loop

  6      mytable_api.ins
  7      ( trunc(dbms_random.value(0,2))
  8      , trunc(dbms_random.value(0,2))
  9      , sysdate - dbms_random.value(-.2,.2)
 10      );

 11 end loop;
 12 dbms_output.put_line(to_char(dbms_utility.get_time - l_start_time) || ' hsecs');
 13 end;
 14 /
24 hsecs

PL/SQL-procedure is geslaagd.

SQL> select count(*) from mytable
  2 /

                              COUNT(*)
--------------------------------------
                                   159

1 rij is geselecteerd.

Regards,
Rob. Received on Tue Jun 26 2007 - 07:57:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US