数据的插入、删除和修改

DM_SQL 语言的数据更新语句包括:数据插入、数据修改和数据删除三种语句,其中数据插入和修改两种语句使用的格式要求比较严格。在使用时要求对相应基表的定义,如列的个数、各列的排列顺序、数据类型及关键约束、唯一性约束、引用约束、检查约束的内容均要了解得很清楚,否则就很容易出错。下面将分别对这三种语句进行讨论。在讨论中,如不特别说明,各例均使用示例库 BOOKSHOP,用户均为建表者 SYSDBA。

5.1 数据插入语句

数据插入语句用于向已定义好的表中插入单个或成批的数据。

INSERT 语句有两种形式。一种形式是值插入,即构造一行或者多行,并将它们插入到表中;另一种形式为查询插入,它通过<查询表达式>返回一个查询结果集以构造要插入表的一行或多行。

数据插入语句的语法格式如下:

语法格式

<插入表达式>::=
[@]INSERT  <single_insert_stmt> | <multi_insert_stmt>;

<single_insert_stmt>::=[INTO] <full_tv_name> [<t_alias>] <insert_tail> [<return_into_obj>]
<full_tv_name>::=
    | <单表引用>  [@ <dblink_name>]
    | [<模式名>.]<基表名>  INDEX <索引名>
    | [<模式名>.]<基表名>  PARTITION (<分区名>)
| <子查询表达式>
<单表引用>::=[<模式名>.]<基表或视图名>
<基表或视图名>::=<基表名>|<视图名>
<子查询表达式>::=(<查询表达式>) [[AS] <表别名>]
<t_alias>::=[AS] <表别名>
<insert_tail>::= [(<列名>{,<列名>})]<insert_action>
<insert_action>::= VALUES <ins_value> 
	| <查询表达式>|(<查询表达式>)
    | (<select_clause>)
    | DEFAULT VALUES
    | TABLE <full_tv_name>
<return_into_obj>::=
	 <RETURN|RETURNING><expr{,expr}>INTO <data_item {,data_item }>
	|<RETURN|RETURNING><expr{,expr}>BULK COLLECT INTO <data_item {,data_item}>
<select_clause> 参见第四章

<multi_insert_stmt>::=ALL <multi_insert_into_list> <查询表达式>
		|[ALL|FIRST]<multi_insert_into_condition_list> [<multi_insert_into_else>]<查询表达式>
<multi_insert_into_list>::= <insert_into_single>{<insert_into_single>}
<insert_into_single>::=
	INTO <full_tv_name> [<t_alias>] [(<列名>{,<列名>})][VALUES <ins_value>]
<ins_value>::= 
 (<expr>|DEFAULT {,<expr>|DEFAULT}){,(<expr>|DEFAULT {,<expr>|DEFAULT})}
<multi_insert_into_condition_list> ::=
<insert_into_single_condition>{,< insert_into_single_condition>}
<insert_into_single_condition>::=
 WHEN <bool_exp> THEN <multi_insert_into_list>
<multi_insert_into_else>::= ELSE <multi_insert_into_list>

参数

  1. <模式名> 指明该表或视图所属的模式,缺省为当前模式;
  2. <基表名> 指明被插入数据的基表的名称;
  3. <视图名> 指明被插入数据的视图的名称,实际上 DM 将数据插入到视图引用的基表中;
  4. <列名> 表或视图的列的名称。在插入的记录中,这个列表中的每一列都被 VALUES 子句或查询说明赋一个值。如果在此列表中省略了表的一个列名,则 DM 用先前定义好的缺省值插入到这一列中。如果此列表被省略,则在 VALUES 子句和查询中必须为表中的所有列指定值;
  5. <ins_value> 指明在列表中对应的列的插入的列值,如果列表被省略了,插入的列值按照基表中列的定义顺序排列。所有的插入值和系统内部相关存储信息一起构成了一条记录,一条记录的长度不能大于页面大小的一半;
  6. <查询表达式> 将一个 SELECT 语句所返回的记录插入表或视图的基表中,子查询中选择的列表必须和 INSERT 语句中列名清单中的列具有相同的数量;带有<查询表达式>的插入方式,称查询插入。插入中使用的<查询表达式>也称为查询说明。
  7. @ 当插入的是大数据数据文件时,启用 @。同时对应的 < 插入值 > 格式为:@'path'。比如:@INSERT INTO T1 VALUES(@'e:\DSC_1663.jpg')。@ 用法只能在 DIsql 中使用,客户端工具不支持;
  8. <dblink_name> 表示创建的 dblink 名字,如果添加了该选项,则表示插入远程实例的表。

图例

<插入表达式>

数据插入语句 1

<single_insert_stmt>

数据插入语句 2

<full_tv_name>

数据插入语句 3

<insert_tail>

数据插入语句 4

<return_into_obj>

数据插入语句 5

<multi_insert_stmt>

数据插入语句 6

<insert_into_single>

数据插入语句 7

<ins_value>

数据插入语句 8

<insert_into_single_condition>

数据插入语句 9

使用说明

  1. <基表名>或<视图名>后所跟的<列名>必须是该表中的列,且同一列名不允许出现两次,但排列顺序可以与定义时的顺序不一致;
  2. <ins_value>中插入值的个数、类型和顺序要与<列名>一一对应;
  3. 插入在指定值的时候,可以同时指定多行值,这种叫做多行插入或者批量插入。多行插入不支持列存储表;
  4. 如果某一<列名>未在 INTO 子句后面出现,则新插入的行在这些列上将取空值或缺省值,如该列在基表定义时说明为 NOT NULL 时将会出错;
  5. 如果<基表名>或<视图名>后没指定任何<列名>,则隐含指定该表或视图的所有列,这时,新插入的行必须在每个列上均有<插入值>;
  6. 当使用<子查询表达式>作为 INSERT 的目标时,实际上是对查询表达式的基表进行操作,查询表达式的查询项必须都来源于同一个基表且不能是计算列,查询项所属的基表即是查询表达式的基表,如果查询表达式是带有连接的查询,那么对于连接中视图基表以外的表,连接列上必须是主键或者带有 UNIQUE 约束。不支持 PIVOT/UNPIVOT,不支持 UNION/UNION ALL 查询;
  7. 如果两表之间存在引用和被引用关系时,应先插入被引用表的数据,再插入引用表的数据;
  8. <查询表达式>是指用查询语句得到的一个结果集插入到插入语句中<表名>指定的表中,因此该格式的使用可供一次插入多个行,但插入时要求结果集的列与目标表要插入的列是一一对应的,不然会报错;
  9. 多行插入时,对于存在行触发器的表,每一行都会触发相关的触发器;同样如果目标表具有约束,那么每一行都会进行相应的约束检查,只要有一行不满足约束,所有的值都不能插入成功;
  10. 在嵌入方式下工作时,<ins_value>插入的值可以为主变量;
  11. 如果插入对象是视图,同时在这个视图上建立了 INSTEAD OF 触发器,则会将插入操作转换为触发器所定义的操作;如果没有触发器,则需要判断这个视图是否可更新,如果不可更新则报错,否则是可以插入成功的;
  12. RETURN INTO 返回列支持返回 ROWID;
  13. RETURN INTO 语句中返回结果对象支持变量和数组。如果返回列为记录数组,则返回结果数只能为 1,且记录数组属性类型与个数须与返回列一致;如果为变量,则变量类型与个数与返回列一致;如果返回普通数组,则数组个数和数组元素类型与返回列一致;返回结果不支持变量、普通数组和记录数组混和使用;
  14. 增删改语句当前修改表称为变异表(MUTATE TABLE),其调用函数中,不能对此变异表进行插入操作;
  15. BULK COLLECT 的作用是将检索结果批量地、一次性地赋给集合变量。与每次获取一条数据,并每次都要将结果赋值给变量相比,可以很大程度上的节省开销。使用 BULK COLLECT 时,INTO 后的变量必须是集合类型的。

举例说明

在 VENDOR 表中插入一条供应商信息:账户号为 00,名称为华中科技大学出版社,活动标志为 1,URL 为空,信誉为 2。

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO, NAME, ACTIVEFLAG, WEBURL, CREDIT) VALUES ('00', '华中科技大学出版社', 1, '', 2);

如果需要同时多行插入,则可以用如下的 SQL 语句实现:

INSERT INTO PURCHASING.VENDOR(ACCOUNTNO, NAME, ACTIVEFLAG, WEBURL, CREDIT)
VALUES ('00', '华中科技大学出版社', 1, '', 2), ('00', '清华大学出版社', 1, '',3);

在定义 VENDOR 表时,设定了检查约束:CHECK(CREDIT IN(1,2,3,4,5)),说明 CREDIT 只能是 1,2,3,4,5。在插入新数据或修改供应商的 CREDIT 时,系统按检查约束进行检查,如果不满足条件,系统将会报错,多行插入中,每一行都会做检查。

由于 DM 支持标量子查询,标量子查询允许用在标量值合法的地方,因此在数据插入语句的<插入值>位置允许出现标量子查询。

将书名为长征的图书的出版社插入到 VENDOR 表中。

INSERT  INTO  PURCHASING.VENDOR(ACCOUNTNO, NAME, ACTIVEFLAG, WEBURL, CREDIT)
VALUES('00', (SELECT  PUBLISHER  FROM  PRODUCTION.PRODUCT  WHERE  NAME ='长征'),1, '', 1);

若是需要插入一批数据时,可使用带<查询说明>的插入语句,如下例所示。


构造一个新的基表,表名为 PRODUCT_SELL,用来显示出售的商品名称和购买用户名称,并将查询的数据插入此表中。

CREATE  TABLE  PRODUCTION.PRODUCT_SELL
( PRODUCTNAME  VARCHAR(50)  NOT  NULL,
  CUSTOMERNAME  VARCHAR(50)  NOT  NULL);
INSERT  INTO  PRODUCTION.PRODUCT_SELL
SELECT  DISTINCT  T1.NAME ,  T5.NAME
FROM  PRODUCTION.PRODUCT  T1,  SALES.SALESORDER_DETAIL  T2,
        SALES.SALESORDER_HEADER  T3,  SALES.CUSTOMER  T4,
        PERSON.PERSON  T5
WHERE  T1.PRODUCTID = T2.PRODUCTID  AND  T2.SALESORDERID = T3.SALESORDERID
         AND  T3.CUSTOMERID = T4.CUSTOMERID  AND  T4.PERSONID = T5.PERSONID;

该插入语句将已销售的商品名称和购买该商品的用户名称插入到新建的 PRODUCT_SELL 表中。查询结果如下表 5.1.1 所示。

表5.1.1
PRODUCTNAME CUSTOMERNAME
红楼梦 刘青
老人与海 刘青

值得一提的是,BIT 数据类型值的插入与其他数据类型值的插入略有不同:其他数据类型皆为插入的是什么就是什么,而 BIT 类型取值只能为 1/0,又同时能与整数、精确数值类型、不精确数值类型和字符串类型相容(可以使用这些数据类型对 BIT 类型进行赋值和比较),取值时有一定的规则。

数值类型常量向 BIT 类型插入的规则是:非 0 数值转换为 1,数值 0 转换为 0。例如:

CREATE TABLE T10 (C BIT);
INSERT INTO T10 VALUES(1); --插入1
INSERT INTO T10 VALUES(0); --插入0
INSERT INTO T10 VALUES(1.2); --插入1

字符串类型常量向 BIT 类型插入的规则是:

全部由 0 组成的字符串转换为 0,其他全数字字符串(例如 123),转换为 1。非全数字字符串(例如:1e1, 2a5, 3.14)也转换为 1。

INSERT INTO T10 VALUES(000); --插入0
INSERT INTO T10 VALUES(0); --插入0
INSERT INTO T10 VALUES(10); --插入1
INSERT INTO T10 VALUES(1.0); --插入1

5.2 数据修改语句

数据修改语句用于修改表中已存在的数据。

语法格式

UPDATE <更新列表> {<单列修改子句>|<多列修改子句>}
<更新列表>::= <表引用>{,<表引用>}
<单列修改子句>::= SET<列名>=<<值表达式>|DEFAULT>{,<列名>=<<值表达式>|DEFAULT>}[FROM <表引用>{,<表引用>}][WHERE <条件表达式>][<return_into_obj>];
<多列修改子句>::= SET <列名>{,<列名>}= <subquery>;
<表引用>::= 请参考第4章数据查询语句
<return_into_obj>::=
	 <RETURN|RETURNING><列名>{,<列名>}INTO <结果对象>
	|<RETURN|RETURNING><列名>{,<列名>}BULK COLLECT INTO <结果对象>
<结果对象>::= <数组>|<变量>

参数

1.<列名> 表或视图中被更新列的名称,如果 SET 子句中省略列的名称,列的值保持不变;

2.<值表达式> 指明赋予相应列的新值;

3.<条件表达式>指明限制被更新的行必须符合指定的条件,如果省略此子句,则修改表或视图中所有的行。

图例

数据修改语句 1

表引用:请参考第 4 章 数据查询语句

set_singlecol

数据修改语句 3

set_manycol

数据修改语句 4

RETURN_INTO_OBJ

数据修改语句 5

使用说明

  1. SET 后的<列名>不能重复出现;

  2. WHERE 子句也可以包含子查询。如果省略了 WHERE 子句,则表示要修改所有的元组;

  3. 如果<列名>为被引用列,只有被引用列中未被引用列引用的数据才能被修改;如果<列名>为引用列,引用列的数据被修改后也必须满足引用完整性。在 DM 系统中,以上引用完整性由系统自动检查;

  4. 执行基表的 UPDATE 语句触发任何与之相联系的 UPDATE 触发器;

  5. 对于未指定 ENABLE ROW MOVEMENT 属性水平分区表的更新,如果更新后的值将导致记录所属分区发生修改,则不能进行更新;更新包含大字段的水平分区表时不允许分区更改。

  6. 如果视图的定义查询中含有以下结构则不能更新视图:
    1)联结运算;
    2)集合运算符;
    3)GROUP BY 子句;
    4)集函数;
    5)INTO 子句;
    6)分析函数;
    7)HAVING 字句;
    8)层次查询子句。

  7. 如果更新对象是视图,同时在这个视图上建立了 INSTEAD OF 触发器,则会将更新操作转换为触发器所定义的操作;如果没有触发器,则需要判断这个视图是否可更新,如果不可更新则报错,否则可以继续更新,如果上面的条件都满足,则可以更新成功;

  8. RETURN INTO 不支持返回 ROWID 列;

  9. RETURN INTO 语句中返回列如果是更新列,则返回值为列的新值。返回结果对象支持变量和数组。如果返回列为记录数组,则返回结果数只能为 1.且记录数组属性类型和个数须与返回列一致;如果为变量,则变量类型与个数与返回列一致;如果返回普通数组,则数组个数与数组元素类型与返回列一致;返回结果不支持变量、普通数组和记录数组混和使用;

  10. UPDATE 语句支持一次进行多列修改,多列修改存在以下限制:

    1. 集合操作情况(UNION 等):只有当查询语句为非相关子查询才支持集合操作;
    2. 多列修改不支持 EXPLAIN 操作;
    3. 子查询的结果不能多于 1 行数据。
  11. 如果更新为子查询,则存在以下限制:

    1. 更新子查询对应的最终更新对象目前仅仅必须为基表;
    2. 更新的子查询的查询结果必须保证所更新基表的唯一性特性,类似于更新视图是否可更新概念;
    3. 更新子查询不支持多列更新;
    4. 集合操作、DISTINCT 操作、集函数操作、带有 GROUP BY、CONNECT BY 等语句都不满足视图的更新性要求,报错;
    5. 分区表暂不支持。
  12. 增删改语句当前修改表称为变异表(MUTATE TABLE),其调用函数中,不能对此变异表进行删除操作;

  13. 半透明加密列支持通过 UPDATE 语句进行修改,具体介绍请参考手册《DM8 安全管理》;

  14. 多表联合更新说明:

    1. 更新列表中有多个表时,不支持使用多列修改子句;
    2. 更新列表中有多个表时,不允许指定 FROM 项;
    3. 若有多个 SET 项,则各个 SET 项的左表达式必须为同一个表对象(同一个表的不同别名认为是不同对象)的列;
    4. 要求用户对于更新列表的所有对象具有查询权限,对最终修改的目标对象具有修改权限;
    5. 多表联合更新最多支持 100 个表。

举例说明

将出版社为中华书局的图书的现在销售价格增加 1 元。

UPDATE PRODUCTION.PRODUCT SET NOWPRICE = NOWPRICE + 1.0000 WHERE PUBLISHER = '中华书局';

由于标量子查询允许用在标量值合法的地方,因此在数据修改语句的<值表达式>位置也允许出现标量子查询。下例将折扣高于 7.0 且出版社不是中华书局的图书的折扣设成出版社为中华书局的图书的平均折扣。

UPDATE  PRODUCTION.PRODUCT  SET  DISCOUNT =
( SELECT  AVG(DISCOUNT) 
    FROM  PRODUCTION.PRODUCT  
WHERE PUBLISHER = '中华书局')
WHERE  DISCOUNT > 7.0  AND  PUBLISHER != '中华书局';

注:自增列的修改例外,它一经插入,只要该列存储于数据库中,其值为该列的标识,不允许修改。关于自增列修改的具体情况,请参见 5.6 节——自增列的使用。

带 RETURN INTO 的更新语句。

CREATE TABLE T1(C1 INT,C2 INT,C3 INT);
DECLARE
  TYPE RRR IS RECORD(X INT, Y INT);
  TYPE CCC IS ARRAY RRR[];
  A INT;
  C CCC;
BEGIN
  C = NEW RRR[2];
  UPDATE T1 SET C2=4 WHERE C3 = 2 RETURN C1 INTO A;
  PRINT A;
  UPDATE T1 SET C2=5 WHERE C3 = 2 RETURN C1,C2 INTO C;
  SELECT * FROM ARRAY C;
END;

使用一次进行多列修改的更新语句。

UPDATE PURCHASING.PURCHASEORDER_HEADER SET(TAX,FREIGHT)=(select ORIGINALPRICE,
NOWPRICE from PRODUCTION.PRODUCT where NAME='长征');

5.3 数据删除语句

数据删除语句用于删除表中已存在的数据。

语法格式

DELETE  [FROM]  <表引用> 
[WHERE <条件表达式>][RETURN <列名>{,<列名>} INTO <结果对象>,{<结果对象>}];
<表引用>::= [<模式名>.]{<基表或视图名> |<子查询表达式>}
<基表或视图名>::= <基表名>|<视图名>
<子查询表达式>::=(<查询表达式>) [[AS] <表别名> [<新生列>]]
<结果对象>::=<数组>|<变量>

参数

1.<模式名> 指明该表或视图所属的模式,缺省为当前模式;

2.<基表名> 指明被删除数据的基表的名称;

3.<视图名> 指明被删除数据的视图的名称,实际上 DM 将从视图的基表中删除数据;

4.<条件表达式> 指明基表或视图的基表中被删除的记录须满足的条件。

图例

数据删除语句

数据删除语句 1

table_ref

数据删除语句 2

使用说明

  1. 如果不带 WHERE 子句,表示删除表中全部元组,但表的定义仍在字典中。因此,DELETE 语句删除的是表中的数据,并未删除表结构;

  2. 由于 DELETE 语句一次只能对一个表进行删除,因此当两个表存在引用与被引用关系时,要先删除引用表里的记录,只有引用表中无记录时,才能删被引用表中的记录,否则系统会报错;

  3. 执行与表相关的 DELETE 语句将触发所有定义在表上的 DELETE 触发器;

  4. 如果视图的定义查询中包含以下结构之一,就不能从视图中删除记录:

    1. 联结运算;
    2. 集合运算符;
    3. GROUP BY 子句;
    4. 集函数;
    5. INTO 语句;
    6. 分析函数;
    7. HAVING 语句;
    8. CONNECT BY 语句。
  5. 当<子查询表达式>作为 DELETE 的目标时,实际上是对查询表达式的基表进行操作,查询表达式的查询项必须都来源于同一个基表且不能是计算列,查询项所属的基表即是查询表达式的基表,如果查询表达式是带有连接的查询,那么对于连接中视图基表以外的表,连接列上必须是主键或者带有 UNIQUE 约束。不支持 PIVOT/UNPIVOT,不支持 UNION/UNION ALL 查询;

  6. RETURN INTO 不支持返回 ROWID 列;

  7. RETURN INTO 返回结果对象支持变量和数组。如果返回列为记录数组,则返回结果数只能为 1.且记录数组属性类型和个数须与返回列一致;如果为变量,则变量类型与个数与返回列一致;如果返回普通数组,则数组个数与数组元素类型与返回列一致;返回结果不支持变量、普通数组和记录数组混和使用;

  8. 增删改语句当前修改表称为变异表(MUTATE TABLE),其调用函数中,不能对此变异表进行删除操作。

将没有分配部门的员工的住址信息删除。

DELETE  FROM  RESOURCES.EMPLOYEE_ADDRESS
WHERE  EMPLOYEEID  IN
( SELECT  EMPLOYEEID  
FROM  RESOURCES.EMPLOYEE
WHERE  EMPLOYEEID  NOT  IN
( SELECT  EMPLOYEEID  FROM  RESOURCES.EMPLOYEE_DEPARTMENT));

5.4 MERGE INTO 语句

使用 MERGE INTO 语法可合并 UPDATE 和 INSERT 语句。通过 MERGE 语句,根据一张表(或视图)的连接条件对另外一张表(或视图)进行查询,连接条件匹配上的进行 UPDATE(可能含有 DELETE),无法匹配的执行 INSERT。其中,数据表包括:普通表、分区表、加密表、压缩表和堆表。

语法格式

MERGE INTO <merge_into_obj> [<表别名>] USING <表引用> ON (<条件判断表达式>)
<[<merge_update_clause>] [<merge_insert_clause>]>
<merge_into_obj> ::= <单表引用> | <子查询>
<单表引用> ::= [<模式名>.]<基表或视图名>
<子查询> ::= (<查询表达式>)
<merge_update_clause>::=WHEN MATCHED THEN UPDATE SET <set_value_list> <where_clause_null> [DELETE <where_clause_null>]
<merge_insert_clause>::=WHEN NOT MATCHED THEN INSERT [<full_column_list>] VALUES <ins_value_list> <where_clause_null>;
<表引用>::=<普通表> | <连接表>  详见《DM8_SQL语言使用手册》第四章 数据查询语句
<set_value_list> ::= <列名>=<值表达式| DEFAULT> {,<列名>=<值表达式| DEFAULT>}
<where_clause_null> ::= [WHERE <条件表达式>]
<full_column_list>::= (<列名>{,<列名>})
<ins_value_list>::= (<插入值>{,<插入值>})

参数

1.<模式名> 指明该表或视图所属的模式,缺省为当前用户的缺省模式;

2.<基表名> 指明被修改数据的基表的名称;

3.<视图名> 指明被修改数据的视图的名称,实际上 DM 对视图的基表更新数据;

4.< 查询表达式 > 指明被修改数据的子查询表达式,不支持带有计算列、连接、pIVOT、UNPIVOT/UNION/UNION ALL 的查询,DM 实际是对子查询的基表进行数据更新;

5.<条件表达式> 指明限制被操作执行的行必须符合指定的条件,如果省略此子句,则对表或视图中所有的行进行操作。

图例

MERGE INTO 语句

MERGE INTO 语句 1

merge_update_clause

MERGE INTO 语句 2

merge_insert_clause

MERGE INTO 语句 3

使用说明

  1. INTO 后为目标表,表示待更新、插入的表、可更新视图及可更新查询表达式;

  2. USING 后为源表(普通表或可更新视图),表示用于和目标表匹配、更新或插入的数据源;

  3. ON(<条件判断表达式>)表示目标表和源表的连接条件,如果目标表有匹配连接条件的记录则执行更新该记录,如果没有匹配到则执行插入源表数据;

  4. MERGE_UPDATE_CLAUSE:当目标表和源表的 JOIN 条件为 TRUE 时,执行该语句;

    1. 如果更新执行,更新语句会触发所有目标表上的 UPDATE 触发器,也会进行约束检查;
    2. 可以指定更新条件,如果不符合条件就不会执行更新操作。更新条件既可以和源表相关,也可以和目标表相关,或者都相关;
    3. DELETE 子句只删除目标表和源表的 JOIN 条件为 TRUE,并且是更新后的符合删除条件的记录,DELETE 子句不影响 INSERT 项插入的行。删除条件作用在更新后的记录上,既可以和源表相关,也可以和目标表相关,或者都相关。如果 JOIN 条件为 TRUE,但是不符合更新条件,并没有更新数据,那么 DELETE 将不会删除任何数据。当执行了删除操作,会触发目标表上的 DELETE 触发器,也会进行约束检查。
  5. MERGE_INSERT_CLAUSE:当目标表和源表的 JOIN 条件为 FALSE 时,执行该语句。同时会触发目标表上的 INSERT 触发器,也会进行约束检查。可指定插入条件,插入条件只能在源表上设置;

  6. MERGE_UPDATE_CLAUSE 和 MERGE_INSERT_CLAUSE 既可以同时指定,也可以只出现其中任何一个;

  7. 需要有对源表的 SELECT 权限,对目标表的 UPDATE/INSERT 权限,如果 UPDATE 子句有 DELETE,还需要有 DELETE 权限;

  8. UPDATE 子句不能更新在 ON 连接条件中出现的列;

  9. 如果匹配到,源表中的匹配行必须唯一,否则报错;

  10. <ins_value_list > 不能包含目标表列;

  11. 插入的 WHERE 条件只能包含源表列。

举例说明

下面的例子把 T1 表中 C1 值为 2 的记录行中的 C2 列,更新为表 T2 中 C3 值为 2 的记录中 C4 列的值,同时把 T2 中 C3 列为 4 的记录行插入到 T1 中。

DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1 (C1 INT,  C2 VARCHAR(20));
CREATE TABLE T2 (C3 INT,  C4 VARCHAR(20));
INSERT INTO T1 VALUES(1,'T1_1');
INSERT INTO T1 VALUES(2,'T1_2');
INSERT INTO T1 VALUES(3,'T1_3');
INSERT INTO T2 VALUES(2,'T2_2');
INSERT INTO T2 VALUES(4,'T2_4');
COMMIT;

MERGE INTO T1 USING T2 ON (T1.C1=T2.C3) 
WHEN MATCHED THEN UPDATE SET T1.C2=T2.C4 
WHEN NOT MATCHED THEN INSERT (C1,C2) VALUES(T2.C3, T2.C4);

下面的例子把 T1 表中 C1 值为 2,4 的记录行中的 C2 列更新为表 T2 中 C3 值为 2,4 的记录中 C4 列的值,同时把 T2 中 C3 列为 5 的记录行插入到了 T1 中。由于 UPDATE 带了 DELETE 子句,且 T1 中 C1 列值为 2 和 4 的记录行被更新过,而 C1 为 4 的行符合删除条件,最终该行会被删除掉。

DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1 (C1 INT,  C2 VARCHAR(20));
CREATE TABLE T2 (C3 INT,  C4 VARCHAR(20));
INSERT INTO T1 VALUES(1,'T1_1');
INSERT INTO T1 VALUES(2,'T1_2');
INSERT INTO T1 VALUES(3,'T1_3');
INSERT INTO T1 VALUES(4,'T1_4');
INSERT INTO T2 VALUES(2,'T2_2');
INSERT INTO T2 VALUES(4,'T2_4');
INSERT INTO T2 VALUES(5,'T2_5');
COMMIT;

MERGE INTO T1 USING T2 ON (T1.C1=T2.C3) 
WHEN MATCHED THEN UPDATE SET T1.C2=T2.C4 WHERE T1.C1 >= 2 DELETE WHERE T1.C1=4
WHEN NOT MATCHED THEN INSERT (C1,C2) VALUES(T2.C3, T2.C4);

5.5 伪列的使用

除了 4.12.2 层次查询相关伪列4.14 ROWNUM 中介绍的伪列外,DM 中还提供包括 ROWID、UID、USER、TRXID 等伪列。

5.5.1 ROWID

伪列从语法上和表中的列很相似,查询时能够返回一个值,但实际上在表中并不存在。用户可以对伪列进行查询,但不能插入、更新和删除它们的值。DM 支持的伪列有:ROWID,USER,UID,TRXID、ROWNUM 等。

DM 中行标识符 ROWID 用来标识数据库基表中每一条记录的唯一键值,标识了数据记录的确切的存储位置。如果用户在选择数据的同时从基表中选取 ROWID,在后续的更新语句中,就可以使用 ROWID 来提高性能。如果在查询时加上 FOR UPDATE 语句,该数据行就会被锁住,以防其他用户修改数据,保证查询和更新之间的一致性。例如:

SELECT  ROWID, VENDORID, NAME, CREDIT  FROM  PURCHASING.VENDOR WHERE  NAME = '广州出版社';
--假设查询的ROWID=CF06000000 
UPDATE  PURCHASING.VENDOR  SET  CREDIT  = 2 WHERE  ROWID = 0XCF06000000;

5.5.2 UID 和 USER

伪列 USER 和 UID 分别用来表示当前用户的用户名和用户标识。

5.5.3 TRXID

伪列 TRXID 用来表示当前事务的事务标识。

5.5.4 SESSID

伪列 SESSID 用来表示当前会话的 ID 标识。

5.5.5 PHYROWID

伪列 PHYROWID 用来表示当前记录的物理存储信息。

PHYROWID 值由聚集 B 树或二级 B 树中物理记录的文件号、页号、页内槽号组成,能体现聚集 B 树或二级 B 树的存储信息,聚集 B 树记录的最高位为 1。

当查询语句中实际使用 CSCN、CSEK、BLKUP 操作符时,PHYROWID 内容是聚集 B 树中记录的物理存储地址;当查询语句中实际仅使用 SSEK、SSCN 操作符时,PHYROWID 内容是二级 B 树中记录的物理存储地址。

5.6 DM 自增列的使用

5.6.1 DM 自增列定义

1.自增列功能定义

在表中创建一个自增列。该属性与 CREATE TABLE 语句一起使用,一个表只能有一个自增列。

语法格式

IDENTITY [ (种子, 增量) ]

参数

1.种子 装载到表中的第一个行所使用的值;

2.增量 增量值,该值被添加到前一个已装载的行的标识值上。增量值可以为正数或负数,但不能为 0。

使用说明

1.IDENTITY 适用于 INT(-2147483648~+2147483647)、BIGINT(-263~+263-2)类型的列。每个表只能创建一个自增列;

2.不能对自增列使用 DEFAULT 约束;

3.必须同时指定种子和增量值,或者二者都不指定。如果二者都未指定,则取默认值(1,1)。若种子或增量为小数类型,报错;

4.最大值和最小值为该列的数据类型的边界;

5.建表种子和增量大于最大值或者种子和增量小于最小值时报错;

6.自增列一旦生成,无法更新,不允许用 UPDATE 语句进行修改;

7.临时表、列存储表、水平分区表不支持使用自增列。

2.自增列查询函数

  1. IDENT_SEED(函数)

语法格式

IDENT_SEED ('tablename')

功能:返回种子值,该值是在带有自增列的表中创建自增列时指定的。

参数:tablename 是带有引号的字符串常量,也可以是变量、函数或列名。tablename 的数据类型为 CHAR 或 VARCHAR。其含义是表名,可带模式名前缀。

返回类型:返回数据类型为 INT/NULL

  1. IDENT_INCR(函数)

语法格式

IDENT_INCR ('tablename')

功能:返回增量值,该值是在带有自增列的表中创建自增列时指定的。

参数:tablename 是带有引号的字符串常量,也可以是变量、函数或列名。tablename 的数据类型为 CHAR 或 VARCHAR。其含义是表名,可带模式名前缀。

返回类型:返回数据类型为 INT/NULL

用自增列查询函数获得表 PERSON_TYPE 的自增列的种子和增量信息。

SELECT  IDENT_SEED('PERSON.PERSON_TYPE');
查询结果为:1
SELECT IDENT_INCR('PERSON.PERSON_TYPE');
查询结果为:1

5.6.2 SET IDENTITY_INSERT 属性

设置是否允许将显式值插入表的自增列中。ON 是,OFF 否。

语法格式

SET IDENTITY_INSERT [<模式名>.]<表名> ON WITH REPLACE NULL; 
SET IDENTITY_INSERT [<模式名>.]<表名> OFF; 

参数

1.<模式名> 指明表所属的模式,缺省为当前模式;

2.<表名> 指明含有自增列的表名。

图例

SET IDENTITY_INSERT

SET IDENTITY_INSERT

使用说明

1.IDENTITY_INSERT 属性的默认值为 OFF。SET IDENTITY_INSERT 的设置是在执行或运行时进行的。当一个连接结束,IDENTITY_INSERT 属性将被自动还原为 OFF;

2.DM 要求一个会话连接中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON,当设置一个新的表 IDENTITY_INSERT 属性设置为 ON 时,之前已经设置为 ON 的表会自动还原为 OFF。当一个表的 IDENTITY_INSERT 属性被设置为 ON 时,该表中的自动增量列的值由用户指定。如果插入值大于表的当前标识值(自增列当前值),则 DM 自动将新插入值作为当前标识值使用,即改变该表的自增列当前值;否则,将不影响该自增列当前值;

3.当设置一个表的 IDENTITY_INSERT 属性为 OFF 时,新插入行中自增列的当前值由系统自动生成,用户将无法指定;

4.自增列一经插入,无法修改;

5.手动插入自增列,除了将 IDENTITY_INSERT 设置为 ON,还要求在插入列表中明确指定待插入的自增列列名。插入方式与非 IDENTITY 表是完全一样的。如果插入时,既不指定自增列名也不给自增列赋值,则新插入行中自增列的当前值由系统自动生成;

6.WITH REPLACE NULL 此模式下允许显式插入 NULL 值,同时,系统自动将 NULL 值替换为自增值。

举例说明

SET IDENTITY_INSERT 的使用。

  1. PERSON_TYPE 表中的 PERSON_TYPEID 列是自增列,目前拥有的数据如表 5.6.1 所示。
表5.6.1
PERSON_TYPEID NAME
1 采购经理
2 采购代表
3 销售经理
4 销售代表
  1. 在该表中插入数据,自增列的值由系统自动生成。
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('销售总监');
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('人力资源部经理');

插入结果如表 5.6.2 所示:

表5.6.2
PERSON_TYPEID NAME
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 销售总监
6 人力资源部经理
  1. 当插入数据并且要指定自增列的值时,必须要通过语句将 IDENTITY_INSERT 设置为 ON 时,插入语句中必须指定 PERSON_TYPEID 中要插入的列。例如:
SET IDENTITY_INSERT PERSON.PERSON_TYPE ON;
INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES( 8, '广告部经理');
INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES( 9, '财务部经理');

插入结果如表 5.6.3 所示:

表5.6.3
PERSON_TYPEID NAME
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 销售总监
6 人力资源部经理
8 广告部经理
9 财务部经理
  1. 不允许用户修改自增列的值。
UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 9 WHERE NAME = '广告部经理';

修改失败。对于自增列,不允许 UPDATE 操作。

  1. 还原 IDENTITY_INSERT 属性。
SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;
  1. 插入后再次查询。注意观察自增列当前值的变化。
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('市场总监');
表5.6.4
PERSON_TYPEID NAME
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 销售总监
6 人力资源部经理
8 广告部经理
9 财务部经理
10 市场总监

7)使用 WITH REPLACE NULL 模式,显式插入 NULL 值。同时,系统自动将 NULL 值替换为自增值。

SET IDENTITY_INSERT PERSON.PERSON_TYPE ON WITH REPLACE NULL;

INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES(NULL, '总经理');
表5.6.5
PERSON_TYPEID NAME
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 销售总监
6 人力资源部经理
8 广告部经理
9 财务部经理
10 市场总监
11 总经理
微信扫码
分享文档
扫一扫
联系客服