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;--结果为空