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

From: Paul Scott <pscott_at_r.co.uk>
Date: Tue, 22 Jul 2003 12:50:22 +0100
Message-ID: <1g8qhvcm4sdksa27vdqgvtsgn42g1gkkdi@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 - 06:50:22 CDT