Oracle中实现分页的方法 .

转：http://blog.csdn.net/andkylee/article/details/5638033

 

在oracle中显示分页很简单， 利用两次rownum就实现了。

 

下面的语句用来返回DBA_OBJECTS表中类型为TABLE的所有记录中的第100行至第200行。

 

select * from
( select rownum rnm, a.* from ( select OBJECT_NAME from DBA_OBJECTS where object_type='TABLE' ) a
    where rownum <= 200 )
where rnm > 100

 

 

 

执行结果为：

 

 

SQL> select * from
  2  ( select rownum rnm, a.* from ( select OBJECT_NAME from DBA_OBJECTS where object_type='TABLE' ) a
  3      where rownum <= 200 )
  4  where rnm > 100
  5  ;
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       101 HISTGRM$
       102 HIST_HEAD$
       103 DUAL
       104 PARTOBJ$
       105 PARTCOL$
       106 TABPART$
       107 INDPART$
       108 SUBPARTCOL$
       109 TABSUBPART$
       110 INDSUBPART$
       111 TABCOMPART$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       112 INDCOMPART$
       113 PARTLOB$
       114 LOBFRAG$
       115 LOBCOMPPART$
       116 DEFSUBPART$
       117 DEFSUBPARTLOB$
       118 SYSTEM_PRIVILEGE_MAP
       119 TABLE_PRIVILEGE_MAP
       120 STMT_AUDIT_OPTION_MAP
       121 RESOURCE_MAP
       122 USER_ASTATUS_MAP
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       123 EXPACT$
       124 NOEXP$
       125 PROCEDUREJAVA$
       126 PROCEDUREC$
       127 PROCEDUREPLSQL$
       128 RESULT$
       129 KOTTD$
       130 KOTTB$
       131 KOTAD$
       132 KOTMD$
       133 KOTTBX$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       134 KOTADX$
       135 KOPM$
       136 VTABLE$
       137 ATEMPTAB$
       138 LIBRARY$
       139 USER_HISTORY$
       140 MIGRATE$
       141 ICOLDEP$
       142 OPERATOR$
       143 OPBINDING$
       144 OPANCILLARY$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       145 OPARG$
       146 INDTYPES$
       147 INDOP$
       148 INDARRAYTYPE$
       149 SECOBJ$
       150 ASSOCIATION$
       151 USTATS$
       152 JAVASNM$
       153 SUM$
       154 SUMDETAIL$
       155 SUMINLINE$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       156 SUMKEY$
       157 SUMAGG$
       158 SUMJOIN$
       159 SUMDEP$
       160 SUMPRED$
       161 SUMQB$
       162 DIM$
       163 DIMLEVEL$
       164 DIMLEVELKEY$
       165 DIMJOINKEY$
       166 DIMATTR$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       167 HIER$
       168 HIERLEVEL$
       169 RLS$
       170 RLS_SC$
       171 RLS_GRP$
       172 RLS_CTX$
       173 CONTEXT$
       174 RULESET$
       175 INDPART_PARAM$
       176 OL$
       177 OL$HINTS
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       178 OL$NODES
       179 SQL$
       180 SQL$TEXT
       181 SQLPROF$
       182 SQLPROF$DESC
       183 SQLPROF$ATTR
       184 EXPPKGOBJ$
       185 EXPPKGACT$
       186 EXPDEPOBJ$
       187 EXPDEPACT$
       188 MON_MODS$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       189 MON_MODS_ALL$
       190 METAVIEW$
       191 METAFILTER$
       192 METAXSL$
       193 METAXSLPARAM$
       194 METASTYLESHEET
       195 METASCRIPT$
       196 METASCRIPTFILTER$
       197 METANAMETRANS$
       198 METAPATHMAP$
       199 EXTERNAL_TAB$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       200 EXTERNAL_LOCATION$
已选择100行。
SQL>

SQL> select * from
  2  ( select rownum rnm, a.* from ( select OBJECT_NAME from DBA_OBJECTS where object_type='TABLE' ) a
  3      where rownum <= 200 )
  4  where rnm > 100
  5  ;
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       101 HISTGRM$
       102 HIST_HEAD$
       103 DUAL
       104 PARTOBJ$
       105 PARTCOL$
       106 TABPART$
       107 INDPART$
       108 SUBPARTCOL$
       109 TABSUBPART$
       110 INDSUBPART$
       111 TABCOMPART$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       112 INDCOMPART$
       113 PARTLOB$
       114 LOBFRAG$
       115 LOBCOMPPART$
       116 DEFSUBPART$
       117 DEFSUBPARTLOB$
       118 SYSTEM_PRIVILEGE_MAP
       119 TABLE_PRIVILEGE_MAP
       120 STMT_AUDIT_OPTION_MAP
       121 RESOURCE_MAP
       122 USER_ASTATUS_MAP
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       123 EXPACT$
       124 NOEXP$
       125 PROCEDUREJAVA$
       126 PROCEDUREC$
       127 PROCEDUREPLSQL$
       128 RESULT$
       129 KOTTD$
       130 KOTTB$
       131 KOTAD$
       132 KOTMD$
       133 KOTTBX$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       134 KOTADX$
       135 KOPM$
       136 VTABLE$
       137 ATEMPTAB$
       138 LIBRARY$
       139 USER_HISTORY$
       140 MIGRATE$
       141 ICOLDEP$
       142 OPERATOR$
       143 OPBINDING$
       144 OPANCILLARY$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       145 OPARG$
       146 INDTYPES$
       147 INDOP$
       148 INDARRAYTYPE$
       149 SECOBJ$
       150 ASSOCIATION$
       151 USTATS$
       152 JAVASNM$
       153 SUM$
       154 SUMDETAIL$
       155 SUMINLINE$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       156 SUMKEY$
       157 SUMAGG$
       158 SUMJOIN$
       159 SUMDEP$
       160 SUMPRED$
       161 SUMQB$
       162 DIM$
       163 DIMLEVEL$
       164 DIMLEVELKEY$
       165 DIMJOINKEY$
       166 DIMATTR$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       167 HIER$
       168 HIERLEVEL$
       169 RLS$
       170 RLS_SC$
       171 RLS_GRP$
       172 RLS_CTX$
       173 CONTEXT$
       174 RULESET$
       175 INDPART_PARAM$
       176 OL$
       177 OL$HINTS
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       178 OL$NODES
       179 SQL$
       180 SQL$TEXT
       181 SQLPROF$
       182 SQLPROF$DESC
       183 SQLPROF$ATTR
       184 EXPPKGOBJ$
       185 EXPPKGACT$
       186 EXPDEPOBJ$
       187 EXPDEPACT$
       188 MON_MODS$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       189 MON_MODS_ALL$
       190 METAVIEW$
       191 METAFILTER$
       192 METAXSL$
       193 METAXSLPARAM$
       194 METASTYLESHEET
       195 METASCRIPT$
       196 METASCRIPTFILTER$
       197 METANAMETRANS$
       198 METAPATHMAP$
       199 EXTERNAL_TAB$
       RNM OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------
       200 EXTERNAL_LOCATION$
已选择100行。
SQL>

 

 

总结：在oracle中利用rownum产生伪列真的是太方便了， 尤其利用派生表再加上伪列可以写出很复杂的sql语句来。