※複製表
CREATE TABLE xxx AS SELECT * FROM emp;
※這是DDL的操作，再過幾篇會寫，先照做
※表的名稱叫xxx，內容和emp表一樣，但要注意CONSTRAINT 不複製，所以PK、FK都沒有
※複製這個表來練習，可以隨便亂搞，這裡的增刪改都是DML，所以增刪改完成後，只有目前的使用者才有用，或者重新登入就沒了，如果想讓其他使用者或者重新登入都有上次的資料，就要下COMMIT;，而如果在還沒COMMIT之前，目前的使用者感覺操作都有修改到，如果不想要這次的執行結果，可以下ROLLBACK;
※練習完可用「DROP TABLE xxx PURGE;」刪除表
※新增
INSERT INTO xxx(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(9999, 'x', 'CLERK', 7902, sysdate, 1000, 0, 20); INSERT INTO xxx VALUES(8888, 'o', 'MANAGER', 7839, sysdate, 3000, 0, 30); INSERT INTO xxx SELECT * FROM emp WHERE deptno = 30;
※新增有三種
第一個SQL有打欄位，可以視需要，新增想增加的欄位即可，而值打在VALUES後面，不過PK、not null一定要打，否則編譯會失敗
第二個SQL不打欄位，所以值要按照資料庫的順序全部打出來
第三個SQL可以一次新增多筆，查詢一張表，欄位的型態要和想新增的表一樣即可
※修改
UPDATE xxx SET job = 'MANAGER', sal = 1500 WHERE empno = 7369; UPDATE xxx SET (job,sal) = ( SELECT job, sal FROM emp WHERE empno = 7499 ) WHERE empno = 7369;
※要注意不加 WHERE 會將所有表都修改，如果表裡有10筆，10筆都會修改
※多個欄位用逗號隔開
※第二個SQL是配合 SELECT 使用的
※刪除
DELETE FROM xxx WHERE empno = 7369; DELETE FROM xxx WHERE job = ( SELECT job FROM xxx WHERE empno = 7902 );
※要注意不加 WHERE 會將所有表都刪除，如果表裡有10筆，10筆都會被刪除，如果不小心刪除了，記得ROLLBACK
※DELETE沒有「*」
※第二個SQL是配合 SELECT 使用的
※MERGE INTO USING ON
有找到就修改；沒找到就新增MERGE INTO emp e USING (SELECT deptno FROM dept WHERE deptno = 40) d ON( e.deptno = d.deptno ) WHEN NOT MATCHED THEN INSERT (empno, ename) VALUES (EMP_SEQ.nextval, 'xxx') WHEN MATCHED THEN UPDATE SET comm = comm * 1.2 DELETE WHERE (ename = 'TURNER');
※USING 和 ON 都是一定要有的
※MERGE INTO 後面接要修改或新增的table
※注意USING裡的SQL，欄位值只能唯一，否則會報「ORA-30926:無法取得來源表格中可信的資料列集」，可用distinct
※ON裡的欄位也不能修改，否則會報『ORA-38104:無法更新「ON子句」中參照的資料欄:table.column』
※UPDATE可以加上DELETE，但where條件是update後的條件
※假設t1表的tid有1、2、3，而t2表有2、3、4，那結果就會修改2筆
又假設t2表變成4、5、6，那就會執行新增
※MATCHED和NOT MATCHED可擇一使用，順序也可以互換
※注意新增和修改的語法變的比較精簡了，insert如果要每個欄位都新增，insert後的圓括號可不打
※事務
※Transaction 翻譯成事務※commit 可以讓其他的使用者可以看到修改的結果
※rollbak：在還沒commit前，可以回復到修改前的狀態
※資料庫要有四個特性才能叫資料庫，就是ACID
．Atomicity(原子性)：整體一起commit或rollback
．Consistency(一致性)：假設A轉帳給B1000元，
成功時，A少1000元，B也多1000元 --> 一致
失敗時，A和B的錢都不會增加或減少 --> 一致
．Isolation(隔離性)：多個事務可以同時進行，而且彼此之間看不見
．Durability(持久性)：事務提交成功後，即使資料庫馬上就壞了，也必須通過某種機制恢復資料
在12c預設不自動提交，SET AUTOCOMMIT ON|OFF;可以設定要不要自動提交
※SAVEPOINT
可以將當前的狀態存起來，以下一定要使用不自動提交已經commit的就不能rollback了
※例1
SELECT * FROM xxx;-- 1.看一下目前資料 UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改 SELECT * FROM xxx;-- 3.確認有修改到 SAVEPOINT s1;-- 4.將目前狀態存起來 ROLLBACK;-- 5.回到修改前，也就是第一和第二行之間 ROLLBACK TO s1;-- 6.想回到s1
※因為第5步已經回到1和2之間，那時並沒有存s1，這時就會出
「ORA-01086: 從未在此階段作業建立儲存點 'S1' 或此儲存點無效」
※例2
SELECT * FROM xxx;-- 1.看一下目前資料 UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改 SELECT * FROM xxx;-- 3.確認有修改到 SAVEPOINT s1;-- 4.將目前狀態存起來 UPDATE xxx SET comm = 50 WHERE empno = 7566;-- 5.修改 SELECT * FROM xxx;-- 6.確認有修改到 ROLLBACK TO s1;-- 7.回到s1
※第7步因為前面沒有commit或rollback，所以回復成功
※例3
SELECT * FROM xxx;-- 1.看一下目前資料 UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改 SELECT * FROM xxx;-- 3.確認有修改到 SAVEPOINT s1;-- 4.將目前狀態存起來 UPDATE xxx SET comm = 50 WHERE empno = 7566;-- 5.修改 SELECT * FROM xxx;-- 6.確認有修改到 SAVEPOINT s2;-- 7.將目前狀態存起來 ROLLBACK TO s1;-- 8.回到s1 SELECT * FROM xxx;-- 9.確定回復成功 ROLLBACK TO s2;-- 10.想回到s2
※因為第8步已經回到4和5之間，那時並沒有存s2，這時就會出
ORA-01086: 從未在此階段作業建立儲存點 'S2' 或此儲存點無效
※例4
SELECT * FROM xxx;-- 1.看一下目前資料 UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改 SELECT * FROM xxx;-- 3.確認有修改到 SAVEPOINT s1;-- 4.將目前狀態存起來 UPDATE xxx SET comm = 50 WHERE empno = 7566;-- 5.修改 SELECT * FROM xxx;-- 6.確認有修改到 SAVEPOINT s2;-- 7.將目前狀態存起來 UPDATE xxx SET comm = 50 WHERE empno = 7698;-- 8.修改 SELECT * FROM xxx;-- 9.確認有修改到 ROLLBACK TO s2;-- 10.回到s2 SELECT * FROM xxx;-- 11.確定回復成功 ROLLBACK TO s1;-- 12.回到s1 SELECT * FROM xxx;-- 13.確定回復成功
※第10步回到s2，也就是6和7之間，有包括第4步的s1記錄，所以12步可以順利的回到s1
※鎖
在執行UPDATE、DELETE都有鎖，也就是其他使用者無法看到你鎖定的資料又分成行級鎖定和表級鎖定
說其他使用者不夠正確，應該是說其他session，每個登入進來的都有一個session，就算是同一個帳號也是分多個session，所以以下測試都是用同一個帳號，一個用SQL Developer，另一個用SQL plus
※行級鎖定
※例1sessionA
SELECT * FROM xxx WHERE deptno = 10 FOR UPDATE;
※FOR UPDATE 是SELECT的行級鎖，部門10的 empno 有7782、7839、7934
※例1sessionB
SELECT * FROM xxx WHERE deptno = 10 FOR UPDATE; SELECT * FROM xxx WHERE empno = 7782 FOR UPDATE; SELECT * FROM xxx FOR UPDATE; UPDATE xxx SET comm = 50 WHERE empno = 7369; DELETE FROM xxx WHERE empno = 7369;
※sessionB在執行上的其中一行SQL時，只要sessionA還沒commit或rollback，畫面就好像在讀什麼一樣，都不動，還以為是當機了
※FOR UPDATE在工作中，常有人執行後，配合類似SQL Developer的軟體，然後用滑鼠修改值，算是一種應用吧！
※修改或刪除，例2sessionA
UPDATE xxx SET comm = 50 WHERE empno = 7369;
※這時還沒commit或rollback
※例2sessionB
UPDATE xxx SET comm = 50 WHERE empno = 7369; DELETE FROM xxx WHERE empno = 7369;
※這時一樣會停住不動
※表級鎖定
官網連結LOCK TABLE emp IN EXCLUSIVE MODE NOWAIT/WAIT;
WAIT是預設的選項，如果表被鎖起來會停住不動，不想等就用NOWAIT
類似 EXCLUSIVE 的有6種模式，官網都有寫，我盡我的能力翻出來如下：
1.ROW SHARE：
允許同時存取鎖定的表，但禁止使用者鎖定整個表獨占存取
ROW SHARE 和SHARE UPDATE一樣，這是因為早期版本兼容性的關係
2.ROW EXCLUSIVE：
很像ROW SHARE，但禁止在SHARE模式鎖定
ROW EXCLUSIVE在增刪改時是自動取得的
3.SHARE UPDATE：
和ROW SHARE一樣
4.SHARE：
允許同時查詢，但禁止更新鎖定的表
5.SHARE ROW EXCLUSIVE：
用在尋找整個表，而且允許看到表中的行
可是在SHARE模式禁止其他人鎖定表或修改行
6.EXCLUSIVE：
只允許查詢鎖定的表
※例
--sessionA LOCK TABLE xxx IN SHARE MODE NOWAIT; --sessionB DELETE FROM xxx;
※sessionA執行後，sessionB執行DELETE就會停住不動
sessionA一rollback，sessionB才會執行
※解鎖
官網連結※例
SELECT * FROM xxx WHERE deptno = 10 FOR UPDATE; SELECT SESSION_ID FROM V$LOCKED_OBJECT;-- 23、250 SELECT SID, SERIAL#, LOCKWAIT FROM V$SESSION WHERE SID IN (23,250); /* 23 5289 250 18727 */ ALTER SYSTEM KILL SESSION '250,18727';
※第二行以後都必須用有權限的帳號才能執行
※第一行用兩個session分別執行，其中一個session會停住不動
※查詢V$LOCKED_OBJECT表，會發現有二筆鎖定了
※將查到的兩筆查詢V$SESSION表，取得SID和SERIAL#也會有兩筆
※刪除其中一筆後，解除鎖定
沒有留言:
張貼留言