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 empincomeview2 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