Oracle中rownum深入学习

y45t4r3

Oracle中rownum作为重要的伪列（另一个ROWID，伪列只能查询不能进行增删改操作），在查询的结果集中，rownum为结果集中每一行标识一个行号，第一行返回1，第二行返回2……以此类推。通过rownum伪列可以限制查询结果集中返回的行数，故此它在分页查询中比较常用。但是它有些细节的东西可能被大家忽略了，现总结如下：准备数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

-- 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行以后的数据，查询的结果也是为空。解决的办法是使用实例化查询，例如

1 2	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三层分页查询语句增强理解。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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，又被删除，依次类推，便没有了数据。
附件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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