Oracle中rownum作为重要的伪列（另一个ROWID，伪列只能查询不能进行增删改操作），在查询的结果集中，rownum为结果集中每一行标识一个行号，第一行返回1，第二行返回2……以此类推。通过rownum伪列可以限制查询结果集中返回的行数，故此它在分页查询中比较常用。但是它有些细节的东西可能被大家忽略了，现总结如下：
准备数据
-- CREATE TABLE
CREATE TABLE STU_INFO
(
STUID VARCHAR2(7) NOT NULL, --学生ID
STUNAME VARCHAR2(10) NOT NULL, --学生姓名
GENDER VARCHAR2(2) NOT NULL, --性别
AGE NUMBER(2) NOT NULL, --年龄
SEAT NUMBER(2) NOT NULL, --座次
ENROLLDATE DATE, --入学日期
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --地址
CLASSNO VARCHAR2(4) NOT NULL --班级
)
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255;
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS
ALTER TABLE STU_INFO
ADD CONSTRAINT PK_STU_INFO PRIMARY KEY (STUID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255;
ALTER TABLE STU_INFO
ADD CONSTRAINT UN_STUNAME UNIQUE (STUNAME)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255;
-- CREATE/RECREATE CHECK CONSTRAINTS
ALTER TABLE STU_INFO
ADD CONSTRAINT CK_STU_INFO_AGE
CHECK (AGE >=0 AND AGE<=100);
ALTER TABLE STU_INFO
ADD CONSTRAINT CK_STU_INFO_CLASSNO
CHECK ((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999'));
ALTER TABLE STU_INFO
ADD CONSTRAINT CK_STU_INFO_GENDER
CHECK (GENDER = '男' OR GENDER = '女');
ALTER TABLE STU_INFO
ADD CONSTRAINT CK_STU_INFO_SEAT
CHECK (SEAT >=0 AND SEAT <=50);
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200101', '学生01', '男', 18, 01, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2001');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200102', '学生02', '男', 19, 11, to_date('01-09-2010', 'dd-mm-yyyy'), '广州', '2002');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200103', '学生03', '男', 18, 31, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2003');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200104', '学生04', '男', 20, 23, to_date('01-09-2010', 'dd-mm-yyyy'), '广州', '2004');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200105', '学生05', '男', 17, 10, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2005');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200106', '学生06', '男', 18, 24, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2006');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200107', '学生07', '女', 18, 25, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2007');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200108', '学生08', '男', 18, 27, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2008');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200109', '学生09', '女', 18, 22, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2009');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200110', '学生10', '女', 18, 29, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2010');
insert into STU_INFO (stuid, stuname, gender, age, seat, enrolldate, stuaddress, classno)
values ('S200111', '学生11', '女', 18, 13, to_date('01-09-2010', 'dd-mm-yyyy'), '深圳', '2011');一、查询某行数据
如果使用rownum = 1进行查询学生第一条信息，查询结果是正确的，但是如果使用rownum查询非第一行数据，则返回空结果集。
二、查询前N行数据
如果使用rownum < N 查询前N行数据其不包括第N行数据。可以使用 rownum <= N 或rownum between 1 and N （between 是从1开始的）。
三、查询大于N行数据
如果使用rownum > N 查询大于N行以后的数据，查询的结果也是为空。解决的办法是使用实例化查询，例如
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM > 4;--结果为空
SELECT s.* FROM (SELECT st.*,ROWNUM row_num FROM stu_info st)s WHERE row_num > 4; 这也能帮助我们理解使用分页查询时可以通过rownum大于某行，rownum分页查询正是实例化了的sql语句，所以查询结果和实际一致。在此插入一下Oracle三层分页查询语句增强理解。
PROCEDURE STUINFO_DETIAL(I_STU_SEX IN STU.STU_SEX%TYPE,
I_BEGIN_NUM IN NUMBER,
I_FETCHNUM IN NUMBER,
O_RESULT OUT SYS_REFCURSOR,
O_RETCODE OUT VARCHAR2,
O_RETMSG OUT VARCHAR2) IS
ROW_NUM NUMBER;
BEGIN
O_RETCODE := '0';
O_RETMSG := '查询成功';
OPEN O_RESULT FOR
SELECT T3.STU_ID,
T3.STU_NAME,
T3.STU_SEX,
T3.STU_AGE,
T3.STU_CLASS --（第三层）
FROM (SELECT T2.STU_ID,
T2.STU_NAME,
T2.STU_SEX,
T2.STU_AGE,
T2.STU_CLASS,
ROWNUM AS ROW_NUM --给定行号(第二层)
FROM (SELECT T.STU_ID,
T.STU_NAME,
T.STU_SEX,
T.STU_AGE,
T.STU_CLASS
FROM STU T ORDER BY T.STU_ID ASC ) T2) T3 --排序(第一层)
WHERE STU_SEX = I_STU_SEX
AND ROW_NUM < (I_BEGIN_NUM + I_FETCHNUM)
AND ROW_NUM > I_BEGIN_NUM;
EXCEPTION
WHEN OTHERS THEN
O_RETCODE := '-1';
O_RETMSG := SQLCODE ||'-'|| SQLERRM;
END STUINFO_DETIAL;四、between查询一定要从1开始
如果通过between子句查询，一定要从1开始及rownum between 1 and N，例如使用rownum between 2 and N则查询结果为空。
五、总结
rownum和where在同一层查询中，where条件之后使用rownum比较只能使用<=,<,!=,<>,between 1 and N 。不能使用>,>=(>=1,>=0,>0和不加效果一样),=(使用=，只能是where rownum=1才可以,rownum>1不可以)。否则返回结果为空。
因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一列（强调：先要有结果集）。简单的说rownum是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。所以没办法期望得到下面的结果集：rownum >N（N正整数）没有记录，因为第一条不满足去掉的话，第二条的ROWNUM又成了1，所以永远没有满足条件的记录。或者可以这样理解：ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如 果你用>，>=，=，between……and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下 条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。
附件
SELECT ROWNUM, s.* FROM stu_info s;
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM = 1;--返回第一行结果
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM = 2;--无结果
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM <= 4;--返回前四行结果（包含第四行）
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM != 4;--不包括第四行
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM > 1;--使用 rownum和where在同一层，无结果
SELECT s.* FROM (SELECT st.*,ROWNUM row_num FROM stu_info st)s WHERE row_num > 4;--实例化查询，可以查到想要的结果
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM BETWEEN 1 AND 4;--可以查询到实际结果
SELECT ROWNUM, s.* FROM stu_info s WHERE ROWNUM BETWEEN 2 AND 4;--结果为空
746
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
