程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習（10）：其他數據庫對象

Oracle學習（10）：其他數據庫對象

編輯：Oracle教程

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

Oracle教程