Re: returning a constant set of numbers from a "select"

I've just looked at Thomas Kyte's solution, essentially we have found the same approach to solving the problem. He mentions using PIPELINED which should make fetches appear faster, but I believe this is a new 9i feature and I'm on 8i. In my defence, mine is a little more flexible as I've allowed reverse loops and the start to be defined, and it is more backward compatible. Horses for courses!

On Tue, 22 Jul 2003 09:35:03 -0700, "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote:

>I think pipelined version would be faster because it wouldn't necessarily
>materialize intermediate table. Benchmark anybody?
>
>"Paul Scott" <pscott_at_r.co.uk> wrote in message
>news:1g8qhvcm4sdksa27vdqgvtsgn42g1gkkdi_at_4ax.com...
>> I wanted this a while ago and so wrote the following. It has the
>> following benefits :
>> 1. It is fast
>> 2. It is flexible (caller defines range)
>> 3. It supports both forward and reverse loops
>> 4. It can be joined to other tables.
>>
>>
>> An example of how to use it for your situation:
>>
>> select L.COLUMN_VALUE from table(cast(Loop(1, 1000) as TY_LOOP)) L
>> /
>>
>> but of course you can define the range e.g 10000 downto 5 :
>>
>> select L.COLUMN_VALUE from table(cast(Loop(10000, 5) as TY_LOOP)) L
>> /
>>
>> --==============================================================
>>
>> create or replace type TY_LOOP as table of Integer; /* cannot be
>> PLS_integer in 8i! */
>> /
>>
>> create or replace function Loop(pStartValue PLS_Integer, pStopValue
>> PLS_Integer) return TY_LOOP is
>> /* Returns a collection of integers between pStartValue and
>> pStopValue,
>> To use in SQL :
>> (forward) 1. select L.COLUMN_VALUE from table(cast(Loop(1, 3) as
>> TY_LOOP)) L
>> (reverse) 2. select L.COLUMN_VALUE from table(cast(Loop(5, 3) as
>> TY_LOOP)) L
>> 1. COLUMN_VALUE 2. COLUMN_VALUE
>> --------------- ------------
>> 1 5
>> 2 4
>> 3 3
>>
>> NOTE : Null parameters behave like standard for loops : VALUE_ERROR
>> exception */
>>
>> vResult TY_LOOP := TY_LOOP();
>> begin
>> if pStartValue > pStopValue then
>> for n in reverse pStopValue..pStartValue
>> loop
>> /* for performance reason next 2 lines are NOT made into local
>> sub proc */
>> vResult.Extend;
>> vResult(vResult.Last) := n;
>> end loop;
>> else
>> for n in pStartValue..pStopValue
>> loop
>> vResult.Extend;
>> vResult(vResult.Last) := n;
>> end loop;
>> end if;
>> return vResult;
>> end;
>> /
>>
>> Paul Scott
>>
>>
>>
>>
>> On Mon, 21 Jul 2003 13:10:40 GMT, p!@unix.ndo.co.uk (p!) wrote:
>>
>> >hi,
>> >
>> >could anyone tell me how i can do the following:
>> >
>> >would like a select statement which returns all the numbers between a
>> >variable start and end point as seperate rows without using a "proper"
>> >table, ie:
>> >
>> >result
>> >----------
>> >1
>> >2
>> >3
>> >4
>> >5
>> >6
>> >7
>> >8
>> >9
>> >10
>> >..
>> >1000
>> >
>> >
>> >any ideas?!
>> >
>> >thanks,
>> >p!
>>
>
Received on Tue Jul 22 2003 - 13:02:27 CDT