【第32天】Oracle的索引、存储过程、触发器以及SQL语句的优化

最新推荐文章于 2021-04-12 16:47:28 发布

來秋

最新推荐文章于 2021-04-12 16:47:28 发布

阅读量227

点赞数

分类专栏：培训日记文章标签： Oracle DB

本文链接：https://blog.csdn.net/qq_30257081/article/details/85052381

版权

培训日记专栏收录该内容

49 篇文章 3 订阅

订阅专栏

1 索引
2 存储过程
3 触发器
4 Oracle和Mysql的区别
5 SQL语句的优化

1 索引

index，也称目录。数据库会在具有唯一性的列上自动添加唯一性索引。

创建索引：
create index 索引名 on 表名（字段名）;
删除索引：
drop index 索引名;
修改索引只能修改索引的名字，若修改其它，只能删除这个索引后重新创建。
如何查看表中的索引：
select table_name,index_name,uniqueness,status from user_indexes where table_name = ‘表名（大写）’;
索引类型：
- 普通索引：normal
  create index 索引名 on 表名（字段名）;
- 唯一性索引：unique（在添加前字段中一定不能有重复的值）
  create unique index 索引名 on 表名(字段名);
- 位图（分类）索引：bitmap （用于在数据量比较大，数据可选范围比较小的字段，比如：性别，每个人都有，但是数据可选的范围很小）
  create bitmap index 索引名 on 表名(字段名);
- 函数索引：在一个列上通过函数计算后得到的结果上创建索引（一般也属于普通索引）
  create index 索引名 on 表名(函数名(字段名));
创建索引的优缺点：
- 能够更快的定位到查询数据，有效提高查询效率
- 但增删改数据的时候，数据库会浪费资源去维护索引
所以一般加在何处？
- 数据量比较少不需要加索引
- 数据量比较多，查询比较多，增删改比较少的列适合添加索引

2 存储过程

procedure，相当于新华字典的目录。在服务器端，能够被一个或者多个程序调用的SQL语句集。

创建存储过程：

  create [or replace] procedure 
  过程名（参数名 in 参数类型，参数名 out 参数类型）
  as
  变量名  变量类型  :=  值;
  begin
  SQL语句集；
  end;

or replace的意思是若已存在这个过程名，则替换覆盖掉这个过程。

存储过程的参数列表
- 参数名后面的in表示参数列表中这个参数为传入参数，out表示参数列表中这个参数为传出参数。
- 存储过程可以没有参数，如果没有参数则过程名之后不能出现括号。当然也可传入多个传入参数，多个传出参数。
- 存储过程没有返回值，而是通过传出参数来返回数据。

例：实现一个名为pro_hi的存储过程，参数中，若传入参数是1，则返回你好；若传入参数是2，则返回再见。

create or replace procedure
pro_hi(dzh in number,bb out varchar)
as
begin
if dzh = 1
then bb := '你好';
else if dzh = 2
then bb := '再见';
end if;
end if;
end;
/

调用存储过程

  declare 
  变量  数据类型  := 初始值；
  begin 
  过程名(参数，变量);
  end;

运行存储过程，首先需要开启输出行：
set serveroutput on;
put_line()将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。

运行上面的例子：

declare
val varchar2(20) := '';
begin
pro_hi(1,val);
dbms_output.put_line(val);
pro_hi(2,val);
dbms_output.put_line(val);
end;
/

3 触发器

trigger，创建时默认创建表级触发器。常用于调用序列实现主键自增、实现对表操作记录生成日志表等。

for each row:行级触发器

创建触发器：

  create or replace trigger 触发器名
  before/after insert/update/delete on 表名 --什么时候的什么操作时执行触发器
  for each row   --默认表级触发器，加这一行代表这是一个行级触发器
  begin
  SQL语句集；
  end;

例1：Oracle通过触发器调用序列实现主键自增

1.创建表

create table test(
id int primary key,
name varchar2(20)
);

2.创建序列

create sequence seq_test;

3.创建触发器

create or replace trigger tri_insert_test
before insert on test
for each row
begin
select seq_test.nextval
into:new.id
from dual;
end;
/

4.新增数据

insert into test (name) values('苹果');
insert into test (name) values('香蕉');

5.查询数据

select * from test;

例2：通过触发器实现对表操作记录:

1.创建日志表

create table dept_log(
name varchar2(20),
time date
);

2.创建触发器

create or replace trigger dept_log
before delete or insert or update on dept
declare val varchar2(20);
begin
if inserting then
val := '插入一条数据';
elsif updating then
val := '修改一条数据';
elsif deleting then
val := '删除一条数据';
end if;
insert into dept_log values(val,sysdate);
end;
/

3.对表进行操作

insert into dept values(60,'市场部','南京');
update dept set loc = '北京' where deptno = 60;
delete from dept where deptno = 60;

4.查询日志表

select * from dept_log;

4 Oracle和Mysql的区别

Oracle有表空间而Mysql不存在
Oracle主键自增通过序列实现，调用下一个值nextVal；Mysql直接使用autoincrement实现主键自增
Oracle字符（串）仅能使用单引号的处理，Mysql可以用双引号包起字符（串）
分页的SQL语句的处理，Mysql处理分页的SQL语句比较简单，使用LIMIT开始位置，记录个数；Oracle处理分页比较繁琐，使用伪列的rownum字段标明它的位置，且rownum的不等号只能>=0、>=1以及<=任意数，分页需嵌套子查询。

5 SQL语句的优化

建议少用‘*’代替所有列名
用exitsts代替in
连表查询的时候尽量减少表的查询次数
用truncate代替delete
合理使用索引，数据库会在具有唯一性的列上自动添加唯一性索引。索引能够更快的定位到查询数据，有效提高查询效率。但增删改数据的时候，数据库会浪费资源去维护索引。所以在数据量比较少不需要加索引，数据量比较多，查询比较多，增删改比较少的列适合添加索引
sql语句尽量大写，Oracle会把所有语句转换成大写然后在执行
在保证语句完整的情况下，尽量多使用commit(多用在begin…end)
优化group by,将不需要的数据尽量在group by之前过滤掉
连表查询的时候尽量使用表的别名，减少解析时间
表连接在where条件之前（这是一个硬性条件）。and连接条件时，where条件能够过滤掉更多数据的条件放在最开始（得到数据少的先使用where过滤掉）