Oracle學習筆記九 資料庫物件

標籤：

Oracle 資料庫物件又稱模式對象，資料庫物件是邏輯結構的集合，最基本的資料庫物件是表。其他資料庫物件包括：   同義字 同義字是現有對象的一個別名。它的作用有：　　1.簡化SQL語句　　2.隱藏對象的名稱和所有者(隱藏原始對象內容)　　3.提供對對象的公用訪問  同義字共有兩種類型：  

公有同義字可被所有的資料庫使用者訪問。

私人同義字只能在其模式內訪問，且不能與當前模式的對象同名。

 建立或替換現有同義字

create or replace synonym emp_syn for scott.emp;

 

刪除同義字

drop synonym emp;drop public synonym emp_syn

 

 使用同義字

Create public synonym Newer for scott.emp2;Select * from Newer ;

 序列序列是用於產生唯一、連續序號的對象，序列可以是升序的，也可以是降序的，可以用於表示資料的數量。我們可以使用CREATE SEQUENCE語句建立序列。

Create sequence 序列名    Start with 1             --指序列開始大小，預設為1    Increment by 1           --每次的增量，預設為1    Maxvalue 2000            --序列的最大數    Minvalue 1               --序列的最小數    Nocycle                  --當序列最大了將不再產生整數（預設）    Cache 10;                 --保留在記憶體中整數的個數，預設20    

 注意：序列在使用的時候，如果給的是主鍵，為防止出現不連續的情況，應該設定為NOCACHE。

 

從user_sequences中查出所有的序列資訊。

select * from user_sequences;

 

通過序列的偽列來訪問序列的值1.NEXTVAL 返回序列的下一個值2.CURRVAL 返回序列的當前值

注意：序列在使用的時候，如果給的是主鍵，為防止出現不連續的情況，應該設定為NOCACHE。

INSERT INTO toys (toyid, toyname, toyprice)      VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25);INSERT INTO toys (toyid, toyname, toyprice)      VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’, 75);--toys_seq.NEXTVAL  指定序列的下一個值

 

SELECT toys_seq.CURRVAL FROM dual;  --檢索序列的當前值

 

使用ALTER SEQUENCE語句修改序列，不能更改序列的START WITH參數

ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

 

使用DROP SEQUENCE語句刪除序列

DROP SEQUENCE toys_seq;

 

視圖視圖以經過定製的方式顯示來自一個或多個表的資料，視圖可以視為“虛擬表”或“儲存的查詢”，建立視圖所依據的表稱為“基表”。 視圖的優點有：1.提供了另外一種層級的表安全性2.隱藏的資料的複雜性3.簡化的使用者的SQL命令4.隔離基表結構的改變5.通過重新命名列，從另一個角度提供資料 例：  

CREATE VIEW stud_view AS SELECT studno, studname, subno FROM Stud_details;

 通過這個視圖，查詢到的結果：

 建立視圖建立視圖的文法：

 CREATE [OR REPLACE] [FORCE] VIEW    view_name [(alias[, alias]...)]   AS select_statement  [WITH CHECK OPTION][CONSTRAINT C_NAME]  [WITH READ ONLY];  --禁止對錶進行更新刪除操作--W ITH CHECK OPTION CONSTRAINT _NAME ：表示增加一個檢查約束，INSERT和UPDATE時候必須要求: --INSERT時候要求插入的資料必須符合定義時候設定的where條件一致，UPDATE 時候只能改除where條件列以外的其他資料列或者修改的條件列的值符合where條件--

 

使用 WITH CHECK OPTION 選項建立視圖

create or replace view pause_view as　　select * from order_master WHERE ostatus = ‘p‘　　with check option constaint chk_pv;

 

使用 ORDER BY 子句建立視圖

 

create or replace view ord_ven as　　select * from vendor_master order by venname;

 

 

聯結視圖:兩個表的結果

create view Stud_sub_view as    select Studno, Studname, Submrks, Subname    from Stud_details, Sub_Details     where Stud_details.Subno=Sub_details.Subno;

 

 建立外聯結視圖

CREATE VIEW ven_ord_outj_view ASSELECT vm.vencode, venname, orderno, odate, ostatusFROM   vendor_master vm, order_master omWHERE  vm.vencode = om.vencode(+);

 等同於

SELECT vm.vencode, venname, orderno, odate, ostatusFROM vendor_master vm LEFT OUTER JOIN order_master omON vm.vencode = om.vencode;

 

 視圖上的DML語句在視圖上也可以使用修改資料的DML語句，如INSERT、UPDATE和DELETE 視圖上的DML語句有如下限制：1.只能修改一個底層的基表2.如果修改違反了基表的約束條件，則無法更新視圖3.如果視圖包含串連操作符、DISTINCT 關鍵字、集合操作符、彙總函式或 GROUP BY 子句，則將無法更新視圖4.如果視圖包含偽列或運算式，則將無法更新視圖 如果往視圖中添加資料，資料可以添加到基表，但如果插入的資料不符合視圖的where等條件的要求，則視圖中不會顯示。如果希望插入的資料要符合視圖定義的時候，where要確則需要添加參數。 如果要從視圖中刪除基表的資料，被刪除的這條資料必須符合視圖的查詢條件，才能通過視圖刪除該資料。鍵保留表鍵保留表是理解串連視圖修改限制的一個基本概念。該表的主鍵列全部顯示在視圖中,並且它們的值在視圖中都是唯一且非空的。也就是說，表的索引值在一個串連視圖中也是索引值，那麼就稱這個表為鍵保留表。 

因為 Studno 既是Stud_details 中的主鍵，也是聯結結果中的主鍵，因此Stud_details是鍵保留表

 視圖中的函數視圖中可以使用單行函數、分組函數和運算式

CREATE VIEW item_view AS     SELECT itemcode, LOWER(itemdesc) item_desc    FROM itemfile; 

 

刪除視圖使用DROP VIEW語句刪除視圖

DROP VIEW toys_view; 

 

索引索引是與表相關的一個可選結構，使用索引可以提高 SQL 陳述式執行的效能，減少磁碟I/O，索引在邏輯上和物理上都獨立於表的資料，Oracle 能夠自動維護索引。 

索引的原理：索引的工作原理是與一個叫做ROWID的概念緊密相關的，Oracle在建立資料表時，預設會為每個資料表建立一個隱含欄位，叫ROWID。在向資料表中插入記錄時，系統將自動為每條記錄分配惟一的一個ROWID號，利用這個ROWID號，就可以快速定位到記錄。

 操作索引使用 CREATE INDEX 語句建立索引：建立標準索引：Create index 索引名 on 表名(列名);

CREATE INDEX item_index ON itemfile (itemcode)     TABLESPACE index_tbs;

 

重建索引

ALTER INDEX item_index REBUILD; 

 

刪除索引

DROP INDEX item_index; 

 

索引的類型索引有各種類型，除了標準索引外，還有一些特殊類型的索引：  唯一索引唯一索引確保在定義索引的列中沒有重複值，Oracle 自動在表的主鍵列上建立唯一索引使用CREATE UNIQUE INDEX語句建立唯一索引

CREATE UNIQUE INDEX item_index     ON itemfile (itemcode);

 複合式索引複合式索引是在表的多個列上建立的索引，索引中列的順序是任意的，如果 SQL 陳述式的 WHERE 子句中引用了複合式索引的所有列或大多數列，則可以提高檢索速度。

CREATE INDEX comp_index     ON itemfile(p_category, itemrate);

 反向鍵索引反向鍵索引反轉索引列索引值的每個位元組，通常建立在值是連續增長的列上，可以使資料均勻地分布在整個索引上建立索引時使用REVERSE關鍵字

CREATE INDEX rev_index      ON itemfile (itemcode) REVERSE;

 

ALTER INDEX rev_index REBUID NOREVERSE;

位元影像索引位元影像索引適合建立在低基數列上，位元影像索引不直接儲存ROWID，而是儲存位元組位到ROWID的映射，使用位元影像索引可以減少回應時間，節省空間的佔用。

CREATE BITMAP INDEX bit_index     ON order_master (orderno);

 

索引組織表索引組織表的資料存放區在與其關聯的索引中，索引中儲存的是行的實際資料，而不是ROWID，它基於主鍵訪問資料。CREATE TABLE 命令與 ORGANIZATION INDEX 子句一起用於建立索引組織表。

CREATE TABLE ind_org_tab (       vencode NUMBER(4) PRIMARY KEY,       venname VARCHAR2(20)     )      ORGANIZATION INDEX;

 

普通表與索引組織表的比較

普通表	索引組織表
ROWID 唯一地標識行	主鍵唯一地標識行
隱式的 ROWID 列	沒有隱式的 ROWID 列
基於 ROWID 的訪問	基於主鍵的訪問
順序掃描返回所有行	完全索引掃描返回所有行，並按主鍵順序排列
支援分區	不支援分區

 基於函數的索引基於一個或多個列上的函數或運算式建立的索引，運算式中不能出現彙總函式，不能在LOB類型的列上建立，建立時必須具有 QUERY REWRITE 許可權，當查詢使用了函數的時候，索引將不再使用，所以要建立函數索引。

CREATE INDEX lowercase_idx      ON toys (LOWER(toyname));

 

SELECT toyid FROM toys     WHERE LOWER(toyname)=‘doll‘;

 

 索引中的分區我們可以將索引儲存在不同的分區中。 與分區有關的索引有三種類型：1.局部分區索引 － 在分區表上建立的索引，在每個表分區上建立獨立的索引，索引的分區範圍與表一致2.全域分區索引 － 在分區表或非分區表上建立的索引，索引單獨指定分區的範圍，與表的分區範圍或是否分區無關3.全域非分區索引 － 在分區表上建立的全域普通索引，索引沒有被分區 擷取索引的資訊與索引有關的資料字典視圖有：1.USER_INDEXES － 使用者建立的索引的資訊2.USER_IND_PARTITIONS － 使用者建立的分區索引的資訊3.USER_IND_COLUMNS － 與索引相關的表列的資訊

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME     FROM USER_IND_COLUMNS     ORDER BY INDEX_NAME, COLUMN_POSITION;

 

 

 

Oracle學習筆記九 資料庫物件