ITPub博客

整型序号产生器(一)

原创 Linux操作系统 作者:guoge 时间:2007-12-13 09:40:07 0 删除 编辑

原文地址: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

 

如果你有个表。其数据为06,那么你可以写如下的查询:

 

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/9036/viewspace-497/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    72
  • 访问量
    208763