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

 

 

 

执行结果为：

 

 

view plain copy to clipboard print ?

1. SQL> select * from  
2.   2  ( select rownum rnm, a.* from ( select OBJECT_NAME from DBA_OBJECTS where object_type='TABLE' ) a  
3.   3      where rownum <= 200 )  
4.   4  where rnm > 100  
5.   5  ;  
6.        RNM OBJECT_NAME  
7. ---------- --------------------------------------------------------------------------------------------------------------------  
8.        101 HISTGRM$  
9.        102 HIST_HEAD$  
10.        103 DUAL  
11.        104 PARTOBJ$  
12.        105 PARTCOL$  
13.        106 TABPART$  
14.        107 INDPART$  
15.        108 SUBPARTCOL$  
16.        109 TABSUBPART$  
17.        110 INDSUBPART$  
18.        111 TABCOMPART$  
19.        RNM OBJECT_NAME  
20. ---------- --------------------------------------------------------------------------------------------------------------------  
21.        112 INDCOMPART$  
22.        113 PARTLOB$  
23.        114 LOBFRAG$  
24.        115 LOBCOMPPART$  
25.        116 DEFSUBPART$  
26.        117 DEFSUBPARTLOB$  
27.        118 SYSTEM_PRIVILEGE_MAP  
28.        119 TABLE_PRIVILEGE_MAP  
29.        120 STMT_AUDIT_OPTION_MAP  
30.        121 RESOURCE_MAP  
31.        122 USER_ASTATUS_MAP  
32.        RNM OBJECT_NAME  
33. ---------- --------------------------------------------------------------------------------------------------------------------  
34.        123 EXPACT$  
35.        124 NOEXP$  
36.        125 PROCEDUREJAVA$  
37.        126 PROCEDUREC$  
38.        127 PROCEDUREPLSQL$  
39.        128 RESULT$  
40.        129 KOTTD$  
41.        130 KOTTB$  
42.        131 KOTAD$  
43.        132 KOTMD$  
44.        133 KOTTBX$  
45.        RNM OBJECT_NAME  
46. ---------- --------------------------------------------------------------------------------------------------------------------  
47.        134 KOTADX$  
48.        135 KOPM$  
49.        136 VTABLE$  
50.        137 ATEMPTAB$  
51.        138 LIBRARY$  
52.        139 USER_HISTORY$  
53.        140 MIGRATE$  
54.        141 ICOLDEP$  
55.        142 OPERATOR$  
56.        143 OPBINDING$  
57.        144 OPANCILLARY$  
58.        RNM OBJECT_NAME  
59. ---------- --------------------------------------------------------------------------------------------------------------------  
60.        145 OPARG$  
61.        146 INDTYPES$  
62.        147 INDOP$  
63.        148 INDARRAYTYPE$  
64.        149 SECOBJ$  
65.        150 ASSOCIATION$  
66.        151 USTATS$  
67.        152 JAVASNM$  
68.        153 SUM$  
69.        154 SUMDETAIL$  
70.        155 SUMINLINE$  
71.        RNM OBJECT_NAME  
72. ---------- --------------------------------------------------------------------------------------------------------------------  
73.        156 SUMKEY$  
74.        157 SUMAGG$  
75.        158 SUMJOIN$  
76.        159 SUMDEP$  
77.        160 SUMPRED$  
78.        161 SUMQB$  
79.        162 DIM$  
80.        163 DIMLEVEL$  
81.        164 DIMLEVELKEY$  
82.        165 DIMJOINKEY$  
83.        166 DIMATTR$  
84.        RNM OBJECT_NAME  
85. ---------- --------------------------------------------------------------------------------------------------------------------  
86.        167 HIER$  
87.        168 HIERLEVEL$  
88.        169 RLS$  
89.        170 RLS_SC$  
90.        171 RLS_GRP$  
91.        172 RLS_CTX$  
92.        173 CONTEXT$  
93.        174 RULESET$  
94.        175 INDPART_PARAM$  
95.        176 OL$  
96.        177 OL$HINTS  
97.        RNM OBJECT_NAME  
98. ---------- --------------------------------------------------------------------------------------------------------------------  
99.        178 OL$NODES  
100.        179 SQL$  
101.        180 SQL$TEXT  
102.        181 SQLPROF$  
103.        182 SQLPROF$DESC  
104.        183 SQLPROF$ATTR  
105.        184 EXPPKGOBJ$  
106.        185 EXPPKGACT$  
107.        186 EXPDEPOBJ$  
108.        187 EXPDEPACT$  
109.        188 MON_MODS$  
110.        RNM OBJECT_NAME  
111. ---------- --------------------------------------------------------------------------------------------------------------------  
112.        189 MON_MODS_ALL$  
113.        190 METAVIEW$  
114.        191 METAFILTER$  
115.        192 METAXSL$  
116.        193 METAXSLPARAM$  
117.        194 METASTYLESHEET  
118.        195 METASCRIPT$  
119.        196 METASCRIPTFILTER$  
120.        197 METANAMETRANS$  
121.        198 METAPATHMAP$  
122.        199 EXTERNAL_TAB$  
123.        RNM OBJECT_NAME  
124. ---------- --------------------------------------------------------------------------------------------------------------------  
125.        200 EXTERNAL_LOCATION$  
126. 已选择100行。  
127. 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语句来。