查看: 3950|回复: 3

【俊哥儿张】生成列和标识列 实例详解

[复制链接]
求职 : 数据库管理员
论坛徽章:
186
授权会员
日期:2008-07-27 22:25:202014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14版主4段
日期:2015-02-26 02:21:03慢羊羊
日期:2015-03-04 14:51:35
发表于 2008-11-28 09:29 | 显示全部楼层 |阅读模式
1. 生成列和标识列简介
对于自增字段,可以通过两种方式指定:
GENERATED BY DEFAULT AS IDENTITY
GENERATED ALWAYS AS IDENTITY

区别是,第一种方式在插入数据时允许手工指定自增字段的值,只要不重复即可,并且数据库会自动设置下一个值;第二种方式则不允许指定,只能由数据库自动分配并插入。

IDENTITY列可生成用作主键的递增序列号。

CREATE TABLE TEST(C1 INT GENERATED BY DEFAULT AS IDENTITY,C2 INT)
这种方式当指定插入default时,j字段为自增1.2........

CREATE TABLE TEST(C1 INT GENERATED ALWAYS AS IDENTITY ,C2 INT )
这种方式只能指定插入default,如果指定j的值将会出错.

2. GENERATED列实例详解
自增列语法:

generated-column-spec:

|--+-| default-clause |-----------------------------------------------+--|
   +-GENERATED--+-ALWAYS-----+--AS IDENTITY--+----------------------+-+
   |            '-BY DEFAULT-'               '-| identity-options |-' |
   '-GENERATED ALWAYS AS--(--generation-expression--)-----------------'

identity-options:

|--+---------------------------------------------------------+--|
   |    .-----------------------------------------------.    |
   |    V  (1)                 .-1----------------.     |    |
   '-(-----------+-START WITH--+-numeric-constant-+---+-+--)-'
                 |               .-1----------------. |
                 +-INCREMENT BY--+-numeric-constant-+-+
                 | .-NO MINVALUE----------------.     |
                 +-+-MINVALUE--numeric-constant-+-----+
                 | .-NO MAXVALUE----------------.     |
                 +-+-MAXVALUE--numeric-constant-+-----+
                 | .-NO CYCLE-.                       |
                 +-+-CYCLE----+-----------------------+
                 | .-CACHE 20----------------.        |
                 +-+-NO CACHE----------------+--------+
                 | '-CACHE--integer-constant-'        |
                 | .-NO ORDER-.                       |
                 '-+-ORDER----+-----------------------'



实例1
CREATE TABLE TEST1
(
    C1 BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1) ,
    C2 DOUBLE,
    C3 DOUBLE GENERATED ALWAYS AS (C1 + C2),
    C4 SMALLINT GENERATED ALWAYS AS
        (CASE
            WHEN C1 > C2 THEN 1
            ELSE NULL
        END),
    C5 GENERATED ALWAYS AS ( C1 / 50)
)
GO

INSERT INTO TEST1(C1) VALUES (1)
GO
INSERT INTO TEST1(C2) VALUES (1)
GO
INSERT INTO TEST1(C3) VALUES (1)
GO
INSERT INTO TEST1(C2) VALUES (50)
GO
INSERT INTO TEST1(C2) VALUES (100)
GO
SELECT * FROM TEST1
GO

说明:
在TEST1表中
C1 字段 : 自增型,从1开始,每次增加1,无法指定值,由数据库自己生成值。
C2 字段 :   不能自动生成值,需用户指定。
C3/C4/C5字段 :由数据库自己生成值,用户不能指定值。

实例2
CREATE TABLE TEST2
(
    C1 BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1) ,
    C1_1 BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1)
)



报错如下:
SQL0372N  A column with data type or attribute ROWID, IDENTITY, security label,
or row change timestamp can only be specified once for a table.  
SQLSTATE=428C1

说明:
TEST2 表中不能出现 2个 GENERATED ALWAYS AS IDENTITY 的列。
列属性为 ROWID, IDENTITY, row change timestamp 或者 安全标号,只能被赋予表1次。

实例3
CREATE TABLE TEST3
(
    C1      CHAR(6) NOT NULL,
    C2      TIMESTAMP NOT NULL GENERATED ALWAYS
       FOR EACH ROW ON UPDATE
       AS ROW CHANGE TIMESTAMP,
    PRIMARY KEY (C1)
)
GO

INSERT INTO TEST3 (C1) VALUES ('A')
GO
INSERT INTO TEST3 (C2) VALUES  ( CURRENT TIMESTAMP)
GO
SELECT * FROM TEST3
GO


说明:
TEST3 表中
C2 是 ROW CHANGE TIMESTAMP , 不能指定,由数据库自己生成。

3. CREATE SEQUENCE

CREATE SEQUENCE num_seq
    START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE

INSERT INTO sale VALUES(NEXTVAL FOR num_seq,.....)


4. 修改列的生成或标识属性

可以使用 ALTER TABLE 语句中的 ALTER COLUMN 子句,在表中添加或删除列的生成或标识属性。

可执行下列其中一项操作:

使用现有非生成列时,可以添加生成的表达式属性。修改的列则成为生成列。
使用现有生成列时,可以删除生成的表达式属性。修改的列则成为正常的非生成列。
使用现有非标识列时,可以添加标识属性。修改的列则成为标识列。
使用现有标识列时,可以删除标识属性。修改的列则成为正常的非生成、非标识列。
使用现有生成列时,可以将生成列从 GENERATED ALWAYS 改变为 GENERATED BY DEFAULT。反之亦然,即可将生成列从 GENERATED BY DEFAULT 改变为 GENERATED ALWAYS。只有使用生成列时,此操作才有可能。
可以从用户定义的缺省列中删除缺省属性。执行此操作时,新缺省值为空。
可以删除缺省值、标识或生成属性,然后在相同的 ALTER COLUMN 语句中设置新的缺省值、标识或生成属性。
对于 CREATE TABLE 和 ALTER TABLE 语句而言,“ALWAYS”是 GENERATED 子句中的可选字。这意味着在 ALTER TABLE 语句中使用时,GENERATED ALWAYS 与 GENERATED 等价。



5. 管理序列行为

可以通过调整序列的行为来满足应用程序要求。在发出 CREATE SEQUENCE 语句以创建新序列或对现有序列发出 ALTER SEQUENCE 语句时,可以更改序列的属性。

以下是您可以指定的一些序列属性:
数据类型
CREATE SEQUENCE 语句的 AS 子句指定序列的数字数据类型。数据类型确定序列的可能最小值和最大值。SQL 和 XML 限制中列示了数据类型的最小值和最大值。不能更改序列的数据类型;而是必须通过发出 DROP SEQUENCE 语句来删除序列,然后发出带有新数据类型的 CREATE SEQUENCE 语句。
起始值
CREATE SEQUENCE 语句的 START WITH 子句设置序列的初始值。ALTER SEQUENCE 语句的 RESTART WITH 子句将序列值重新设置为指定的值。
最小值
MINVALUE 子句设置序列的最小值。
最大值
MAXVALUE 子句设置序列的最大值。
增量值
INCREMENT BY 子句设置每个 NEXT VALUE 表达式添加至序列当前值的值。要使序列值递减,请指定一个负数值。
序列循环
CYCLE 子句导致达到其最大值或最小值的序列值在随后的 NEXT VALUE 表达式中生成其各自的最小值或最大值。

注: 只有在不需要唯一数字或者可以保证在序列循环后不再使用较旧的序列值时,才应该使用 CYCLE。

例如,如果要创建一个名为 id_values 的序列,其最小值为 0、最大值为 1000、使用每个 NEXT VALUE 表达式使值递增 2,并且在达到最大值时返回到其最小值,那么请发出以下语句:
CREATE SEQUENCE id_values
  START WITH 0
  INCREMENT BY 2
  MAXVALUE 1000
  CYCLE

6. 比较序列与标识列

虽然对于 DB2® 应用程序来说,序列和标识列用途相似,但它们之间存在一个重要差别。标识列使用装入实用程序自动生成单个表中的列值。序列根据请求使用 CREATE SEQUENCE 语句生成可在任何 SQL 语句中使用的顺序值。

标识列
允许数据库管理器自动为添加至表的每一行生成唯一数字值。如果您正在创建一个表并且知道需要唯一标识将添加至该表的每一行,那么可通过 CREATE TABLE 语句向该表添加一个标识列。

CREATE TABLE <table name>
(
    <column name 1>   INT,
    <column name 2>   DOUBLE,
    <column name 3>   INT NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH <value 1>, INCREMENT BY <value 2>)
)

在本示例中,第三列标识标识列。可以定义的其中一个属性是在添加行时用来唯一定义每一行的列中使用的值。INCREMENT BY 子句后面的值显示对于添加至该表的每一行来说标识列内容的后续值的增量。

创建标识属性后,可以使用 ALTER TABLE 语句更改或除去这些属性。还可以使用 ALTER TABLE 语句在其他列中添加标识属性。

序列
允许自动生成值。序列特别适合于生成唯一键值这一任务。应用程序可以使用序列来避免通过其他方法生成唯一计数器所引起的可能的并行性和性能问题。与标识列不同,未使序列与特定表列相关,也未将它绑定至唯一表列,只是仅可通过该表列访问。
可以创建序列并在以后改变它,以便它通过无限递增或递减值来生成值;或者递增或递减至用户定义的限制,然后停止;或者递增或递减至用户定义的限制,然后循环回至起点并重新开始。序列仅在单分区数据库中受支持。

以下示例显示如何创建一个名为 orderseq 的序列:

CREATE SEQUENCE ORDERSEQ
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE
    CACHE 50


在本示例中,序列从 1 开始,并以 1 为增量增加,且没有上限。由于没有指定上限,所以没有理由循环回至起点并从 1 重新开始。CACHE 参数指定了数据库管理器预分配并保存在内存中的序列值的最大数目。

7. 确定何时使用标识列或序列

虽然在标识列和序列之间存在相似之处,但是也存在差别。在设计数据库和应用程序时可以使用其各自的特征。

根据您的数据库设计和使用数据库的应用程序,下列特征将帮助您确定何时使用标识列以及何时使用序列。

标识列特征
        标识列自动为单个表生成值。
        当将标识列定义为 GENERATED ALWAYS 时,始终由数据库管理器生成所用的值。在修改表的内容期间,不允许应用程序来提供它们自己的值。
        在插入行后,通过使用 IDENTITY_VAL_LOCAL() 函数或通过使用 SELECT FROM INSERT 语句从插入中重新选择标识列,可以检索生成的标识值。
        装入实用程序可以生成标识值。
序列特征
        未使序列与任何一个表相关。
        序列生成可在任何 SQL 或 XQuery 语句中使用的顺序值。

由于任何应用程序可以使用序列,所以有两种表达式可用来控制如何检索指定序列中的下一个值和在正在执行的语句之前生成的值。对于当前会话中的先前语句,PREVIOUS VALUE 表达式对指定序列返回最新生成的值。NEXT VALUE 表达式对指定序列返回下一个值。使用这些表达式允许在几个表内的几个 SQL 和 XQuery 语句中使用相同值。

[ 本帖最后由 zhangzongjun 于 2008-11-28 09:31 编辑 ]
求职 : 数据库管理员
论坛徽章:
186
授权会员
日期:2008-07-27 22:25:202014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14版主4段
日期:2015-02-26 02:21:03慢羊羊
日期:2015-03-04 14:51:35
 楼主| 发表于 2008-11-28 09:30 | 显示全部楼层
自制PDF文档,仅供大家参考,
所有实例 均测试通过

[ 本帖最后由 zhangzongjun 于 2008-11-28 09:36 编辑 ]

【俊哥儿张】生成列和标识列 实例详解.pdf

110.8 KB, 下载次数: 51

使用道具 举报

回复
论坛徽章:
3
八级虎吧徽章
日期:2008-12-17 17:53:09CTO参与奖
日期:2009-02-20 09:44:20授权会员
日期:2009-03-06 12:50:31
发表于 2008-11-28 13:09 | 显示全部楼层
学习中,谢谢!

使用道具 举报

回复
论坛徽章:
3
八级虎吧徽章
日期:2009-01-24 11:59:34CTO参与奖
日期:2009-02-20 09:44:20授权会员
日期:2009-03-21 09:17:30
发表于 2009-2-28 11:59 | 显示全部楼层
总结的太好了~
感谢~~!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表