Oracle中实现分页的方法 .

jusb959e5n

　　转：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

　　
　　
　　
　　执行结果为：
　　
　　
view plaincopy to clipboardprint?

• 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 * from2  ( select rownum rnm, a.* from ( select OBJECT_NAME from DBA_OBJECTS where object_type='TABLE' ) a3      where rownum <= 200 )4  where rnm > 1005  ;RNM OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------101 HISTGRM$102 HIST_HEAD$103 DUAL104 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_MAP119 TABLE_PRIVILEGE_MAP120 STMT_AUDIT_OPTION_MAP121 RESOURCE_MAP122 USER_ASTATUS_MAPRNM 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$HINTSRNM OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------178 OL$NODES179 SQL$180 SQL$TEXT181 SQLPROF$182 SQLPROF$DESC183 SQLPROF$ATTR184 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 METASTYLESHEET195 METASCRIPT$196 METASCRIPTFILTER$197 METANAMETRANS$198 METAPATHMAP$199 EXTERNAL_TAB$RNM OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------200 EXTERNAL_LOCATION$已选择100行。SQL>
　　
　　
　　总结：在oracle中利用rownum产生伪列真的是太方便了， 尤其利用派生表再加上伪列可以写出很复杂的sql语句来。