SQL語(yǔ)言共分為四大類(lèi)：數據查詢(xún)語(yǔ)言DQL，數據操縱語(yǔ)言DML， 數據定義語(yǔ)言DDL，數據控制語(yǔ)言DCL。其中用于定義數據的結構，比如 創(chuàng )建、修改或者刪除數據庫；DCL用于定義數據庫用戶(hù)的權限；在這篇文章中我將詳細講述這兩種語(yǔ)言在Oracle中的使用方法。
DML語(yǔ)言
DML是SQL的一個(gè)子集，主要用于修改數據，下表列出了ORACLE支持的DML語(yǔ)句。
事務(wù)控制
事務(wù)控制包括協(xié)調對相同數據的多個(gè)同步的訪(fǎng)問(wèn)。當一個(gè)用戶(hù)改變了另一個(gè)用戶(hù)正在使用的數據時(shí)，oracle使用事務(wù)控制誰(shuí)可以操作數據。
事務(wù) 事務(wù)表示工作的一個(gè)基本單元，是一系列作為一個(gè)單元被成功或不成功操作的SQL語(yǔ)句。在SQL和PL/SQL中有很多語(yǔ)句讓程序員控制事務(wù)。程序員可以：
1、顯式開(kāi)始一個(gè)事物，選擇語(yǔ)句級一致性或事務(wù)級一致性
2、設置撤銷(xiāo)回滾點(diǎn)，并回滾到回滾點(diǎn)
3、完成事務(wù)永遠改變數據或者放棄修改。
事務(wù)控制語(yǔ)句 | 語(yǔ)句 | 用途 |
| Commit | 完成事務(wù)，數據修改成功并對其他用戶(hù)開(kāi)放 |
| Rollback | 撤銷(xiāo)事務(wù)，撤銷(xiāo)所有操作 |
| rollback to savepoint | 撤銷(xiāo)在設置的回滾點(diǎn)以后的操作 |
| set transaction | 響應事務(wù)或語(yǔ)句的一致性；特別對于事務(wù)使用回滾段 |
例：
BEGIN
UPDATE checking
SET balance=balance-5000
WHERE account=‘Kieesha‘;
INSERT INTO checking_log(action_date,action,amount)
VALUES (SYSDATE,‘Transfer to brokerage‘,-5000);
UPDATE brokerage
SET cash_balance=cash_balance+5000
WHERE account=‘Kiesha‘;
INSERT INTO brokerage_log(action_date,action,amount)
VALUES (SYSDATE,‘Tracfer from checking‘,5000)
COMMIT
EXCEPTION
WHEN OTHERS
ROLLBACK
END |
Savepoint 和 部分回滾(Partial Rollback) 在SQL和PL/SQL中Savepoint是在一事務(wù)范圍內的中間標志。經(jīng)常用于將一個(gè)長(cháng)的事務(wù)劃分為小的部分。保留點(diǎn)Savepoint可標志長(cháng)事務(wù)中的任何點(diǎn)，允許可回滾該點(diǎn)之后的操作。在應用程序中經(jīng)常使用Savepoint；例如一過(guò)程包含幾個(gè)函數，在每個(gè)函數前可建立一個(gè)保留點(diǎn)，如果函數失敗，很容易返回到每一個(gè)函數開(kāi)始的情況。在回滾到一個(gè)Savepoint之后，該Savepoint之后所獲得的數據封鎖被釋放。為了實(shí)現部分回滾可以用帶TO Savepoint子句的ROLLBACK語(yǔ)句將事務(wù)回滾到指定的位置。
例
BEGIN
INSERT INTO ATM_LOG(who,when,what,where)
VALUES (‘Kiesha‘,SYSDATE,‘Withdrawal of $100‘,‘ATM54‘)
SAVEPOINT ATM_LOGGED;
UPDATE checking
SET balance=balance-100
RETURN balance INTO new_balance;
IF new_balance<0
THEN
ROLLBACK TO ATM_LOGGED;
COMMIT
RAISE insufficient_funda;
END IF
END
|
關(guān)鍵字SAVEPOINT是可選的，所以下面兩個(gè)語(yǔ)句是等價(jià)的：
ROLLBACK TO ATM_LOGGED;
ROLLBACK TO SAVEPOINT ATM_LOGGED; |
一致性和事務(wù) 一致性是事物控制的關(guān)鍵慨念。掌握了oracle 的一致性模型，能使您更好的，更恰當的使用事務(wù)控制。oracle通過(guò)一致性保證數據只有在事務(wù)全部完成后才能被用戶(hù)看見(jiàn)和使用。這項技術(shù)對多用戶(hù)數據庫有巨大的作用。
oracle常常使用語(yǔ)句級（state-level)一致性，保證數據在語(yǔ)句的生命期之間是可見(jiàn)的但不能被改變。事務(wù)由多個(gè)語(yǔ)句組成，當使用事務(wù)時(shí)，事物級（transaction-level)一致性在整個(gè)事務(wù)生命期中保證數據對所有語(yǔ)句都是可見(jiàn)的。
oracle通過(guò)SCN(syatem change number)實(shí)施一致性。一個(gè)SCN是一個(gè)面向時(shí)間的數據庫內部鍵。SCN只會(huì )增加不會(huì )減少，SCN表示了時(shí)間上的一個(gè)點(diǎn)，每個(gè)數據塊都有一個(gè)SCN,通過(guò)比較這個(gè)點(diǎn)實(shí)施操作。
事務(wù)級一致性 SET TRANSACTION 的一個(gè)作用是確保事務(wù)級一致或語(yǔ)句級一致中有一個(gè)實(shí)施。ORACLE使用這些術(shù)語(yǔ)：
ISOLATION LEVEL READ COMMIT 表示語(yǔ)句級一致
ISOLATION LEVEL SERIALIZABLE 表示事務(wù)級一致。
例：
SET TRANSACTION ISOLATION LEVEL READ COMMIT;
SET TRANSACTION ISOLATION LEVEL READ COMMIT |
下面的語(yǔ)句也能確保事務(wù)級一致：
| SET TRANSCATION READ ONLY |
任何企圖在只讀（READ ONLY）事務(wù)中修改數據的操作都會(huì )拋出一個(gè)異常。但是，READ ONLY事務(wù)只能在下列語(yǔ)句中使用：
SELECT(沒(méi)有FOR UPDATE子句）
LOCK TABLE
SET ROLE
ALTER SYSTEM
ALTER ALARM |
即使沒(méi)有改變任何數據，READ ONLY事務(wù)依然必須使用一個(gè)COMMIT或ROLLBACK以結束整個(gè)事務(wù)。
SET TRANSCTION的另外一個(gè)應用是在回滾時(shí)直接使用回滾段（ROLLBACK SEGMENT）?；貪L段是ORACLE的一個(gè)特殊的數據對象，回滾段的頭部包含正在使用該回滾段事務(wù)的信息。當用戶(hù)回滾事務(wù)（ROLLBACK）時(shí)，ORACLE將會(huì )利用回滾段中的數據前影像來(lái)將修改的數據恢復到原來(lái)的值。oracle用round-robin給事務(wù)隨機分配回滾段。一個(gè)大的事務(wù)可以分配任何回滾段，這也許會(huì )導致回滾段的大小變得很大。因此要避免讓大的事務(wù)隨機分配回滾段。
事務(wù)以SET TRANSACTION開(kāi)始,象下面這樣：
| SET TRANSACTION USE ROLLBACK SEGMENT rb_large; |
rb_large是一個(gè)大的回滾段的名稱(chēng)，現在就給一個(gè)大的事務(wù)分配了一個(gè)大的回滾段，其他的小的回滾段將不由動(dòng)態(tài)空間管理，這樣就更有效率。
下面我們看一個(gè)例子.我們有一個(gè)回滾段表空間大小是2G，在高峰時(shí)期需要10個(gè)回滾段以滿(mǎn)足用戶(hù)的需要，這些高峰在線(xiàn)用戶(hù)只有小的事務(wù)。一周我們連續運行了4個(gè)大的事務(wù)，這些事務(wù)需要刪除和加載數據，每一個(gè)撤銷(xiāo)需要1G，回滾段的大小如下：
rb_large(initial 100M minextenta 2)
rb1 (initial 1M next minextents 5)
rb2 (initial 1M next minextents 5)
rb3 (initial 1M next minextents 5)
rb4 (initial 1M next minextents 5)
rb5 (initial 1M next minextents 5)
rb6 (initial 1M next minextents 5)
rb7 (initial 1M next minextents 5)
rb8 (initial 1M next minextents 5)
rb9 (initial 1M next minextents 5)
rb10 (initial 1M next minextents 5) |
所有的都非常恰當的安排在2G的表空間中，如果我們缺省的round-robin給事務(wù)分配回滾段，4個(gè)大事務(wù)將有4個(gè)獨立的回滾段，每個(gè)回滾段的大小將是1G,如果這樣我們的2G表空間就不夠，而數據庫管理員就不得不在夜晚2點(diǎn)起來(lái)工作，每個(gè)事務(wù)都由以下面的語(yǔ)句開(kāi)始：
| SET TRANSACTION USE ROLLBACK SEGMENT rb_large |
現在 4個(gè)事務(wù)重用相同的表空間，保正4個(gè)回滾段的表空間在2G以?xún)?。數據庫管理員可以睡到天亮。
建立和修改用戶(hù)
CREATE USER 語(yǔ)句將建立一個(gè)用戶(hù)。當一個(gè)用戶(hù)連接到ORACLE數據庫時(shí)，它必須被驗證。ORACLE中驗證有三種類(lèi)型：
Database
external
Global
缺省是數據庫驗證，當用戶(hù)連接到數據庫時(shí)，oracle將檢測用戶(hù)是否是數據庫的合法用戶(hù)，并且要提供正確的password.external驗證，oracle將只檢測用戶(hù)是否是合法用戶(hù)，password已經(jīng)被網(wǎng)絡(luò )或系統驗證了。global驗證也是只檢測是否是合法用戶(hù)，password由oraclesecurity server驗證。
Database驗證用戶(hù)賬號 數據庫驗證賬號是張好的缺省類(lèi)型，也是最普通的類(lèi)型。建立一個(gè)賬號是piyush,口令是welcome的賬號，只需執行下面的命令：
| CREATE USE piyush IDENTIFIED BY welcome |
piyush可以通過(guò)下面的語(yǔ)句將口令改變?yōu)閟araswatt:
| ALTER USER piyush IDENTIFIED BY saraswati; |
外部驗證用戶(hù)賬號 用戶(hù)賬號進(jìn)入數據庫時(shí)可以不提供口令，這種情況下代替數據庫識別口令的是客戶(hù)端操作系統。外部驗證賬號有時(shí)也叫OPS$賬號，當他們最初在oracle6開(kāi)始介紹時(shí)，oracle賬號都有關(guān)鍵字前綴OPS$,這也就是為什么init.ora 參數os_authent_prefix是OPS$--默認特征與oracle6保持一致。os_authent_prefix定義的字符串必須被預處理為用于Oracle外部識別賬號的操作系統賬號名。創(chuàng )建操作系統用戶(hù)appl的語(yǔ)句是：
| CREATE USER ops$appl IDENTIFIED EATERNALLY |
但在通常情況下，os_authent_prefix將被設置為空，像下面這樣：
| CREATE USER appl IDENTIFIED EATERNALLY |
這樣效果是一樣的,關(guān)鍵字IDENTIFIED EXTERNALLY告訴ORACLE這是一個(gè)外部識別賬號。
GLOBAL用戶(hù)賬號
GLOBAL類(lèi)型的用戶(hù)賬號數據庫不檢測口令，而是由X.509目錄服務(wù)器檢測口令。創(chuàng )建一個(gè)GLOBAL類(lèi)型的用戶(hù)賬號的方法是：
| CREATE USER scott IDENTIFIED GLOBALLY AS "CN=scott,OU=divisional,O=sybex,C=US" |
關(guān)鍵字IDENTIFIED GLOBALLY AS表示建立的是一個(gè)GLOBAL類(lèi)型的用戶(hù)賬號.
創(chuàng )建和更改用戶(hù)賬號 CREATE USER 用于建立用戶(hù)賬號和給用戶(hù)賬號的屬性賦值。ALTER USER用于更改用戶(hù)賬號和屬性。但CREATE USER語(yǔ)句必須包括用戶(hù)名和口令。
有部分屬性能用CREATER USER和ALTER USER語(yǔ)句設置，下面對是這些的屬性具體描述：
給用戶(hù)分配缺省表空間
表空間（tablespace)是放置表、索引、叢等用戶(hù)對象的。如果在create user語(yǔ)句中沒(méi)有包含表空間，那么缺省的是系統表空間。
CREATE USER piyush IDENTIFIED BY saraswati
DEFAULTE TABLESPACE user_data;
ALTER USER manoj DEFAULTE TABLESPACE dev1_data; |
給用戶(hù)分配臨時(shí)表空間
臨時(shí)表空間，顧名思義是臨時(shí)存放表、索引等用戶(hù)對象的臨時(shí)段。建立方法一樣
CREATE USER piyush IDENTIFIED BY saraswati
Temporary TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev1_data; |
給用戶(hù)分配表空間的使用定額
使用定額限制用戶(hù)在表空間中使用磁盤(pán)的數量。定額可以按字節、千字節、兆字節或者無(wú)限制來(lái)制定。
CREATE USER piyush IDENTIFIED BY saraswati
DEFAULT TABLESPACE user_data
QUOTA UNLIMITED ON user_data
QUOTA 20M ON tools;
ALTER USER manoj QUOTA 2500K ON tools; |
給用戶(hù)分配一個(gè)簡(jiǎn)表
簡(jiǎn)表可以限制用戶(hù)在會(huì )話(huà)時(shí)消耗的資源。這些資源包括：連接數據庫的時(shí)間，空閑時(shí)間，每次會(huì )話(huà)的邏輯讀數據的數量等等，缺省的簡(jiǎn)表對資源無(wú)限制。
CREATE USER piyush IDENTIFIED BY saraswati
PROFILE TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev1_data; |
為用戶(hù)響應指定角色
這個(gè)屬性只能由ALTER USER語(yǔ)句設置，試圖用CREATE USER語(yǔ)句設置將回返回一個(gè)例外。
| ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm; |
為用戶(hù)的password設定到期時(shí)間以便在用戶(hù)下次登錄時(shí)更改
當用戶(hù)的password到期，在下一次登錄時(shí)將強迫修改password，oracle提示用戶(hù)輸入舊的password,然后輸入新的password。這項功能常用于新用戶(hù)，當新用戶(hù)用缺省的password登錄時(shí)必須修改立即修改password.
ALTER USER manoj IDENTIFIED BY welcome;
ALTER USER manoj PASSWORD EXPIRE; |
鎖定賬號，是用戶(hù)不能登錄
ALTER USER ql AC
COUNT LOCK |
對賬號解鎖，以便用戶(hù)能登錄數據庫
| ALTER USER ql ACCOUNT UNLOCK |
權限和角色 權限允許用戶(hù)訪(fǎng)問(wèn)屬于其它用戶(hù)的對象或執行程序，ORACLE系統提供三種權限：
Object 對象級
System 系統級
Role 角色級
這些權限可以授予給用戶(hù)、特殊用戶(hù)public或角色，如果授予一個(gè)權限給特殊用戶(hù)"Public"（用戶(hù)public是oracle預定義的，每個(gè)用戶(hù)享有這個(gè)用戶(hù)享有的權限）,那么就意味作將該權限授予了該數據庫的所有用戶(hù)。
對管理權限而言，角色是一個(gè)工具，權限能夠被授予給一個(gè)角色，角色也能被授予給另一個(gè)角色或用戶(hù)。用戶(hù)可以通過(guò)角色繼承權限，除了管理權限外角色服務(wù)沒(méi)有其它目的。權限可以被授予，也可以用同樣的方式撤銷(xiāo)。
建立和使用角色 如前所訴，角色存在的目的就是為了使權限的管理變得輕松。建立角色使用CREATE ROLE語(yǔ)句，他的語(yǔ)法如下：
CREATE ROLE role_name IDENTIFIED BY password
CREATE ROLE role_name IDENTIFIED EXTERNALLY
CREATE ROLE role_name IDENTIFIED GLOBALLY |
缺省情況下建立的角色沒(méi)有password或者其他的識別。如果使用IDENTIFIED BY 子句建立，那么角色不會(huì )自動(dòng)響應，必須用SET ROLE激活。
| SET ROLE role_name IDENTIFIED BY password |
EXTERNALLY和GLOBALLY類(lèi)型的角色由操作系統和ORACLE Service server驗證。通常用戶(hù)需要權限修改應用程序中使用的表單中的數據，但是只有在應用程序運行時(shí)而不是在使用ad hoc工具時(shí)，這種上下文敏感安全可以通過(guò)有PASSWORD的角色來(lái)實(shí)現。當用戶(hù)在應用程序內部連結數據庫時(shí)，代碼將執行SET ROLE命令，通過(guò)安全驗證。所以用戶(hù)不需要知道角色的password,也不需要自己輸入SET ROLE命令。
對象權限 對象權限就是指在表、視圖、序列、過(guò)程、函數或包等對象上執行特殊動(dòng)作的權利。有九種不同類(lèi)型的權限可以授予給用戶(hù)或角色。如下表：
| 權限 | ALTER | DELETE | EXECUTE | INDEX | INSERT | READ | REFERENCE | SELECT | UPDATE |
| Directory | no | no | no | no | no | yes | no | no | no |
| function | no | no | yes | no | no | no | no | no | no |
| procedure | no | no | yes | no | no | no | no | no | no |
| package | no | no | yes | no | no | no | no | no | no |
| DB Object | no | no | yes | no | no | no | no | no | no |
| Libary | no | no | yes | no | no | no | no | no | no |
| Operation | no | no | yes | no | no | no | no | no | no |
| Sequence | yes | no | no | no | no | no | no | no | no |
| Table | yes | yes | no | yes | yes | no | yes | yes | yes |
| Type | no | no | yes | no | no | no | no | no | no |
| View | no | yes | no | no | yes | no | no | yes | yes |
對象由不止一個(gè)權限，特殊權限ALL可以被授予或撤銷(xiāo)。如TABLE的ALL權限就包括：
SELECT,INSERT,UPDATE和DELETE,還有INDEX,ALTER,和REFERENCE。
如何看這個(gè)表我們以ALTER權限為例進(jìn)行說(shuō)明
ALTER權限
允許執行ALTER TABLE和LOCK TABLE操作，ALTER TABLE可以進(jìn)行如下操作：
. 更改表名
. 增加或刪除列
. 改變列的數據類(lèi)型或大小
. 將表轉變?yōu)榉謪^表
在SEQUENCE上的ALTER權限允許執行ALTER Sequence語(yǔ)句，重新給sequence分配最小值、增量和緩沖區大小。
系統權限 系統權限需要授予者有進(jìn)行系統級活動(dòng)的能力，如連接數據庫，更改用戶(hù)會(huì )話(huà)、建立表或建立用戶(hù)等等。你可以在數據字典視圖SYSTEM_PRIVILEGE_MAP上獲得完整的系統權限。對象權限和系統權限都通過(guò)GRANT語(yǔ)句授予用戶(hù)或角色。需要注意的是在授予對象權限時(shí)語(yǔ)句應該是WITH GRANT OPTION子句，但在授予系統權象時(shí)語(yǔ)句是WITH ADMIN OPTION，所以在你試圖授予系統權限時(shí)，使用語(yǔ)句WITH GRANT OPTION系統會(huì )報告一個(gè)錯誤：ONLY ADMIN OPTION can be specified。在考試中要特別注意這個(gè)語(yǔ)法和錯誤信息。
角色和角色權限 角色權限就是將屬于用戶(hù)的權限授予一個(gè)角色。任何權限都可以授予給一個(gè)角色。授予系統權限給被授予者必須使用WITH_ADMIN_OPTION子句，在會(huì )話(huà)期間通過(guò)SET ROLE語(yǔ)句授予或撤銷(xiāo)角色權限。然而，角色權限不能依靠存儲在SQL中的權限。如果函數、程序、包、觸發(fā)器或者方法使用另一個(gè)計劃擁有的對象，那么就必須直接給對象的擁有者授權，這是因為權限不會(huì )在會(huì )話(huà)之間改變。
授予和撤銷(xiāo)權限 給用戶(hù)或者角色授予權限使用GRANT 語(yǔ)句,GRANT語(yǔ)句的語(yǔ)法如下：
| GRANT ROLE（或system privilege） TO user(role,Public) WITH ADMIN OPTION（可選） |
對象權限被授予 WITH GRANT OPTION，
權限和數據字典 數據字典是ORACLE存儲有關(guān)數據庫結構信息的地方，數據本身存放在其他地方，數據字典由表和視圖組成。在考試中關(guān)于數據字典最容易考的內容是：查看那一類(lèi)權限已經(jīng)被授予。比如DBA_TAB_PRIV包含了用戶(hù)授予給另一用戶(hù)的對象權限和在授予時(shí)是否帶有WITH GRANT OTPION子串的信息。注意DBA_TAB_PRIV不僅僅包含了對表的權限的關(guān)系，他還包括函數、包、隊列等等上的權限的關(guān)系。下表列出了所有的權限和角色的數據字典視圖：
表： 權限的數據字典視圖
| 視圖 | 作用 |
| ALL_COL_PRIVS | 表示列上的授權，用戶(hù)和PUBLIC是被授予者 |
| ALL_COL_PRIVS_MADE | 表示列上的授權，用戶(hù)是屬主和被授予者 |
| ALL_COL_RECD | 表示列上的授權，用戶(hù)和PUBLIC是被授予者 |
| ALL_TAB_PRIVS | 表示對象上的授權，用戶(hù)是PUBLIC或被授予者或用戶(hù)是屬主 |
| ALL_TAB_PRIVS_MADE | 表示對象上的權限，用戶(hù)是屬主或授予者 |
| ALL_TAB_PRIVS_RECD | 表示對象上的權限, 用戶(hù)是PUBLIC或被授予者 |
| DBA_COL_PRIVS | 數據庫列上的所有授權 |
| DBA_ROLE_PRIVS | 顯示已授予用戶(hù)或其他角色的角色 |
| DBA_SYS_PRIVS | 已授予用戶(hù)或角色的系統權限 |
| DBA_TAB_PRIVS | 數據庫對象上的所有權限 |
| ROLE_ROLE_PRIVS | 顯示已授予用戶(hù)的角色 |
| ROLE_SYS_PRIVS | 顯示通過(guò)角色授予用戶(hù)的系統權限 |
| ROLE_TAB_PRIVS | 顯示通過(guò)角色授予用戶(hù)的對象權限 |
| SESSION_PRIVS | 顯示用戶(hù)現在可利用的所有系統權限 |
| USER_COL_PRIVS | 顯示列上的權限，用戶(hù)是屬主、授予者或被授予者 |
| USER_COL_PRIVS_MADE | 顯示列上已授予的權限，用戶(hù)是屬主或授予者 |
| USER_COL_PRIVS_RECD | 顯示列上已授予的權限，用戶(hù)是屬主或被授予者 |
| USER_ROLE_PRIVS | 顯示已授予給用戶(hù)的所有角色 |
| USER_SYS_PRIVS | 顯示已授予給用戶(hù)的所有系統權限 |
| USER_TAB_PRIVS | 顯示已授予給用戶(hù)的所有對象權限 |
| USER_TAB_PRIVS_MADE | 顯示已授予給其他用戶(hù)的對象權限，用戶(hù)是屬主 |
| USER_TAB_PRIVS_RECD | 顯示已授予給其他用戶(hù)的對象權限，用戶(hù)是被授予者 |
