Oracle的視圖、同義字、序列

l       管理檢視

 

建立視圖

可用CREATE VIEW 語句建立視圖 每個視圖是由參考表的查詢物化的視圖或其他視圖定義的就像所有的子查詢 定義視圖的查詢不能包含FOR UPDATE子句

 

CREATE VIEW sales_staff AS

SELECT empno, ename, deptno FROM emp WHERE deptno = 10

WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

 

定義CHECK OPTION視圖的查詢僅參考部門號為的行此外建立帶有約束名字為sales_staff_cnst的視圖針對該視圖發布的INSERT 和UPDATE 語句不能產生該視圖不能選擇的行

 

INSERT INTO sales_staff VALUES (7584, 'OSTER', 10); --可以成功的Insert

INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);

--失敗,只能Insert或update用sales_staff 視圖where deptno = 10的記錄

 

可選的 可用指定WITH READ ONLY 子句的方式構造視圖 從而防止通過視圖對錶的任何更改 插入或刪除 如果沒有指定WITH 子句 帶有某些限制的視圖 自然是可更新的.

 

連接視圖

在FROM 子句中 也可以指定多個基礎資料表或視圖來建立視圖 這種視圖就叫連接視圖

 

CREATE VIEW division1_staff AS SELECT ename, empno, job, dname FROM emp, dept

WHERE emp.deptno IN (10, 30) AND emp.deptno = dept.deptno;

--建立連接emp 表和dept 表中資料的division1_staff 視圖

 

建立帶錯誤視圖

要能建立帶有錯誤的視圖 必須在CREATE VIEW 語句中包括FORCE 選項

CREATE FORCE VIEW AS select * from employee 

--此時的表employee不存在,用FORCE關鍵字可以先建好視圖,後再建表.

 

更新連接視圖

如果在表中明確地定義了主鍵和外鍵 或者定義了唯一的索引 下面的例子才能工作

下面是針對emp 和dept 表的合適的但受約束的表定義

 

CREATE TABLE dept (

deptno NUMBER(4) PRIMARY KEY,  dname VARCHAR2(14),  loc VARCHAR2(13)   );

 

CREATE TABLE emp (

    empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10),

    job VARCHAR2(9),    mgr NUMBER(4),

    sal NUMBER(7,2),    comm NUMBER(7,2),

    deptno NUMBER(2),    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)    );

 

下面的語句建立例子中所參照的emp_dept 連接視圖

 

CREATE VIEW emp_dept AS

SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc  FROM emp, dept

    WHERE emp.deptno = dept.deptno

    AND dept.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

 

鍵預留表的概念是理解修改連接視圖的限制的基礎,如果表中的每個鍵也是連接的結果鍵,則該表是鍵預留的,所以一個鍵預留表通過連接預留它自己的鍵

注意 不需要將表中一個或多個鍵選擇作為預留鍵 選擇了鍵就可以了 它們也就作為該連接的結果的鍵了

表中的鍵的預留屬性不依賴於表中的實際資料 它更是其模式的屬性 例如 如果在 emp 表中每個部門最多有一名僱員 那麼deptno 在emp 和dept 的連接結果中是唯一的 但 dept 還不是一個鍵預留表

 

    如果從emp_dept 中SELECT 所有行 結果如下

 

    EMPNO  ENAME   DEPTNO   DNAME      LOC

    ----- -------- ------- ----------  -----------

    7782  CLARK    10      ACCOUNTING  NEW YORK

    7839  KING     10      ACCOUNTING  NEW YORK

    7934  MILLER   10      ACCOUNTING  NEW YORK

    7369  SMITH    20      RESEARCH    DALLAS

    7876  ADAMS    20      RESEARCH    DALLAS

    7902  FORD     20      RESEARCH    DALLAS

    7788  SCOTT     20      RESEARCH    DALLAS

    7566  JONES     20      RESEARCH    DALLAS

    8 rows selected.

 

在此視圖中 emp 是鍵預留表 因為empno 是emp 表的一個鍵 也是一個連接的結果,鍵 dept 不是鍵預留表 因為儘管deptno 是dept 表的一個鍵 但它不是一個連接的鍵

 

DML 語句和連接視圖

一般的規則是 連接視圖上的任何UPDATE,DELETE 或者INSERT 語句僅能修改一個下層基礎資料表,下面的例子說明了針對UPDATE,DELETE 和INSERT 語句的規則

 

UPDATE emp_dept SET sal = sal * 1.10 WHERE deptno = 10;

-- 正確修改emp_dept 視圖的UPDATE 語句

 

    UPDATE emp_dept  SET loc = 'BOSTON' WHERE ename = 'SMITH';

    --該語句失敗了產生一個錯誤 ORA-01779 cannot modify a column which maps to a non key-preserved table不能修改映射到非鍵預留表中的列,因為該語句試圖修改基礎dept表 但dept 表不是emp_dept 視圖中的鍵預留表

連接視圖中的所有可更新的列必須映射到鍵預留表中列 如果視圖是使用WITH CHECK OPTION子句定義的,那麼所有連接的列和所有從在視圖中參照多次的表中取出的列都是不可修改的

所以 例如 如果emp_dept 視圖是使用WITH CHECK OPTION 子句定義的 下面的 UPDATE 語句會失敗

 

 UPDATE emp_dept SET deptno = 10 WHERE ename = 'SMITH'--失敗是因為試圖更新一個連接的列

 

DELETE 語句能夠通過所提供的在連接中有一個而且僅有一個鍵預留表的連接視圖進行刪除工作

下面的DELETE 語句在emp_dept 視圖上工作

 

    DELETE FROM emp_dept WHERE ename = 'SMITH';

--DELETE 語句是合法的 因為該語句能被轉換成在基礎emp表上的DELETE 操作 並且emp 表是該連接中的唯一的鍵預留表

                                                    

如果假使建立了下面的視圖 則在該視圖上就不能執行操作 因為 和二者都是鍵預留表

 

CREATE VIEW emp_emp AS

SELECT e1.ename, e2.empno, deptno FROM emp e1, emp e2 WHERE e1.empno = e2.empno

 

如果視圖用WITH CHECK OPTION 子句定義的,並且鍵預留表是重複的那麼就不能通過這樣的視圖來刪除行

 

CREATE VIEW emp_mgr AS

    SELECT e1.ename, e2.ename mname FROM emp e1, emp e2 WHERE e1.mgr = e2.empno

WITH CHECK OPTION;

--在該視圖上不能執行刪除 因為該視圖包括了鍵預留表的自身連接

 

INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 40);

--這條語句能運行是因為僅修改一個鍵預留基礎資料表emp  40是dept表中的有效deptno

( 即滿足在emp表上的FOREIGN KEY完整性條件約束)

 

INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 77);

    --INSERT 語句會失敗 失敗的原因與UPDATE 在基礎emp 表中失敗的原因相同違反了emp  表中FOREIGN KEY完整性條件約束 因為沒有deptno 77

 

     -

INSERT INTO emp_dept (empno, ename, loc) VALUES (9010, 'KURODA', 'BOSTON');

--下面的INSTER 語句也會失敗 產生一個錯誤 ORA-01776 cannot modify more than one base table through a view 不能通過視圖修改多個基礎資料表

 

INSERT語句不能隱含地或明確地涉及非鍵預留表的列如果連接視圖是用 WITH CHECK OPTION 子句定義的 那麼就不能對該視圖執行INSERT 操作

 

使用UPDATABLE_COLUMNS 視圖

當要修改連接視圖時 UPDATABLE_COLUMNS 視圖表 中所描述的視圖對你有協助

視圖名稱 
 說明
 
DBA_UPDATABLE_COLUMNS
 顯示所有表及視圖中的所有可修改的列
 
ALL_UPDATABLE_COLUMNS     
 顯示使用者可訪問的所有表及視圖中的所有可修改的列
 
USER_UPDATABLE_COLUMNS     
 顯示使用者模式中所有表及視圖中的所有可修改的列
 

         

刪除視圖

可以刪除包含在你的模式中的任何視圖 為了刪除其他使用者模式中的視圖 你必須擁有DROP ANY VIEW 系統許可權 使用DROP VIEW 語句刪除視圖

下面的語句刪除emp_dept 視圖

    DROP VIEW emp_dept;

 

替換視圖

    要想替換視圖 必須擁有刪除和建立一個視圖所需的所有許可權如果必須修改視圖的定義 就必須替換視圖 而不能修改視圖的定義 可用下面的幾種方式替換視圖

    

刪除並重建視圖 警告:當視圖被刪除時 相應的對象許可權的所有授權也被從角色和使用者中撤消重新

建立視圖之後 必須重新授予許可權

    可以用包含 OR REPLACE選項的 CREATE VIEW語句重新定義視圖 OR REPLACE 選項替換視圖的當前定義並且保留當前的安全授權 例如 假設像前面  介紹的那樣建立過sales_staff 視圖 另外 為角色和其他使用者授予過幾種對象許可權

    但是 現在需要重新定義sales_staff 視圖來改變在WHERE 子句中指定的部門號 可用下面的語句替換目前的版本的sales_staff 視圖

 

    CREATE OR REPLACE VIEW sales_staff AS

    SELECT empno, ename, deptno  FROM emp WHERE deptno = 30            

WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

 

在替換視圖之前 要考慮如下幾個方面 :

 

替換視圖也就替換了資料字典中視圖的定義 並不影響視圖所涉及的所有下層對象

如果前面已經定義CHECK OPTION中的約束而新的視圖定義中又不包含這種約束該約束就被刪除了,依賴於被替換視圖的所有視圖及PL/SQL 程式單元都變成無效不能使用了  

 

l       同義字

同義字是模式對象的別名 同義字通過屏蔽對象的名稱和擁有者 以及通過為分散式資料庫的遠程對象提供位置透明性來提供一個安全層同義字使用方便 為資料庫使用者降低了SQL 陳述式的複雜性

     同義字允許重新命名或移動下層對象在那兒僅需要重新定義同義字 而基於同義字的應用無須做任何修改仍能繼續工作 ,即能建立公用的也能建立私人的同義字 公用同義字由名為PUBLIC  的特殊使用者組所擁有,並且資料庫中的每個使用者都可訪問 私人同義字被包含在特殊使用者的模式中 並且僅僅使用者和使用者的受讓人可使用

 

建立同義字

    要想在你擁有的模式中建立私人同義字你必須擁有CREATE SYNONYM 許可權,要想在其他使用者的模式中建立私人同義字 你必須擁有CREATE ANY SYNONYM 許可權要想建立公用同義字 必須擁有CREATE PUBLIC SYNONYM 系統許可權

    使用CREATE SYNONYM 語句建立同義字 在建立同義字時 下層模式對象不必存在

你也不需要許可權以訪問對象 下面的語句在包含在jward模式中的emp 表上建立名為public_emp 的公用同義字

 

CREATE PUBLIC SYNONYM public_emp FOR jward.emp;

    

a001使用者訪問Scott.emp

select * from scott.emp

示範建立同義字scemp;

         create synonym scemp for scott.emp

     示範在a001訪問emp：select * from scemp;

 

刪除同義字

使用DROP SYNONYM語句刪除不再需要的同義字 要想刪除私人同義字 就省略 PUBLIC 關鍵字 要想刪除公用同義字 就要包括PUBLIC 關鍵字

 

    DROP SYNONYM emp; --刪除名為emp 的私人同義字

DROP PUBLIC SYNONYM public_emp; --刪除名為public_emp的公有同義字

 

l       序列

 

建立序列

    要想在你的模式中建立序列 你必須擁有CREATE SEQUENCE系統許可權 為了在其他使用者的模式中建立序列 必須擁有CREATE ANY SEQUENCE 許可權

    使用CREATE  SEQUENCE 語句建立序列 例如 下面的語句建立一個用於給emp,表中empno 列產生僱員號的序列

 

    CREATE SEQUENCE emp_sequence

    INCREMENT BY 1             --步長為1

    START WITH 1               --起始為 1

    NOMAXVALUE                 --沒有最大值[或Maxvalue 100,Minvalue 1]

    NOCYCLE                    --表示不可迴圈[或CYCLE  表示到Maxvalue值迴圈,從minvalue開始]

    CACHE 10;                  -- 預分配一組10個序號 ,預設為20           

例:

Insert into 表名value(emp_sequence.Nextval,’ddd’)

--自增列, emp_sequence.Nextval表示返回當前自增列的號

   

CACHE選項預分配一組序號,並且把它們存在記憶體中,以便更快地訪問序號 當快取中最後一個序號被使用了 Oracle  就再讀入另外一組序號 並將其放入到快取中.

    如果選擇用快取存放一組序號 Oracle  可能跳過序號 例如 當執行個體不順利關機時 例如 當執行個體出現失敗或發布了SHUTDOWN ABORT 語句  已經被放在快取中但沒有被使用的序號就會丟失 同樣 已經被使用但沒有被儲存的序號也會丟失在匯入和匯出之後 Oracle 也可能跳過已在快取中的序號

 

更改序列

    要想更改序列 你的模式中必須包含序列或者你必須擁有ALTER ANY SEQUENCE系統許可權 能更改序列 以修改定義它是如何產生序號的任何參數 除序列的起始號以外 為了更改序列的起始點 可以先刪除序列 然後再重新建立序列 當在序號上執行

DDL 時 就會丟失快取中的值

    使用ALTER SEQUENCE 語句更改序列 例如 下面的語句改變emp_sequence 序列

 

    ALTER SEQUENCE emp_sequence

    INCREMENT BY 10

    MAXVALUE 10000

    CYCLE

    CACHE 20;

 

  刪除序列

    可以刪除你的模式中的任何序列要想刪除其他模式中的序列 你必須擁有DROP ANY SEQUENCE 系統許可權 如果不再需要某個序列 可用DROP SEQUENCE 語句刪除它

   例如 下面的語句刪除order _ seq 序列

 

    DROP SEQUENCE order_seq;

 

    刪除序列時 它的定義就會從資料字典中刪除該序列的所有同義字保留下來 但參

照這些同義字時 會返回一個錯誤

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/blackstreet/archive/2006/10/25/1351071.aspx