Oracle—视图和其他数据库对象

视图

开发工具与关键技术：Oracle
作者：吴东梅
撰写时间：2019-04-20

•视图是一种虚表。
•视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
•向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.(select语句后面跟的就是数据就是在查询视图时显示出来的数据)
•视图向用户提供基表数据的另一种表现形式
创建视图
•在 CREATE （OR REPLACE） VIEW 语句中嵌入子查询
•子查询可以是复杂的 SELECT 语句

第一种写法,用create View 直接创建
用create View 方法创建完命名视图名为empview基于employees e,departments d 表的视图，再用查询视图的方法查询这个视图，那么这个视图显示出来的就是子查询里面查询出来的字段。如下图：

第二种写法,如果在创建视图的时候，遇到要创建相同的视图时，不用去把原来的视图删除掉，直接用create or replace 把原来的视图替换掉,把最新的视图更新上去。用上面的截图和下图对比就会发现，视图名相同，但是下图数据已经是我最新创建的，因为我用create or replace去把原来的视图换掉了，更新了原来的数据。

但是用这种方法去创建视图会有一个很大的弊端：就是用CREATE OR REPLACE VIEW 子句去创建视图，如果你创建的视图名跟别人的是一样的，但是子查询的数据又不是一样的，这样就会造成冲突，到时候查出来的数据就不知道是谁想要的了。所以起视图名要慎重。就如上面的两个截图效果可以看出，所以一定要注意。
查询视图
SELECT *
FROM empview; --查询视图 empview 视图名
修改视图
•使用CREATE OR REPLACE VIEW 子句修改视图。
（就相当于用create or replace 这个方法去创建一个新的视图，把原来的视图替换掉.）
图一：原视图：

图二：用create or replace 对视图进行修改过后的视图

删除视图
删除视图只是删除视图的定义，并不会删除基表的数据
语法：DROP VIEW + 视图名。
当执行完删除视图的语句时，再次去查询此视图，那么就会报以下的错：所以要注意

ROWNUM 伪列
注意:
对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。
1、对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
2、查询rownum在某区间的数据，必须使用子查询。
3、如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于
rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成
立，所以查不到记录。
以下来个例题，就会了解如何去使用rownum 伪列了。
–把工资按降序排序之后，在查出排名50到100的员工信息。
select * from(
select rownum r,e.*
from(
select employee_id,salary
from employees
order by salary desc
) e
) where rownum>50 and r<=100;
其他数据库对象
CREATE SEQUENCE 语句
定义序列:
CREATE SEQUENCE sequence --sequence 序列名
[INCREMENT BY n] --每次增长的数值
[START WITH n] --从哪个值开始
[{MAXVALUE n | NOMAXVALUE}] --最大是多少
[{MINVALUE n | NOMINVALUE}] --最小是多少
[{CYCLE | NOCYCLE}] --是否需要循环
[{CACHE n | NOCACHE}]; --是否缓存登录
创建序列
•创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键
•不使用 CYCLE 选项
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10 --每次增长的数值是10
START WITH 120 --从120开始
MAXVALUE 9999 --最大是9999
NOCACHE --不需要循环
NOCYCLE --不缓存登录
使用上面的方法可以创建序列，根据自己的需求去设置序列里面的定义，一般要求的都是的按顺序来，所以每次的增长数值是1。
查询序列
•查询数据字典视图 USER_SEQUENCES 获取序列定义信息
•如果指定 NOCACHE 选项，则列LAST_NUMBER 显示序列中下一个有效的值。
如下图，就是查询出刚刚新建的序列的详细信息：

NEXTVAL 和 CURRVAL 伪列
•NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
•CURRVAL 中存放序列的当前值
•NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。
1、查询 dept_deptid_seq 序列的 nextval（下一个的值）
select dept_deptid_seq.nextval FROM dual;
从上面创建的序列方法中，可以知道数值是从120开始的，所以当我们执行此语句的第一次的时候，返回的数据值是120，如图一，当第二次执行此语句的时候，返回的数据值是130，因为每次的增长数值是10，如图二：
图一：

图二：

2、查询 dept_deptid_seq 序列的 CURRVAL（当前是哪个值）
select dept_deptid_seq.CURRVAL from dual;
因为前面我们只查询了两次的查询下一个值，刚好到了130，所以此时查询当前有效值也就是130了如图一，执行此语句的时候，必须先执行过NEXTVAL 返回序列中下一个有效的值，否则直接运行此语句将会报图二的错。
图一：

图二：

修改序列
修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20 --增量
MAXVALUE 999999 --最大值
NOCACHE --循环选项
NOCYCLE --是否装入内存
修改序列的注意事项
•必须是序列的拥有者或对序列有 ALTER 权限
•只有将来的序列值会被改变
•改变序列的初始值只能通过删除序列之后重建序列的方法实现
1、当我用上面修改序列的方法修改成功序列之后，我们可以再次查询序列的详细信息，你会发现每次的增量已经由10变成了20，最大值也从9999变成了999999，如下图所示：

我们知道前面运行这个序列的数据值到了130，然后修改成功之后我再次运行的时候就会变成150，因为我把每次的增量从10变成了20，如下图：

删除序列
•使用 DROP SEQUENCE 语句删除序列
•删除之后，序列不能再次被引用
DROP SEQUENCE + 序列名。
DROP SEQUENCE dept_deptid_seq;
使用上面删除语句，把创建的序列删除掉之后，再次去查询这个序列，则会报以下的错误，这就证明了已经删除成功了。

索 引
•索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
•通过指针加速 Oracle 服务器的查询速度
创建索引
•自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
•手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
•在一个或多个列上创建索引
•索引不需要用，只是说我们在用name进行查询的时候，速度会更快。当然查的速度快了，插入的速度就会慢。因为插入数据的同时，还需要维护一个索引。
语法：
CREATE INDEX index
ON table (column[, column]…);–可以多个字段创建索引
1、在表 EMPLOYEES的列 LAST_NAME 上创建索引
CREATE INDEX emp_last_name_idx
ON employees(last_name);
查询索引
•可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
前面已经创建了表 EMPLOYEES的列 LAST_NAME 上的索引，我们可以查询出来查看。如下图所示，刚刚是已经创建好一个索引了。

删除索引
•使用DROP INDEX 命令删除索引
DROP INDEX index; --index 是索引名
•只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引
•删除操作是不可回滚的
1、 把表 EMPLOYEES的列 LAST_NAME 上的索引删除掉。
DROP INDEX emp_last_name_idx; --删除索引
把前面创建的索引用上面的删除语句删掉，再次查询就会发现那个删除的索引已经不存在了。如下图所示：