Oracle資料庫入門之DML與事務控制

　　insert語句

　　文法：insert into table [(column1, column2...)] values (value1, value2...);

　　說明：insert語句每次只能向表中插入一條記錄。預設欄位名列表時，應為新插入記錄中的每個欄位顯式的設定新值

　　也可在insert語句中指定賦值欄位列表，只為部分欄位顯式設定新值，其餘欄位將被預設賦值為null

　　舉例：insert into dept values(88,'研發部','北京');--此句便是預設欄位名列表的情況

　　insert into dept values(66,'美工部');--這句將執行出錯，提示為沒有足夠的值。即必須為所有欄位設定值

　　insert into dept(deptno,dname) values(99,'財務部');--也可以單獨為deptno和dname欄位賦值

　　select table_name from user_tables;--用到了資料字典表。目的是查詢目前使用者方案下所有的表的名字

　　select user from dual;--查詢當前串連到資料庫的使用者名稱

　　補充：可以在insert語句中使用子查詢，實現表間資料拷貝。在現實開發中很少用的到，有點類似Java中的數組拷貝

　　比如insert into dept1(id, name) select deptno, dname from dept;

　　此時不必再給出values子句。子查詢中的值列表應與insert子句中的欄位列表相匹配

　　實現資料複製的時候，也可以在應用程式層面採用這種方式進行資料備份，但一般來說這種備份是不可靠的

　　應該在資料庫的層面做備份，也可以由DBA對整個資料庫做自動的資料備份處理，包括出現問題的時候的恢複

　　update語句

　　文法：update table set column1=value1 [, column2=value2, ...] [where condition];

　　說明：update語句用於更新表中的資料。update語句每次可更新多條記錄

　　可使用where子句限定要更新的記錄，如果預設where子句，則更新表中的所有記錄

　　舉例：update emp set sal=sal+88;--將所有員工的工資都漲88元錢

　　update student2 set phone='010-51288984' where name='張三';--將張三的電話改為010-51288984

　　delete語句

　　文法：delete [from] table [where condition];

　　說明：delete語句用於從表中刪除資料。delete語句每次可刪除多條記錄

　　可使用where子句限定要刪除的記錄，如果預設where子句，則刪除表中的所有記錄

　　delete語句並不是刪除整個表，只是刪除表中的記錄，表仍然存在，還可用來存放資料

　　舉例：delete emp;--等價於delete from emp;

　　delete emp where empno=7778;--刪除empno欄位的值為7778的所有記錄

　　merge語句

　　概述：merge語句用於進行資料合併，它是根據條件在表中執行資料的修改或插入操作

　　如果要插入的記錄在目標表中已經存在，則執行更新操作，否則執行插入操作

　　實際開發的過程中使用它的機會並不是很多

　　文法：merge into table [alias]

　　using(table|view|sub_query) [alias]

　　on(join_condition)

　　when matched then

　　update set col1=col1_val, col2=col2_val

　　when not matched then

　　insert (column_list) values(column_values);

　　舉例：create table test1(eid number(10), name varchar2(20), birth date, salary number(8,2));

　　insert into test1 values (1001, 'Stone', '21-1月-10', 8888);

　　insert into test1 values (1002, 'Smith', '04-1月-09', 6666);

　　select * from test1;

　　create table test2(eid number(10), name varchar2(20), birth date, salary number(8,2));

　　select * from test2;

　　merge into test2

　　using test1

　　on(test1.eid=test2.eid)

　　when matched then

　　update set name=test1.name,birth=test1.birth,salary=test1.salary

　　when not matched then

　　insert (eid, name, birth) values(test1.eid, test1.name, test1.birth);

　　select * from test2;

　　說明：set設定的是目標表中的欄位。即將源表中的欄位值賦給目標表中的欄位

　　insert設定的也是將欄位值插入到目標表中。如果要插入全部的欄位的值，那麼column_list是可以省略的

　　注意：由於set和insert預設都是對目標表進行操作，所以，它們後面的欄位不可以加目標表首碼

　　即本例中若出現set test2.name=test1.name或insert (test2.eid)，執行則出錯，提示資訊為標識符無效

　　事務控制

　　概述：也叫做交易處理。是通過將一組相關操作組合為一個要麼全部成功，要麼全部失敗的邏輯工作單元

　　以簡化錯誤恢複，提高應用程式的可靠性。這裡指的是在資料庫層面所進行的一種資料庫操作的整合或者說單元化控制

　　也可以在應用程式層面進行類似的處理。比如說逐個向資料庫發送多條DML指令，通過先後更新兩個表以對應轉賬操作

　　如果後面的一個操作出錯了，那麼就取消前一個指令，或者進行相反的對沖的操作

　　但應用程式層面的這種原子的組合，實際上是不可靠的。在資料庫的層面或者在底層來進行這種整合，會更有效有些

　　事務：組成單個邏輯工作單元的一系列操作被稱為事務(Transaction)。實際上事務不是僅限於在資料庫領域中的一個概念

　　資料庫事務通常由0到多條DML語句或1條DDL(Data Define Language)語句或1條DCL(Data Control Language)語句組成

　　所謂的單個邏輯工作單元就是能夠完成一個相對獨立的功能，或者說是不應該分隔開的一個操作系列

　　比如銀行的轉賬業務，該業務至少可以分為A賬戶的轉出和B賬戶的轉入。也就是從A賬戶的餘額中減掉一定的數額

　　然後再將B賬戶的餘額增加一定的數額。整個過程等於是修改了兩條記錄，這兩個操作就可以認為就組成了一個事務

　　它們應該是一個單個的邏輯工作單元，也就是銀行的一個單筆業務

　　ACID：事務必須滿足ACID屬性，即原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、持久性(Durability)

　　原子性：即事務中所有操作要麼全成功，要麼全失敗，它們應該作為一個整體被處理

　　一致性：事務執行完畢之後，資料必須處於一致性的狀態，不能出現資料狀態上的錯誤。比如將員工工資層級從C升到B級

　　但工資卻沒有從應該的4000升到6000，於是就出現了資料的不一致。也就是說這時的工資層級雖然是B級

　　但工資的數額卻屬於C級的範圍。這就說明事務結束的時候，資料處於一種不一致性的狀態，會影響後續的使用

　　隔離性：這是相對其它事務而言的。有時可能出現多個事務並發執行的情況，比如多個使用者同時對同一個資料庫表進行操作

　　碰巧操縱的又是該表的同一個資料。此時應該有這樣的一個保證，即當前事務在執行過程中所做的資料狀態的改變

　　是不受其它事務影響的。其它事務所讀取或查看的資料，仍是當前事務執行之前的狀態

　　直到當前事務結束，其它事務看到的才會是當前事務結束之後的資料狀態

　　所有事務中間的狀態對其它事務而言，是被隔離開的，不受其它事務影響

　　持久性：當事務執行完畢並提交操作之後，資料將永久生效，永久儲存在資料庫中，將來不可撤銷，不可恢複

　　開始：事務開始於第一條可執行語句

　　結束：當遇到commit或rollback語句、遇到DDL或DCL語句、使用者會話結束、系統崩潰等情況時會結束事務

　　提交：在執行一個DDL語句、執行一個DCL語句、正常結束會話等情況下會自動認可事務

　　提交就是讓事務永久生效，不可撤銷。復原就是撤銷先前操作，但復原到事務開始之前的狀態時，資料仍然是一致的

　　復原：當會話異常終止或系統崩潰時，事務會被自動復原。事務的顯式的提交和復原操作是為了更好的保證資料的一致性

　　狀態：交易回復後：資料的修改被撤銷。資料恢複到修改前的狀態。記錄鎖被釋放

　　事務提交後：資料的修改永久生效，不可撤銷。資料以前的狀態永久性丟失，無法恢複。儲存點(savepoints)被清除

　　所有使用者(會話)都將看到操作後的結果。記錄鎖被釋放，其它使用者此時才可以對這些資料進行修改操作

　　提交或復原前：事務中DML操作結果只對目前使用者(會話)可見，其它使用者(會話)看不到當前事務中資料的改變，直到事務結束

　　事務中DML語句所涉及到的行會被鎖定，其它使用者(會話)不能對其進行修改操作，但可以查詢

　　事務中資料狀態的改變是可以恢複的

　　SqlPlus的自動認可

　　概述：SqlPlus中執行SQL語句時可以設定是否自動認可，預設為非自動認可。這裡的提交指的不是事務，而是SqlPlus的每條語句

　　將來在commit或正常關閉視窗即關閉本地到資料庫連接的時候，Sql Plus中的語句也會被自動的提交

　　設定：show autocommit;--查看設定。其中autocommit OFF表示當前設定為非自動認可，而autocommit IMMEDIATE代表自動認可

　　set autocommit on;--更改為自動認可。set autocommit off;--更改為非自動認可

　　舉例：insert into dept values(88,'Stone','Beijing');

　　select * from dept;

　　說明：自動認可狀態為OFF的情況下，這是一個未提交的事務。插入新記錄的操作並沒有永久生效，只是目前使用者(會話)可見

　　其它會話中看不見這個未提交事務，即使目前使用者再一次串連到資料庫，即建立了一個新的會話，也查詢不到88號記錄

　　比如不關閉當前SQLPlus，然後再開啟一個SQLPlus視窗，並使用當前登入的scott使用者再一次登入新開啟的SQLPlus視窗

　　然後執行查詢，在結果中根本沒有查詢到前一次會話中沒有提交的操作指令，即查詢結果中沒有88號記錄

　　這時可以在原SQLPlus視窗中顯式的提交一下，即執行commit指令。提交完成後，插入88號記錄的操作便永久生效了

　　然後回到後開啟的SQLPlus視窗中執行查詢，查詢結果中很自然的就查到了88號記錄

　　也就是說已經提交的事務影響所有其它的事務和會話。而對於未提交的事務，其它使用者(會話)是看不見的

　　儲存點(Savepoint)

　　概述：通過儲存點在當前的事務中建立標記，將來可回退到指定的標記(儲存點)處，實現事務的部分復原

　　舉例：insert into dept values(55,'Adv','Beijing');

　　insert into dept values(56,'Sec','Shanghai');

　　savepoint p1;

　　insert into dept values(57,'Acc','Dalian');

　　select * from dept;

　　rollback to p1;

　　select * from dept;

　　說明：前提是當前SqlPlus的設定是非自動認可。所以這些都是當前會話中未提交的事務

　　代碼執行時p1之後的資料狀態的改變被撤銷，p1之前的操作仍然存在

　　若執行rollback;則復原到整個事務的最初