1.优化技巧

1.表名和字段名都尽量使用别名，可以减少oracle数据库解析表名和字段名的时间，且不要直接使用select *

2.⽤EXISTS替代IN、⽤NOT EXISTS替代 NOT IN。在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).

语句1 
SELECT dname, deptno FROM dept 
WHERE deptno NOT IN (SELECT deptno FROM emp); 

语句2 
SELECT dname, deptno FROM dept WHERE NOT EXISTS 
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

语句2要比语句1的执行性能好很多。因为1中对emp进行了full table scan（全表扫描）,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

3.尽量用UNION-ALL 替换UNION (业务情况符合的话)，UNION-ALL可能会返回重复的数据，UNION会进行过滤，所以UNION的效率比较慢

4.利⽤>=替代>，因为>=可以直接定位到＝的位置，⽽>必须先定位位置，然后再查询下⼀个数据，耗时更久。

5.通配符%的使用，不要让’%'在前面，
WHERE子句中, 如果索引列所对应的值的第一个字符由通
配符(WILDCARD)开始, 索引将不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES 
WHERE USER_NO LIKE '%109204421';
在这种情况下，ORACLE将使用全表扫描.
WHERE USER_NO LIKE '109204421%';
这钟情况ORACLE则不会进行全表扫描

6.用EXISTS替换DISTINCT
低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME FROM DEPT D
WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

7.减少对表的查询，特别在⼦查询中，能尽量少重复访问表，就减少。

8.如果分组当中含有查询条件，要改写为where条件进⾏过滤后，再进⾏分组，⽽不是直接进⾏ having 条件。

9.组合索引必须使用先导列,如果索引是建立在多个列上, 只有在它的第一个列(先导列) 被where子句引用时,优化器才会选择使用该索引.

create table multiindexusage ( inda number , indb number , descr varchar2(10));
create index multindex on multiindexusage(inda,indb);
select * from multiindexusage where inda = 1; --走索引
select * from multiindexusage where indb = 1; --不走索引

10.sql语句中，where条件中要正确使用函数或者表达式，不正确使用，会进行字段类型转换，字段上索引失效

下面语句会使索引失效，并且把数据库库中所有记录的log_time时间值转换
为字符后，和条件中的字符串进行比较，走全表扫描，sql 执行效率大大降
低。
select log_time from account_login where to_char(log_time,'yyyy-mm
-dd hh24:mi:ss')='2009-04-01 00:00:00';
正确写法，使用to_date函数
select log_time from account_login where log_time=to_date('2009-0
4-01 00:00:00','YYYY-MM-DD HH24:MI:SS');

当比较不同数据类型的数据时, ORACLE自动对列进行简单
的类型转换.

假设EMP_TYPE是一个字符类型的索引列.
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
这个语句被ORACLE转换为:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因为内部发生的类型转换, 这个索引将不会被用到!

11.避免在索引列上使用函数.WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL  > 25000/12;

12.避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT, NOT会产生在和在
索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就
会停止使用索引转而执行全表扫描.

低效: (这里,不使用索引)
SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;
高效: (这里,使用了索引)
SELECT … FROM DEPT WHERE DEPT_CODE > 0;

13.避免在索引列上使用 IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．

如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!

因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该引.

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

14.在含有子查询的SQL语句中,要特别注意减少对表的查询.

低效
      SELECT TAB_NAME
      FROM TABLES
      WHERE TAB_NAME = ( SELECT TAB_NAME  FROM TAB_COLUMNS WHERE VERSION = 604)
      AND　DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS
       WHERE VERSION = 604)
高效
    SELECT TAB_NAME FROM TABLE WHERE  (TAB_NAME,DB_VER)
	= ( SELECT TAB_NAME,DB_VER)  FROM TAB_COLUMNS
    WHERE VERSION = 604)

15.where 条件后⾯的的条件解析是从下向上，从后到前解析执⾏的，所以可以把过滤数据量较多的条件放在最后⾯。

（在mysql中正好相反，mysql中是从左到右）

16.关联查询时，选择好主表。oracle解析器对from 后⾯的表的解析是从右到左的，所以把数据量较⼩的表作为主表，然后和其他表进⾏关联，假如存在三个以下表，把同时交叉关联的表作为主表，提⾼查询效率。即大表写前面，小表写后面，过滤多的在后，同时交叉的在后。（mysql刚好相反，mysql尽量把小表写在前面，或者说写在左边）

如果从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：
	select * from emp e,dept d 
	where d.deptno >10 and e.deptno =30 ; 
如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。
	select * from emp e,dept d 
	where e.deptno =30 and d.deptno >10 ;

核心思想.

合理使用索引，避免索引失效和全面扫描的情况

优化建议

1.可以使用执行计划，执行计划可以查看相关查询条件是否进⼊索引，方便定位问题

2.避免会导致全表扫描的操作，如distinct、Union、在索引列使用函数、计算、转换、NOT、IN、NOT IN等等

2.Oracle优化

10.1 数据库的设计优化

1、合理设计数据表结构。

合理选择建模方法（范式建模、维度建模）、字段、数据类型、约束条件、表空间大小。

（1）索引优化。

创建合适的索引，避免全表扫描，加快查询速度。要注意索引数量不能过多。

在使用索引的时候，也要尽量避免产生回表，尽管这很多时候很难避免。回表：使用普通索引的时候，如果索引里不包含全部要查找的字段，则需要回到表中查找需要的字段，这个过程也叫做回表。

（2）分区表优化。

通过合理分区减少查询的数据量，加快查询速度。

（3）计算方式优化。

通过分布式、并行计算，加快查询速度。

10.2 SQL调优

1、尽量少用子查询。子查询的结果集会占用内存，影响查询效率。

2、使用表别名。简化SQL语句，就减少了SQL解析，加快了查询速度。

3、减少以下效率低关键字的使用。

（1）union并集去重、minus差集、intersect交集的集合运算，这些关键字效率很差；

（2）用exists代替in、distinct关键字；

（3）or的效率比较低，可以使用union all去进行替换；

（4）模糊查询like；

4、使用临时表。简化SQL语句，就减少了SQL解析，加快了查询速度。

5、尽量不用排序order by。排序是相当耗费资源的。

6、避免使用select *，会导致全表扫描，造成不必要的资源开销且增加了查询时间。所以尽量只查需要的字段。

7、有大量DML语句时多使用commit，可以释放缓存，同时释放锁资源。

8、表连接的调优。

表的内部逻辑有三种不同的算法连接方式：

（1）hash join(哈希连接)

定义：等值关联，性能好，但占用内存较大。它是将被驱动表和其计算的hash值表一起放入缓存，然后在驱动表中逐条取数据匹配相应hash值，再精确匹配数据。所以，适用于大型数据集或大规模连接。

（2）nested loops(嵌套循环连接)

定义：内存占用小，但性能好。它是依次从驱动表中取一条数据，遍历被驱动表，将匹配的数据放入缓存中。所以适用于两表的数据差异大得情况。

（3）merge join(排序归并)

定义：不等关联，将关联的a表跟b表分别进行排序，生成临时的两张表后，随机取一张表逐条抽取记录与另一张表匹配。

以上的3中连接方式Oracle的优化器会评估查询的条件、表大小、索引使用等因素，并选择最适合的表连接算法来执行查询。当然也可以采用hints优化器强制改变连接方式：

改变表连接的逻辑：

/*+use_hash(a b) */ 强制使用哈希连接

/*+use_merge(a b)*/ 强制使用排序归并

/*+use_nl(a b) */ 强制使用嵌套连接

/*+leading(a b) */ a一定要是小表(驱动表)，b一定是大表(匹配表)

/*+ full(a) */ 强制全表扫描

/*+ index(表名索引名) */ 强制使用索引

/*+ parallel(8) */ 强制使用并行的资源，来执行这个sql语句

10.3 参数配置优化

合理调整数据库的参数配置，如SGA和PGA的大小调整、日志和缓存的配置、并发连接数的设置等。主要参数有：SGA_TARGET（SGA）、DB_BLOCK_SIZE（块）、UNDO_RETENTION、SORT_AREA_SIZE（排序时内存）、LOG_BUFFER（日志缓存）。

10.4 硬件优化

一般是增加内存、磁盘。还可以采用集群

10.5 优化常用工具

SQL调优时常用的几个监控和诊断工具：

1、执行计划。

可以通过PLSQL或终端查看执行计划SQL语句执行顺序、CPU耗费、IO耗费等。（PLSQL中在编译计划窗口打开SQL即可查看）

2、 SQL Trace。

可以跟踪SQL语句的执行情况，包括执行时间、IO操作、锁等信息。

打开方法：

用管理员用户打开session的trace：

ALTER SESSION SET SQL_TRACE = TRUE;

查看trace文件的存储位置：

SELECT value FROM V$DIAG_INFO WHERE name= 'Diag Trace';

在该目录下打开文件即可。

3、AWR报告。

通过AWR报告查看数据库的性能指标资源使用情况，比如CPU使用率、内存使用率、IO等待时间等；以及top10的SQL分析。

打开方法：

1.终端下登陆dba用户：（验证是否连接）

sqlplus / as sysdba

2.然后输入下面这句：

@?/rdbms/admin/awrrpt.sql

3.输入 report_type 的值:

html或text

4.输入 num_days 的值:（1就是今天，2就是昨天和今天，依次排列）

5.输入 begin_snap 的值:（开始的snap id）

按照实际输入

6.输入 end_snap 的值:

按照实际输入

7.输入 report_name 的值:

文件名.html

8.然后去相应的目录找到该文件用网页打开即可

具体更多的指标解读分析请查询相关的知识。

10.6 优化思路

对于Oracle已存在的SQL语句运行很慢的问题的处理：

先通过执行计划或AWR报告获取该语句影响运行存在的问题，制定合适的方案。

数据非常大，那么建分区，只查询数据所在的分区表，避免整张大表的查询。

查看有没有导致索引失效的语句。

给需查询的字段创建索引。

查找SQL语句中有无低效率的关键字。

SQL语句频繁的对硬盘进行了读写，可以把语句写成代码块。

使用并行或分布式计算。

有内存的原因则增加内存。

3.数据查询语言

语法：
select [DISTINCT] [字段]|[*]
from 表名
[where 查询条件]
[group by 分组条件]
[having 过滤条件]
[order by 排序条件 asc|desc]
[分页条件];
执行顺序：
1. FROM阶段
2. WHERE阶段
3. GROUP BY阶段
4. HAVING阶段
5. SELECT阶段
6. ORDER BY阶段

3.1 去重distinct查询

--查询所有部门的编号

select distinct deptno from emp;

3.2 where条件查询

--输出工资在2000-4000范围的数据

select * from emp

where sal between 2000 and 4000;

3.3 group by分组查询

--求各个部门的总工资

select deptno,sum(sal) from emp group by deptno

3.4 having过滤查询

--求各个部门的总工资

select deptno,sum(sal) as sum sal from emp group by deptno having sum(sal) > 7000

3.5 order by排序查询

--求各个部门的总工资

select deptno, sum(sal) as sum sal from emp group by deptno having sum(sal) > 7000 order by sum_sal

3.6 分页查询

在Oracle的用法是：

给表加上行号的字段，再通过子查询的方式来实现。

--求表中前5条数据

select from(select e.*,rownum from emp e) where rownum <=5;

3.7 多表查询

多表查询是多张表相关联进行的查询，关键点在于表关联字段。

3.7.1 内连接

select * from a join b on a.条件=b.条件 where 条件;

e.deptno=d.deptno的作用：它对两个表进行连接查询，oracle会扫描dept表，分别检查每条记录在连接条件e.deptno=d.deptno中字段的值从emp表取出的记录的列值是否相等。如果相等，则将这两个记录连接，产生一条新的记录作为查询到的一行，再继续从表emp中取下一条记录。重复这个步骤，一直到处理完emp表的全部符合条件的记录。

select e.ename,e.deptno,d.dname,d.locfrom emp e join dept d on e.deptno=d.deptno where e.ename='KING';

3.7.2 外连接

外连接有三种类型：左连接、右连接、全连接

3.7.2.1 左连接

作用：可以显示相等连接时左表存在右表不存在的数据，右表不存在的记录用空值显示

select * from a left join b on a.条件=b.条件

3.7.2.2 右连接

作用：可以显示相等连接时左表不存在右表存在的数据，左表不存在的记录用空值显示

select * from a right join b on a.条件=b.条件

3.7.2.3 全连接

作用：可以显示相等连接时左右表互相不存在的数据、左右表互相不存在的记录用空值显示

select * from a full join b on a.条件=b.条件

3.7.3 交叉连接

交叉连接的原理是笛卡尔积，也就是两表的乘积。

假如a表有3条数据，b表有4条数据，那么交叉连接后共12条数据。

select * from a cross join b

3.8 联合查询

联合查询就是将上下两个结果集进行并集、交集、差集运算。

取结果集时有几点注意：

上下两个结果集的字段的类型要一致。

上下两个结果集的字段的顺序要一致。

上下两个结果集的字段的数量要一致。

3.8.1 并集

并集有两种处理方式：union all和union，一个是两个结果集数据全取合并成一个结果集，一个是做去重后的结果集。

3.8.2 交集

intersect：显示两个结果集共有的数据部分。

select from emp where deptno=20

union

select from emp where sal>2000;

3.8.3 差集

minus：显示第一个结果集A有的内容，但是第二个结果集B没有的数据，即A-B。

select job from emp where deptno=10

minus

select job from emp where deptno=20;

3.9 子查询

3.9.1 FROM子句中的子查询

select * from (select empno,ename,job,sal,deptno from emp) e where sal < 2000;

3.9.2 运算符的子查询

运算符主要有：>、>=、<、<=、!=、<>、=、<=>、in、not in、any、some、all、exits、not exists

现以exists举例（主要当初学习exists，摸索了好两天才学明白它的作用）：

用法：select from A where exists(select from B where A.id = B.id);

原理：

先执行外查询select * from A，然后取出一条数据传给内查询。

再执行内查询select * from B，外查询传入的数据和内查询的数据如果满足where条件A.id = B.id，则返回ture，如果一条都不满足，则返回false。

内查询返回true，则外查询的这行数据保留，反之内查询返回false则外查询的这行数据不保留。外查询的所有数据逐行查询传入内查询匹对。

--查询己有部门的所有员工

select from emp where exists (select from dept where emp.deptno=dept.deptno);

3.9.3 SELECT下的子查询

3.9.4 with...as 子查询

with...as用来定义一个sql片段,且该片段会被整个sql语句反复使用很多次，这个sql片段就相当于是一个公用临时表

例：

with aaa as (select * from emp)

select * from aaa;

with...as需和查询语句一起执行，否则报错无select关键字；还有不能在with..as后加分号，不然也报错无select关键字。另外查询语句有多表查询不能有相同的列，否则，with..as无法识别具体哪个列。

3.Oracle函数

3.1 数值型函数

近似函数，五舍四入：round(x,2)

向下取整函数：floor(x)

向上取整函数：ceil(x)

取随机函数：DBMS_RANDOM.VALUE(a,b)

幂运算函数：power(x,n)

平方根函数：sqrt(x)

绝对值函数：abs(x)

取余函数：mod(x,y)

3.2 字符型函数

截取字符串的一部分：substr(字符串, 开始位置, 连续取值的长度)

两个字符串的拼接：concat(x1, x2)

字符串替换：replace(x, old, new)

字符查找函数:instr(源字符串, 目标字符串, 起始位置, 匹配序号)

转大写字母:upper()

转小写字母:lower()

返回一个字符串的长度:length()

3.3 时间日期函数

返回当前日期和时间：SYSDATE

从一个date类型中截取year,month,day：extract()

计算日期的月份偏移正数时间往后推移，负数时间往前推移：add_months(x, m)

计算日期的最后一天：last_day(x)

计算两个日期之间的月份差：months_between(x1, x2)

例：

select extract(year from sysdate),extract(month from sysdate),extract(day from sysdate) from dual;

select months_between(date'2020-9-2',date'2019-9-1') from dual;

3.4 聚合分组函数

SUM：计算一组数值的总和。

AVG：计算一组数值的平均值。

MAX：返回一组数值中的最大值。

MIN：返回一组数值中的最小值。

COUNT：计算一组数值的个数。

3.5 开窗函数

3.5.1 用法

计算函数部分() over(partition by 列名 order by 列名 asc|desc)

--计算函数部分：sum,count,max,min,avg,row_number(),rank()等，只能是一个函数

--over() 关键字

--partition by ：分组，根据分区表达式的条件逻辑将单个结果集分成N组，不是必选项

--order by：对分区中的数据排序,不是必选项

例：

--计算工资与部门最高工资的差

select

emp.*,max(sal) over(partition by deptno) maxsal,

sal-max(sal) over(partition by deptno) c

from emp ;

3.5.2 排名函数

1.row_number()：根据某个列，按照顺序进行排序 1 2 3 4

2.rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，会跳过占用的名次 1 2 2 4

3.dense_rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，不会跳过名次 1 2 2 3

3.5.3 平移函数

在平常业务中一般用来实现数据的同比和环比的计算。

同比：今年的7月和去年的7月相比，在两个时间范围内，取相同时间节点的数据进行比较

环比：今年的7月和今年的6月相比，在同一个时间范围内，取相邻的时间节点的数据进行比较

lag()：将数据往下平移一行 + 开窗函数

lead()：将数据往上平移一行 + 开窗函数

表及数据：

create table sale_info(
y number,
m number,
amount number(7,2)
);
insert into sale_info values(2018,1,2342);
insert into sale_info values(2018,2,1234);
insert into sale_info values(2018,3,3545);
insert into sale_info values(2018,4,3456);
insert into sale_info values(2018,5,2342);
insert into sale_info values(2018,6,4534);
insert into sale_info values(2018,7,3453);
insert into sale_info values(2018,8,2342);
insert into sale_info values(2018,9,4352);
insert into sale_info values(2018,10,1312);
insert into sale_info values(2018,11,3453);
insert into sale_info values(2018,12,1235);
insert into sale_info values(2019,1,3453);
insert into sale_info values(2019,2,1233);
insert into sale_info values(2019,3,3445);
insert into sale_info values(2019,4,1233);
insert into sale_info values(2019,5,1231);
insert into sale_info values(2019,6,4234);
insert into sale_info values(2019,7,1231);
insert into sale_info values(2019,8,2131);
insert into sale_info values(2019,9,1231);
insert into sale_info values(2019,10,3421);
insert into sale_info values(2019,11,1231);
insert into sale_info values(2019,12,1231);
commit;
需求1：计算2019年每一个月和2018年同月份的数据增长率。(同比)
需求2：计算2019年的每一个月，比上个月增长了百分之多少？(环比)
需求1：
select 
       b.*,c/lo,
       round(c/lo*100,2)||'%' 增长率
from 
       (select
              a.*,lag(amount) over(partition by m order by y) lo,
              amount-lag(amount) over(partition by m order by y) c 
       from sale_info a) b
where y=2019;
注：这里可以看到同比增长率显示有问题，-0.08%不能显示。这是Oracle的显示问题，对于小于1的小数，小数点前面的0是不显示的。
解决方案：对该数值转为字符类型。
to_char(round(c/lo*100,2),'fm99990.0099')||'%' 增长率
需求2：
select 
      b.*,amount-lo c, 
      to_char(round((amount-lo)/lo*100,2),'fm99990.0099')||'%' 环比增长率
from 
      (select a.*,lag(amount) over(order by m) lo from sale_info a where y=2019) b;  
当然，同环比的计算方法也可以用多表连接来处理，在方法上都不难

3.6 转换函数

转换函数最常用的就4个：

转换成日期类型：to_date()

转换成字符串类型：to_char()

转换成数值类型：to_number()

将某种数据类型的表达式显式转换为另一种数据类型：cast(表达式 as 数据类型)

3.7其他函数

rownum：取行号函数

rowid：数据去重，表里面每一行数据，在数据库中的唯一编号，不会出现重复的数据

pivot：行列转换

case when：条件取值函数

decode：功能同case when差不多，也是条件取值

unpivot：列转行函数

nvl(列名, 如果这列为空时设置的默认值)

nvl2(列名, 如果不为空显示的内容, 如果为空显示的内容)

sign()：是用来判断数据是负数、0、正数分别返回-1、0、1

4.PLSQL编程

4.1 格式写法

declare

  --变量的声明部分

  a number;

  b varchar2(10);

  c date;

begin

  --代码的逻辑和执行部分

  a:=100;

  b:='hello';

  c:=sysdate;

  dbms_output.put_line(a||b||c);

end;

4.2 变量

变量主要有3种类型：普通变量、引用型变量、记录型变量。

4.2.1 普通变量

普通变量

a number;

4.2.2 引用型变量

格式：变量名字表名.列名%type;

declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=7369;

dbms_output.put_line('工资是'||v_sal);

end;

4.2.3 记录型变量

记录型变量也叫数组型变量

格式：变量名表名%rowtype;

declare

v_user emp%rowtype;

begin

select * into v_user from emp where empno=7369;

dbms_output.put_line(v_user.ename||','||v_user.job);

end;

4.2.4 变量输入

变量值的输入：

数字类型的变量名:=&提示语句;

字符串类型的变量名:='&提示语句';

例：

declare

v_user emp%rowtype;

--v_empno emp.empno%type;

v_ename emp.ename%type;

begin

--v_empno:=&输入要查询的员工编号;

v_ename:='&输入员工姓名';

select * into v_user from emp where ename=v_ename;

dbms_output.put_line(v_user.ename||','||v_user.job);

end;

注：

1.在代码块中，所有的select都一定要有into来存放你查询出来的数据

into 变量，只能放一个数据，并且一定要有数据。

2.在提示语句中，字符串和数值的一个有引号一个没有引号

4.3 If条件判断语句

If判断语句3种写法，应该都不难于理解。

1.

if 条件判断 then

    执行sql语句;

end if;

2.

if 条件判断 then

    执行sql语句;

else

    执行sql语句;

end if;

3.

if 条件判断 then

    执行sql语句;

elsif 条件判断 then

    执行sql语句;

elsif 条件判断 then

    执行sql语句;

......

else

    执行sql语句;

end if;

和if语句相同类型和效果的另一些判断语句的写法：case when

case

when 条件判断 then 执行的sql语句 ;

when 条件判断 then 执行的sql语句 ;

when 条件判断 then 执行的sql语句 ;

......

else 执行的sql语句;

end case;

例：输入部门编号得到部门员工数量。

declare

  v_deptno number :=&输入部门编号;

   c number;

   v_user emp%rowtype;

begin

     select count(*) into c from emp where deptno=v_deptno;

     if c=0 then

       dbms_output.put_line('部门编号:'||v_deptno||'的部门没有员工');

    elsif c>=2 then

       dbms_output.put_line('部门编号:'||v_deptno||'的部门有'||c ||'名员工');

     else 

       select * into v_user from emp where deptno=v_deptno;

       dbms_output.put_line('部门编号:'||v_deptno||'的部门'||'员工编号：'||v_user.empno

       ||'的工资是'||v_user.sal);

     end if;

end;

4.4 循环语句

Oracle循环有3种：for循环、while循环、loop循环。

循环终止有两个关键字：

continue：直接开始下一次的循环，忽略掉continue后面的代码。

exit：跳出整个循环，停止循环。

在循环中有个特别的关键语句：goto，但是不建议使用，容易引起逻辑的混乱，不过也有奇效，后续不再讲，就在此处单独说说。

定义：可以理解为一个标签，使用goto能进行代码的任意的跳转。

例：（可以自己试验下结果就能理解它的定义作用）

declare

begin

for i in 1..10 loop

if i=5 then

goto here;

end if;

dbms_output.put_line(i);

end loop;

dbms_output.put_line('world');

<<here>>

dbms_output.put_line('hello');

end;

4.4.1 for循环

功能：有循环范围的循环方式，一开始就知道运行次数的循环方法。

格式：

for 变量名 in 循环范围 loop

执行的sql语句;

end loop;

例1：将1+3+5++......+99的和，计算出来

declare

s number;

begin

s:=0;

for i in 1..99 loop

if mod(i,2)=1 then

s:=s+i;

end if;

end loop;

dbms_output.put_line(s);

end;

例2：输入部门编号，输出部门人数。

declare

v_dept number :=&请输入部门编号;

c number;

--v_user emp%rowtype;

begin

select count(*) into c from emp where deptno= v_dept;

case

when c=0 then

dbms_output.put_line('部门编号：'||v_dept || '的部门没有员工');

else

dbms_output.put_line('部门编号：'||v_dept || '的部门有' || c ||'名员工！！！');

for i in (select * from emp where deptno=v_dept) loop

dbms_output.put_line('-----'||i.empno||' '||i.ename||' '||i.job);

end loop;

end case;

end;

4.4.2 While循环

while循环，当循环的范围不是特别确定的时候。

while条件如果判断为真，就进入到循环，否则跳出循环

格式：

while 条件判断 loop

执行的sql语句;

end loop;

例：输出数字1到10

declare

n number;

begin

n:=1;

while n<=10 loop

dbms_output.put_line('数字'||n);

n:=n+1;

end loop;

end;

4.4.3 Loop循环

Loop循环也是适用当循环的范围不是特别确定的时候。

Loop循环条件判断为真，则exit退出循环，否则进入循环。

格式：

loop

exit when 条件判断;

执行的sql语句;

end loop;

例：输出10,8,6,4,2,0

declare

n number;

begin

n:=10;

loop

exit when n<0;

dbms_output.put_line(n);

n:=n-2;

end loop;

end;

4.3.4 continue和exit

定义：

continue：直接开始下一次的循环，忽略掉continue后面的代码

exit：跳出整个循环，停止循环

用例来说明它们之间的区别：

declare
    begin 
       for i in 1..5 loop
          for j in 1..3 loop
             if j=2 then  --当i=5时结束内层循环，继续下一个内循环j=3。
                continue;
             end if;
             dbms_output.put_line(i);
          end loop;
          dbms_output.put_line('a');
       end loop;       
end;
输出结果：
1
1
a
2
2
a
3
3
a
4
4
a
5
5
a

declare
    begin 
       for i in 1..5 loop
          for j in 1..3 loop
             if j=2 then --当i=5时跳出内层循环，继续下一个外循环i
                exit;
             end if;
             dbms_output.put_line(i);
          end loop;
          dbms_output.put_line('a');
       end loop;       
end;
输出结果：
1
a
2
a
3
a
4
a
5
a

根据结果显示：很明显的看出continue是结束内层当前循环，继续下一次的内层循环。exit是直接结束整个循环，整个循环没有后续循环操作了。

4.5 动态SQL

定义：将SQL语句写在一个字符串中，在存储过程中解析字符串执行SQL。

一般情况下代码块只支持dml和dql语句。增加关键字 execute immediate 可以执行动态sql语句。

继续举例说明：

需求：备份所有E开头的表，备份的表名格式：原表名_月日，如：emp_0729。

--要先准备一个表，用来存储当前所需要的找出来的表名

create table tmp_t(tn varchar2(100));

declare

c number; --E开头的表的数量

v_tn varchar2(100);--存储表名的变量

s varchar2(200);

begin

--找出E开头的表的数量

select count(*) into c from user_tables where table_name like 'E%';

--将表名保存到表中

insert into tmp_t select table_name from user_tables where table_name like 'E%';

commit;

for i in 1..c loop

select tn into v_tn from (select tn,rownum r from tmp_t) where r=i;

s:='create table '||v_tn||'_0926 as select * from ' || v_tn;

dbms_output.put_line(s);

execute immediate s;

end loop;

end;

另外说明一个知识点，打印输出：

dbms_output.put是总共就输出一行，然后在后面依次输出数据（变成一行多列）。

dbms_output.put_line是总共就输出一列，然后在下面依次输出数据（变成一列多行）。

5.索引

5.1 索引含义

索引相当于目录，是一种数据结构。是对某些特定列中的数据进行排序，生成索引表，该列作为WHERE条件时，扫描索引列，根据ROWID快速定位具体记录，提高查询效率。

创建索引的条件：

首先表要大，如果只有几兆，还不如不建，全表扫描说不定更好；

select 操作占大部分的表上创建索引；

where 子句中出现最频繁的列上创建索引；

索引的数量不宜过多，看表的大小和字段数量合适规划；

索引需要维护，它会降低dml语句的速度，所以大量的dml时，可以先删除再创建。

5.2 使用索引

添加索引的语法：

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

删除索引的语法：

drop index 索引名;

查看索引：

select * from user_indexes 查询现有的索引

select * from user_ind_columns 可获知索引建立在那些字段上

修改索引：alter

写法都没必要讲，一个表最多就那么几个索引，直接删了重建更好。

例：快速查到某个表的索引建立在那些字段上

select * from user_ind_columns where lower(table_name)='emp'

5.3 索引类别

主要索引结构：B树索引、位图索引和HASH索引。

B树索引：

含义：使用树状结构，通过分支和叶子节点来存储索引信息

类型：主键、唯一、外键、普通、组合、函数索引

位图索引：

含义：使用二进制位来表示索引信息，适用于具有少量不同取值的列。

Hash索引：

定义：哈希索引适用于等值查询。

5.3.1 主键索引、唯一索引、外键索引

主键索引：在创建表的时候，添加了主键约束，那么就会自动生成主键索引。

alter table emp add constraint pri_empno primary key(empno);

--通过主键约束的创建，来自动生成这个主键索引

唯一索引：在创建表的时候，会根据唯一约束自动的生成唯一索引，唯一索引确保索引列中的值唯一。

外键索引：外键索引用于定义表的主键；外键索引用于加速外键关联查询。

5.3.2 普通索引

普通索引：这个列经常需要被查询，但是这个列又没有什么特点

create index idx_ename on emp(ename);

5.3.3 组合索引

就是普通索引的升级版。

组合索引：和普通索引相同，有多个列需要同时被查询，但是这些列也没有什么特点，那么就放在一起，创建一个组合索引

create index 索引名 on 表名(a,b,c);

create index idx_sal_job on emp(sal,job);

注：

1.在查询时需要使用第一列的列查询，否则组合索引会失效。

2.组合索引中应将最具选择性的列放在最前面。选择性就是列中的值唯一或几乎唯一。这样能有效的减少查询的结果集大小，从而提高查询性能。

5.3.4 函数索引

函数索引：如果你的列，是需要被使用函数计算之后再用来查询，那么计算过程需要的函数要写在索引里面

create index idx_func_hiredate on emp(to_char(hiredate,'yyyy'));

5.3.5 位图索引

位图索引：列的内容是由大量的重复的内容组成的 bitmap

create bitmap index idx_emp_deptno on emp(deptno);

5.3.6 分区表索引

分区表索引分为：本地索引、全局索引。

定义：分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。

应用：本地索引更适合用于频繁查询单个分区的情况，而全局索引更适合用于需要跨多个分区进行查询的场景。

1.本地索引（分为有前缀索引和无前缀索引）

create index idx_hash_ename on emp_hash(ename) local;

2.全局索引

create index idx_hash_job on emp_hash(job) global;

--全局索引测试1，索引分区键和表分区键相同,成功

create index ig_test_id on testindex(id) global

partition by range(id)

( partition p1 values less than (5),

partition p2 values less than (30),

partition p3 values less than (maxvalue)

);

--全局索引测试2，索引的分区键和表分区键不相同,成功

create index ighash_test_id on testindex(name) global

partition by hash(name)

( partition p1,

partition p2,

partition p3

);

--全局索引测试3,：报错GLOBAL 分区索引必须加上前缀。（全局索引是指索引键值必须和分区键值相同，也就是所谓的前缀索引）

create index i_test_id on testindex(name) global

partition by range(id)

( partition p1 values less than (5),

partition p2 values less than (30),

partition p3 values less than (maxvalue)

);

区别：

本地索引必须建在分区表上，全局索引和表没有直接的关联；使用场景不同；

5.4 索引失效情况及解决方案

1. 经常需要被修改的列，索引是一直处于失效状态的

重建索引

2. 数据发生了隐性的转换，这里使用了函数。

select * from emp where to_char(hiredate,'yyyy')=1981;

新建针对该列的函数索引

3. 计算的时候，公式放在了等号的左边

select * from emp where sal+1000=4000 and job='MANAGER'；

修改为：

select * from emp where sal=3000 and job='MANAGER';

4. 查询的时候使用不等于

select * from emp where deptno!=20;

修改为：

select * from emp where deptno<20 or deptno>20;

5. 查询的时候使用了like模糊查询

select * from emp where ename like '%LL%';

6. 空值查询

select * from emp where ename is null;

7. 组合索引没有使用第一列的列查询

8. 查询的时候，使用了非函数索引的函数

6.游标

游标是用来存储多条查询查询数据的一种数据结构，通过指针，从上而下移动，遍历每条数据。因为遍历的数据依次是保存到内存中，所以当数据量很大，容易造成内存不足而崩溃。

游标分为静态游标和动态游标。

6.1 静态游标

-定义：在编译期间就声明定义了数据结果集，所以结果集是固定的。如果在静态游标打开后对表进行修改，则游标的结果集不会受到影响。

-写法：

声明一个游标

cursor 游标名 is select 语句;

-打开游标

open 游标名;

-使用游标，获取游标当前的内容

fetch 游标名 into 变量;

-关闭游标

close 游标名;

-实例：（用while循环操作游标，打印出emp表所有员工信息）

declare

--声明一个游标

cursor mc is select * from emp;

v_user emp%rowtype;

begin

--打开游标

open mc;

--使用游标

fetch mc into v_user;

while mc%found loop

dbms_output.put_line('员工编号'||v_user.empno||'的部门号是'||v_user.deptno||'工资是'||v_user.sal);

fetch mc into v_user;

end loop;

--关闭游标

close mc;

end;

-游标属性及其作用

属性, 返回值类型, 作用

sql%isopen 布尔型判断游标是否 '开启'

sql%found 布尔型判断游标是否 '获取' 到值

sql%notfound 布尔型判断游标是否 '没有获取' 到值（常用于 "退出循环"）

sql%rowcount 整型 '当前' 成功执行的数据行数（非 "总记录数"）

例：通过sql关键字来查看，本次数据修改的范围，一共有多少行。

declare

begin

delete from emp where sal>=5000;

dbms_output.put_line(sql%rowcount);

update emp set sal=2000 where sal<2000;

dbms_output.put_line(sql%rowcount);

end;

6.2 动态游标

定义：动态游标是在运行时定义的，用于在查询结果集可能发生更改的情况下遍历结果集。如果在动态游标打开后对表进行修改，则游标的结果集会更新。

写法上与静态游标不同，需先声明游标类型，且静态游标是在声明部分定义赋值，动态游标是在代码执行部分定义赋值的。

直接举例：（用loop循环打印出emp表中部门号20的所有员工信息）

declare

--声明一个动态游标的类型自己定义的dongtai_c和number varchar2 date是一个意思

type dongtai_c is ref cursor;

--声明游标

mc dongtai_c;

v_user emp%rowtype;

begin

--打开游标的同时，给游标赋值

open mc for select * from emp where deptno=20;

--使用loop循环读取游标

loop

fetch mc into v_user;

exit when mc%notfound;

dbms_output.put_line('员工编号'||v_user.empno||'的部门号是'||v_user.deptno||'工资是'||v_user.sal);

end loop;

--关闭游标

close mc;

end;

6.3 案例

建表语句：

CREATE TABLE EMPLOYEES (

ID NUMBER PRIMARY KEY,

NAME VARCHAR2(50) NOT NULL,

SALARY NUMBER NOT NULL,

DEPARTMENT_ID NUMBER

);

INSERT INTO EMPLOYEES (ID, NAME, SALARY, DEPARTMENT_ID) VALUES (1, '张三', 10000, 101);

INSERT INTO EMPLOYEES (ID, NAME, SALARY, DEPARTMENT_ID) VALUES (2, '李四', 12000, 101);

INSERT INTO EMPLOYEES (ID, NAME, SALARY, DEPARTMENT_ID) VALUES (3, '王五', 11000, 102);

INSERT INTO EMPLOYEES (ID, NAME, SALARY, DEPARTMENT_ID) VALUES (4, '赵六', 9000, 103);

INSERT INTO EMPLOYEES (ID, NAME, SALARY, DEPARTMENT_ID) VALUES (5, '钱七', 13000, 101);

7.视图

视图是从数据库一个或多个表中导出的虚拟表，视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

作用：是为了简化查询，也提高了数据的安全性。

视图有两种分类：普通视图和物化视图。

7.1 视图操作

7.1.1 创建视图

语句：

create view 视图名 as

select 语句

with read only;

7.1.2 删除视图

drop view 视图名；

7.1.3 修改视图

修改视图：（可以在不用删除原视图的情况下更新）

create or replace view 修改；

没有则创建。

7.1.4 更新视图

和更新表的数据一样。

update view_name set name ='开发部'；

注意：

因为在使用update或delete 执行某些数据时，会出现某一行删除的情况，一般会在原创建视图中末尾加上：

WITH CHECK OPTION

7.2 物化视图

定义：这是一个真实的物理的表，它将查询语句的结果集当成一个新的表保存起来，同时会对原表的数据进行同步。

格式：

create materialized view 物化视图名字

refresh on commit | demand

start with 时间点 next 下一次更新的时间点

select 语句;

含义：refresh on commit：类似于实时更新，原表提交数据时更新

refresh on demand: 根据需要更新

物化视图更新的方法：

1. complete 完全刷新，整个表格全部都更新一次数据

2. fast 快速刷新，更新你变更部分的数据

3. force 默认的更新方式，默认的更新方法就是fast

4. never 不要更新表格

格式：

declare

begin

dbms_mview.refresh('物化视图的名字','更新的方法');

end;

例：

create materialized view stu_nv_201

refresh on demand

select * from student_a02 where ssex='男' and sage>20;

update student_a02 set sname='王小san' where sno='s021';

commit;

declare

begin

dbms_mview.refresh('stu_nv_201','complete');

end;

7.3 普通视图与物化视图的区别

1.存储不同：普通视图是虚拟的表，不占用磁盘空间。物化视图是真实的表，会占用磁盘空间。

2.作用不同：普通视图是为了简化查询。物化视图是为了保存更新的数据。

3.更新方式不同：普通视图是实时更新。物化视图有两种：按需更新、提交数据时更新。

4.查询时间不同：如果原表占用内存增大，普通视图查询时间不变，而物化视图是会减少查询时间的。

5.物化视图可以建索引，普通视图不行。

8.锁

锁是面试中经常问到的题，在工作中也经常会碰到相关的问题。

锁是用于管理并发访问数据库对象的机制。锁可以防止多个会话同时修改同一个对象，从而确保数据的一致性和完整性。也就是事务只能被一个线程占用。只有这个锁释放了，其他线程才能占用。

8.1 事务

讲锁得先理解事务，事务是什么？

事务是最小的不可再分的工作单元，有DML语句产生。它有四个特征：

原子性：事务是最小单元，不可再分

一致性：事务要求DML的语句，必须保证全部同时成功提交或者同时失败回滚。

隔离性：事务A和事务B具有隔离性。

持久性：内存的数据持久到硬盘文件中。

开启事务：任何一条DML语句(insert、update、delete)执行，标志事务的开启。

事务结束：

提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步。

回滚：失败的结束，将所有的DML语句操作历史记录全部清空。

8.2 锁的定义

锁从需求上看分为：共享锁和排他锁。

共享锁：允许多个会话同时读取一个对象，但不允许任何会话修改该对象。这种锁适用于读取操作，可以提高并发性能。

排他锁：只允许一个会话同时对一个对象进行读取和修改操作。当一个会话持有排他锁时，其他会话无法读取或修改该对象。这种锁适用于写操作，可以确保数据的一致性。

细化从范围上看分为：表级锁和行锁。

表级锁：为了保证在修改表数据时，表的结构不会改变。在使用DDL或DML语句时产生。

行锁：使用DML时，会生成行锁，被修改的数据所在的行会被锁定，他人无法进行操作，属于排他锁。这时候也会自动生成该表的表级锁，不允许其他会话进行对该表DDL的操作。

从上锁的主动性和被动性上看分为：乐观锁和悲观锁。

乐观锁：数据库默认的。

悲观锁：需要通过for update提前占用数据的资源。

写法：select 语句 + for update;

在用户A修改之前就对返回的数据集进行上锁，防止其他用户的修改。如果用户B修改数据，那么他就会产生阻塞，需等到用户A事务结束。

悲观锁的缺点：主动加锁可能会导致时间过长，限制其他用户的访问。所以从并发性上看不推荐使用。

8.3 死锁原因及处理

死锁产生的原因：当两个用户互相希望持有对方的资源时就会发生死锁,也就是说两个用户互相等待对方释放资源时,这个时候就产生了死锁。

（举例易懂）：

1：用户 1 对 A 表某行数据进行 Update，没有提交事务。

2：用户 2 对 B 表某行数据进行 Update，没有提交事务。

此时双方不存在资源共享的问题。

3：如果用户 2 此时对 A 表中那行数据作 update,则会发生阻塞，需要等到用户1的事务结束。

4：如果此时用户 1 又对 B 表的那一行数据作 update，也产生阻塞，等待用户2的事务结束。

这样双方都在等待对方释放锁资源，这就是死锁。

处理办法：只需释放掉其中一个资源即可。

--查看所有被上了锁的对象 69539

select * from v$locked_object;

--找到锁的对象，是在哪个表上面，根据自己找到的这个表的名字，回去找session_id 131

select * from dba_objects where object_id=69539;

--找锁的id的 serial# 9555

select * from v$session where sid=131;

--根据这两个编号，杀掉这个锁

alter system kill session '131,9555';

9 存储过程

存储过程的形式学习简单，就是带有名字的代码块。

什么时候会用到存储过程？

一个固定的功能的代码块，并且这个代码需要经常的反复的运行。

比如：

常用的业务计算。

数据增量、全量操作。

日志的操作（如每天建立新的日志表分区）。

9.1 创建存储过程

存储过程的写法有4种形式，无参、带入参、带出参、入参出参都有

1.没有参数的存储过程

create or replace procedure 存储过程名字

变量的声明;

begin

执行的sql语句;

end;

2.创建一个有输入参数的存储过程

create or replace procedure 存储过程名字(输入参数名字 in 数据类型)

变量的声明;

begin

执行的sql语句;

end;

3.有输出参数的存储过程

create or replace procedure 存储过程名字(参数名字 out 数据类型)

变量的声明;

begin

执行的sql语句;

end;

4.同时有输入和输出的存储过程

create or replace procedure 存储过程名字(参数名字 in 数据类型,参数名字 out 数据类型)

变量的声明;

begin

执行的sql语句;

end;

注：带有输出参数的存储过程，需要放入到代码块中进行调用

例：

create or replace procedure pro_p4(n1 in number,n2 in number,s out number)

begin

s:=n1+n2;

end;

--调用

declare

s number;

begin

pro_p4(1.5,1.8,s);

dbms_output.put_line(s);

end;

9.2 调用存储过程

调用存储过程有两种方式：

1.call调用

call pro_name();

2.代码块调用

declare

s number;

begin

pro_p4(1.5,1.8,s);

dbms_output.put_line(s);

end;

9.3 异常处理

在代码中捕获所有出现的异常和错误

create or replace procedure 过程名

--声明部分

begin

--执行部分

exception

when 异常的名字 then

对异常的处理

when 异常的名字 then

对异常的处理

when others then

对异常的处理

end;

others表示所有的错误。

异常有三类：系统预定义的、非预定义的、自定义的

系统预定义的异常：有名字有编号，大致有20来个吧。（我就不一一说明，百度吧）

非预定义的异常：有错误代码，但没有名字，这个名字，我们自己来取。

例：（给外键约束取名的验证）

create or replace procedure p8(v_deptno in number)

--定义一个变量，这个变量数据类型是exception异常类型

fk_error exception;

--将这个变量名字和错误的代码绑定在一起

pragma exception_init(fk_error,-2291);

begin

update emp set deptno=v_deptno where empno=9999;

commit;

exception

when fk_error then

dbms_output.put_line('违反外键约束！！！！');

end;

--调用存储过程

call p8(40);

call p8(60);

注： pragma exception_init：绑定错误名字和错误代码。

3.自定义异常：通过raise_application_error(错误编号, 错误提示)，错误编号的范围是-20001到-20999

例：现在有人要加工资，但是规定是，每次加工资，不能超过原有工资的20%

create or replace procedure p8(v_empno in number,v_sal in number)

s number;

m varchar2(300);

begin

select sal into s from emp where empno=v_empno;

if v_sal >=s*1.2 then

raise_application_error(-20001,'加工资，不能超过原有工资的20%');--错误编号的范围是-20001到-20999

else

update emp set sal=v_sal where empno=v_empno;

dbms_output.put_line(sql%rowcount||'行数据被修改！！！');

end if;

exception

when others then

m:=sqlerrm;

dbms_output.put_line(sqlcode||m);

end;

call p8(7639,10000);

call p8(7369,10000);

call p8(7788,3500);

9.4 案例

需求：用存储过程保存错误日志

--1、新建一个错误日志表

create table error_log(

proce_name varchar2(300), --对应存储过程的名字

table_name varchar2(300), --对应的表的名字

mcode varchar2(100),--错误代码

merror varchar2(300),--错误信息

mtime date);--时间

--2、新建一个存储过程保存错误信息

create or replace procedure insert_error_log(v_proce_name in varchar2,v_table_name in varchar,v_mcode in varchar2,v_merror in varchar2)

begin

insert into error_log values(v_proce_name,v_table_name,v_mcode,v_merror,sysdate);

dbms_output.put_line(sql%rowcount||'行数据被插入！！！');

--commit;

end;

--3.在另一个过程里面，如果运行报错了，那么就调用存入错误日志的存储过程

create or replace procedure p8(v_empno in number,v_sal in number)

s number;

m varchar2(300);

begin

select sal into s from emp where empno=v_empno;

if v_sal >=s*1.2 then

raise_application_error(-20001,'加工资，不能超过原有工资的20%');--错误编号的范围是-20001到-20999

--调用保存错误日志的存储过程

insert_error_log('p8','emp',sqlcode,sqlerrm);

--commit;

else

update emp set sal=v_sal where empno=v_empno;

dbms_output.put_line(sql%rowcount||'行数据被修改！！！');

--commit;

end if;

--异常处理部分

exception

when others then

m:=sqlerrm;

dbms_output.put_line('错误代码: '||sqlcode||' 错误信息：'||m);

--调用保存错误日志的存储过程

insert_error_log('p8','emp',sqlcode,sqlerrm);

--commit;

end;

--通过代码块来调用存储过程

declare

empno1 number :=&输入员工编号;

sal1 number :=&输入工资;

Begin

--调用保存错误日志的存储过程

p8(empno1,sal1);

end;

select * from error_log;

9.5 自定义函数

自定义函数和存储过程在用法没什么差异。主要差异有下：

存储过程中可以调用其他的存储过程以及一系列SQL语句。而函数有很多语句不能使用，比如DML语句。

存储过程是返回参数，函数是返回值，有return语句。

用法：

create or replace function 函数名(输入的参数数据类型)

return 返回的数据类型

声明部分

begin

执行部分

return 返回值的变量

end;

例：

--实现一个和数据库power()相同的函数的功能，自己写一个求数字的次方的方法：

create or replace function cf(n number,c number)

return number

s number :=1;

begin

for i in 1..c loop

s:=s*n;

end loop;

return s ;

end;

--dql语句调用函数验证两个函数结果是否一致

select power(10,3),cf(10,3),power(4,3),cf(4,3),power(2,10),cf(2,10) from dual;

10.触发器

触发器是一种数据库对象。在事先为某张表绑定一段代码，当表中的数据发生增、删、改的时候，系统会自动触发代码并执行。

作用：检查输入的数据；实时备份表格的数据；记录表格操作的日志。

注：触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

触发器在业务上主要应用于数据库的备份和审计。

触发器的类型分为：前置触发器、后置触发器。

区别：

触发时间：前置触发器是在执行DML之前被激活；而后置触发器是在执行DML之后被激活。所以前置触发器可以在DML之前验证或修改数据；而后置触发器在DML之后对数据进行处理，常用于日志记录和数据统计。

功能上：前置触发器可以做增、删、改的操作；而后置触发器不能，只能做select操作。

10.1 用法

创建触发器：
create or replace trigger 触发器名字
before|after insert or update or delete on 表名
for each row
begin
    执行的sql语句;
end;
--前置触发器：before
--后置触发器：after
删除触发器：
drop trigger 触发器名称;

10.2 案例（前置触发器--验证数据）

需求：在emp表中如果要去更新用户的工资，新增的用户，工资不能超过2000元；老用户涨工资不能超过原来工资的10%；禁止删除岗位PRESIDENT。
create  or replace trigger check_emp_sal
before insert or update or delete on emp
for each row
begin
      if inserting then
         if :new.sal>2000 then
           raise_application_error('-20007','新员工工资不能超过2000');
         end if;
      elsif updating then
         if :new.sal>:old.sal*1.1 then
           raise_application_error('-20008','老员工涨工资不能超过原来的10%');    
         end if;
      else 
         if :old.job='PRESIDENT' then
           raise_application_error('-20006','不能删除岗位是PRESIDENT的员工信息');
         end if;
      end if;
end;
--这里报错：无法对sys拥有的对象创建触发器，换个普通用户，把emp表备份出来，再创建触发器
create table emp as select * from scott.emp;
--测试触发器
insert into emp values(6666,'ADDFD66','CLERK',null,date'2021-09-20',2001,null,40);
insert into emp values(6667,'ADDFD67','CLERK',null,date'2021-09-20',1500,null,40);  
update emp set sal=4000 where empno=7369;
update emp set sal=850 where empno=7369;
delete from emp where job='CLERK' and sal<2000;
delete from emp where job='PRESIDENT';
经测试，结果符合预期，满足需求。
注：
1.sys的对象不能用来创建触发器，会报错。
2. "："有2中意思；
一：给变量赋值， 如， names varchar2(10) :='aa'; 这是把"aa"赋值给变量names。
二：表示引用，即引用表中字段所对应的值，如：emp表中有个name为“Bob”，那么 : old.name 的值就是"Bob"。只是表示引用的时候，只能出现在触发器里面。
3:NEW 和:OLD使用方法和意义，
new 只出现在insert和update时，old只出现在update和delete时。
在insert时new表示新插入的行数据，update时new表示要替换的新数据、old表示要被更改的原来的数据行，delete时old表示要被删除的数据。

10.3 案例（后置触发器--审计）

触发器的另一个常见用途是为了之后审计的目的而记录的对数据库的修改。

比如：当一个人增加或删除了某条记录的时候，我们可以把这个操作记录下来。这样就知道了谁进行了什么样的操作。
需求：对dept表进行增删改，建立记录其改动的操作的审计表。
--1.先创建审计表。
create table dept_audit(
    user_name varchar(64),
    action_type varchar(64),
    action_date date,
    new_deptno number(2),
    old_deptno number(2),
    new_dname varchar(16),
    old_dname varchar(16),
    new_loc varchar(16),
    old_loc varchar(16)
);
select *from dept_audit;
--2.创建触发器：
create or replace trigger after_dept
after insert or update or delete on dept
for each row
begin
      if inserting then
            --dept表插入数据时
            insert into dept_audit
            values(user,'insert',sysdate,:new.deptno,'',:new.dname,'',:new.loc,'');
      elsif updating then
            --dept表更新数据时
            insert into dept_audit
            values(user,'update',sysdate,:new.deptno,:old.deptno,:new.dname,:old.dname,:new.loc,:old.loc);
      else
            --dept表删除数据时
            insert into dept_audit
            values(user,'delete',sysdate,'',:old.deptno,'',:old.dname,'',:old.loc);
      end if;  
end;
--3.验证触发器的结果
insert into dept values(60,'java','chongqing');
update dept set dname='python' where deptno=32;
delete from dept where deptno=60;
select * from dept_audit;
结果显示：
这里如果换个用户A登陆来对YANGFENG用户下的dept表的数据进行修改，那么user_name的数据也会变成用户A。

11.分区表

当表的数据量不断增大，查询数据的速度就会变慢，性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

分区表的划分方式有4种：范围分区、hash分区、列表分区、组合分区。

11.1 范围分区

关键字：range

最常用的一种分区，范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。

常用于范围分区的字段：数值范围类（比如工资区间）、时间范围类（比如一月一月的来）。

--范围分区（用sal列进行范围划分）
create table amp(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)partition by range(sal)
(
           partition sal_0_1000 values less than (1001),
           partition sal_1001_2000 values less than (2001),
           partition sal_2001_3000 values less than (3001),
           partition sal_3001_maxv values less than (maxvalue)
);
--录入数据检验
insert into amp select * from emp;
select * from amp partition(sal_0_1000);

11.2 hash分区

关键字：hash

Hash分区通过数据库的内部的哈希算法，将所有的行，根据数据，放到不同的分区中进行保存。

Hash分区常用于划分没有啥规律的字段。

--hash分区(划分没有规律的字段),本例子划分4个区
create table bmp(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)partition by hash(ename)
(
           partition p1,
           partition p2,
           partition p3,
           partition p4
);
--录入数据检验，查看分区1的数据
insert into bmp select * from emp;
select * from bmp partition(p1);

11.3 列表分区

关键字：list

该分区的特点是某列的值只有几个，并且存在一样的规律，基于这样的特点我们可以采用列表分区。

-比如说性别字段，就可以分为男女两个分区。
create table cmp(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)partition by list(deptno)
(
           partition dept10 values(10),
           partition dept20 values(20),
           partition dept30 values(30),
           partition dept40 values(40)
);
--录入数据检验
insert into cmp select * from emp;
select * from cmp partition(dept10);

11.4 组合分区

组合分区就是将范围分区、hash分区、列表分区组合起来进行分区。

写法：
create table 表名(
列名 数据类型
)partition by 父分区类型(列名)
subpartition by 子分区类型(列名)
(
    partition 父分区名字 values 分区规则(
        subpartition 子分区名字 values 分区规则，
        subpartition 子分区名字 values 分区规则，
        ...
    )
);

--组合分区(以列表——范围为例子)
create table dmp(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)partition by list(deptno)
subpartition by range(sal)
(
           partition d10 values(10)
           (
               subpartition sal_10_1000 values less than (1001),
               subpartition sal_10_2000 values less than (2001),
               subpartition sal_10_maxv values less than (maxvalue)      
           ),
           partition d20 values(20)
           (
               subpartition sal_20_1000 values less than (1001),
               subpartition sal_20_2000 values less than (2001),
               subpartition sal_20_maxv values less than (maxvalue)
           ),
           partition d30 values(30)
           (
               subpartition sal_30_1000 values less than (1001),
               subpartition sal_30_2000 values less than (2001),
               subpartition sal_30_maxv values less than (maxvalue)
           ),
           partition d40 values(40)
           (
               subpartition sal_40_1000 values less than (1001),
               subpartition sal_40_2000 values less than (2001),
               subpartition sal_40_maxv values less than (maxvalue)
           )
);
--录入数据检验
insert into dmp select * from emp;
--共4个主分区，12个子分区
select * from cmp partition(d10);

11.5 分区中分析常用语句

--增加分区subpartition  
alter table 表名 modify partition 父分区名 add subpartition 子分区名;
--删除分区subpartition  
alter table 表名 drop subpartition 子分区名;
select * from user_tables;      --查看当前用户所有的表的信息
select * from user_tab_columns;--查看当前用户所有表的列信息
select * from user_tab_partitions;    --查看用户所有的分区表信息
--主分区数据字典表
 select * from user_tab_partitions;
--主分区数据字典表
SELECT * FROM user_tab_partitions WHERE TABLE_NAME='EMP_RANGE';
 --子分区数据字典表
 select * from user_tab_subpartitions;
--删除一个表的数据是  
truncate table table_name;  
--删除分区表一个分区的数据是  
alter table table_name truncate partition p2;

14 数据仓库

数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。数据仓库从多个源系统中提取、转换和加载数据（ETL流程），通过数据清洗、整合、转换等生成目标的过程。

按照数据的流向，可以将数据仓库分为3层（常见），也可以分为更多层（根据实际情况分层）：

贴源层（ODS）：数据来源于现有业务库，与源表的数据结构保持一致，一般不做改动，为其他逻辑层提供数据来源。

数据仓库层（DW）：数据来源于ODS层，对ODS的数据进行规范化（编码转换、清洗、统一格式、脱敏等），再进行各表数据的关联整合，输出主题宽表。

集市层（DM）：数据来源可以是ODS层，也可以是DW层，主要是面向业务需求进行开发。数据应用于前端报表展示或输出到项目库中。

总体来说，数仓像是一个逻辑性的概念，是为了帮助开发更好去管理数据，为业务提供数据支撑而延伸的概念过程。

14.1 数据建模

数据建模就是基于对业务的理解，将各种数据进行整合关联。在数据仓库DW和DM逻辑层需要分析数据，通常我们的中间层宽表就基于维度建模-星型模型来实现的。

数据建模方式有两类：范式建模、维度建模。

14.1.1 范式建模

范式建模的主要作用是减少数据冗余提高更新数据的效率，同时保证数据完整性，但是这样存在一个问题，划分的表会很多，表连接的查询越多就会影响性能。

范式建模主要有3种常用：第一范式、第二范式、第三范式。

1.第一范式

表中的每一列都是不可拆分的原子项，只能存在一个值。（属性不可再分）

2.第二范式

第二范式要同时满足下面两个条件：

（1）满足第一范式。

（2）没有部分依赖。（表中的非主键列都必须依赖于主键列）

3.第三范式

第三范式要同时满足下面两个条件：

（1）满足第二范式。

（2）没有传递依赖。（表中非主键列关系重复，能互相推导出来）

比如说：emp表再加一个部门名称的字段，那么：

部门编号依赖于员工编号，部门名称依赖于部门编号，部门名称间接依赖于员工编号，则产生了传递依赖。

在表的设计上采用ER模型（实体关系图）：

1.一对一关系

外键列设置在任意一张表中，都是可以的。

2.一对多关系

外键列要设置在多的一方。

3.多对多关系

假设是A表和B表，这种情况下，需要设计第三张表（桥表），桥表中设置俩个外键，分别引用A表的主键和B表的主键。

14.1.2 维度建模

维度建模是通过维度和指标来进行设计，它是面向分析的，目的是提高查询性能，快速完成需求分析且对于复杂查询及时响应。相应的缺点就是会造成数据冗余，可能会违反范式要求。

维度建模常用的有3种：星型模型、雪花模型、星座模型。

（1）星型模型

星形模型中有一张事实表和多个维度表，事实表与维度表通过主键外键相关联，维度表之间没有关联。当所有维表都直接连接到事实表上时，整个图就像星星一样，故将该模型称为星型模型。

（2）雪花模型

当有一个或多个维度表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。

（3）星座模型

星座模型是由星型模型延伸而来，星型模型是基于一张事实表而星座模式是基于多张事实表，并且共享维度表信息，这种模型往往应用于数据关系比星型模型和雪花模型更复杂的场合。星座模型需要多个事实表共享维度表，因而可以视为星形模型的集合。

14.1.3 建模工具

常用的有Navicat、PowerDesigner

14.2 常见表类型

14.2.1 拉链表

一些表的数据不是静态的，而是会随着时间而缓慢地变化，这种随着时间发生变化的维度称之为缓慢变化维。例如用户修改了自己的住址、商品的描述信息更改等。

有时候的某些需求需要查看或统计某一个时间点或者时间段的历史快照信息。这个时候就需要拉链表。

拉链表定义：记录数据在某一时间内的状态以及数据在某一时点上的变化的数据存储方式。

算法：

新增数据 ==> 开链

删除数据 ==> 关链

修改数据 ==> 先关链，在开新的拉链

start_time

表示该条记录的生命周期开始时间——周期快照时的状态

end_time

该条记录的生命周期结束时间

end_time= ‘9999-12-31’ 表示该条记录目前处于有效状态

以emp表为原表举例：（阐述拉链表的过程，分为原表的更新和新增）

原表为更新时：

第一步：先以emp表为原表将结构及数据批量导入到新创建的拉链表empb中，同时在拉链表中新建两个字段，起始创建时间和结束时间。

create table empb as select emp.*,date'2023-03-21' starttime,date'9999-12-31' endtime from emp;

第二步：对原表某个值进行更新，这里选的是姓名为king的工资为5500。

update emp set sal=5500 where lower(ename)='king';

第三步：对拉链表进行更新，只更新结束时间，（结束时间与该条数据的下条新增的起始创建时间一致。因为原数据只有先结束标记为失效状态，再新增下条数据，标记为有效状态），然后再将原表emp的更新数据插入到拉链表empb中。

update empb set endtime=date'2023-03-22' where lower(ename)='king';

insert into empb select emp.*,date'2023-03-22',date'9999-12-31' from emp where lower(ename)='king';

原表为插入数据时：

第一步：与更新数据一致。

第二步：对原表插入数据。

第三步：不用对拉链表的日期更新，直接根据原表的数据插入即可（只需要第三步中的第二步）。

拉链表能反应出某个时间的所有信息的有效情况，相当于快照表。比如说：

已知拉链表中：用户A002有两条数据，分别是：

用户起始时间结束时间状态

A002 2016-01-01 2017-01-03 失效

A002 2017-01-03 9999-12-31 有效

要求查询出在2016-06-01的用户情况：

select * from empb where start_time <= date‘2016-06-01’ and end_time >= date‘2016-06-01’;

此SQL语句会查询出这天所有用户的有效信息。

14.2.2 全量表

全量表就是记录所有的数据，一般使用它时都会清空目标表。用于数据量不大的表。

14.2.3 增量表

增量表只记录更新周期内的新增数据，就是基于原表，记录每次变化的数据。

特殊增量表：da表，每天的分区就是当天的数据，其数据特点就是数据产生后就不会发生变化，如日志表

14.2.4 流水表

对于表的每一个修改都会记录，可以用于反映实际记录的变更。

与拉链表的区别：流水表是只新增，不对原来的结束时间做出更新，且流水表只有创建时间，没有结束时间。流水表就是字面意思，直接罗列出来即可，无论原表是新增还是更新，流水表都是新增。

Oracle - 笔记

1.优化技巧

核心思想.

优化建议

2.Oracle优化

10.1 数据库的设计优化

10.2 SQL调优

10.3 参数配置优化

10.4 硬件优化

10.5 优化常用工具

10.6 优化思路

3.数据查询语言

3.1 去重distinct查询

3.2 where条件查询

3.3 group by分组查询

3.4 having过滤查询

3.5 order by排序查询

3.6 分页查询

3.7 多表查询

3.7.1 内连接

3.7.2 外连接

3.7.2.1 左连接

3.7.2.2 右连接

3.7.2.3 全连接

3.7.3 交叉连接

3.8 联合查询

3.8.1 并集

3.8.2 交集

3.8.3 差集

3.9 子查询

3.9.1 FROM子句中的子查询

3.9.2 运算符的子查询

3.9.3 SELECT下的子查询

3.9.4 with...as 子查询

3.Oracle函数

3.1 数值型函数

3.2 字符型函数

3.3 时间日期函数

3.4 聚合分组函数

3.5 开窗函数

3.5.1 用法

3.5.2 排名函数

3.5.3 平移函数

3.6 转换函数

3.7其他函数

4.PLSQL编程

4.1 格式写法

4.2 变量

4.2.1 普通变量

4.2.2 引用型变量

4.2.3 记录型变量

4.2.4 变量输入

4.3 If条件判断语句

4.4 循环语句

4.4.1 for循环

4.4.2 While循环

4.4.3 Loop循环

4.3.4 continue和exit

4.5 动态SQL

5.索引

5.1 索引含义

5.2 使用索引

5.3 索引类别

5.3.1 主键索引、唯一索引、外键索引

5.3.2 普通索引

5.3.3 组合索引

5.3.4 函数索引

5.3.5 位图索引

5.3.6 分区表索引

5.4 索引失效情况及解决方案

6.游标

6.1 静态游标

6.2 动态游标

6.3 案例

7.视图

7.1 视图操作

7.1.1 创建视图

7.1.2 删除视图

7.1.3 修改视图

7.1.4 更新视图