6.1 数据操作语言（DML）语句

严格来说，有5种DML命令：

● SELECT

● INSERT

● UPDATE

● DELETE

● MERGE

实际上，大多数数据库专家从来没有将SELECT作为DML的一部分。人们认为它是一门单独的语言，如果考虑到后面的5章都将专门讨论它，那么这种想法也不是没有道理。通常不讨论MERGE命令，不是因为它不是数据操作命令，而是因为它能做的，其他命令都能做。可以认为MERGE命令是依据某种条件执行INSERT或者UPDATE或者DELETE的快捷方式。通常与DML一起考虑的命令是TRUNCATE。它实际上是一个数据定义语言(Data Definition Language, DDL)命令，但对于最终用户而言，其结果与DELETE相同(虽然其实现方式完全不同)，因此它确实适合DML。

6.1.1 INSERT

在表中，Oracle 以行的形式存储数据。用行填充表(就像人居住在一个国家里一样)有几种方法，最常见的方法是使用INSERT语句。SQL是一种面向集合的语言，因此任何一个命令都可以影响一行或者行集合。一条INSERT语句可将一行插入一个表中，或者将许多行插入许多表中。该语句的基本形式的确只插入一行，但更复杂的变体(只使用一个命令)就可以将多行插入多个表中。

INSERT语句的最简单形式是在表中插入一行，使用联机提供的值作为命令的一部分。其语法如下所示：

        INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

例如：

        insert into hr.regions values (10, 'Great Britain');
        insert into hr.regions (region_name, region_id) values ('Australasia',11);
        insert into hr.regions (region_id) values (12);
        insert into hr.regions values (13, null);

前面的第一个命令为REGIONS表的两列提供值。如果表有三列，语句就会失败，因为它依赖于位置表示法(positional notation)。语句没有说明将哪个值插入哪一列；这取决于值的位置：它们在命令中的顺序。当数据库使用位置表示法接收语句时，它会将值的顺序与定义表的列的顺序相匹配。如果列顺序错误，语句也会失败：数据库会尝试插入，但会因为数据类型不匹配而失败。

第二个命令指定了要填充的列和用来填充这些列的值。注意，引用列的顺序现在变得无关紧要——只要列的顺序与值的顺序相同就行。

第三个示例将列出一列，因此只有一个值。其他所有列都是空值。如果REGION_NAME列不能为空，那么该语句会失败。第四个示例产生相同的结果，但因为没有列列表，所以必须为各列提供某种类型的值——至少是NULL。

要使用一个INSERT命令插入许多行，行的值必须来自查询。其语法如下所示：

        INSERT INTO table [column [, column...] ] subquery;

注意，这里的语法没有使用VALUES关键字。如果省略列列表，那么子查询必须提供表中所有列的值。要将一个表的每一行都复制到另一个表，如果两个表有相同的列结构，那么使用下面这样的命令即可：

        insert into regions_copy select * from regions;

先假设表REGIONS_COPY确实存在。SELECT子查询从源表(这里是REGIONS)中读取每一行，然后INSERT命令将它们插入目标表(即REGIONS_COPY)。

要结束对INSERT命令的描述，还应该提到一点：可使用一条语句将多行插入多个表。这不是OCP考试的内容，但为完整起见，下面给出它的示例：

        insert all
        when 1=1 then
          into emp_no_name (department_id, job_id, salary, commission_pct, hire_date)
          values (department_id, job_id, salary, commission_pct, hire_date)
        when department_id <> 80 then
          into emp_non_sales (employee_id, department_id, salary, hire_date)
          values (employee_id, department_id, salary, hire_date)
        when department_id = 80 then
          into emp_sales (employee_id, salary, commission_pct, hire_date)
          values (employee_id, salary, commission_pct, hire_date)
        select employee_id, department_id, job_id, salary, commission_pct, hire_date
        from employees where hire_date > sysdate - 30;

从底部开始阅读语句。子查询检索最近30天雇用的所有员工。然后到顶部。ALL关键字意味着将选中的所有行都可考虑插入下列所有表中，而不是只插入满足条件的第一个表中。第一个条件是1=1，它总是为true，因此每个源行都会在EMP_NO_ NAME表中创建一行。这是EMPLOYEES表的副本，并且已经删除了个人标识符。第二个条件是DEPARTMENT_ID<>80，它会在EMP_NON_ SALES表中生成一行，表示不在销售部门的所有员工；不要求这个表有COMMISSION_PCT列。第三个条件在EMP_SALES表中生成一行表示所有销售员；不需要有DEPARTMENT_ID列，因为它们都在部门80中。

这是多表插入的简单示例，但它很明显只使用一条语句，因此只有一次通过源数据，它就可能填充多个目标表，这会大大减轻数据库的负担。

练习6-1 使用INSERT命令

在这个练习中，我们将使用各种方法在表中插入行。

(1) 使用SQL Developer或者SQL*Plus，连接到HR模式。

(2) 查询练习5-5创建的PRODUCTS、ORDERS和ORDER_ITEMS表，确认当前存储了哪些数据：

        select * from products;
        select * from orders;
        select * from order_items;

(3) 向PRODUCTS表中插入两行，并依次提供值：

        insert into products values (prod_seq.nextval, '12c SQL Exam Guide',
    'ACTIVE',60, sysdate, 20);
        insert into products
        values (prod_seq.nextval, '12c All-in-One Guide',
        'ACTIVE',100, sysdate, 40);

(4) 向ORDERS表中插入两行，并显式提供列名：

        insert into orders (order_id, order_date, order_status, order_amount,
    customer_id)
        values (order_seq.nextval, sysdate, 'COMPLETE', 3, 2);
        insert into orders (order_id, order_date, order_status, order_amount,
    customer_id)
        values (order_seq.nextval, sysdate, 'PENDING', 5, 3);

(5) 向ORDER_ITEMS表中插入三行，使用替换变量：

        insert into order_items values (&item_id, &order_id, &product_id, &quantity);

当系统提示时，提供值{1, 1, 2,5}、{2,1,1,3}和{1,2,2,4}。

(6) 向PRODUCTS表中插入一行，计算PRODUCT_ID使它比当前的最大值还高100。这需要使用一个标量子查询：

        insert into products values ((select max(product_id)+100 from products),
        '12c DBA2 Exam Guide', 'INACTIVE', 40, sysdate-365, 0);

(7) 确认插入行：

        select * from products;
        select * from orders;
        select * from order_items;

(8) 提交插入：

        commit;

图6-1显示了该练习的结果(使用SQL*Plus)。

图6-1 显示练习的结果

6.1.2 UPDATE

UPDATE命令用来改变已经存在的行——由INSERT命令或者由另一种工具(例如Data Pump)创建的行。和其他SQL命令一样，UPDATE可以影响一行或者行集合。UPDATE影响的集合的范围由WHERE子句确定，就像SELECT语句检索的行集合由WHERE子句定义一样，两者语法相同。所有更新的行都在一个表中；单个更新命令不能影响多个表中的行。

当更新行或者行集合时，UPDATE命令指定更新行的哪些列。不一定(或者不常见)更新行的所有列。如果要更新的列已经有一个值，那么用UPDATE命令指定的新值取代它。如果以前没有填充列，也就是说，它的值是NULL，执行UPDATE命令之后就会用新值填充它。

UPDATE的典型用法是检索一行，更新这一行的一列或者多列。使用WHERE子句完成检索，它按主键(确保只检索一行的唯一标识符)选择一行。那么更新的列就是除了主键列之外的所有列。通常不改变主键的值。行的生命周期从插入它时开始，经过几次更新，直到删除它为止。在整个生命周期中，通常不改变它的主键。

要更新行集合，可以使用比主键约束更少的WHERE子句。要更新表中的所有行，就不要使用任何WHERE子句。当偶然出现这种情况时，该集合行为可能会令人不安。如果使用除了主键之外的任意列来更新行，那么就会更新多行而不是一行。当只想改变一行时，如果完全省略WHERE子句，就会更新整个表，只用一条语句也许就能更新数百万行。

UPDATE命令必须遵循表定义的所有约束，就像原始的INSERT命令一样。例如，不可以将标记为强制列的列更新为NULL值，也不可以更新主键列让它不再唯一。它的基本语法如下所示：

        UPDATE table SET column=value [, column=value...] [WHERE condition];

该命令更复杂的形式可以使用子查询表示一个或者多个列值或者WHERE条件。图6-2显示了从SQL*Plus执行的不同复杂度的更新。

图6-2 使用UPDATE语句的示例

第一个示例最简单，一行的一列被设置为字面值。因为使用WHERE子句选择行，它在主键上使用相等谓词，所以绝对确保最多只有一行会受到影响。如果WHERE子句根本没有找到任何行，那么就不会改变任何行。

第二个示例使用算术和现有列来设置新值，行选择没有在主键列上完成。如果选择没有在主键上完成，或者使用不相等谓词(如BETWEEN)，那么被更新的行数可能不止一个。如果完全省略WHERE子句，更新就会应用于表中的每一行。

图6-2中的第三个示例介绍了如何使用子查询来定义要更新的行集合。稍微复杂的情况是使用替换变量来提示用户输入在子查询的WHERE子句中使用的值。在本示例中，子查询(第3行和第4行)选择的所有员工都在名称包含字符串’IT’的部门，并将他们的当前薪水增加10%(实际上不可能发生)。

也可以使用子查询来确定列要设置的值，如第四个示例所示。在这里，一名员工(由主键确定，在第5行)被转到部门80(销售部门)，然后第3行和第4行中的子查询将其佣金率设置为部门中最低的佣金率。

使用子查询更新的语法如下所示：

        UPDATE table
        SET column=[subquery] [, column=subquery...]
        WHERE column = (subquery) [AND column=subquery...] ;

对于在SET子句中使用更新列的子查询有严格的限制：子查询必须返回标量值。标量值是所需数据类型的单个值：查询必须返回一行，该行只有一列。如果查询返回几个值，UPDATE就会失败。分析下面两个示例：

        update employees
        set salary=(select salary from employees where employee_id=206);
        update employees
        set salary=(select salary from employees where last_name='Abel');

第一个示例在主键上使用相等谓词，它总是成功。即使子查询没有检索行(如果没有员工的EMPLOYEE_ID等于206，就会出现这种情况)，查询还是会返回标量值：空值。在那种情况下，EMPLOYEES表中的所有行都将SALARY设置为NULL——并不希望这样，但对于SQL而言，这不是错误。第二个示例在LAST_NAME上使用相等谓词(不能确保它是唯一的)。如果只有一名员工使用该名称，语句就会成功；如果有多个员工使用该名称，语句就会失败，并出现错误“ORA-01427: single-row subquery returns more than one row”。对于稳定运行的代码而言，不管数据的状态如何，确保用于设置列值的子查询是标量至关重要。

                update employees
                set salary=(select max(salary) from employees where
                last_name='Abel');

如果使用了相等谓词(如上面的示例所示)或者大于/小于谓词，那么WHERE子句中的子查询也必须是标量。如果使用IN谓词，那么查询会返回多行，例如下面使用IN的示例：

        update employees
        set salary=10000
        where department_id in (select department_id from departments
        where department_name like '%IT%');

这会将更新应用于名称中包含字符串’IT’的部门里的所有员工，结果有几行。但即使查询可以返回几行，但它还是必须只返回一列。

练习6-2 使用UPDATE命令

在这个练习中，我们将使用各种方法更新表中的行。假设HR.PRODUCTS表与练习6-1中的图6-1一样。如果不一样，可以对值进行必要的调整。

(1) 使用SQL Developer或者SQL*Plus，连接到HR模式。

(2) 更新由主键确定的一行：

        update products set product_description='DBA1 Exam Guide'
        where product_id=102;

这条语句应该返回消息“1 row updated”。

(3) 使用不相等谓词并提供值，更新一组行：

        update products
        set product_id=(1+(select max(product_id) from products where
                          product_id <> 102))
        where product_id=102;

这条语句返回消息“1 row updated”。

(4) 确认行的状态：

        select * from products;

(5) 提交所做的变更：

        commit;

6.1.3 DELETE

使用DELETE命令可以从表中删除前面插入的行。该命令依据WHERE子句从表中删除一行或者一组行。如果没有WHERE子句，就会删除表中所有行(如果不小心遗漏WHERE子句，就有点麻烦)。

删除是什么都不剩下。不可以选择要删除的列。插入行时，可以选择填充哪些列。更新行时，可以选择更新哪些列。但如果要删除整行——唯一的选择就是选择哪个表中的哪些行。从语法上说，这一点让DELETE命令比其他DML命令更简单。其语法如下所示：

        DELETE [FROM] table [WHERE condition];

这是最简单的DML命令，特别是在省略条件的情况下。这种情况下，会在没有任何提示的情况下删除表中所有行。唯一复杂的是带有条件，这可能是列与字面值的简单匹配：

        delete from employees where employee_id=206;
        delete from employees where last_name like 'S%';
        delete from employees where department_id=&Which_department;
        delete from employees where department_id is null;

第一条语句用主键标识行。只删除一行—— 或者不会删除任何行(如果给定的值没有找到匹配)。第二条语句使用不相等谓词，它会导致删除许多行：姓氏以大写字母“S”开头的所有员工。第三条语句使用相等谓词，但没有在主键上使用。它使用替换变量提示输入部门编号，这个部门的所有员工都会被删除。最后一条语句删除当前没有分配部门的所有员工。

这种条件也可以是子查询：

        delete from employees where department_id in
        (select department_id from departments where location_id in
          (select location_id from locations where country_id in
            (select country_id from countries where region_id in
              (select region_id from regions where region_name='Europe')
            )
          )
        )

本示例使用子查询来表示行选择，它导航HR地理树(使用更多子查询)来删除所有在位于欧洲的部门工作的员工。对子查询返回的值的数量，应用的规则与对UPDATE命令应用的规则相同：如果行选择基于相等谓词(如前面的示例所示)，子查询必须是标量；但如果它使用IN，子查询就可以返回多行。

如果DELETE命令找不到要删除的行，这并不是一个错误。命令会返回消息“0 rows deleted”而不会返回一条错误消息，因为语句其实已经成功完成——它只是什么也没做而已。

练习6-3 使用DELETE命令

在这个练习中，我们将使用各种方法删除表中的行。假设HR.PRODUCTS表已在前两个练习中作了修改。如果没有，可以对值进行必要的调整。

(1) 使用SQL Developer或者SQL*Plus，连接到HR模式。

(2) 在主键上使用相等谓词来删除一行：

        delete from products where product_id=3;

这条语句应该返回消息“1 row deleted”。

(3) 尝试通过省略WHERE子句来删除表中的所有行：

        delete from products;

这条语句会失败，因为它违反约束，ORDER_ITEMS表中有子记录通过外键FK_PRODUCT_ID引用PRODUCTS表中的PRODUCT_ID值。

(4) 提交删除：

        commit;

要从表中删除行，有两个选择：DELETE命令和TRUNCATE命令。DELETE不是那么极端，因为删除可以回滚，而TRUNCATE则不能。DELETE更可控，因为可以选择删除哪些行，而TRUNCATE总是作用于整个表。然而，DELETE更缓慢，可能对数据库造成许多压力。TRUNCATE通常瞬间完成、毫不费力。

6.1.4 TRUNCATE

TRUNCATE命令不是DML命令；它是DDL命令，这差别很大。当DML命令影响数据时，它们插入、更新和删除行作为事务的一部分。本章6.2节将定义事务。现在，假设事务可以被控制，即可以选择是否让在事务中完成的工作变得永久，或者是否反转事务。这非常有用，但需要数据库在后台完成一些用户不知道的其他工作。DDL命令不是用户事务(虽然在数据库内，它们实际上作为事务执行—— 但开发人员无法控制它们)，无法选择是让它们变得永久还是反转它们。执行之后，它们就完成了。然而，与DML相比，它们执行的速度更快。

从用户的角度看，表截断相当于对所有行执行DELETE：没有WHERE子句的DELETE命令。删除需要一些时间(如果表有许多行的话，可能是数小时)，但截断会立即完成。不管表是包含一行还是十亿行，都没有区别；TRUNCATE实际上瞬间完成。表将仍然存在，但会成为空的。

表的物理位置是保存在数据字典中表的定义的一部分。首次创建时，在数据库的数据文件内给表分配了一个固定大小的空间。这就是所谓的区间并且为空。那么当插入行时，就会填充该区间。填满后，就会自动给表分配更多的区间。因此表由一个或多个区间组成，它们保存行。除了跟踪区间分配之外，数据字典还跟踪分配给表的空间已经使用了多少。这通过高水位线(high water mark)来完成。高水位线是已经使用的最后一个区间中的最后一个位置；高水位线以下的所有空间都在某段时间被使用过，高水位线以上的空间还没有被使用。

注意，在高水位线以下可能有大量当时没有使用的空间；这是因为已经用DELETE命令删除了行。在表中插入行会提高高水位线。删除它们则高水位线的位置不变；它们占用的空间仍然被分配给表，但可以自由地插入更多行。

截断表就会重置高水位线。在数据字典内，高水位线记录的位置被移到表的第一个区间的开头。因为Oracle假设没有行在高水位线之上，因此其效果就是从表中删除所有行。清空表，并且一直是空的，直到后续的插入重新抬高高水位线为止。按照这种方法，一个仅在数据字典中进行更新的DDL命令，就可以销毁表中的几十亿行。

截断表的语法非常简单：

        TRUNCATE TABLE table;

6.1.5 MERGE

许多情况下，我们都要将一组数据(源)合并到现有表(目标)中。如果源数据中的行已经存在于目标表中，那么可以更新目标行，或者可以完全取代它，或者保持目标行不变。如果源数据中的行不在目标表中，那么可以插入它。MERGE命令就能这样做。MERGE为试图定位目标表中匹配行的每一行传递源数据。如果没有找到匹配行，就可以插入行；如果发现匹配行，就可以更新匹配行。版本10g的增强意味着在匹配和更新行之后，可以删除目标行。最终结果是源表中的数据被合并到一个目标表中。

MERGE运算能够完成的任务使用INSERT、UPDATE和DELETE语句都能完成，但它传递一次源数据，就能够实现这三种操作。没有MERGE的另一种代码要求传递三次数据，一个命令一次。

MERGE语句的源数据可以是表或者任何子查询。用于查找目标表中匹配行的条件与WHERE子句类似。更新或者插入行的子句像UPDATE或者INSERT命令一样复杂。MERGE是最复杂的DML命令(但不是不可理解)，因为其功能(无可争辩)最强大。MERGE的使用不在OCP课程提纲范围之内，但出于完整性考虑，下面给出一个简单示例：

        merge into employees e using new_employees n
          on (e.employee_id = n.employee_id)
        when matched then
          update set e.salary=n.salary
        when not matched then
          insert (employee_id, last_name, salary)
          values (n.employee_id, n.last_name, n.salary);

上面的语句使用表NEW_EMPLOYEES的内容在EMPLOYEES表中更新或者插入行。情况是EMPLOYEES是所有员工的表，NEW_EMPLOYEES表包含的行表示新员工和现有员工的薪水变化。命令会通过NEW_EMPLOYEES表，对于每一行而言，在EMPLOYEES表中查找具有相同EMPLOYEE_ID的行。如果找到这样的行，就用NEW_EMPLOYEES表中行的值更新它的SALARY列。如果没有这样的行，就插入一行。语法变体允许使用子查询来选择源行，甚至可以删除匹配行。

6.1.6 DML语句失败

命令失败的原因有许多，包括：

● 语法错误

● 引用不存在的对象或者列

● 访问权限

● 约束违背

● 空间问题

图6-3显示了使用SQL*Plus尝试执行语句。

图6-3 语句失败的一些示例

图6-3中，用户作为SUE(口令，SUE——这不是一个好的安全性的例子)连接，并查询EMPLOYEES表。语句会因为简单的语法错误而失败，这个错误由SQL*Plus正确标识。注意，SQL*Plus不会更正这种错误，即使它知道你要输入什么。有些第三方工具可能更有帮助，它们提供了自动错误更正功能。

第二次尝试运行语句失败并产生一个错误，表明对象不存在。这是因为它不存在于当前用户的模式中；它存在于HR模式中。更正后，第三次运行语句成功——但只是刚好而已。传递到WHERE子句的值是字符串’07-JUN-2002'，但表中没有将列HIRE_DATE定义为字符串，而是定义为日期。要执行语句，数据库必须知道用户要做什么，并将字符串强制转换为日期。在最后一个示例中，类型强制转换失败。

如果语句从语法上说是正确的，并且它引用的对象也没有错误，它还有可能因为访问权限而失败。如果执行语句的用户没有获得它引用的表的相应权限，数据库就会返回一个错误，这个错误与当对象不存在时返回的错误一样。对于用户而言，它不存在。

由访问权限造成的错误是这样一种情况，即SELECT和DML语句可能返回不同的结果：用户可能有权查看表中的行，但无权插入、更新或者删除它们。这种安排不是不常见；这通常在商业方面比较有意义。也许更让人不解的是，可以设置权限，插入不允许用户查看的行。也许最糟的是，还可以删除用户看不到也不能更新的行，但是这种情况并不常见。

违背约束会导致DML语句失败。例如，INSERT命令可将多行插入表中，对于每一行，数据库都会检查主键相同的行是否已经存在。随着各行的插入会出现这种情况：可能前几行(或者前几百万行)没有问题，随后语句遇到包含重复值的一行。此时它会返回一个错误，且语句失败，这个失败会触发撤消所有已经成功的插入。这是SQL标准的一部分：语句必须完全成功，或者根本不成功。这种反转就是回滚。本章6.2节将详细讨论回滚机制。

如果语句因为空间问题而失败，那么效果也一样。在数据库占用完空间之前，部分语句会成功。已经成功的这一部分会自动回滚。语句的回滚是一件严重的事情，它需要数据库做大量额外工作，花费的时间通常至少等于执行语句已经花费的时间(有时更长)。