整型序号产生器(一)

原文地址：http://www.sqlsnippets.com/en/topic-11814.html

有的时候。你必须创建连续的整数。例如假设你有如下的数据:

select * from t ;

DAY_OF_WEEK       VAL

----------- ----------

         1       100

         3       300

         4       400

         5       500

你想如下输出：

DAY_OF_WEEK       VAL

----------- ----------

         0

         1       100

         2

         3       300

         4       400

         5       500

         6

如果你有个表。其数据为0到6，那么你可以写如下的查询：

select

 day_of_week,

 t.val

from

 days_of_the_week d

   left outer join t using ( day_of_week )

order by

 day_of_week

;

DAY_OF_WEEK       VAL

----------- ----------

         0

         1       100

         2

         3       300

         4       400

         5       500

         6

如果你有大量这样使用连续整数的查询语句，那么你最好有DAYS_OF_THE_WEEK这么一个表。

但是，你可能只是偶然使用连续整数，或者你没有CREATE TABLE权限，这个时候创建这么一个表是不现实或者是不可能的。实际上，有多种产生这样连续整数的方法，本文将介绍几种方法，下面的图将帮助你决定使用何种方法。

在本文结尾，将给出各种方法性能的比较。

MODEL方法

在SELECT命令中，使用MODEL子句。这个方法只适用于10g或者更高版本。

使用这个技术，用下面的查询可以产生从1开始的整数

select integer_value

from  dual

where 1=2

model

 dimension by ( 0 as key )

 measures    ( 0 as integer_value )

 rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )

;

INTEGER_VALUE

-------------

           1

           2

           3

           4

           5

           6

           7

           8

           9

          10

INCREMENT值可以让你控制整数值的间隔.

select integer_value

from  dual

where 1=2

model

 dimension by ( 0 as key )

 measures    ( 0 as integer_value )

 rules upsert ( integer_value[ for key from 2 to 10 INCREMENT 2 ] = cv(key) )

;

INTEGER_VALUE

-------------

           2

           4

           6

           8

          10

我们可以使用绑定变量使这个结果更加普遍些。

variable v_first_key number

variable v_last_key  number

variable v_increment number

execute :V_FIRST_KEY := 1

execute :V_LAST_KEY  := 5

execute :V_INCREMENT := 2

select key, integer_value

from  dual

where 1=2

model

 dimension by ( 0 as key )

 measures    ( 0 as integer_value )

 rules upsert

 ( integer_value[ for key from :V_FIRST_KEY to :V_LAST_KEY increment 1 ]

     = nvl2( integer_value[cv()-1], integer_value[cv()-1] + :V_INCREMENT, cv(key) )

 )

;

      KEY INTEGER_VALUE

---------- -------------

        1            1

        2            3

        3            5

        4            7

        5            9

如果v_last_key值为空或者比v_first_key小，则不返回任何值。

execute :v_first_key := 1

PL/SQL procedure successfully completed.

execute :v_last_key := null

PL/SQL procedure successfully completed.

/

no rows selected

execute :v_last_key := 0

PL/SQL procedure successfully completed.

/

no rows selected

execute :v_last_key := -5

PL/SQL procedure successfully completed.

/

no rows selected

Day of the Week案例研究

我们在文中开始的例子中使用这个方法

select

 day_of_week ,

 t.val

from

 ( select day_of_week

   from  dual

   where 1=2

   model

     dimension by ( 0 as key )

     measures    ( 0 as day_of_week )

     rules upsert ( day_of_week[ for key from 0 to 6 increment 1 ] = cv(key) )

 ) i

   left outer join t using ( day_of_week )

order by

 day_of_week

;

DAY_OF_WEEK       VAL

----------- ----------