SQL - 資料操作

Oracle 提供資料操縱語言 (DML) 命令來執行資料庫中的資料操作。資料操作可以是使用應用程式或業務資料填充資料庫表，修改資料以及根據需要從資料庫中刪除資料。除了資料操作外，還有一些命令用於控制這些操作。這些命令被分組為事務控制語言 (TCL)。

在邏輯 SQL 事務中涉及三種類型的 DML 語句，即插入、更新、刪除和合並。事務是在資料庫會話中 DML 操作的邏輯集合。

INSERT 語句

INSERT 命令用於將資料儲存到表中。INSERT 命令通常在更高階的程式語言（如 Visual Basic.NET 或 C++）中用作嵌入式 SQL 命令；但是，此命令也可以在命令模式下的 SQL*PLUS 提示符下執行。INSERT 命令有兩種不同的形式。如果新行將為行的每一列插入一個值，則使用第一種形式。INSERT 命令的第二種形式用於插入某些列資料未知或從其他業務邏輯中預設的行。此形式的 INSERT 命令要求您指定正在儲存資料的列名。

語法

如果表中所有列的值都是確定的和已知的，則可以遵循以下語法。

INSERT INTO table
VALUES (column1 value, column2 value, 
...);

如果表中只有少數幾列需要填充值，則可以使用以下語法。其餘列可以將它們的值推斷為 NULL 或來自不同的業務邏輯。

INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);

下面的 INSERT 語句在 EMPLOYEES 表中建立一個新的員工記錄。請注意，它為主鍵列 EMPLOYEE_ID、FIRST_NAME、SALARY 和 DEPARTMENT_ID 插入值。

INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, 'KEMP', 3800, 10);

否則，如果預先知道值並且必須符合表中列的資料型別和位置，則可以使用以下 INSERT 語句在 EMPLOYEES 表中插入完整的員工資料，而無需指定列列表。

INSERT INTO employees
VALUES (130, 'KEMP','GARNER', 'kemp.garner@xxx.com', '48309290',TO_DATE ('01-JAN-2012'), 'SALES', 3800, 0, 110, 10);

要插入的值必須與列的資料型別相容。文字、固定值和特殊值（如函式、SYSDATE、CURRENT_DATE、SEQ.CURRVAL (NEXTVAL) 或 USER）可以用作列值。指定的值必須遵循通用規則。字串文字和日期值必須用引號括起來。日期值可以使用 DD-MON-RR 或 D-MON-YYYY 格式提供，但首選 YYYY，因為它清楚地指定了世紀，並且不依賴於內部 RR 世紀計算邏輯。

INSERT-AS-SELECT (IAS) 語句

可以使用 INSERT..AS..SELECT (IAS) 操作將資料從源表填充到目標表。這是一個直接路徑讀取操作。這是一種簡單的方法，可以將資料從一個表複製到另一個表，或者建立一個源表操作線上的表的備份副本。

例如，可以將資料從 EMPLOYEES 表複製到 EMP_HISTORY 表。

INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;

UPDATE 語句

UPDATE 命令修改儲存在列中的資料。它可以一次更新一行或多行，具體取決於 WHERE 子句中指定的條件過濾的結果集。請注意，更新列與更改列不同。在本章前面，您學習了 ALTER 命令。ALTER 命令更改表結構，但不會影響表資料。UPDATE 命令更改表中的資料，而不是表結構。

語法

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

從語法來看，

SET column = expression 可以是任何字元、公式或函式的組合，這些字元、公式或函式將更新指定列名中的資料。WHERE 子句是可選的，但如果包含，則它指定將更新哪些行。一次只能使用 UPDATE 命令更新一個表。

下面的 UPDATE 語句將員工 JOHN 的薪水更新為 5000。

UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = 'JOHN';

雖然 WHERE 謂詞是可選的，但必須邏輯地附加，以便僅修改表中的所需行。下面的 UPDATE 語句更新表中所有員工的薪水。

UPDATE employees
SET salary = 5000;

也可以透過在 SET 子句中指定多個用逗號分隔的列來更新多個列。例如，如果 JOHN 的薪水和職位都必須分別更改為 5000 和 SALES，則 UPDATE 語句如下所示：

UPDATE employees
SET	SALARY = 5000,
	JOB_ID = 'SALES'
WHERE UPPER (first_name) = 'JOHN';

1 row updated.

另一種更新同一行多個列的方法顯示了子查詢的使用。

UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, 'SALES' FROM DUAL)
WHERE UPPER (ENAME) = 'JOHN'

DELETE 語句

DELETE 命令是 SQL 語句中最簡單的語句之一。它從表中刪除一行或多行。SQL 中不允許多表刪除操作。DELETE 命令的語法如下所示。

DELETE FROM table_name
    [WHERE condition];

DELETE 命令刪除表中滿足可選 WHERE 子句中條件的所有行。由於 WHERE 子句是可選的，因此可以透過省略 WHERE 子句輕鬆地從表中刪除所有行，因為 WHERE 子句限制了 DELETE 操作的範圍。

下面的 DELETE 語句將從 EMP 表中刪除 EDWIN 的詳細資訊。

DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'

1 row deleted.

注意：DELETE [TABLE NAME] 和 DELETE FROM [TABLE NAME] 具有相同的含義。

條件刪除語句中的 WHERE 條件可以使用子查詢，如下所示。

DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
				    FROM LOCATIONS
				    WHERE LOCATION_CODE = 'SFO')

TRUNCATE

TRUNCATE 是一個 DDL 命令，用於清空表中的所有記錄，但保留表結構。它不支援 WHERE 條件來刪除選定的記錄。

語法

TRUNCATE [table name]

它是自動提交的，即它提交會話中的當前活動事務。截斷表不會刪除依賴的索引、觸發器或表約束。如果表 A 是資料庫中表 B 的引用約束的父表，則無法截斷表 A。

事務

事務是在資料庫中完成的邏輯工作單元。它可以包含：

以 TCL 命令（即 COMMIT 或 ROLLBACK）結尾的多個 DML 命令
一個 DDL 命令
一個 DCL 命令

事務的開始以第一個 DML 命令為標記。它以 TCL、DDL 或 DCL 命令結尾。顯式發出 TCL 命令（即 COMMIT 或 ROLLBACK）以結束活動事務。由於其基本行為，如果資料庫會話中執行任何 DDL 或 DCL 命令，則會提交會話中正在進行的活動事務。如果資料庫例項異常崩潰，則事務將停止。

COMMIT、ROLLBACK 和 SAVEPOINT 是事務控制語言。COMMIT 將資料更改永久應用到資料庫，而 ROLLBACK 執行反提交操作。SAVEPOINT 透過在不同事務階段設定標記來控制事務的系列。使用者可以將當前事務回滾到之前設定的所需儲存點。

**COMMIT** - Commit 透過將資料更改永久應用到資料庫表來結束當前活動事務。COMMIT 是顯式結束事務的 TCL 命令。但是，DDL 和 DCL 命令會隱式提交事務。

**SAVEPOINT** - 儲存點用於標記會話中當前事務中的特定點。因為它是在事務中的邏輯標記，所以無法在資料字典中查詢儲存點。

**ROLLBACK** - ROLLBACK 命令用於透過丟棄資料更改來結束整個事務。如果事務包含標記的儲存點，則可以使用 ROLLBACK TO SAVEPOINT [name] 將事務回滾到指定的儲存點。結果，直到指定儲存點的所有資料更改都將被丟棄。

演示

考慮 EMPLOYEES 表，該表每年第一季度都會填充新聘用員工的詳細資訊。文員員工使用儲存點附加每個員工的詳細資訊，以便在資料饋送活動期間隨時回滾任何錯誤資料。請注意，他保留了與員工姓名相同的儲存點名稱。

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, 'Allen',TO_DATE ('15-JAN-2013','SALES',10000,10);

SAVEPOINT Allen;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',10000,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',12000,30);

SAVEPOINT McMan;

假設，資料饋送操作員意識到他錯誤地輸入了“Kate”和“McMan”的薪水。他將活動事務回滾到儲存點 Kate，然後重新輸入 Kate 和 McMan 的員工詳細資訊。

ROLLBACK TO SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',12500,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',13200,30);

SAVEPOINT McMan;

完成資料輸入後，他可以透過在當前會話中發出 COMMIT 來提交整個事務。

讀取一致性

Oracle 在每個會話中保持使用者之間在資料訪問和讀/寫操作方面的一致性。

當對錶執行 DML 時，操作更改的原始資料值將記錄在資料庫撤銷記錄中。只要事務未提交到資料庫，任何稍後查詢修改資料的其他會話中的使用者都會檢視原始資料值。Oracle 使用系統全域性區域中的當前資訊和撤銷記錄中的資訊來構建查詢的表的讀取一致性檢視。只有當事務提交時，事務的更改才會永久儲存。事務是 Oracle 提供讀取一致性策略的關鍵。

讀取一致性檢視的起點是代表讀取器生成的

控制其他資料庫事務何時可以看到修改後的資料以進行讀取或更新

列印頁面