Re: Retrieving the prior record

From: Mark D Powell <mark.powell_at_eds.com>
Date: 13 Jul 2001 06:31:50 -0700
Message-ID: <178d2795.0107130531.5d4e699d@posting.google.com>

Stephen Bell <stephen.bell_at_cgi.ca> wrote in message news:<3B4DAF6E.C8C05803_at_cgi.ca>...
> Hi John..
>
> There is a PL/SQL table method called 'prior'....so,
>
> plsql_table(i).prior should do it (note the 'dot' before the word prior)
>
> Hope this helps,
>
> Steve
>
> John Johnson wrote:
>
> > I need to take any record in a table, given the key, and retreive the
> > immediately prior record in the table.
> > All of the records have a sequence number, I just cannot figure out what the
> > PL/SQL should look like.
> >
> > Thanks
> > -John

Steve, your code will only work for a pl/sql table which is really more like an single dimension array than anything else. He could use your suggestion if he can load all the data into a pl/sql table and work with it there, but the size of a pl/sql table is OS dependent and past tests have shown me a limit of around 10M (back in 7.x days. I have yet to retest 8.1). If the data is in an Oracle table then John could do something like the following if he wants the data in logical key order as opposed to physical storage:

fetch the current record
subtract 1 from the numberic sequence generated key attempt to fetch this record

The record may or may not be there and an exception block would probably be necessary to handle missing key values due to shutdown, rollback, and sequence cache flushes.

Here is a hasty code example:
FLD1 FLD2 FLD3
---------- ---------- --------- note physical order different key order

one                 1 16-MAY-01
three               3 15-JUN-01
two                 2 13-JUL-01
four                4 13-JUL-01

UT1> set serveroutput on
UT1> declare
  2 v_key number;
  3 v_priorkey number;
  4 --
  5 begin
  6 v_key := 5;
  7 for i in 1..5 loop
  8 begin
  9 select (fld2 - 1)

 10   into      v_priorkey
 11   from      marktest
 12   where     fld2 = v_key;

13 dbms_output.put_line('Got it key = '||v_key); 14 v_key := v_priorkey;
15 exception
16 when no_data_found then

 17        dbms_output.put_line('Missing key = '||v_key);
 18        v_key := v_key - 1;

19 end;
20 end loop;
21 end;
22 /
Missing key = 5

Got it key = 4
Got it key = 3
Got it key = 2
Got it key = 1

PL/SQL procedure successfully completed.

John would need better code with perhaps a check for a minimum key and/or a limit on the number of searches, better looping etc....

Mark D Powell --

Received on Fri Jul 13 2001 - 08:31:50 CDT