Oracle數據庫數據操作和控制語言詳解

　　SQL語言共分為四大類數據查詢語言DQL數據操縱語言DML 數據定義語言DDL數據控制語言DCL其中用於定義數據的結構比如 創建修改或者刪除數據庫DCL用於定義數據庫用戶的權限在這篇文章中我將詳細講述這兩種語言在Oracle中的使用方法

　　DML語言

　　DML是SQL的一個子集主要用於修改數據下表列出了ORACLE支持的DML語句

語句 用途 INSERT 向表中添加行 UPDATE 更新存儲在表中的數據 DELETE 刪除行 SELECT FOR UPDATE 禁止其他用戶訪問DML語句正在處理的行 LOCK TABLE 禁止其他用戶在表中使用DML語句

　　插入數據

　　INSERT語句常常用於向表中插入行行中可以有特殊數據字段或者可以用子查詢從已存在的數據中建立新行

　　列目錄是可選的缺省的列的目錄是所有的列名包括comlumn_idcomlumn_id可以在數據字典視圖ALL_TAB_COLUMNSUSER_TAB_COLUMNS或者DBA_TAB_COLUMNS中找到

　　插入行的數據的數量和數據類型必須和列的數量和數據類型相匹配不符合列定義的數據類型將對插入值實行隱式數據轉換NULL字符串將一個NULL值插入適當的列中關鍵字NULL常常用於表示將某列定義為NULL值

　　下面的兩個例子是等價的
以下為引用的內容
INSERT INTO customers(cust_idstatepost_code)
VALUE(ArielNULL);

　　或
以下為引用的內容
INSERT INTO customers(cust_idstatepost_code)
VALUE(Ariel); 　　更新數據

　　UPDATE命令用於修改表中的數據 以下為引用的內容
UPDATE order_rollup
SET(qtyprice)=(SELECT SUM(qty)SUM(price) FROM order_lines WHERE customer_id=KOHL
WHERE cust_id=KOHL
AND order_period=TO_DATE(Oct)
刪除數據

　　DELETE語句用來從表中刪除一行或多行數據該命令包含兩個語句

　　　關鍵字DELETE FROM後跟准備從中刪除數據的表名

　　　WHERE後跟刪除條件
以下為引用的內容
DELETE FROM po_lines
WHERE ship_to_state IN (TXNYIL)
AND order_date 　　清空表

　　如果你想刪除表中所有數據清空表可以考慮使用DDL語言的TRUNCATE語句TRUNCATE就像沒有WHERE子句的DELETE命令一樣TRUNCATE將刪除表中所有行TRUNCATE不是DML語句是DDL語句他和DELETE右不同的特點 以下為引用的內容
TRUNCATE TABLE (schema)table DROP(REUSE) STORAGE
　　STORAGE子串是可選的缺省是DROP STORAGE當使用DROP STORAGE時將縮短表和表索引將表收縮到最小范圍並重新設置NEXT參數REUSE STORAGE不會縮短表或者調整NEXT參數

　　TRUNCATE和DELETE有以下幾點區別

　　TRUNCATE在各種表上無論是大的還是小的都非常快如果有ROLLBACK命令DELETE將被撤銷而TRUNCATE則不會被撤銷

　　TRUNCATE是一個DDL語言向其他所有的DDL語言一樣他將被隱式提交不能對TRUNCATE使用ROLLBACK命令

　　TRUNCATE將重新設置高水平線和所有的索引在對整個表和索引進行完全浏覽時經過TRUNCATE操作後的表比DELETE操作後的表要快得多

　　TRUNCATE不能觸發任何DELETE觸發器

　　不能授予任何人清空他人的表的權限

　　當表被清空後表和表的索引講重新設置成初始大小而delete則不能

　　不能清空父表

　　SELECT FOR UPDATE

　　select for update語句用於鎖定行阻止其他用戶在該行上修改數據當該行被鎖定後其他用戶可以用SELECT語句查詢該行的數據但不能修改或鎖定該行

　　鎖定表

　　LOCK語句常常用於鎖定整個表當表被鎖定後大多數DML語言不能在該表上使用LOCK語法如下
以下為引用的內容
LOCK schema table IN lock_mode 　其中lock_mode有兩個選項

　　　share 共享方式

　　　exclusive 唯一方式

　　例
以下為引用的內容
LOCK TABLE intentory IN EXCLUSIVE MODE

　　死鎖

　　當兩個事務都被鎖定並且互相都在等待另一個被解鎖這種情況稱為死鎖

　　當出現死鎖時ORACLE將檢測死鎖條件並返回一個異常

　　事務控制

　　事務控制包括協調對相同數據的多個同步的訪問當一個用戶改變了另一個用戶正在使用的數據時oracle使用事務控制誰可以操作數據

　　事務

　　事務表示工作的一個基本單元是一系列作為一個單元被成功或不成功操作的SQL語句在SQL和PL/SQL中有很多語句讓程序員控制事務程序員可以

　　　顯式開始一個事物選擇語句級一致性或事務級一致性

　　　設置撤銷回滾點並回滾到回滾點
　
　　　完成事務永遠改變數據或者放棄修改
　　
　　事務控制語句

　　 語句 用途 Commit 完成事務數據修改成功並對其他用戶開放 Rollback 撤銷事務撤銷所有操作 rollback to savepoint 撤銷在設置的回滾點以後的操作 set transaction 響應事務或語句的一致性特別對於事務使用回滾段

　　 以下為引用的內容
BEGIN
UPDATE checking
SET balance=balance
WHERE account=Kieesha;

INSERT INTO checking_log(action_dateactionamount)
VALUES (SYSDATETransfer to brokerage);

UPDATE brokerage
SET cash_balance=cash_balance+
WHERE account=Kiesha;

INSERT INTO brokerage_log(action_dateactionamount)
VALUES (SYSDATETracfer from checking)

COMMIT

EXCEPTION
WHEN OTHERS
ROLLBACK

END

　　Savepoint 和 部分回滾(Partial Rollback)

　　在SQL和PL/SQL中Savepoint是在一事務范圍內的中間標志經常用於將一個長的事務劃分為小的部分保留點Savepoint可標志長事務中的任何點允許可回滾該點之後的操作在應用程序中經常使用Savepoint例如一過程包含幾個函數在每個函數前可建立一個保留點如果函數失敗很容易返回到每一個函數開始的情況在回滾到一個Savepoint之後該Savepoint之後所獲得的數據封鎖被釋放為了實現部分回滾可以用帶TO Savepoint子句的ROLLBACK語句將事務回滾到指定的位置

　　例

　　 以下為引用的內容
BEGIN

INSERT INTO ATM_LOG(whowhenwhatwhere)
VALUES (KieshaSYSDATEWithdrawal of $ATM)
SAVEPOINT ATM_LOGGED;

UPDATE checking
SET balance=balance
RETURN balance INTO new_balance;

IF new_balance<
THEN
ROLLBACK TO ATM_LOGGED;
COMMIT
RAISE insufficient_funda;
END IF

END

　　關鍵字SAVEPOINT是可選的所以下面兩個語句是等價的

　　 以下為引用的內容
ROLLBACK TO ATM_LOGGED;
ROLLBACK TO SAVEPOINT ATM_LOGGED;

　　一致性和事務

　　一致性是事物控制的關鍵慨念掌握了oracle 的一致性模型能使您更好的更恰當的使用事務控制oracle通過一致性保證數據只有在事務全部完成後才能被用戶看見和使用這項技術對多用戶數據庫有巨大的作用

　　oracle常常使用語句級（statelevel)一致性保證數據在語句的生命期之間是可見的但不能被改變事務由多個語句組成當使用事務時事物級（transactionlevel)一致性在整個事務生命期中保證數據對所有語句都是可見的

　　oracle通過SCN(syatem change number)實施一致性一個SCN是一個面向時間的數據庫內部鍵SCN只會增加不會減少SCN表示了時間上的一個點每個數據塊都有一個SCN通過比較這個點實施操作

　　事務級一致性

　　SET TRANSACTION 的一個作用是確保事務級一致或語句級一致中有一個實施ORACLE使用這些術語

　　　ISOLATION LEVEL READ COMMIT 表示語句級一致

　　　ISOLATION LEVEL SERIALIZABLE 表示事務級一致

　　例

　　 以下為引用的內容
SET TRANSACTION ISOLATION LEVEL READ COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMIT

　　下面的語句也能確保事務級一致

　　 以下為引用的內容
SET TRANSCATION READ ONLY

　　任何企圖在只讀（READ ONLY）事務中修改數據的操作都會拋出一個異常但是READ ONLY事務只能在下列語句中使用

　　 以下為引用的內容
SELECT(沒有FOR UPDATE子句）
LOCK TABLE
SET ROLE
ALTER SYSTEM
ALTER ALarm

　　即使沒有改變任何數據READ ONLY事務依然必須使用一個COMMIT或ROLLBACK以結束整個事務

　　SET TRANSCTION的另外一個應用是在回滾時直接使用回滾段（ROLLBACK SEGMENT）回滾段是ORACLE的一個特殊的數據對象回滾段的頭部包含正在使用該回滾段事務的信息當用戶回滾事務（ROLLBACK）時ORACLE將會利用回滾段中的數據前影像來將修改的數據恢復到原來的值oracle用roundrobin給事務隨機分配回滾段一個大的事務可以分配任何回滾段這也許會導致回滾段的大小變得很大因此要避免讓大的事務隨機分配回滾段

　　事務以SET TRANSACTION開始象下面這樣

　　 以下為引用的內容
SET TRANSACTION USE ROLLBACK SEGMENT rb_large;

　　rb_large是一個大的回滾段的名稱現在就給一個大的事務分配了一個大的回滾段其他的小的回滾段將不由動態空間管理這樣就更有效率

　　下面我們看一個例子我們有一個回滾段表空間大小是G在高峰時期需要個回滾段以滿足用戶的需要這些高峰在線用戶只有小的事務一周我們連續運行了個大的事務這些事務需要刪除和加載數據每一個撤銷需要G回滾段的大小如下

　　 以下為引用的內容
rb_large(initial M minextenta )

rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )
rb (initial M next minextents )

　　所有的都非常恰當的安排在G的表空間中如果我們缺省的roundrobin給事務分配回滾段個大事務將有個獨立的回滾段每個回滾段的大小將是G如果這樣我們的G表空間就不夠而數據庫管理員就不得不在夜晚點起來工作每個事務都由以下面的語句開始

　　 以下為引用的內容
SET TRANSACTION USE ROLLBACK SEGMENT rb_large

　　現在 個事務重用相同的表空間保正個回滾段的表空間在G以內數據庫管理員可以睡到天亮

　　建立和修改用戶

　　CREATE USER 語句將建立一個用戶當一個用戶連接到ORACLE數據庫時它必須被驗證ORACLE中驗證有三種類型

　　　Database

　　　external

　　　Global

　　缺省是數據庫驗證當用戶連接到數據庫時oracle將檢測用戶是否是數據庫的合法用戶並且要提供正確的passwordexternal驗證oracle將只檢測用戶是否是合法用戶password已經被網絡或系統驗證了global驗證也是只檢測是否是合法用戶password由oraclesecurity server驗證

　　Database驗證用戶賬號

　　數據庫驗證賬號是張好的缺省類型也是最普通的類型建立一個賬號是piyush口令是welcome的賬號只需執行下面的命令

　　 以下為引用的內容
CREATE USE piyush IDENTIFIED BY welcome

　　piyush可以通過下面的語句將口令改變為saraswatt:

　　 以下為引用的內容
　piyush可以通過下面的語句將口令改變為saraswatt:

　　外部驗證用戶賬號

　　用戶賬號進入數據庫時可以不提供口令這種情況下代替數據庫識別口令的是客戶端操作系統外部驗證賬號有時也叫OPS$賬號當他們最初在oracle開始介紹時oracle賬號都有關鍵字前綴OPS$這也就是為什麼initora 參數os_authent_prefix是OPS$默認特征與oracle保持一致os_authent_prefix定義的字符串必須被預處理為用於Oracle外部識別賬號的操作系統賬號名創建操作系統用戶appl的語句是

　　 以下為引用的內容
CREATE USER ops$appl IDENTIFIED EATERNALLY

　　但在通常情況下os_authent_prefix將被設置為空像下面這樣

　　 以下為引用的內容
CREATE USER appl IDENTIFIED EATERNALLY

　　這樣效果是一樣的關鍵字IDENTIFIED EXTERNALLY告訴ORACLE這是一個外部識別賬號

　　GLOBAL用戶賬號

　　GLOBAL類型的用戶賬號數據庫不檢測口令而是由X目錄服務器檢測口令創建一個GLOBAL類型的用戶賬號的方法是

　　 以下為引用的內容
CREATE USER scott IDENTIFIED GLOBALLY AS CN=scottOU=divisionalO=sybexC=US

　　關鍵字IDENTIFIED GLOBALLY AS表示建立的是一個GLOBAL類型的用戶賬號

　　創建和更改用戶賬號

　　CREATE USER 用於建立用戶賬號和給用戶賬號的屬性賦值ALTER USER用於更改用戶賬號和屬性但CREATE USER語句必須包括用戶名和口令

　　有部分屬性能用CREATER USER和ALTER USER語句設置下面對是這些的屬性具體描述

　　給用戶分配缺省表空間

　　表空間（tablespace)是放置表索引叢等用戶對象的如果在create user語句中沒有包含表空間那麼缺省的是系統表空間

　　 以下為引用的內容
CREATE USER piyush IDENTIFIED BY saraswati
DEFAULTE TABLESPACE user_data;
ALTER USER manoj DEFAULTE TABLESPACE dev_data;

　　給用戶分配臨時表空間

　　臨時表空間顧名思義是臨時存放表索引等用戶對象的臨時段建立方法一樣

　　 以下為引用的內容
CREATE USER piyush IDENTIFIED BY saraswati
Temporary TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev_data;

　　給用戶分配表空間的使用定額

　　使用定額限制用戶在表空間中使用磁盤的數量定額可以按字節千字節兆字節或者無限制來制定

　　 以下為引用的內容
CREATE USER piyush IDENTIFIED BY saraswati
DEFAULT TABLESPACE user_data
QUOTA UNLIMITED ON user_data
QUOTA M ON tools;
ALTER USER manoj QUOTA K ON tools;

　　給用戶分配一個簡表

　　簡表可以限制用戶在會話時消耗的資源這些資源包括連接數據庫的時間空閒時間每次會話的邏輯讀數據的數量等等缺省的簡表對資源無限制

　　 以下為引用的內容
CREATE USER piyush IDENTIFIED BY saraswati
PROFILE TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev_data

　　為用戶響應指定角色

　　這個屬性只能由ALTER USER語句設置試圖用CREATE USER語句設置將回返回一個例外

　　 以下為引用的內容
ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm;

　　為用戶的password設定到期時間以便在用戶下次登錄時更改

　　當用戶的password到期在下一次登錄時將強迫修改passwordoracle提示用戶輸入舊的password然後輸入新的password這項功能常用於新用戶當新用戶用缺省的password登錄時必須修改立即修改password

　　 以下為引用的內容
ALTER USER manoj IDENTIFIED BY welcome;
ALTER USER manoj PASSWORD EXPIRE;

　　鎖定賬號是用戶不能登錄

　　 以下為引用的內容
ALTER USER ql AC
COUNT LOCK

　　對賬號解鎖以便用戶能登錄數據庫

　　 以下為引用的內容
ALTER USER ql ACCOUNT UNLOCK

　　權限和角色

　　權限允許用戶訪問屬於其它用戶的對象或執行程序ORACLE系統提供三種權限

　　　Object 對象級

　　　System 系統級

　　　Role 角色級

　　這些權限可以授予給用戶特殊用戶public或角色如果授予一個權限給特殊用戶Public（用戶public是oracle預定義的每個用戶享有這個用戶享有的權限）那麼就意味作將該權限授予了該數據庫的所有用戶

　　對管理權限而言角色是一個工具權限能夠被授予給一個角色角色也能被授予給另一個角色或用戶用戶可以通過角色繼承權限除了管理權限外角色服務沒有其它目的權限可以被授予也可以用同樣的方式撤銷

　　建立和使用角色

　　如前所訴角色存在的目的就是為了使權限的管理變得輕松建立角色使用CREATE ROLE語句他的語法如下
以下為引用的內容
CREATE ROLE role_name IDENTIFIED BY password
CREATE ROLE role_name IDENTIFIED EXTERNALLY
CREATE ROLE role_name IDENTIFIED GLOBALLY 　　缺省情況下建立的角色沒有password或者其他的識別如果使用IDENTIFIED BY 子句建立那麼角色不會自動響應必須用SET ROLE激活
以下為引用的內容
SET ROLE role_name IDENTIFIED BY password 　　EXTERNALLY和GLOBALLY類型的角色由操作系統和ORACLE Service server驗證通常用戶需要權限修改應用程序中使用的表單中的數據但是只有在應用程序運行時而不是在使用ad hoc工具時這種上下文敏感安全可以通過有PASSWORD的角色來實現當用戶在應用程序內部連結數據庫時代碼將執行SET ROLE命令通過安全驗證所以用戶不需要知道角色的password也不需要自己輸入SET ROLE命令

　　對象權限

　　對象權限就是指在表視圖序列過程函數或包等對象上執行特殊動作的權利有九種不同類型的權限可以授予給用戶或角色如下表

權限 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
　　對象由不止一個權限特殊權限ALL可以被授予或撤銷如TABLE的ALL權限就包括

　　　SELECTINSERTUPDATE和DELETE還有INDEXALTER和REFERENCE

　　如何看這個表我們以ALTER權限為例進行說明

　　ALTER權限

　　允許執行ALTER TABLE和LOCK TABLE操作ALTER TABLE可以進行如下操作

　　　　 更改表名

　　　　 增加或刪除列

　　　　 改變列的數據類型或大小

　　　　 將表轉變為分區表

　　在SEQUENCE上的ALTER權限允許執行ALTER Sequence語句重新給sequence分配最小值增量和緩沖區大小

　　系統權限

　　系統權限需要授予者有進行系統級活動的能力如連接數據庫更改用戶會話建立表或建立用戶等等你可以在數據字典視圖SYSTEM_PRIVILEGE_MAP上獲得完整的系統權限對象權限和系統權限都通過GRANT語句授予用戶或角色需要注意的是在授予對象權限時語句應該是WITH GRANT OPTION子句但在授予系統權象時語句是WITH ADMIN OPTION所以在你試圖授予系統權限時使用語句WITH GRANT OPTION系統會報告一個錯誤ONLY ADMIN OPTION can be specified在考試中要特別注意這個語法和錯誤信息

　　角色和角色權限

　　角色權限就是將屬於用戶的權限授予一個角色任何權限都可以授予給一個角色授予系統權限給被授予者必須使用WITH_ADMIN_OPTION子句在會話期間通過SET ROLE語句授予或撤銷角色權限然而角色權限不能依靠存儲在SQL中的權限如果函數程序包觸發器或者方法使用另一個計劃擁有的對象那麼就必須直接給對象的擁有者授權這是因為權限不會在會話之間改變

　　授予和撤銷權限

　　　給用戶或者角色授予權限使用GRANT 語句GRANT語句的語法如下
以下為引用的內容
GRANT ROLE（或system privilege） TO user(rolePublic) WITH ADMIN OPTION（可選） 　　對象權限被授予 WITH GRANT OPTION

　　權限和數據字典

　　數據字典是ORACLE存儲有關數據庫結構信息的地方數據本身存放在其他地方數據字典由表和視圖組成在考試中關於數據字典最容易考的內容是查看那一類權限已經被授予比如DBA_TAB_PRIV包含了用戶授予給另一用戶的對象權限和在授予時是否帶有WITH GRANT OTPION子串的信息注意DBA_TAB_PRIV不僅僅包含了對表的權限的關系他還包括函數包隊列等等上的權限的關系下表列出了所有的權限和角色的數據字典視圖

　　表 權限的數據字典視圖

視圖 作用 ALL_COL_PRIVS 表示列上的授權用戶和PUBLIC是被授予者 ALL_COL_PRIVS_MADE 表示列上的授權用戶是屬主和被授予者 ALL_COL_RECD 表示列上的授權用戶和PUBLIC是被授予者 ALL_TAB_PRIVS 表示對象上的授權用戶是PUBLIC或被授予者或用戶是屬主 ALL_TAB_PRIVS_MADE 表示對象上的權限用戶是屬主或授予者 ALL_TAB_PRIVS_RECD 表示對象上的權限 用戶是PUBLIC或被授予者 DBA_COL_PRIVS 數據庫列上的所有授權 DBA_ROLE_PRIVS 顯示已授予用戶或其他角色的角色 DBA_SYS_PRIVS 已授予用戶或角色的系統權限 DBA_TAB_PRIVS 數據庫對象上的所有權限 ROLE_ROLE_PRIVS 顯示已授予用戶的角色 ROLE_SYS_PRIVS 顯示通過角色授予用戶的系統權限 ROLE_TAB_PRIVS 顯示通過角色授予用戶的對象權限 SESSION_PRIVS 顯示用戶現在可利用的所有系統權限 USER_COL_PRIVS 顯示列上的權限用戶是屬主授予者或被授予者 USER_COL_PRIVS_MADE 顯示列上已授予的權限用戶是屬主或授予者 USER_COL_PRIVS_RECD 顯示列上已授予的權限用戶是屬主或被授予者 USER_ROLE_PRIVS 顯示已授予給用戶的所有角色 USER_SYS_PRIVS 顯示已授予給用戶的所有系統權限 USER_TAB_PRIVS 顯示已授予給用戶的所有對象權限 USER_TAB_PRIVS_MADE 顯示已授予給其他用戶的對象權限用戶是屬主 USER_TAB_PRIVS_RECD 顯示已授予給其他用戶的對象權限用戶是被授予者

From:http://tw.wingwit.com/Article/program/Oracle/201311/17424.html