整型序号产生器 zt

原文地址：http://www.sqlsnippets.com/en/topic-11814.htm l[@more@]

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

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权限，这个时候创建这么一个表是不现实或者是不可能的。实际上，有多种产生这样连续整数的方法，本文将介绍几种方法，下面的图将帮助你决定使用何种方法。

Feature	Integer Table	MODEL	ROWNUM + a Big Table	CONNECT BY LEVEL	CUBE	Type Constructor	Pipelined Function
纯SQL方法，不需要其它自定义对象	N	Y	Y	Y	Y	N	N
适用10g以前版本	Y	N	Y	Y	Y	Y	Y

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

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1023087/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82387/viewspace-1023087/