Oracle學習筆記九 數據庫對象，oracle學習筆記

Oracle學習筆記九 數據庫對象，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 AS
SELECT vm.vencode, venname, orderno, odate, ostatus
FROM   vendor_master vm, order_master om
WHERE  vm.vencode = om.vencode(+);

 等同於

SELECT vm.vencode, venname, orderno, odate, ostatus
FROM vendor_master vm LEFT OUTER JOIN order_master om
ON 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;