Home

转载：generate_series: an Oracle implementation in light of SQL Design Patterns - The Spatial Database Advisor

# GENERATE_SERIES

There is a very useful function in PostgreSQL called generate_series that can be used to generate a series of integer numbers from some start value to an end value with an optional step value.

Here is the function and its description from the PostgreSQL help.

Function Argument Type Return Type Description
generate_series(start, stop) int or bigint setof int or setof bigint (same as argument type) Generate a series of values, from start to stop with a step size of one
generate_series(start, stop, step) int or bigint setof int or setof bigint (same as argument type) Generate a series of values, from start to stop with a step size of step

There are a number of ways we can code this function in Oracle. Since the original function is a “set returning function”, we need to code generate_series so that it returns a table of numbers:

1
TYPE t_numbers IS TABLE OF number;

The most efficient way to do this is via a PIPELINED function, so that is what I will code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create or replace
 function generate_series(p_start in pls_integer,
                          p_end   in pls_integer,
                          p_step  in pls_integer := 1 )
       Return t_numbers PIPELINED
   As
    v_i    PLS_INTEGER := CASE WHEN p_start IS NULL THEN 1 ELSE p_start END;
    v_step PLS_INTEGER := CASE WHEN p_step IS NULL OR p_step = 0 THEN 1 ELSE p_step END;
    v_terminating_value PLS_INTEGER := p_start + TRUNC(ABS(p_start-p_end) / abs(v_step) ) * v_step;
   Begin
     -- Check for impossible combinations
     If ( p_start > p_end AND SIGN(p_step) = 1 )
        Or
        ( p_start < p_end AND SIGN(p_step) = -1 ) Then
       Return;
     End If;
     -- Generate integers
     LOOP
       PIPE ROW ( v_i );
       EXIT WHEN ( v_i = v_terminating_value );
       v_i := v_i + v_step;
     End Loop;
     Return;
  End generate_series;
/

Now, to run the tests on the PostgreSQL help page:

Let’s start with a simple, additional, example not on the page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
select g.column_value as generate_series
  from table(generate_series(1,5)) g;
-- Results
GENERATE_SERIES
----------------------
1
2
3
4
5
.
5 rows selected
--
-- Now, let's execute the ones on the help page.
--
select g.column_value as generate_series
  from table(generate_series(2,4)) g
-- Results
GENERATE_SERIES
---------------
              2
              3
              4
.
3 rows selected.
--
-- Another
--
select g.column_value as generate_series
  from table(generate_series(5,1,-2)) g
-- Results
GENERATE_SERIES
---------------
              5
              3
              1
.
3 rows selected.
--
-- And again
--
select g.column_value as generate_series
  from table(generate_series(4,3)) g
-- Results
no rows selected
--
--With one additional:
--
select g.column_value as generate_series
  from table(generate_series(-4,-1,1)) g
-- Results
GENERATE_SERIES
---------------
             -4
             -3
             -2
             -1
.
4 rows selected.
--
-- And finally.
--
select to_char(current_date + sa.column_value,'YYYY-MM-DD') as da
  from table(generate_series(0,14,7)) sa
-- Results
     DATES
----------
2008-11-06
2008-11-13
2008-11-20
.
3 rows selected.

# Alternative Table Function

Now all this is very good, but there is some debate as to implementing a series of integers in this way.

Vadim Tropashko, in his excellent book, “SQL Design Patterns, The Expert Guide to SQL Programming”, Rampart Press has a whole chapter (2) devoted to “Integer Generators in SQL”. In this chapter, Vadmin presents an coding of a simple Integer generating table function called “Integers”. His coding is as follows.

1
2
3
4
5
6
7
8
9
create or replace function Integers
       return t_integers PIPELINED
As
Begin
   Loop
     PIPE ROW ( 0 );
   End Loop;
   Return;
end Integers;

We will now use this function to implement the PostgreSQL help examples above.

Firstly, generating numbers between 1 and 5.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
select rownum as rin
  from table(Integers)
 where rownum <= 5
-- Results
       RIN
----------
         1
         2
         3
         4
         5
.
5 rows selected.
--
-- All numbers between 2 and 4.
--
select rin
  from (select rownum as rin
          from table(Integers)
         where rownum <= 5)
 where rin between 2 and 4
-- Results
       RIN
----------
         2
         3
         4
.
2 rows selected.
--
-- Series 5,3,1 using a step of -2.
--
select rin
  from (select 5 + ((rownum-1) * -1) as rin
          from table(Integers)
         where rownum < 10)
 where rin between 1 and 5
   and mod(rin,ABS(-2)) = 1;
-- Results
       RIN
----------
         5
         3
         1
.
3 rows selected

I won’t code the invalid series. So let’s move on to the negative series.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
select rin
  from (select -1 + (rownum - 1 ) * -1 as rin
          from table(Integers)
         where rownum < 10
         order by 1)
 where rin between -4 and -1;
-- Results
       RIN
----------
        -4
        -3
        -2
        -1
.
4 rows selected
--
-- And finally.
--
select to_char(current_date + sa.rin,'YYYY-MM-DD') as dates
  from (select (rownum - 1) as rin
          from table(Integers)
         where rownum <= 15) sa
 where mod(rin,7) = 0;
-- Results
     DATES
----------
2008-11-06
2008-11-13
2008-11-20
.
3 rows selected.

# Hierarchical Queries

Vadim goes on in his book to describe the use of hierarchical queries for generating integer series. Here is an example of how to generate all even numbers between 5 and 8.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
elect level
  from dual
 where level between 5 and 8
   and MOD(level,2) = 0
  connect by level <= 10
-- Results
     LEVEL
----------
         6
         8
.
2 rows selected.
--
-- And the negative series -4,-3,-2,-1 above.
--
select level * -1
  from dual
 where level between 1 and 4
 connect by level <= 5
 order by level desc;
-- Results
     LEVEL
----------
        -4
        -3
        -2
        -1
.
4 rows selected

I am a big fan of hierachical queries and have used them a lot in my work but mainly in the area of generating sample data (see other articles in this blog for examples). My personal view is that coding integer ranges with the “connect by level” hierarchical query is simpler to use and code than the Integers() function. However, the “connect by level” usage above does not work in versions of Oracle before 10g. So all you 9i users will have to resort to coding a suitable table function.

# Application to Oracle Spatial Data Processing

Since 11g, Oracle has included a function called SDO_UTIL.EXTRACT that can be used to extract the elements of an SDO_GEOMETRY object. For example, it can be used to extract the linestrings in a multilinestring object or the polygons in a multipolygon object.

In addition, Oracle has a function called SDO_UTIL.GetNumElem which can return the number of polygons/linestrings in a multipolygon/multilinestring.

I will show how to use both the generate_series and CONNECT BY LEVEL approaches to extracting single geometries from a multigeometry.

CONNECT BY LEVEL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH mGeom AS (
SELECT rownum as id,
       sdo_geometry(2006,null,null,
                    sdo_elem_info_array(1,2,1,
                                        5,2,1,
                                        9,2,1),
                    sdo_ordinate_array(1,1,10,10,
                                       20,1,50,50,
                                       100,0,150,50)) as geom
  FROM dual
)
SELECT id,
       line_id,
       mdsys.sdo_util.GetNumElem(geom) as line_Count,
       mdsys.sdo_util.Extract(geom,line_id,0) as geom
    FROM mGeom p,
        (select level as line_id
          from dual
          connect by level <= (select max(mdsys.sdo_util.GetNumElem(geom)) from mGeom p ) ) i
   WHERE i.line_id <= mdsys.sdo_util.GetNumElem(geom)
ORDER BY 1,2;
--
-- Results
--
ID LINE_ID LINE_COUNT GEOM
-- ------- ---------- ----------------------------------------------------------------------------------------
1  1       3          SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1,1,10,10))
1  2       3          SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(20,1,50,50))
1  3       3          SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(100,0,150,50))

That query, I think, is unnecessarily complicated. Let’s try generate_series.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH mGeom AS (
SELECT rownum as id,
       sdo_geometry(2006,null,null,
                    sdo_elem_info_array(1,2,1,
                                        5,2,1,
                                        9,2,1),
                    sdo_ordinate_array(1,1,10,10,
                                       20,1,50,50,
                                       100,0,150,50)) as geom
  FROM dual
)
SELECT id,
       i.column_value as line_id,
       mdsys.sdo_util.GetNumElem(geom) as Line_Count,
       mdsys.sdo_util.Extract(geom,i.column_value,0) as geom
    FROM mGeom p,
         TABLE(geom.generate_series(1,mdsys.sdo_util.GetNumElem(geom),1)) i
ORDER BY 1,2;
--
-- Results
--
ID LINE_ID LINE_COUNT GEOM
-- ------- ---------- ----------------------------------------------------------------------------------------
1  1       3          SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1,1,10,10))
1  2       3          SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(20,1,50,50))
1  3       3          SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(100,0,150,50))

Both work nicely, but I rather prefer the cleanliness of the generate_series query.

# Conclusion

Tropashko’s preference (as also Mikito Harakiri) in coding an integer series generation table function is to code one without parameters, such as Integers(), and not like generate_series. The main reason is that he prefers the use of predicates in SQL SELECT statements (such as “rownum < = 1000”) than parameters passed to a function. This is because they are at a “higher abstraction level”, claiming that “programs with predicate expressions are shorter and cleaner”. Because I am a “SQL man”, preferring a single SQL statement to complex PL/SQL, and because I like things to be done “orthogonally”, “theoretically” and via patterns/templates I am tempted to agree with Tropashko. However, when I look at the SQL above, I find the generate_series implementation much, much cleaner and less complicated.

The problem with the Integers() function is that it forces me to “reinvent the wheel” each time I need a series of integers. With simple series the SQL Is not too bad. But more complicated series create much more complicated SQL. Now, many of my uses of SQL result in large SQL statements with can result in many uses of generate_series. To integrate multiple calls to the same series generation can be neatly encapsulated using the WITH construct, but even so it does add, to my way of thinking, unnecessary complicated. And complication usually means lots of errors and longer debugging.

In the end a generate*series function like above allows us to design and algorithm, code and test it and then use it many times without having to remember how to code an integer series every time we need it. This is part of good software engineering: the algorithm is *“encapsulated”_ in a program unit that offers a stable interface. And interfaces are everything.

I hope that this is of use to someone.

Edited on