Oracle資料操作和控制語言詳解

正在看的ORACLE教程是:Oracle資料操作和控制語言詳解。

SQL語言共分為四大類：資料查詢語言DQL，資料操縱語言DML， 資料定義語言 (Data Definition Language)DDL，資料控制語言DCL。其中用於定義資料的結構，比如 建立、修改或者刪除資料庫；DCL用於定義資料庫使用者的許可權；在這篇文章中我將詳細講述這兩種語言在Oracle中的使用方法。

　　DML語言

　　DML是SQL的一個子集，主要用於修改資料，下表列出了ORACLE支援的DML語句。

　　插入資料

　　INSERT語句常常用於向表中插入行，行中可以有特殊資料欄位，或者可以用子查詢從已存在的資料中建立新行。

　　列目錄是可選的，預設的列的目錄是所有的列名，包括comlumn_id,comlumn_id可以在資料字典視圖ALL_TAB_COLUMNS,USER_TAB_COLUMNS,或者DBA_TAB_COLUMNS中找到。

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

　　下面的兩個例子是等價的。

　　或

　　更新資料

　　UPDATE命令用於修改表中的資料。

　　刪除資料

　　DELETE語句用來從表中刪除一行或多行資料，該命令包含兩個語句：

　　　1、關鍵字DELETE FROM後跟準備從中刪除資料的表名。

　　　2、WHERE後跟刪除條件

　　清空表

　　如果你想刪除表中所有資料，清空表，可以考慮使用DDL語言的TRUNCATE語句。TRUNCATE就像沒有WHERE子句的DELETE命令一樣。TRUNCATE將刪除表中所有行。TRUNCATE不是DML語句是DDL語句，他和DELETE右不同的特點。

　　STORAGE子串是可選的，預設是DROP STORAGE。當使用DROP STORAGE時將縮短表和表索引，將表收縮到最小範圍，並重新設定NEXT參數。REUSE STORAGE不會縮短表或者調整NEXT參數。

　　TRUNCATE和DELETE有以下幾點區別

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

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

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

　　4、TRUNCATE不能觸發任何DELETE觸發器。

　　5、不能授予任何人清空他人的表的許可權。

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

　　7、不能清空父表。

　　SELECT FOR UPDATE

　　select for update語句用於鎖定行，阻止其他使用者在該行上修改資料。當該行被鎖定後其他使用者可以用SELECT語句查詢該行的資料，但不能修改或鎖定該行。

　　鎖定表

　　LOCK語句常常用於鎖定整個表。當表被鎖定後，大多數DML語言不能在該表上使用。LOCK文法如下：

　　其中lock_mode有兩個選項：

　　　share 共用方式

　　　exclusive 唯一方式

　　例：

　　死結

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

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

[NextPage]

事務控制

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

　　事務

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

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

　　　2、設定撤銷復原點，並復原到復原點
　
　　　3、完成事務永遠改變資料或者放棄修改。
　　
　　事務控制語句

　　例：

　　Savepoint 和 部分復原(Partial Rollback)

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

　　例

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

　　一致性和事務

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

　　oracle常常使用語句級（state-level)一致性，保證資料在語句的生命期之間是可見的但不能被改變。事務由多個語句組成，當使用事務時，事物級（transaction-level)一致性在整個事務生命期中保證資料對所有語句都是可見的。

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

　　事務級一致性

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

　　　ISOLATION LEVEL READ COMMIT 表示語句級一致

　　　ISOLATION LEVEL SERIALIZABLE 表示事務級一致。

　　例：

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

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

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

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

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

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

　　下面我們看一個例子.我們有一個復原段資料表空間大小是2G，在高峰時期需要10個復原段以滿足使用者的需要，這些高峰線上使用者只有小的事務。一周我們連續運行了4個大的事務，這些事務需要刪除和載入資料，每一個撤銷需要1G，復原段的大小如下：

　　所有的都非常恰當的安排在

[1] [2] [3] 下一頁

正在看的ORACLE教程是:Oracle資料操作和控制語言詳解。2G的資料表空間中，如果我們預設的round-robin給事務分配復原段，4個大事務將有4個獨立的復原段，每個復原段的大小將是1G,如果這樣我們的2G資料表空間就不夠，而資料庫管理員就不得不在夜晚2點起來工作，每個事務都由以下面的語句開始：

　　現在 4個事務重用相同的資料表空間，保正4個復原段的資料表空間在2G以內。資料庫管理員可以睡到天亮。

[NextPage]

建立和修改使用者

　　CREATE USER 語句將建立一個使用者。當一個使用者串連到ORACLE資料庫時，它必須被驗證。ORACLE中驗證有三種類型：

　　　Database

　　　external

　　　Global

　　預設是資料庫驗證，當使用者串連到資料庫時，oracle將檢測使用者是否是資料庫的合法使用者，並且要提供正確的password.external驗證，oracle將只檢測使用者是否是合法使用者，password已經被網路或系統驗證了。global驗證也是只檢測是否是合法使用者，password由oraclesecurity server驗證。

　　Database驗證使用者帳號

　　資料庫驗證帳號是張好的預設類型，也是最普通的類型。建立一個帳號是piyush,口令是welcome的帳號，只需執行下面的命令：

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

　　外部驗證使用者帳號

　　使用者帳號進入資料庫時可以不提供口令，這種情況下代替資料庫識別口令的是用戶端作業系統。外部驗證帳號有時也叫OPS$帳號，當他們最初在oracle6開始介紹時，oracle帳號都有關鍵字首碼OPS$,這也就是為什麼init.ora 參數os_authent_prefix是OPS$--預設特徵與oracle6保持一致。os_authent_prefix定義的字串必須被預先處理為用於Oracle外部識別帳號的作業系統帳號名。建立作業系統使用者appl的語句是：

　　但在通常情況下，os_authent_prefix將被設定為空白，像下面這樣：

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

　　GLOBAL使用者帳號

　　GLOBAL類型的使用者帳號資料庫不檢測口令，而是由X.509目錄伺服器檢測口令。建立一個GLOBAL類型的使用者帳號的方法是：

　　關鍵字IDENTIFIED GLOBALLY AS表示建立的是一個GLOBAL類型的使用者帳號.

　　建立和更改使用者帳號

　　CREATE USER 用於建立使用者帳號和給使用者帳號的屬性賦值。ALTER USER用於更改使用者帳號和屬性。但CREATE USER語句必須包括使用者名稱和口令。

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

　　給使用者指派預設資料表空間

　　資料表空間（tablespace)是放置表、索引、叢等使用者物件的。如果在create user語句中沒有包含資料表空間，那麼預設的是系統資料表空間。

　　給使用者指派暫存資料表空間

　　暫存資料表空間，顧名思義是臨時存放表、索引等使用者物件的臨時段。建立方法一樣

　　給使用者指派資料表空間的使用定額

　　使用定額限制使用者在資料表空間中使用磁碟的數量。定額可以按位元組、KB、MB或者無限制來制定。

　　給使用者指派一個簡表

　　簡表可以限制使用者在會話時消耗的資源。這些資源套件括：串連資料庫的時間，空閑時間，每次會話的邏輯讀資料的數量等等，預設的簡表對資源無限制。

　　為使用者響應指定角色

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

　　為使用者的password設定到期時間以便在使用者下次登入時更改

　　當使用者的password到期，在下一次登入時將強迫修改password，oracle提示使用者輸入舊的password,然後輸入新的password。這項功能常用於新使用者，當新使用者用預設的password登入時必須修改立即修改password.

　　鎖定帳號，是使用者不能登入

　　對帳號解鎖，以便使用者能登入資料庫

　　許可權和角色

　　許可權允許使用者訪問屬於其它使用者的對象或執行程式，ORACLE系統提供三種許可權：

　　　Object 對象級

　　　System 系統級

　　　Role 角色級

　　這些許可權可以授予給使用者、特殊使用者public或角色，如果授予一個許可權給特殊使用者"Public"（使用者public是oracle預定義的，每個使用者享有這個使用者享有的許可權）,那麼就意味作將該許可權授予了該資料庫的所有使用者。

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

　　建立和使用角色

　　如前所訴，角色存在的目的就是為了使許可權的管理變得輕鬆。建立角色使用CREATE ROLE語句，他的文法如下：

　　預設情況下建立的角色沒有password或者其他的識別。如果使用IDENTIFIED BY 子句建立，那麼角色不會自動響應，必須用SET ROLE啟用。

　　EXTERNALLY和GLOBALLY類型的角色由作業系統和ORACLE Service server驗證。通常使用者需要許可權修改應用程式中使用的表單中的資料，但是只有在應用程式運行時而不是在使用ad hoc工具時，這種上下文敏感安全可以通過有PASSWORD的角色來實現。當使用者在應用程式內部連結資料庫時，代碼將執行SET ROLE命令，通過安全驗證。所以使用者不需要知道角色的password,也不需要自己輸入SET ROLE命令。

　　對象許可權

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

　　對象由不止一個許可權，特殊許可權ALL可以被授予或撤銷。如TABLE的ALL許可權就包括：

　　　SELECT,INSERT,UPDATE和DELETE,還有INDEX,ALTER,和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子句，在會話期間

上一頁 [1] [2] [3] 下一頁

正在看的ORACLE教程是:Oracle資料操作和控制語言詳解。通過SET ROLE語句授予或撤銷角色許可權。然而，角色許可權不能依靠儲存在SQL中的許可權。如果函數、程式、包、觸發器或者方法使用另一個計劃擁有的對象，那麼就必須直接給對象的擁有者授權，這是因為許可權不會在會話之間改變。

　　授予和撤銷許可權

　　　給使用者或者角色授予許可權使用GRANT 語句,GRANT語句的文法如下：

　　對象許可權被授予 WITH GRANT OPTION，

　　許可權和資料字典

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

　　表： 許可權的資料字典視圖

上一頁

上一頁 [1] [2] [3]