（五）Oracle函数 序列 约束 索引

DECODE函数

DECODE函数基本语法

DECODE函数的语法如下：DECODE (expr, search1, result1[, search2, result2…][, default])

它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

查询职员表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：

SELECT ename, job, sal,DECODE(job, 'MANAGER', sal*1.2,'ANALYST', sal*1.1,'SALESMAN', sal*1.05, sal) AS bonus FROM emp;

DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。

SELECT ename, job, sal,CASE job 
WHEN 'MANAGER' THEN sal * 1.2
WHEN 'ANALYST' THEN sal * 1.1
WHEN 'SALESMAN' THEN sal * 1.05
ELSE sal END bonus FROM emp;

排序函数

ROW_NUMBER

ROW_NUMBER语法如下：ROW_NUMBER() OVER( PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。

ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集。

场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码

SELECT deptno, ename, empno,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS emp_id FROM emp;

RANK

RANK函数的语法如下：RANK() OVER(PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。

和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。

场景：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示

SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC, comm) "Rank_ID"
FROM emp;

※PARTITION 分区

DENSE_RANK

DENSE_RANK函数的语法如下： DENSE_RANK() OVER( PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。

场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水：

SELECT d.dname, e.ename, e.sal, 
DENSE_RANK()
OVER (PARTITION BY e.deptno ORDER BY e.sal)     
AS drank
FROM emp e join dept d
ON e.deptno = d.deptno;

集合操作

UNION、UNION ALL

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。

集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。

ORDER BY子句只能放在最后的一个查询语句中。

集合操作的语法如下：

SELECT statement1
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;

UNION和UNION ALL用来获取两个或两个以上结果集的并集：

• UNION操作符会自动去掉合并后的重复记录。
• UNION ALL返回两个结果集中的所有行，包括重复的行。

例如，合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别：

--Union
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
Union
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

--Union all
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
Union all
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

INTERSECT

INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

例如：显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：

SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
INTERSECT
SELECT ename, job, sal FROM emp
WHERE sal> 2500;
同理：
SELECT ename,job,sal FROM emp WHERE job = 'MANAGER' AND sal >2500;

MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果。

例如：列出职位是MANAGER但薪水低于2500的员工记录：

SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
MINUS
SELECT ename, job, sal FROM emp
WHERE sal > 2500;
同理：
SELECT ename,job,sal FROM emp WHERE job = 'MANAGER' AND NOT sal > 2500;

主键约束

主键的意义

主键(Primary Key)约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合。主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。

主键可以用来在表中唯一的确定一行数据。一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。

主键选取的原则

• 主键应是对系统无意义的数据
• 永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
• 主键不应包含动态变化的数据，如时间戳
• 主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
• 主键尽量建立在单列上

添加主键约束

在建表时添加主键约束条件：

CREATE TABLE employees2 (
name VARCHAR2(30) constraint pk_name primary key,
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
);

建表后创建主键约束条件，并自定义约束条件名称：

CREATE TABLE employees3 (
eid NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
);
 
ALTER TABLE employees3
ADD CONSTRAINT
employees3_eid_pk PRIMARY KEY (eid);

/*查看表TEST的约束条件*/
SELECT * FROM all_constraints WHERE table_name = 'employees3';

外键约束

外键约束的意义

外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或子表。

添加外键约束

先建表，在建表后建立外键约束条件：

CREATE TABLE employees4 (
eid NUMBER(6),
name VARCHAR2(30),
salary NUMBER(7, 2),
deptno NUMBER(4)
);
 
ALTER TABLE employees4
ADD CONSTRAINT employees4_deptno_fk
FOREIGN KEY (deptno) REFERENCES dept(deptno);

外键约束对一致性的维护

外键约束条件包括两个方面的数据约束：

• 从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL；
• 当主表参照列的值被从表参照时，主表的该行记录不允许被删除。

--成功DML语句：
INSERT INTO employees4(eid, name, deptno) VALUES(1234, ‘rose tyler’, 40);--成功
INSERT INTO employees4(eid, name, deptno) VALUES(1235, ‘martha jones’, NULL); --成功
 
--失败DML语句：
INSERT INTO employees4(eid, name, deptno) VALUES(1236, 'donna noble', 50);--失败，不存在部门50
DELETE FROM dept WHERE deptno = 40;
--失败，40被参照，不允许删除

外键约束对性能的降低

如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去。

另外外键确定了主从表的先后生成关系，有时会影响业务逻辑。

关联不一定需要外键约束

如果业务逻辑要求保证数据完整性，可由程序控制，不一定需要外键约束。

另外为了简化开发，维护数据时不用考虑外键约束，以及大量数据DML操作时不需考虑外键耗费时间。

唯一性约束

什么是唯一性约束

唯一性(Unique)约束条件用于保证字段或者字段的组合不出现重复值。当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL值。

唯一性约束条件可以在建表同时建立，也可以在建表以后再建立。

添加唯一性约束

在建表employees的同时，在eid、email列上创建唯一约束条件，并在建表后在name列上建立一个名为employees_name_uk的唯一约束条件：

DROP TABLE employees ; --将表删掉重新创建
CREATE TABLE employees (
　　eid NUMBER(6) constraint employees_eid_uk unique,
　　name VARCHAR2(30),
　　email VARCHAR2(50),
　　salary NUMBER(7, 2),
　　hiredate DATE,
);

在建表之后增加唯一性约束条件：

ALTER TABLE employees 
ADD CONSTRAINT employees_name_uk UNIQUE(name);

检查约束

什么是检查约束

检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件。

添加检查约束

员工的薪水必须大于2000元，增加检查约束：

ALTER TABLE employees4
ADD CONSTRAINT employees4_salary_check
CHECK (salary > 2000);

当插入大于2000的数据，操作成功：

INSERT INTO employees4(eid, name, salary, deptno) 
VALUES(1236, 'donna noble', 2500, 40);

试图修改职员的薪水为1500元，更新失败：

UPDATE employees4 SET salary = 1500
WHERE eid = 1236; 

序列

什么是序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。

序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。

通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值。

举例说明，创建一个序列，起始数据是100，步进是10：

CREATE SEQUENCE emp_seq
START WITH 100
INCREMENT BY 10;

当序列被创建后，第一个序列值将是100，将要生成的序列号分别是110、120、130等。

序列中有两个伪列：

• NEXTVAL：获取序列的下个值
• CURRVAL：获取序列的当前值

当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRVAL。

获取序列的第一个值，并且使用序列值为EMP表插入新的记录：

SELECT emp_seq.NEXTVAL FROM DUAL;
INSERT INTO emp(empno, ename) VALUES(emp_seq.NEXTVAL, 'donna');

查询刚刚生成的记录，主键值将是110：

SELECT empno, ename FROM emp WHERE ename = 'DONNA';

此时查询序列的当前值，会得到110的数字。

SELECT emp_seq.CURRVAL FROM DUAL;

在序列的使用过程中，比如执行了一条语句：

SELECT emp_seq.NEXTVAL FROM DUAL;

则浪费了一个序列值，会导致表的主键值不连续。而CURRVAL的使用不会导致序列值的递进。

删除序列

删除序列的语法如下：

DROP SEQUENCE sequence_name;

删除序列emp_seq：

DROP SEQUENCE emp_seq;

索引

索引的原理

索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。

索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。

ROWID: 伪列，唯一标识一条数据记录，可理解为行地址。

创建索引

创建索引的语法：

CREATE [UNIQUE] INDEX index_name ON table(column[, column…]);

其中：

• index_name表示索引名称
• table表示表名
• column表示列名，可以建立单列索引或复合索引
• UNIQUE表示唯一索引

在EMP表的ENAME列上建立索引：

CREATE INDEX idx_emp_ename ON emp(ename);

复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序依据，可以建立复合索引：

CREATE INDEX idx_emp_job_sal ON emp(job, sal);

当做下面的查询时，会自动应用索引idx_emp_job_sal

SELECT empno, ename, sal, job FROM emp ORDER BY job, sal;

删除索引

当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法:

DROP INDEX index_name;

删除索引idx_emp_ename：

DROP INDEX idx_emp_ename;

合理使用索引提升查询效率

为提升查询效率，创建和使用索引的原则：

• 为经常出现在WHERE子句中的列创建索引
• 为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
• 为经常作为表的连接条件的列上创建索引
• 不要在经常做DML操作的表上建立索引
• 不要在小表上建立索引
• 限制表上的索引数目，索引并不是越多越好
• 删除很少被使用的、不合理的索引