6.1 數(shù)據(jù)操作語言（DML）語句

嚴格來說，有5種DML命令：

● SELECT

● INSERT

● UPDATE

● DELETE

● MERGE

實際上，大多數(shù)數(shù)據(jù)庫專家從來沒有將SELECT作為DML的一部分。人們認為它是一門單獨的語言，如果考慮到后面的5章都將專門討論它，那么這種想法也不是沒有道理。通常不討論MERGE命令，不是因為它不是數(shù)據(jù)操作命令，而是因為它能做的，其他命令都能做。可以認為MERGE命令是依據(jù)某種條件執(zhí)行INSERT或者UPDATE或者DELETE的快捷方式。通常與DML一起考慮的命令是TRUNCATE。它實際上是一個數(shù)據(jù)定義語言(Data Definition Language, DDL)命令，但對于最終用戶而言，其結果與DELETE相同(雖然其實現(xiàn)方式完全不同)，因此它確實適合DML。

6.1.1 INSERT

在表中，Oracle 以行的形式存儲數(shù)據(jù)。用行填充表(就像人居住在一個國家里一樣)有幾種方法，最常見的方法是使用INSERT語句。SQL是一種面向集合的語言，因此任何一個命令都可以影響一行或者行集合。一條INSERT語句可將一行插入一個表中，或者將許多行插入許多表中。該語句的基本形式的確只插入一行，但更復雜的變體(只使用一個命令)就可以將多行插入多個表中。

INSERT語句的最簡單形式是在表中插入一行，使用聯(lián)機提供的值作為命令的一部分。其語法如下所示：

        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)。語句沒有說明將哪個值插入哪一列；這取決于值的位置：它們在命令中的順序。當數(shù)據(jù)庫使用位置表示法接收語句時，它會將值的順序與定義表的列的順序相匹配。如果列順序錯誤，語句也會失敗：數(shù)據(jù)庫會嘗試插入，但會因為數(shù)據(jù)類型不匹配而失敗。

第二個命令指定了要填充的列和用來填充這些列的值。注意，引用列的順序現(xiàn)在變得無關緊要——只要列的順序與值的順序相同就行。

第三個示例將列出一列，因此只有一個值。其他所有列都是空值。如果REGION_NAME列不能為空，那么該語句會失敗。第四個示例產(chǎn)生相同的結果，但因為沒有列列表，所以必須為各列提供某種類型的值——至少是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考試的內(nèi)容，但為完整起見，下面給出它的示例：

        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表中創(chuàng)建一行。這是EMPLOYEES表的副本，并且已經(jīng)刪除了個人標識符。第二個條件是DEPARTMENT_ID<>80，它會在EMP_NON_ SALES表中生成一行，表示不在銷售部門的所有員工；不要求這個表有COMMISSION_PCT列。第三個條件在EMP_SALES表中生成一行表示所有銷售員；不需要有DEPARTMENT_ID列，因為它們都在部門80中。

這是多表插入的簡單示例，但它很明顯只使用一條語句，因此只有一次通過源數(shù)據(jù)，它就可能填充多個目標表，這會大大減輕數(shù)據(jù)庫的負擔。

練習6-1 使用INSERT命令

在這個練習中，我們將使用各種方法在表中插入行。

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

(2) 查詢練習5-5創(chuàng)建的PRODUCTS、ORDERS和ORDER_ITEMS表，確認當前存儲了哪些數(shù)據(jù)：

        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);

當系統(tǒng)提示時，提供值{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命令用來改變已經(jīng)存在的行——由INSERT命令或者由另一種工具(例如Data Pump)創(chuàng)建的行。和其他SQL命令一樣，UPDATE可以影響一行或者行集合。UPDATE影響的集合的范圍由WHERE子句確定，就像SELECT語句檢索的行集合由WHERE子句定義一樣，兩者語法相同。所有更新的行都在一個表中；單個更新命令不能影響多個表中的行。

當更新行或者行集合時，UPDATE命令指定更新行的哪些列。不一定(或者不常見)更新行的所有列。如果要更新的列已經(jīng)有一個值，那么用UPDATE命令指定的新值取代它。如果以前沒有填充列，也就是說，它的值是NULL，執(zhí)行UPDATE命令之后就會用新值填充它。

UPDATE的典型用法是檢索一行，更新這一行的一列或者多列。使用WHERE子句完成檢索，它按主鍵(確保只檢索一行的唯一標識符)選擇一行。那么更新的列就是除了主鍵列之外的所有列。通常不改變主鍵的值。行的生命周期從插入它時開始，經(jīng)過幾次更新，直到刪除它為止。在整個生命周期中，通常不改變它的主鍵。

要更新行集合，可以使用比主鍵約束更少的WHERE子句。要更新表中的所有行，就不要使用任何WHERE子句。當偶然出現(xiàn)這種情況時，該集合行為可能會令人不安。如果使用除了主鍵之外的任意列來更新行，那么就會更新多行而不是一行。當只想改變一行時，如果完全省略WHERE子句，就會更新整個表，只用一條語句也許就能更新數(shù)百萬行。

UPDATE命令必須遵循表定義的所有約束，就像原始的INSERT命令一樣。例如，不可以將標記為強制列的列更新為NULL值，也不可以更新主鍵列讓它不再唯一。它的基本語法如下所示：

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

該命令更復雜的形式可以使用子查詢表示一個或者多個列值或者WHERE條件。圖6-2顯示了從SQL*Plus執(zhí)行的不同復雜度的更新。

圖6-2 使用UPDATE語句的示例

第一個示例最簡單，一行的一列被設置為字面值。因為使用WHERE子句選擇行，它在主鍵上使用相等謂詞，所以絕對確保最多只有一行會受到影響。如果WHERE子句根本沒有找到任何行，那么就不會改變?nèi)魏涡小?/p>

第二個示例使用算術和現(xiàn)有列來設置新值，行選擇沒有在主鍵列上完成。如果選擇沒有在主鍵上完成，或者使用不相等謂詞(如BETWEEN)，那么被更新的行數(shù)可能不止一個。如果完全省略WHERE子句，更新就會應用于表中的每一行。

圖6-2中的第三個示例介紹了如何使用子查詢來定義要更新的行集合。稍微復雜的情況是使用替換變量來提示用戶輸入在子查詢的WHERE子句中使用的值。在本示例中，子查詢(第3行和第4行)選擇的所有員工都在名稱包含字符串’IT’的部門，并將他們的當前薪水增加10%(實際上不可能發(fā)生)。

也可以使用子查詢來確定列要設置的值，如第四個示例所示。在這里，一名員工(由主鍵確定，在第5行)被轉(zhuǎn)到部門80(銷售部門)，然后第3行和第4行中的子查詢將其傭金率設置為部門中最低的傭金率。

使用子查詢更新的語法如下所示：

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

對于在SET子句中使用更新列的子查詢有嚴格的限制：子查詢必須返回標量值。標量值是所需數(shù)據(jù)類型的單個值：查詢必須返回一行，該行只有一列。如果查詢返回幾個值，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，就會出現(xiàn)這種情況)，查詢還是會返回標量值：空值。在那種情況下，EMPLOYEES表中的所有行都將SALARY設置為NULL——并不希望這樣，但對于SQL而言，這不是錯誤。第二個示例在LAST_NAME上使用相等謂詞(不能確保它是唯一的)。如果只有一名員工使用該名稱，語句就會成功；如果有多個員工使用該名稱，語句就會失敗，并出現(xiàn)錯誤“ORA-01427: single-row subquery returns more than one row”。對于穩(wěn)定運行的代碼而言，不管數(shù)據(jù)的狀態(tài)如何，確保用于設置列值的子查詢是標量至關重要。

                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一樣。如果不一樣，可以對值進行必要的調(diào)整。

(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) 確認行的狀態(tài)：

        select * from products;

(5) 提交所做的變更：

        commit;

6.1.3 DELETE

使用DELETE命令可以從表中刪除前面插入的行。該命令依據(jù)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地理樹(使用更多子查詢)來刪除所有在位于歐洲的部門工作的員工。對子查詢返回的值的數(shù)量，應用的規(guī)則與對UPDATE命令應用的規(guī)則相同：如果行選擇基于相等謂詞(如前面的示例所示)，子查詢必須是標量；但如果它使用IN，子查詢就可以返回多行。

如果DELETE命令找不到要刪除的行，這并不是一個錯誤。命令會返回消息“0 rows deleted”而不會返回一條錯誤消息，因為語句其實已經(jīng)成功完成——它只是什么也沒做而已。

練習6-3 使用DELETE命令

在這個練習中，我們將使用各種方法刪除表中的行。假設HR.PRODUCTS表已在前兩個練習中作了修改。如果沒有，可以對值進行必要的調(diào)整。

(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更緩慢，可能對數(shù)據(jù)庫造成許多壓力。TRUNCATE通常瞬間完成、毫不費力。

6.1.4 TRUNCATE

TRUNCATE命令不是DML命令；它是DDL命令，這差別很大。當DML命令影響數(shù)據(jù)時，它們插入、更新和刪除行作為事務的一部分。本章6.2節(jié)將定義事務。現(xiàn)在，假設事務可以被控制，即可以選擇是否讓在事務中完成的工作變得永久，或者是否反轉(zhuǎn)事務。這非常有用，但需要數(shù)據(jù)庫在后臺完成一些用戶不知道的其他工作。DDL命令不是用戶事務(雖然在數(shù)據(jù)庫內(nèi)，它們實際上作為事務執(zhí)行—— 但開發(fā)人員無法控制它們)，無法選擇是讓它們變得永久還是反轉(zhuǎn)它們。執(zhí)行之后，它們就完成了。然而，與DML相比，它們執(zhí)行的速度更快。

從用戶的角度看，表截斷相當于對所有行執(zhí)行DELETE：沒有WHERE子句的DELETE命令。刪除需要一些時間(如果表有許多行的話，可能是數(shù)小時)，但截斷會立即完成。不管表是包含一行還是十億行，都沒有區(qū)別；TRUNCATE實際上瞬間完成。表將仍然存在，但會成為空的。

表的物理位置是保存在數(shù)據(jù)字典中表的定義的一部分。首次創(chuàng)建時，在數(shù)據(jù)庫的數(shù)據(jù)文件內(nèi)給表分配了一個固定大小的空間。這就是所謂的區(qū)間并且為空。那么當插入行時，就會填充該區(qū)間。填滿后，就會自動給表分配更多的區(qū)間。因此表由一個或多個區(qū)間組成，它們保存行。除了跟蹤區(qū)間分配之外，數(shù)據(jù)字典還跟蹤分配給表的空間已經(jīng)使用了多少。這通過高水位線(high water mark)來完成。高水位線是已經(jīng)使用的最后一個區(qū)間中的最后一個位置；高水位線以下的所有空間都在某段時間被使用過，高水位線以上的空間還沒有被使用。

注意，在高水位線以下可能有大量當時沒有使用的空間；這是因為已經(jīng)用DELETE命令刪除了行。在表中插入行會提高高水位線。刪除它們則高水位線的位置不變；它們占用的空間仍然被分配給表，但可以自由地插入更多行。

截斷表就會重置高水位線。在數(shù)據(jù)字典內(nèi)，高水位線記錄的位置被移到表的第一個區(qū)間的開頭。因為Oracle假設沒有行在高水位線之上，因此其效果就是從表中刪除所有行。清空表，并且一直是空的，直到后續(xù)的插入重新抬高高水位線為止。按照這種方法，一個僅在數(shù)據(jù)字典中進行更新的DDL命令，就可以銷毀表中的幾十億行。

截斷表的語法非常簡單：

        TRUNCATE TABLE table;

6.1.5 MERGE

許多情況下，我們都要將一組數(shù)據(jù)(源)合并到現(xiàn)有表(目標)中。如果源數(shù)據(jù)中的行已經(jīng)存在于目標表中，那么可以更新目標行，或者可以完全取代它，或者保持目標行不變。如果源數(shù)據(jù)中的行不在目標表中，那么可以插入它。MERGE命令就能這樣做。MERGE為試圖定位目標表中匹配行的每一行傳遞源數(shù)據(jù)。如果沒有找到匹配行，就可以插入行；如果發(fā)現(xiàn)匹配行，就可以更新匹配行。版本10g的增強意味著在匹配和更新行之后，可以刪除目標行。最終結果是源表中的數(shù)據(jù)被合并到一個目標表中。

MERGE運算能夠完成的任務使用INSERT、UPDATE和DELETE語句都能完成，但它傳遞一次源數(shù)據(jù)，就能夠?qū)崿F(xiàn)這三種操作。沒有MERGE的另一種代碼要求傳遞三次數(shù)據(jù)，一個命令一次。

MERGE語句的源數(shù)據(jù)可以是表或者任何子查詢。用于查找目標表中匹配行的條件與WHERE子句類似。更新或者插入行的子句像UPDATE或者INSERT命令一樣復雜。MERGE是最復雜的DML命令(但不是不可理解)，因為其功能(無可爭辯)最強大。MERGE的使用不在OCP課程提綱范圍之內(nèi)，但出于完整性考慮，下面給出一個簡單示例：

        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的內(nèi)容在EMPLOYEES表中更新或者插入行。情況是EMPLOYEES是所有員工的表，NEW_EMPLOYEES表包含的行表示新員工和現(xiàn)有員工的薪水變化。命令會通過NEW_EMPLOYEES表，對于每一行而言，在EMPLOYEES表中查找具有相同EMPLOYEE_ID的行。如果找到這樣的行，就用NEW_EMPLOYEES表中行的值更新它的SALARY列。如果沒有這樣的行，就插入一行。語法變體允許使用子查詢來選擇源行，甚至可以刪除匹配行。

6.1.6 DML語句失敗

命令失敗的原因有許多，包括：

● 語法錯誤

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

● 訪問權限

● 約束違背

● 空間問題

圖6-3顯示了使用SQL*Plus嘗試執(zhí)行語句。

圖6-3 語句失敗的一些示例

圖6-3中，用戶作為SUE(口令，SUE——這不是一個好的安全性的例子)連接，并查詢EMPLOYEES表。語句會因為簡單的語法錯誤而失敗，這個錯誤由SQL*Plus正確標識。注意，SQL*Plus不會更正這種錯誤，即使它知道你要輸入什么。有些第三方工具可能更有幫助，它們提供了自動錯誤更正功能。

第二次嘗試運行語句失敗并產(chǎn)生一個錯誤，表明對象不存在。這是因為它不存在于當前用戶的模式中；它存在于HR模式中。更正后，第三次運行語句成功——但只是剛好而已。傳遞到WHERE子句的值是字符串’07-JUN-2002'，但表中沒有將列HIRE_DATE定義為字符串，而是定義為日期。要執(zhí)行語句，數(shù)據(jù)庫必須知道用戶要做什么，并將字符串強制轉(zhuǎn)換為日期。在最后一個示例中，類型強制轉(zhuǎn)換失敗。

如果語句從語法上說是正確的，并且它引用的對象也沒有錯誤，它還有可能因為訪問權限而失敗。如果執(zhí)行語句的用戶沒有獲得它引用的表的相應權限，數(shù)據(jù)庫就會返回一個錯誤，這個錯誤與當對象不存在時返回的錯誤一樣。對于用戶而言，它不存在。

由訪問權限造成的錯誤是這樣一種情況，即SELECT和DML語句可能返回不同的結果：用戶可能有權查看表中的行，但無權插入、更新或者刪除它們。這種安排不是不常見；這通常在商業(yè)方面比較有意義。也許更讓人不解的是，可以設置權限，插入不允許用戶查看的行。也許最糟的是，還可以刪除用戶看不到也不能更新的行，但是這種情況并不常見。

違背約束會導致DML語句失敗。例如，INSERT命令可將多行插入表中，對于每一行，數(shù)據(jù)庫都會檢查主鍵相同的行是否已經(jīng)存在。隨著各行的插入會出現(xiàn)這種情況：可能前幾行(或者前幾百萬行)沒有問題，隨后語句遇到包含重復值的一行。此時它會返回一個錯誤，且語句失敗，這個失敗會觸發(fā)撤消所有已經(jīng)成功的插入。這是SQL標準的一部分：語句必須完全成功，或者根本不成功。這種反轉(zhuǎn)就是回滾。本章6.2節(jié)將詳細討論回滾機制。

如果語句因為空間問題而失敗，那么效果也一樣。在數(shù)據(jù)庫占用完空間之前，部分語句會成功。已經(jīng)成功的這一部分會自動回滾。語句的回滾是一件嚴重的事情，它需要數(shù)據(jù)庫做大量額外工作，花費的時間通常至少等于執(zhí)行語句已經(jīng)花費的時間(有時更長)。