oracle中rownum=,Oracle中rownum深入学习

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 

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