oracle心得5--oracle的增刪改查@oracle的事務處理@案例分析

1.Oracle中的增刪改查

DML(Data Manipulation Language – 資料操作語言) 可以在下列條件下執行:向表中插入資料、修改現存資料、刪除現存資料。

事務是由完成若干項工作的DML語句組成的

2.插入資料操作

按列的預設順序列出各個列的值。 在 INSERT 子句中隨意列出列名和他們的值。字元和日期型資料應包含在單引號中。

在SQL 語句中使用 & 變數指定列值。& 變數放在VALUES子句中。&變數就是一個佔位符，就是在控制檯輸入值

INSERT INTO departments

            (department_id, department_name, location_id)

VALUES      (&department_id, '&department_name',&location);

3.更新資料操作

新增一次只能新增一條資料,但是更新可以一次多條資料。

如果省略WHERE子句，則表中的所有資料都將被更新

 

4.刪除資料操作

5.資料庫事務由以下的部分組成:

一個或多個DML 語句

一個 DDL(Data Definition Language – 資料定義語言) 語句

一個 DCL(Data Control Language – 資料控制語言) 語句

以第一個 DML 語句的執行作為開始；以下面其中之一作為結束:

COMMIT 或 ROLLBACK 語句；DDL 或 DCL 語句（自動提交）；使用者會話正常結束；系統異常終了。

使用COMMIT 和 ROLLBACK語句,我們可以: 確保資料完整性。

資料改變被提交之前預覽。將邏輯上相關的操作分組。

 

提交或回滾前的資料狀態

自動提交在以下情況中執行:DDL 語句；DCL 語句。

不使用 COMMIT 或 ROLLBACK 語句提交或回滾，正常結束會話。

會話異常結束或系統異常會導致自動回滾

提交後的資料狀態

改變前的資料狀態是可以恢復的

執行 DML 操作的使用者可以通過 SELECT 語句查詢之前的修正

其他使用者不能看到當前使用者所做的改變，直到當前使用者結束事務。

DML語句所涉及到的行被鎖定， 其他使用者不能操作。

資料回滾後的狀態

使用 ROLLBACK 語句可使資料變化失效:

資料改變被取消。修改前的資料狀態被恢復。鎖被釋放。

6. 資料庫的隔離級別

對於同時執行的多個事務, 當這些事務訪問資料庫中相同的資料時, 如果沒有采取必要的隔離機制, 就會導致各種併發問題:

髒讀: 對於兩個事物 T1, T2, T1 讀取了已經被 T2 更新但還沒有被提交的欄位. 之後, 若 T2 回滾, T1讀取的內容就是臨時且無效的.

不可重複讀: 對於兩個事物 T1, T2, T1 讀取了一個欄位, 然後 T2 更新了該欄位. 之後, T1再次讀取同一個欄位, 值就不同了.

幻讀: 對於兩個事物 T1, T2, T1 從一個表中讀取了一個欄位, 然後 T2 在該表中插入了一些新的行. 之後, 如果 T1 再次讀取同一個表, 就會多出幾行.

資料庫事務的隔離性: 資料庫系統必須具有隔離併發執行各個事務的能力, 使它們不會相互影響, 避免各種併發問題.

一個事務與其他事務隔離的程度稱為隔離級別. 資料庫規定了多種事務隔離級別, 不同隔離級別對應不同的干擾程度, 隔離級別越高, 資料一致性就越好, 但併發性越弱

7.事務的概念特性

     概念：在資料庫中事務是工作的邏輯單元,一個事務是由一個或多個完成一組的相關行為的SQL語句組成,通過事務機制確保這一組SQL語句所作的操作要麼完全成功執行,完成整個工作單元操作,要麼一點也不執行。

主要特性：確保資料庫的完整性。

事務的ACID特性:

   對一組SQL語句操作構成事務,資料庫作業系統必須確保這些操作的原子性,一致性,隔離性,永續性.

原子性(Atomicity)

事務的原子性是指事務中包含的所有操作要麼全做，要麼不做,也就是說所有的活動在資料庫中要麼全部反映,要麼全部不反映,以保證資料庫的一致性。

一致性(Consistency)

    事務的一致性是指資料庫在事務操作前和事務處理後,其中資料必須滿足業務的規則約束。

隔離性(Isolation)

隔離性是指資料庫允許多個併發的事務同時對其中的資料進行讀寫或修改的能力,隔離性可以防止多個事務的併發執行時,由於它們的操作命令交叉執行而導致資料的不一致性。

永續性(durability)

事務的永續性是指在事務處理結束後,它對資料的修改應該是永久的。即便是系統在遇到故障的情況下也不會丟失,這是資料的重要性決定的。

8.事務的控制語句

    在oracle資料庫中,沒有提供開始事務處理語句,所有的事務都是隱式開始的,也就是說在oracle中,使用者不可以顯示使用命令來開始一個事務.oracle任務第一條修改資料庫的語句,或者一些要求事務處理的場合都是事務的隱式開始。但是當使用者想要終止一個事務處理時,必須顯示使用commit和rollback語句結束。

   根據事務ACID屬性,oracle提供瞭如下的事務控制語句：

   Set transaction 設定事物屬性

   Set constrains  設定事物的約束模式

   約束模式是指：在事務中修改資料時,資料庫中的約束立即應用於資料,還是將約束推遲到當前事務結束後應用。

   Savepoint 在事務中建立一個儲存的點.當事務處理髮生異常而回滾事務時,可指定事務回滾到某儲存點.然後從該儲存點重新執行。

   Release  savepoint  刪除儲存點

   Rollback 回滾事務 取消對資料庫所作的任何操作

   Commit 提交事務 對資料庫的操作做持久的儲存。

9.設定事物的屬性

    set transaction語句可用來設定事物的各種屬性。該語句必須放在事務處理的第一個語句.

也就是說，必須在任何insert、update 、delete語句以及其他的事務處理。Set transaction的語句可以讓使用者對事務的以下屬性進行設定:指定事務隔離層、規定回滾事務所使用的儲存空間、命名事務

    備註：在使用set transaction語句設定屬性時,對於規定回滾事務所使用的儲存空間的設定很少使用.對於命名事務也非常簡單,只有在分散式事務處理中才會體現出命名事務的用途.

注意：set transaction只對當前事務有效,事務終止,事務當前的設定將會失效。

10.資料異常

事務的隔離性定義了一個事務與其它事務的隔離程度.為了更好的理解隔離層,首先討論一下併發事務對同一個資料庫進行訪問可能發生的情況.在併發事務中總體來說會發生如下3種情況

錯讀 |髒讀

非重複讀取|不可重複讀

假讀|幻讀

錯讀|髒讀：當一個事務修改資料時,另一事務讀取了該資料,但是第一事務由於某種原因取消對資料修改,使資料返回了原狀態,這是第二個事務讀取的資料與資料庫中資料不一致.這就叫錯讀。

非重複讀取：是指一個事務讀取資料庫中的資料後,另一個事務則更新了資料,當第一個事務再次讀取其中的資料時,就會發現資料已經發生了改變,這就是非重複讀取。非重複讀取所導致的結果就是一個事務前後兩次讀取的資料不相同。

 假讀：如果一個事務基於某個條件讀取資料後,另一個事務則更新了同一個表中的資料,這時第一個事務再次讀取資料時,根據搜尋的條件返回了不同的行,這就是假讀。

事務中遇到的這些異常與事務的隔離性設定有關,事務的隔離性設定越多,異常就出現的越少,但併發效果就越低,事務的隔離性設定越少,異常出現的越多,併發效果越高。

選擇隔離層

針對3中讀取的資料時產生的不一致現象,在ANSI SQL標準92中定義了4個事務的隔離級別.如下圖所示：

隔離層	錯讀|髒讀	非重複讀取|不可重複讀	假讀|幻讀
READ UNCOMMITTED(非提交讀)	是	是	是
READ COMMITTED(提交讀)	否	是	是
Repeatable READ(可重複讀)	否	否	是
Serializable(序列讀)	否	否	否

Oracle支援上述四種隔離層中的兩種:read committed 和serializable。除此之外oralce中還定義read only 和 read write隔離層。

 Read committed 這是oracle預設的隔離層。

Serializable：設定事物的隔離層位它時,事務與事務之間完全隔開,事務以序列的方式執行,這並不是說一個事務必須結束才能啟動另外一個事務,而是說這些事務的執行的結果於一次執行的事務的結果一致。

Read only和 read write 當使用read only時,事務中不能有任何修改資料庫中資料的操作語句，這包括 insert、update、delete、create語句。Read only是serializable的一個子集，區別是read only 只讀,而serialzable可以執行DML操作。Read write它是預設設定,該選項表示在事務中可以有訪問語句、修改語句.但不經常使用.

建立set transaction的語句

 列舉如下：

  Set transaction read only

  Set transaction read write

  Set transaction isolation level read committed

  Set transaction isolation level serializable

注意：這些語句是互斥的.即不能夠同時設定兩個或者兩個以上的選項。

結束事務：

什麼情況下代表事務的結束呢?

Commit 提交事務

rollback 回滾事務 

 

11.只讀的案例

SQL> set transaction read only;

 

Transaction set

 

SQL> select * from users where username='mj';

 

USERNAME      PASSWORD      NAME             ADDRESS             ZIP

-------------------- -------------------- -------------------- -------------------- -------

mj                  redarmy          陳紅軍                                 

 

SQL> update users set password='123' where username='mj';

 

update users set password='123' where username='mj'

 

ORA-01456: 不能在 READ ONLY 事務處理中執行插入/刪除/更新操作

 

//髒讀的案例

SQL> set transaction isolation level read committed;

 

Transaction set

 

SQL> select * from users where username='mj';

 

USERNAME      PASSWORD      NAME             ADDRESS             ZIP

-------------------- -------------------- -------------------- -------------------- -------

mj                  redarmy          陳紅軍                                 

 

SQL> update users set password='123' where username='mj';

 

1 row updated

 

SQL> select * from users where username='mj';

 

USERNAME      PASSWORD      NAME             ADDRESS             ZIP

-------------------- -------------------- -------------------- -------------------- -------

mj                  123                 陳紅軍                                 

 

SQL> rollback;