Re: Sort PL/SQL Table

From: <sjakobson_at_my-dejanews.com>
Date: Fri, 21 Aug 1998 18:27:33 GMT
Message-ID: <6rke6k$27e$1@nnrp1.dejanews.com>

One of the "tricks" with sorting PL/SQL tables is to populate it in already sorted order. If I understand correctly, you are fetching from a cursor, calculate age based on the fetched row and populate PL/SQL table. Assuming you are not fetching more than 9999999 rows, do the following:

Add ROWNUM to select list of your cursor;
FOR Rec IN Your_Cursor LOOP Age := ...; -- Calculate Age Your_Tbl(Age * 10000000 + Rec.ROWNUM) := ...; -- populate PL/SQL table END LOOP;

This way each index value in PL/SQL table will have corresponding age in the leftmost digits followed by some unique (ROMNUM) value and therefore, PL/SQL table is automatically sorted by AGE without any sorting. 9999999 limitation is based on PL/SQL table index max value of 2147483647. We reserve 3 leftmost digits for AGE, therefore ROWNUM must be < 10000000. If you are fetching fore than 9999999 rows you can do the following:

DECLARE Age_Count_Tbl_Type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER; Age_Count_Tbl Age_Count_Tbl_Type; BEGIN FOR i IN (0..150) -- I hope you will not have people older than 150 Age_Count_Tbl(i) := 0; -- initialize row count for each age END LOOP; FOR Rec IN Your_Cursor LOOP Age := ...; -- Calculate Age Age_Count_Tbl(Age) := Age_Count_Tbl(Age) + 1; -- increment count. Your_Tbl(Age * 10000000 + Age_Count_Tbl(Age)) := ...; -- populate -- PL/SQL table END LOOP; END;

Both solutions will result in a sorted (by age) PL/SQL table. Second solution also provides you with row count by age. Now, if you simply select from PL/SQL table in index order:

DECLARE i BINARY_INTEGER;
BEGIN
  i := Your_Tbl.FIRST;
  WHILE i IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(Your_Tbl(i).Age);     i := Your_Tbl.Next;
  END LOOP;
END;
/

you will get results already sorted by age. Hope you got the idea.

Solomon Yakobson.

In article <6rjni6$3vl$1_at_nnrp1.dejanews.com>, sirohimi_at_my-dejanews.com wrote:
> I need to sort a package defined PL/SQL table. I am populating the table by a
> CURSOR that has an age range that I am breaking down to its individual
> components of that age into the table. I am additionally storing other
> information in the table as well (It is a table of records).
>
> My problem, I need to sort by age and the only way I can see doing this is by
> a bubble sort. I can possible have n^2 traversing in worst case. I have
> looked at many books and I cannot see that I have access to the pointers (so
> I am assuming that I cannot move the pointers of the table. Since the table
> is indexed by binary integers it may be treated as if it were an array or
> records. Does any one have a sorting algorithm they can share for
> accomplishing this? Any help is appreciated.
>
> Thanks in advance,
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Aug 21 1998 - 13:27:33 CDT