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 - 11:35:03 CDT
