Oracle --- 视图 & 索引 &语法结构

最新推荐文章于 2024-01-06 08:15:00 发布

快乐画饼

最新推荐文章于 2024-01-06 08:15:00 发布

阅读量2k

点赞数

分类专栏： oracle 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/qq_57151931/article/details/128468146

版权

oracle 专栏收录该内容

4 篇文章 2 订阅

订阅专栏

视图

概念

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表。
视图所对应的数据，并不是真正的存储在视图中，而是存储在所引用的数据表中。
视图的结构和数据，是对数据表进行查询的结果。

优点

简化数据操作：视图可以简化用户处理数据的方式。
着重于特定数据：不必要的数据或敏感数据，可以不出现在视图中。
视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
提供向后兼容性：视图使用户能够在表的架构更改时，为表创建向后兼容接口。
集中分散数据。
简化查询语句。
重用SQL语句。
保护数据安全。
共享所需数据。
更改数据格式。

创建视图

CREATE [OR REPLACE] [FORCE] VIEW '视图名'
AS '子查询'
[WITH [CASCADED|LOCAL] CHECK OPTION]
-- 只读。
[WITH READ ONLY]

示例代码

create or replace view dept_emp_view as
select SCOTT.EMP.ENAME, SCOTT.EMP.SAL, SCOTT.DEPT.DEPTNO, SCOTT.DEPT.DNAME, SCOTT.DEPT.LOC
from scott.DEPT
         right join SCOTT.EMP on DEPT.DEPTNO = EMP.DEPTNO;

字段说明

字段	说明
OR REPLACE	若所创建的试图已经存在，Oracle 自动重建该视图
FORCE	不管基表是否存在，Oracle 都会自动创建该视图
sub_query	一条完整的 SELECT 语句，可以在该语句中定义别名
WITH CHECK OPTION	数据表插入或修改的数据行，必须满足视图定义的约束
WITH READ ONLY	该视图上不能进行任何 DML 操作

查看视图

SELECT * FROM '视图名';

代码

select * from dept_emp_view;

结果

修改视图

ALTER VIEW '视图名' [('列名1' ..., '列名n')]
AS 'select_statement'
[WITH [CASCADED|LOCAL] CHECK OPTION]

删除视图

DROP VIEW [IF EXISTS] '视图名'[,'视图名2'] ... [RESTRICT|CASCADE];
-- RESTRICT：限制。
-- CASCADE：级联。

索引

概念

索引是用于加速数据存取的数据对象。
合理的使用索引可以大大降低 I/O 次数，从而提高数据访问性能。
索引是需要占据存储空间的，也可以理解为是一种特殊的数据。
形式类似于下图的一棵树，而树的节点存储的就是每条记录的物理地址，也就是提到的 ROWID（伪列）

分类

普通索引

create index '索引名称' on '表名'('列名');

代码

create index only_name on demo (name);

唯一索引

需要在某个表某个列创建索引，而这列的值是不会重复的。
可以创建唯一索引。

create unique index '索引名称' on '表名'('列名');

代码

create unique index only_name on demo (name);

复合索引

经常需要对某几列进行查询。
比如，经常要根据学历和性别对学员进行搜索。
如果对这两列建立两个索引，因为需要查两棵树，查询性能不一定高。
建立复合索引，也就是基于两个以上的列建立一个索引。

create index '索引名称' on '表名'('列名1', '列名2', ...);

代码

create  index only_name on demo (id, name) ;

反向键索引

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。
这样会增加查询的层数，性能会下降。
建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

create index '索引名称' on '表名'('列名') reverse;

代码

create index only_name on demo (name) reverse ;

位图索引

使用场景：位图索引适合创建在低基数列上。
位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射。
优点：减少响应时间，节省空间占用。

create bitmap index '索引名称' on '表名'('列名');

代码

create bitmap index only_name on demo (name);

存储函数

概念

存储函数又称为自定义函数。
可以接收一个或多个参数，返回一个结果。
在函数中可以使用 PL/SQL 进行逻辑的处理。

语法结构

创建 或 修改存储过程。
CREATE [ OR REPLACE ] FUNCTION '函数名称'('参数名称1' '参数类型1', '参数名称2' '参数类型2', ...)
RETURN '结果变量数据类型'
AS
 '变量声明部分';
BEGIN
 '逻辑部分';
    RETURN '结果变量';
[EXCEPTION '异常处理部分']
END;

示例代码

oracle自带的表，查询每个部门的人数

create or replace function part(char1 varchar2) return varchar2
as
    sum1      number;
    resultStr varchar2(32);
begin
    select count(EMP.DEPTNO)
    into sum1
    from SCOTT.DEPT
             left join SCOTT.EMP on DEPT.DEPTNO = EMP.DEPTNO
    where DNAME = char1;

    resultStr := char1 || '有' || sum1 || '个人';
    return resultStr;
end;


select PART('SALES')
from DUAL;

结果