Oracle中的概念：同義詞、序列與視圖

　　　　同義詞 synonym

　　CREATE [PUBLIC]SYNONYM synonym For schemaobject

　　隱藏對象的名稱和所有者:

　　select count(*) from hremployees;

　　create synonym emp for hremployees; 默認屬於donny用戶是donny的私有對象private

　　select count(*) from emp;

　　為分布式數據庫的遠程對象提供了位置透明性:

　　訪問其他數據庫時要首先建立數據庫連結

　　CREATE DATABASE LINK test_link CONNECT TO username IDENTIFIED BY pass USING orabase;

　　Select count(*) from hremployees@test_link;

　　create synonym link_emp for hremployees@test_link;

　　select count(*) from link_emp;

　　提供對象的公共訪問:

　　create public synonym pub_emp for hremployees;

　　pub_emp屬於public用戶數據庫所有用戶都可以訪問

　　同義詞類型

　　–私有 emp 實際上donnyemp

　　–公用 pub_emp 所有用戶都可以直接訪問

　　當公有對象和私有對象同名時(因為數據不同的用戶所以可以)以私有對象優先(類似於局部變量)

　　desc dba_synonyms/ user_synonyms/ all_synonyms 數據字典復數

　　tab公有同義詞

　　建立私有的tab表查看效果

　　刪除同義詞

　　drop synonym donnyemp;

　　drop public synonym pub_emp;

　　序列sequence

　　CREATE SEQUENCE donnyseq　　也是屬於某個用戶的以下參數均可省略使用默認值

　　INCREMENT BY 指定序列之間的間隔正負整數;默認正為升序負為降序

　　START WITH 第一個序列號默認=MINVALUE

　　NOMAXVALUE 設置最大值此處表示默認的次冪MAXVALUE

　　NOMINVALUE 設置最小值此處表示默認的次冪MINVALUE

　　NOCYCLE 或者CYCLE;表示序列達到最大或者最小(降序)後要不要從頭開始

　　CACHE ; 默認CACHE 　事先分配多少序列號放在內存中提高速度

　　訪問序列

　　oracle為序列提供了兩個偽列可以看作其屬性

　　Nextval: 根據increment by得到的一個新的序列值每次執行都會得到一個新值

　　Currval: current value　當前值已經被取得的值

　　Select seqnextval from dual;

　　Select seqcurrval from dual;

　　使用序列

　　insert into t values(seqnextval);

　　修改序列

　　alter sequence seq …重新指定各個參數

　　不能修改start with;除非刪除重建

　　刪除序列

　　drop sequence seq;

　　數據字典

　　desc dba_sequences / user_…/ all…

　　　視圖view:

　　CREATE [OR REPLACE][FORCE/ NOFORCE] VIEW AS

　　Create view mytable

　　As

　　Select first_name||||last_name

　　from hremployees;

　　[試驗]如何使用視圖作為安全 target=_blank>安全機制

　　 desc考察hremployees看作一個公司的員工信息數據庫表簡單說明

　　 目標實現每個員工都可以訪問公司中所有雇員的name email phone_number方便通訊

　　 方案

　　a) 賦予所有員工訪問hremployees表的權限?salary

　　b) 建立一個只包含合適字段的視圖然後賦予所有員工訪問這個視圖的權限而不是表的權限

　　 Alter user hr account unlock;

　　Conn hr/hr

　　Create view company_phone_book as

　　Select first_name|| ||last_name name email phone_number

　　From employees;

　　Grant select on company_phone_book to public;

　　Desc company_phone_book 對比列的長度

　　Select * from company_phone_book;

　　name隱藏數據的復雜性

　　數據字典

　　dba_views

　　text字段long

　　select text from dba_views where view_name=upper(company_phone_book)

　　改變視圖定義

　　新需求想要在現有視圖上增加員工的ID號(employee_id)

　　Create view company_phone_book as

　　Select employee_id emp_id

　　first_name||||last_name name email phone_number

　　From employees;

　　報錯;

　　如果刪掉重建會有什麼缺點?會把關聯的授權全部刪掉Create or replace view保留原有授權

　　Create or replace view company_phone_book as

　　Select employee_id emp_id

　　first_name||||last_name name email phone_number

　　From employees;

　　Desc company_phone_book

　　Drop view company_phone_book

　　視圖中增加約束

　　create view yearly_hire_totals as

　　select to_char(hire_dateYYYY) year

　　count(*) total

　　from hremployees

　　group by to_char(hire_dateYYYY)

　　order by to_char(hire_dateYYYY);

　　聯接視圖

　　desc hremp_details_view

　　set long

　　select text from dba_views where view_name=upper(emp_details_view)

　　with read only

　　驗證視圖有效性

　　基本表的一些改變可能會導致視圖無效

　　) 改變出現在視圖中列的名稱或刪掉列

　　) 刪除構建視圖的基本表或視圖

　　[試驗]使視圖無效並重新編譯並使其有效

　　) 基本表create table base(id numberdata varchar());

　　insert into base values(abc); commit;

　　) view: create view view_b as

　　select id view_id data view_data from t;

　　select * from view_b;

　　) 更新基本表 alter table base modify(id numberdata varchar());

　　alter table base add(data varchar());

　　) 視圖無效select object_name status from dba_objects where object_name=upper(view_b)

　　) 使視圖有效只需要從視圖中選取即可　oracle會自動對視圖編譯

　　select * from view_b;

　　) 手動編譯　alter view view_b compile;

　　FORCE 選項

　　強制ORACLE接受無效的視圖定義

　　) 比如開發過程中A負責建立基本表B負責建立視圖這樣B不必依賴於A的工作進度就可以將視圖建立並編譯進數據庫

　　) 或者B需要建立在A用戶表上視圖但是還暫時沒有對A用戶表select 的權限可以先建立等待授權後再使用

　　Create view invalid_view as

　　Select * from table_not_exist;

　　Create force view invalid_view as

　　Select * from table_not_exist;

　　通過視圖進行更新和刪除

　　類似於company_phone_book是可以跟新的

　　可以通過dba_updatable_columns查看那些列可以做那些更新;

　　desc pany_phone_book

　　select * from dba_updatable_columns where table_name=upper(company_phone_book)

　　嘗試更新email和name

　　update pany_phone_book

　　set name=Chen Donny

　　where emp_id=

　　 使用instead of 觸發器更新視圖

　　create trigger update_name_company_phone_book

　　INSTEAD OF

　　Update on pany_phone_book

　　Begin

　　Update hremployees

　　Set employee_id=:newemp_id

　　First_name=substr(:newname instr(:newname)+)

　　last_name= substr(:newnameinstr(:newname))

　　phone_number=:newphone_number

　　email=:newemail

　　where employee_id=:oldemp_id;

　　end;

　　 With check option 約束

　　作用阻止更新不能通過視圖訪問的數據

　　試驗

　　) 建立視圖只能看到department_id=的雇員

　　create view department_ as

　　select * from hremployees where department_id=

　　With check option

　　) 選擇select employee_idfirst_namelast_name from department_;

　　) 查看可更新列

　　select * from dba_updatable_columns

　　where table_name=upper(department_)

　　) 嘗試將此人移動到部門

　　update department_

　　set department_id=

　　where employee_id=

　　報錯!!

　　這個視圖限制我們只能訪問department=的數據我們要通過視圖修改department=的數據被禁止

　　[試驗]關於前名

　　) 誰是公司前名的雇員

　　select last_namehire_date

　　from hremployees

　　order by hire_date;

　　) 只想取回前五名數據呢?

　　select last_namehire_date

　　from hremployees

　　where rownum<

　　order by hire_date;

　　結果不正確先取了前條數據再排序

　　)select last_namehire_date

　　from (select last_namehire_date

　　from hremployees

　　order by hire_date)

　　where rownum<

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