[Oracle]面试官：你说说rownum、currval与nextval分别是什么，还有没有其他的？

【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
https://www.cnblogs.com/cnb-yuchen/p/18338280
出自【进步*于辰的博客】

目录

• 1、rownum
  • 1.1 介绍
  • 1.2 示例
• 2、nextval、currval
  • 2.1 序列
  • 2.2 介绍
  • 2.3 示例
• 最后

1、rownum

参考笔记一，P18.3/4、P19.5。

1.1 介绍

rownum是虚拟字段，不真实存储，在返回结果集时生成，宏观作用类似序号。在每次查询时，从1开始给结果集编号。常与<、<=连用。

在与>、>=连用时，由于rownum是在查询记录时逐个生成，并迭代。因此，若rownum的判断在第1行记录时就不满足，则无法返回记录，即未迭代，rownum始终为1，最终结果集中无任何记录，与between...and...连用时同理。

注意：所有SQL语句的执行顺序都是：from → where → group by → having → select → order by。

由于rownum是伴随select生成，故与order by连用时，会导致rownum混乱。因此，通常是多层嵌套，先进行排序，再使用rownum进行筛选。（见示例）

扩展：在Oracle中，常言的top-n查询其实是在rownum编号后，使用rownum进行判断，从而获取前n条记录的查询方法。（见示例）

1.2 示例

数据表：emp(no, ..., sal)。
需求：查询工资6 ~ 10名的员工的所有信息。

1：写法一

select e2.*
from (select e1.*, rownum rn
	  from (select * from emp order by sal desc) e1) e2
where rn between 6 and 10

前2层仅对结果集进行一次排序，不做筛选，在第3层时，才进行筛选。rn是rownum的别名，由于rn属于第2层的结果集，非rownum，故已固定，因此可以直接使用rn between 6 and 10筛选出第6 ~ 10名。

2：写法二

select e2.*
from (select e1.*, rownum rn
	  from (select * from emp order by sal desc) e1
	  where rownum <= 10) e2
where rn > 5

在第2层时就进行结果集筛选，rownum初始为1，满足rownum <= 10，则返回记录，同时rownum迭代，如此反复直到条件不满足，这样就查询出前10条记录（工资最高的前10名员工）。此时rownum固定，别名是rn，第3层可以直接使用rn筛选出第6 ~ 10名。

补充说明：

两种写法在第1层时，都得到根据工资降序排序后的所有员工信息。

写法二较写法一，效率高很多。

因为写法一是在第3层才进行结果集筛选，由于条件是rn between 6 and 10，使用的是rn，rn属于结果集，已固定。因此在筛选时，会遍历根据工资降序排序后的所有员工信息。
而写法二，在第2层时，是通过rownum <= 10进行筛选，由于rownum的生成机制，第2层仅遍历前10条记录（工资最高的前10名员工）。因此，第3层仅遍历10条员工信息。

2、nextval、currval

参考笔记一，P23.18。

2.1 序列

这两个伪列皆基于$\color{green}{序列}$，我暂未对序列的相关理论进行整理，大家可以查阅这篇博文《Oracle数据库序列》（转发）。

“序列”是一种按照一定规则自动增加或减少数字的数据库对象，主要用于主键（新增时填充主键）。创建示例：

create sequence swq_emp_empId
increment by 1
start with 1000
nocycle
cache 20

seq_emp是序列名；increment是递增值，默认值为1；start是初始值；nocycle表示不循环；cache 20表示进行缓存，缓存大小为20。

大家也可以使用图形化界面操作：

2.2 介绍

nextval是序列的下一个值，currval是序列的当前值。

使用位置：

1. select子句中，不包括子查询的select子句；
2. insert 语句的select子句或values子句中；
3. update 语句的set子句中。

不能使用位置：

1. 包含distinct、group by、having或order by的视图SQL语句的select子句中；
2. select、update、delete的子查询中；
3. 包含default的create table、alter table语句中。

操作，

--修改序列--
alter sequence 序列名 ...;// 后面格式与创建语句相同
// 注：后面语句中没有start with，并且修改的值不能少于当前值

2.3 示例

insert into emps values(swq_emp_empId.nextval, '张三', 7500.00, 10);

update emps
set sal = 10000.00
where emp_id = swq_emp_empId.currval;

必须先获取nextval，才能使用currval。

最后

掌握伪列，我建议：理解、自测。

本文持续更新中。。。