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

心已赠人 2024-03-30 12:42 65阅读 0赞

**目录**

视图

概念

优点

创建视图

查看视图

修改视图

删除视图

索引

概念

分类

普通索引

唯一索引

复合索引

反向键索引

位图索引

存储函数

概念

语法结构

--------------------

## 视图 ##

### 概念 ###

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

### 优点 ###

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

### 创建视图 ###

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

**字段说明**

<table style="width:595px;"> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th style="width:424px;">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p>OR&nbsp;REPLACE&nbsp;</p> </td> 
   <td style="width:424px;">若所创建的试图已经存在，Oracle&nbsp;自动重建该视图</td> 
  </tr> 
  <tr> 
   <td> <p>FORCE</p> </td> 
   <td style="width:424px;">不管基表是否存在，Oracle&nbsp;都会自动创建该视图</td> 
  </tr> 
  <tr> 
   <td> <p>sub_query&nbsp;</p> </td> 
   <td style="width:424px;">一条完整的&nbsp;SELECT&nbsp;语句，可以在该语句中定义别名</td> 
  </tr> 
  <tr> 
   <td> <p>WITH&nbsp;CHECK&nbsp;OPTION&nbsp;</p> </td> 
   <td style="width:424px;">数据表&nbsp;插入或修改&nbsp;的数据行，必须满足视图定义的约束</td> 
  </tr> 
  <tr> 
   <td> <p>WITH&nbsp;READ&nbsp;ONLY&nbsp;</p> </td> 
   <td style="width:424px;">该视图上不能进行任何&nbsp;DML&nbsp;操作</td> 
  </tr> 
 </tbody> 
</table>

### 查看视图 ###

> SELECT \* FROM '视图名';

代码

select * from dept_emp_view;

结果

### ![39e3a8a03436492a94d5e42cbecf5265.png][]修改视图 ###

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

### 删除视图 ###

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

## 索引 ##

### 概念 ###

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

![fc5d69183f3c4f3aac381a3838badb55.png][]

### 分类 ###

#### 普通索引 ####

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;

结果

![05043f6ca0f44f46919c63d9f26aa0b5.png][]

[39e3a8a03436492a94d5e42cbecf5265.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8670b08cc5a240cfa992966dc420a9cc.png
[fc5d69183f3c4f3aac381a3838badb55.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d717569179344a868e20a31a6385910b.png
[05043f6ca0f44f46919c63d9f26aa0b5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/49749db4a2e241e1979c33952149def3.png