Oracle08、視圖+同義詞+序列+索引

一、視圖

什麼是視圖【View】
（1）視圖是一種虛表
（2）視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表
（3）向視圖提供數據內容的語句為 SELECT 語句,可以將視圖理解為存儲起來的 SELECT 語句
（4）視圖向用戶提供基表數據的另一種表現形式
（5）視圖沒有存儲真正的數據，真正的數據還是存儲在基表中
（6）程式員雖然操作的是視圖，但最終視圖還會轉成操作基表
（7）一個基表可以有0個或多個視圖

什麼情況下會用到視圖
（1）如果你不想讓用戶看到所有數據（欄位，記錄），只想讓用戶看到某些的數據時，此時可以使用視圖
（2）當你需要減化SQL查詢語句的編寫時，可以使用視圖，但不提高查詢效率

視圖應用領域
（1）銀行，電信，金屬，證券軍事等不便讓用戶知道所有數據的項目中

視圖的作用
（1）限制數據訪問
（2）簡化複雜查詢
（3）提供數據的相互獨立
（4）同樣的數據，可以有不同的顯示方式

在瞭解了視圖相關的概念之後，下麵筆者就來介紹一下視圖的創建以及創建之前需要做的準備工作。

首先，在Oracle中，預設普通用戶是沒有創建視圖的許可權的，所以如果你要創建視圖的話，就要讓sysdba給普通用戶分配創建視圖的許可權：

/*以sysdba身份，授權scott用戶create view許可權*/
grant create view to scott;

/*以sysdba身份，撤銷scott用戶create view許可權*/
revoke create view from scott;

當給普通用戶賦予了創建視圖的許可權之後，普通用戶就擁有了創建視圖的許可權了：

/*基於emp表所有列，創建視圖emp_view_1，create view 視圖名 as select對一張或多張基表的查詢*/
create view emp_view_1
as
select * from emp;

/*基於emp表指定列，創建視圖emp_view_2，該視圖包含編號/姓名/工資/年薪/年收入（查詢中使用列別名）*/
create view emp_view_2
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

/*基於emp表指定列，創建視圖emp_view_3(a,b,c,d,e)，包含編號/姓名/工資/年薪/年收入（視圖中使用列名）*/
create view emp_view_3(a,b,c,d,e)
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

/*查詢emp_view_3創建視圖的結構*/
desc emp_view_3;

/*修改emp_view_3(id,name,salary,annual,income)視圖，create or replace view 視圖名 as 子查詢*/
create or replace view emp_view_3(id,name,salary,annual,income)
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

/*查詢emp表，求出各部門的最低工資，最高工資，平均工資*/
select min(sal), max(sal), round(avg(sal), 0), deptno
  from emp
 group by deptno;

/*創建視圖emp_view_4，視圖中包含各部門的最低工資，最高工資，平均工資*/
create or replace view emp_view_4
as
select deptno "部門號",min(sal) "最低工資",max(sal) "最高工資",round(avg(sal),0) "平均工資"
from emp
group by deptno;

/*創建視圖emp_view_5，視圖中包含員工編號，姓名，工資，部門名，工資等級*/
create or replace view emp_view_5
as
select e.empno "編號",e.ename "姓名",e.sal "工資",d.dname "部門名",s.grade "工資等級"
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

/*刪除視圖emp_view_1中的7788號員工的記錄，使用delete操作，會影響基表嗎*/
delete from emp_view_1 where empno=7788;--寫法正確，會影響基表

/*修改emp_view_1為只讀視圖【with read only】，再執行上述delete操作，還行嗎？*/
create or replace view emp_view_1
as
select * from emp
with read only;--不能進行delete操作了

對於視圖，在操作中還有一些需要註意的地方，下麵以問答的形式進行描述：

刪除視圖中的【某條】記錄會影響基表嗎？
會影響基表

將【整個】視圖刪除，會影響表嗎？
不會影響基表

刪除視圖，會進入回收站嗎？
不會進入回收站

刪除基表會影響視圖嗎？
會影響視圖

閃回基表後,視圖有影響嗎？
視圖又可以正常工作了

二、同義詞

什麼是同義詞【Synonym】
（1）對一些比較長名字的對象(表，視圖，索引，序列......）做減化，用別名替代

同義詞的作用
（1）縮短對象名字的長度
（2）方便訪問其它用戶的對象

賦予創建同義詞的許可權以及創建同義詞

/*以sys身份授予scott普通用戶create synonym許可權*/
grant create synonym to scott;

/*以sys身份從scott普通用戶撤銷create synonym許可權*/
revoke create synonym from scott;

/*創建與salgrade表對應的同義詞，create synonym 同義詞 for 表名/視圖/其它對象*/
create synonym e for salgrade;
create synonym ev5 for emp_view_5;

/*使用同義詞操作salgrade表*/
select * from s;

/*刪除同義詞*/
drop synonym ev5;

註意：刪除同義詞之後不會影響到基表，但是刪除基表之後就會影響到同義詞。

三、序列

什麼是序列【Sequence】
（1）類似於MySQL中的auto_increment自動增長機制，但Oracle中無auto_increment機制
（2）是oracle提供的一個產生唯一數值型值的機制
（3）通常用於表的主健值
（4）序列只能保證唯一，不能保證連續
     聲明：oracle中，只有rownum永遠保持從1開始，且繼續
（5）序列值，可放於記憶體，取之較快
 
為什麼oracle不直接用rownum做主健呢？
rownum=1這條記錄不能永遠唯一表示SMITH這個用戶，但主鍵=1確可以永遠唯一表示SMITH這個用戶

為什麼要用序列
（1）以前我們為主健設置值，需要人工設置值，容易出錯
（2）以前每張表的主健值，是獨立的，不能共用

/*為emp表的empno欄位，創建序列emp_empno_seq，create sequence 序列名*/
create sequence emp_empno_seq;

/*刪除序列emp_empno_seq，drop sequence 序列名*/
drop sequence emp_empno_seq;

/*查詢emp_empno_seq序列的當前值currval和下一個值nextval，第一次使用序列時，必須選用：序列名.nextval*/
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;

/*使用序列，向emp表插入記錄，empno欄位使用序列值*/
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);

/*修改emp_empno_seq序列的increment by屬性為20，預設start with是1，alter sequence 序列名*/
alter sequence emp_empno_seq
increment by 20;

/*修改emp_empno_seq序列的的increment by屬性為5*/
alter sequence emp_empno_seq
increment by 5;

/*修改emp_empno_seq序列的start with屬性，行嗎？*/
alter sequence emp_empno_seq
start with 100;--無法變更啟動號錯誤

/*有了序列後，還能為主健手工設置值嗎？*/
insert into emp(empno) values(9999);
insert into emp(empno) values(7900);--可以
select * from emp;--測試插入值情況

刪除表，會影響序列嗎？
你無法做insert操作

刪除序列，會影響表嗎？
表真正亡，序列亡

在hibernate中，如果是訪問oracle資料庫伺服器，那麼User.hbm.xml映射文件中關於<id>標簽如何配置呢？

<id name="id" column="id">
   <generator class="increment/identity/uuid/【sequence】/【native】"/>
</id>

四、索引

什麼是索引【Index】
（1）是一種快速查詢表中內容的機制，類似於新華字典的目錄
（2）運用在表中某個/些欄位上，但存儲時，獨立於表之外

為什麼要用索引
（1）通過指針加速Oracle伺服器的查詢速度
（2）通過rowid快速定位數據的方法，減少磁碟I/O
     rowid是oracle中唯一確定每張表不同記錄的唯一身份證

rowid的特點
（1）位於每個表中，但錶面上看不見，例如：desc emp是看不見的
（2）只有在select中，顯示寫出rowid，方可看見
（3）它與每個表綁定在一起，表亡，該表的rowid亡，二張表rownum可以相同，但rowid必須是唯一的
（4）rowid是18位大小寫加數字混雜體，唯一表代該條記錄在DBF文件中的位置
（5）rowid可以參與=/like比較時，用''單引號將rowid的值包起來，且區分大小寫
（6）rowid是聯繫表與DBF文件的橋梁

索引的特點
（1）索引一旦建立, Oracle管理系統會對其進行自動維護, 而且由Oracle管理系統決定何時使用索引
（2）用戶不用在查詢語句中指定使用哪個索引
（3）在定義primary key或unique約束後系統自動在相應的列上創建索引
（4）用戶也能按自己的需求，對指定單個欄位或多個欄位，添加索引

什麼時候【要】創建索引
（1）表經常進行 SELECT 操作
（2）表很大(記錄超多)，記錄內容分佈範圍很廣
（3）列名經常在 WHERE 子句或連接條件中出現
註意：符合上述某一條要求，都可創建索引，創建索引是一個優化問題，同樣也是一個策略問題
       
什麼時候【不要】創建索引
（1）表經常進行 INSERT/UPDATE/DELETE 操作
（2）表很小(記錄超少)
（3）列名不經常作為連接條件或出現在 WHERE 子句中
註意：符合上述某一條要求，不要創建索引，創建索引是一個優化問題，同樣也是一個策略問題

/*為emp表的empno單個欄位，創建索引emp_empno_idx，叫單列索引，create index 索引名 on 表名(欄位,...)*/
create index emp_empno_idx
on emp(empno);

/*為emp表的ename,job多個欄位，創建索引emp_ename_job_idx，多列索引/聯合索引*/
create index emp_ename_job 
on emp(ename,job);
如果在where中只出現job不使用索引
如果在where中只出現ename使用索引
我們提倡同時出現ename和job
註意：索引創建後，只有查詢表有關，和其它（insert/update/delete）無關,解決速度問題

/*刪除emp_empno_idx和emp_ename_job_idx索引，drop index 索引名*/
drop index emp_empno_idx;
drop index emp_ename_job_idx;

註意：以上內容只是筆者作為一個初學Oracle的菜鳥進行的總結，並不全面，讀者請根據自己實際情況進行學習。