Oracle學習（10）：其他數據庫對象
其他數據庫對象
數據庫對象種類
視圖
視圖介紹
視圖是一種虛表.
視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
向視圖提供數據內容的語句為SELECT 語句, 可以將視圖理解為存儲起來的 SELECT
語句.
視圖向用戶提供基表數據的另一種表現形式
視圖的優點
限制數據訪問
簡化復雜查詢
提供數據的相互獨立
同樣的數據，可以有不同的顯示方式
但視圖不能提高性能
創建視圖語句
使用下面的語法格式創建視圖
?
CREATE [OR REPLACE] [FORCE"NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
?FORCE： 子查詢不一定存在
?NOFORCE： 子查詢存在（默認）
?WITH READ ONLY:只能做查詢操作
子查詢可以是復雜的SELECT 語句
修改視圖語句
使用CREATEOR REPLACEVIEW 子句修改視圖
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
CREATEVIEW 子句中各列的別名應和子查詢中各列相對應
屏蔽DML操作
可以使用WITH READ ONLY 選項屏蔽對視圖的DML操作
任何DML 操作都會返回一個Oracle server 錯誤
刪除視圖
刪除視圖只是刪除視圖的定義，並不會刪除基表的數據
DROP VIEW
view;
視圖示例
SQL> --視圖
SQL> create view empincomeview
2 as
3 select empno,ename,sal,sal*12 annlsal ,sal*12+nvl(comm,0) income from emp;
SQL> show user
USER 為 "SCOTT"
SQL> /
視圖已創建。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP20 TABLE
TESTSAVEPOINT TABLE
TESTDELETE TABLE
BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE
TEST3 TABLE
MYPERSON TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPINCOMEVIEW VIEW
已選擇12行。
SQL> desc EMPINCOMEVIEW
名稱 是否為空? 類型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
ANNLSAL NUMBER
INCOME NUMBER
SQL> select * from 名稱 是否為空
2 ----------------------------------------------------------------------------------- -------
3 EMPNO NOT NUL
4 ENAME
5 SAL
6 ANNLSAL
7 INCOME
8 ;
EMPNO NOT NUL
*
第 3 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL> select * from EMPINCOMEVIEW;
EMPNO ENAME SAL ANNLSAL INCOME
---------- ---------- ----- ---------- ----------
7369 SMITH 800 9600 9600
7499 ALLEN 1600 19200 19500
7521 WARD 1250 15000 15500
7566 JONES 2975 35700 35700
7654 MARTIN 1250 15000 16400
7698 BLAKE 2850 34200 34200
7782 CLARK 2450 29400 29400
7788 SCOTT 3000 36000 36000
7839 KING 5000 60000 60000
7844 TURNER 1500 18000 18000
7876 ADAMS 1100 13200 13200
EMPNO ENAME SAL ANNLSAL INCOME
---------- ---------- ----- ---------- ----------
7900 JAMES 950 11400 11400
7902 FORD 3000 36000 36000
7934 MILLER 1300 15600 15600
已選擇14行。
SQL> host cls
序列
什麼是序列？
序列: 可供多個用戶用來產生唯一數值的數據庫對象
自動提供唯一的數值
共享對象
主要用於提供主鍵值
將序列值裝入內存可以提高訪問效率
定義序列語句
CREATE SEQUENCE sequence
[INCREMENTBY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
創建序列
l創建序列DEPT_DEPTID_SEQ為表
DEPARTMENTS
提供主鍵
l不使用CYCLE
選項
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
查詢序列
查詢數據字典視圖USER_SEQUENCES 獲取序列定義信息
SELECT
sequence_name,
min_value,
max_value,
increment_by,
last_number
FROM
user_sequences;
如果指定NOCACHE選項，則列LAST_NUMBER 顯示序列中下一個有效的值
NEXTVAL和CURRVAL偽列
NEXTVAL 返回序列中下一個有效的值，任何用戶都可以引用
CURRVAL 中存放序列的當前值
NEXTVAL應在 CURRVAL之前指定，二者應同時有效
修改序列
修改序列的增量, 最大值, 最小值, 循環選項, 或是否裝入內存
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
修改序列注意事項
l必須是序列的擁有者或對序列有
ALTER權限
l只有將來的序列值會被改變
l改變序列的初始值只能通過刪除序列之後重建序列的方法實現
刪除序列
l使用DROPSEQUENCE
語句刪除序列
l刪除之後，序列不能再次被引用
DROP SEQUENCE
dept_deptid_seq;
序列語句實例
SQL> --序列
SQL> create sequence myseq;
序列已創建。
SQL> create table testseq
2 (tid number,tname varchar2(20));
表已創建。
SQL> --insert into testseq values( ,'aaa');
SQL> select myseq.currval from dual;
select myseq.currval from dual
*
第 1 行出現錯誤:
ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此會話中定義
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1
已選擇 1 行。
SQL> insert into testseq values(myseq.nextval ,'aaa');
已創建 1 行。
SQL> insert into testseq values(myseq.nextval ,'aaa');
已創建 1 行。
SQL> /
已創建 1 行。
SQL> select * from testseq;
TID TNAME
---------- --------------------
2 aaa
3 aaa
4 aaa
已選擇3行。
SQL> select myseq.currval from dual;
CURRVAL
----------
4
已選擇 1 行。
SQL> hostcls
SP2-0042: 未知命令 "hostcls" - 其余行忽略。
SQL> host cls
索引
索引:
一種獨立於表的模式對象, 可以存儲在與表不同的磁盤或表空間中
索引被刪除或損壞, 不會對表產生影響, 其影響的只是查詢的速度
索引一旦建立, Oracle 管理系統會對其進行自動維護, 而且由Oracle 管理系統決定何時使用索引. 用戶不用在查詢語句中指定使用哪個索引
在刪除一個表時, 所有基於該表的索引會自動被刪除
通過指針加速 Oracle
服務器的查詢速度
通過快速定位數據的方法，減少磁盤I/O
創建索引
自動創建: 在定義PRIMARYKEY 或UNIQUE 約束後系統自動在相應的列上創建唯一性索引
手動創建: 用戶可以在其它列上創建非唯一的索引，以加速查詢
l在一個或多個列上創建索引
CREATE INDEXindex
ON table(column[,column]...);
l在表EMPLOYEES的列LAST_NAME
上創建索引
CREATE INDEX
emp_last_name_idx
ON employees(last_name);
Index created.
何時創建索引
以下情況可以創建索引:
l列中數據值分布范圍很廣
l列經常在WHERE
子句或連接條件中出現
l表經常被訪問而且數據量很大，訪問的數據大概占數據總量的2%到4%
何時不要創建索引
下列情況不要創建索引:
l表很小
l列不經常作為連接條件或出現在WHERE子句中
l查詢的數據大於2%到4%
l表經常更新
查詢索引
l可以使用數據字典視圖USER_INDEXES
和USER_IND_COLUMNS 查看索引的信息
SELECT
ic.index_name,
ic.column_name,
ic.column_positioncol_pos,ix.uniqueness
FROM user_indexes ix,
user_ind_columns
ic
WHERE ic.index_name =
ix.index_name
AND ic.table_name = 'EMPLOYEES';
刪除索引
l使用DROPINDEX
命令刪除索引
DROP INDEX
index;
l刪除索引UPPER_LAST_NAME_IDX
DROP INDEX upper_last_name_idx;
l只有索引的擁有者或擁有DROPANY INDEX權限的用戶才可以刪除索引
索引語句示例
SQL> --索引
SQL> create index myindex on emp(deptno,job);
索引已創建。
SQL> --同義詞:別名
SQL> show user
USER 為 "SCOTT"
SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES
*
第 1 行出現錯誤:
ORA-00942: 表或視圖不存在
SQL> /
COUNT(*)
----------
107
已選擇 1 行。
同義詞
為何使用同義詞
使用同義詞訪問相同的對象:
l方便訪問其它用戶的對象
l縮短對象名字的長度
創建和刪除同義詞
l為視圖DEPT_SUM_VU
創建同義詞
CREATE SYNONYM d_sum
FOR dept_sum_vu;
l刪除同義詞
DROP SYNONYM
d_sum;
同義詞語句實例
SQL> create synonym hremp for hr.EMPLOYEES;
create synonym hremp for hr.EMPLOYEES
*
第 1 行出現錯誤:
ORA-01031: 權限不足
SQL> /
同義詞已創建。
SQL> select count(*) from hremp;
COUNT(*)
----------
107
已選擇 1 行。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP20 TABLE
TESTSAVEPOINT TABLE
TESTDELETE TABLE
BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE
TEST3 TABLE
MYPERSON TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPINCOMEVIEW VIEW
TESTSEQ TABLE
HREMP SYNONYM
已選擇14行。
SQL> spool off
