在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>
总结:在oracle中利用rownum产生伪列真的是太方便了, 尤其利用派生表再加上伪列可以写出很复杂的sql语句来。
比ASE和IQ的实现方式强N倍。 哎,可叹的sybase啊。