Sql: Oracle paging

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
--书分类目录kind 
--涂聚文 Geovin Du 
create table geovindu.BookKindList
(
    BookKindID INT   PRIMARY KEY,
    BookKindName nvarchar2(500) not null,
    BookKindParent INT  null,
    BookKindCode varchar(100)   ---編號
);
--序列创建
  
drop SEQUENCE BookKindList_SEQ;
 
CREATE SEQUENCE geovindu.BookKindList_SEQ
INCREMENT BY 1     -- 每次加几个
START WITH 1     -- 从1开始计数
NOMAXVALUE        -- 不设置最大值
NOCYCLE            -- 一直累加，不循环
NOCACHE;           --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
 
--自增长触发器  
drop trigger BookKindList_ID_AUTO;
 
 
 create or replace trigger geovindu.BookKindList_ID_AUTO
  before insert on geovindu.BookKindList   --BookKindList 是表名
  for each row
declare
  nextid number;
begin
  IF :new.BookKindID IS NULL or :new.BookKindID=0 THEN --BookKindID是列名
    select geovindu.BookKindList_SEQ.Nextval --BookKindList_SEQ正是刚才创建的
    into nextid
    from dual;
    :new.BookKindID:=nextid;
  end if;
end;    
 
--对表的说明
comment on table geovindu.BookKindList is '书分类目录';
--对表中列的说明
comment on column geovindu.BookKindList.BookKindID is '目录ID';
comment on column geovindu.BookKindList.BookKindName is '目录名称';
comment on column geovindu.BookKindList.BookKindParent is '目录父ID';
comment on column geovindu.BookKindList.BookKindCode is '目录code';
 
 
declare
gg nvarchar2(500):='geovindu2';
dd nvarchar2(500):='d';
begin
select REPLACE(gg, chr(10), '') into dd from dual;
dbms_output.put_line(dd);
end;
 
 
 
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('六福书目录',0,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('自然科学',1,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('社会科学',1,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('文学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('设计艺术',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('小说',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('诗词散曲',4,'');
COMMIT;
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('文学理论',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('小品',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('杂文',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('散文',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('文言文',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('设计理论',5,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('平面设计',5,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('立体设计',5,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('影像设计',5,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('广告设计',5,'');
COMMIT;
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('数学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('物理',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('化学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('生物学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('医学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('天文学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('地理学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('空间理论学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('环境环保学',2,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('工程学',2,'');
COMMIT;
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('心理学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('经济学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('统计学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('审计学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('会计学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('社会学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('哲学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('宗教学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('政治学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('法学',3,'');
COMMIT;
 
-- 通过分析函数分页
declare pageNumber int:=1;
pageSize int:=10;
begin
pageNumber:=1;
pageSize:=5;
--1 
SELECT t2.BookKindName,t2.BookKindParent,t2.BookKindID FROM(
  SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID,ROW_NUMBER() OVER(ORDER BY t1.BookKindID) rn
  FROM geovindu.BookKindList t1
) t2 WHERE t2.rn >= ((pageNumber-1)*pageSize+1) AND t2.rn <= (pageNumber*pageSize);
end;
 
 
 
declare pageNumber int:=1;
pageSize int:=10;
rid int:=0;
begin
pageNumber:=1;
pageSize:=5;
 SELECT t2.BookKindName,t2.BookKindParent,t2.BookKindID
  FROM(
  SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID,ROW_NUMBER() OVER(ORDER BY t1.BookKindID) rn
  FROM geovindu.BookKindList t1
) t2 WHERE t2.rn >= ((pageNumber-1)*pageSize+1) AND t2.rn <= (pageNumber*pageSize);
end;
 
 
--
select * from (select rownum rn,a.* from (select BookKindName,BookKindParent,BookKindID from geovindu.BookKindList) a where rownum<=10)  where rn>=6;
 
 
SELECT t2.BookKindName,t2.BookKindParent,t2.BookKindID FROM(
  SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID,ROW_NUMBER() OVER(ORDER BY t1.BookKindID) rn
  FROM geovindu.BookKindList t1
) t2 WHERE t2.rn >= ((1-1)*5+1) AND t2.rn <= (1*5);
 
 
--2
SELECT t2.BookKindName,t2.BookKindParent,t2.BookKindID FROM(
  SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID,ROW_NUMBER() OVER(ORDER BY t1.BookKindID) rn
  FROM geovindu.BookKindList t1
) t2 WHERE t2.rn >= ((2-1)*5+1) AND t2.rn <= (2*5);
 
--3
SELECT t2.BookKindName,t2.BookKindParent,t2.BookKindID FROM(
  SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID,ROW_NUMBER() OVER(ORDER BY t1.BookKindID) rn
  FROM geovindu.BookKindList t1
) t2 WHERE t2.rn >= ((3-1)*5+1) AND t2.rn <= (3*5);
 
 
 
 SELECT t1.BookKindName,t1.BookKindParent,ROW_NUMBER() OVER(ORDER BY t1.BookKindID) rn
  FROM geovindu.BookKindList t1
 
 
-- 1.2、通过 ROWNUM 分页 
SELECT t3.BookKindName,t3.BookKindParent,t3.BookKindID FROM(
  SELECT t2.*,ROWNUM rn FROM(
    SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID FROM geovindu.BookKindList t1 ORDER BY t1.BookKindID
  ) t2 WHERE ROWNUM <= (1*5)
) t3 WHERE t3.rn >= ((1-1)*5+1);
 
 
SELECT t3.BookKindName,t3.BookKindParent,t3.BookKindID FROM(
  SELECT t2.*,ROWNUM rn FROM(
    SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID FROM geovindu.BookKindList t1 ORDER BY t1.BookKindID
  ) t2 WHERE ROWNUM <= (2*5)
) t3 WHERE t3.rn >= ((2-1)*5+1);
 
 
 
 
--通过 ROWNUM 分页的一种变通写法（相对来说更好理解）：
SELECT t3.BookKindName,t3.BookKindParent,t3.BookKindID FROM(
  SELECT t2.*,ROWNUM rn FROM(
    SELECT t1.BookKindName,t1.BookKindParent,t1.BookKindID FROM geovindu.BookKindList t1 ORDER BY t1.BookKindID
  ) t2
) t3 WHERE t3.rn >= ((1-1)*5+1) AND t3.rn <= (1*5);
 
 
 
 
-- 1.3、通过 ROWID 分页
SELECT t4.BookKindName,t4.BookKindParent,t4.BookKindID
FROM geovindu.BookKindList t4
WHERE t4.ROWID IN(
  SELECT t3.BookKindID FROM(
    SELECT t2.BookKindID,ROWNUM rn FROM(
      SELECT t1.ROWID BookKindID FROM geovindu.BookKindList t1 ORDER BY t1.BookKindID
    ) t2 WHERE ROWNUM <= (1*5)
  ) t3 WHERE t3.rn >= ((1-1)*5+1)
) ORDER BY t4.BookKindID;
 
 
SELECT t4.BookKindName,t4.BookKindParent,t4.BookKindID
FROM geovindu.BookKindList t4
WHERE t4.ROWID IN(
  SELECT t3.BookKindID FROM(
    SELECT t2.BookKindID,ROWNUM rn FROM(
      SELECT t1.ROWID BookKindID FROM geovindu.BookKindList t1 ORDER BY t1.BookKindID
    ) t2 WHERE ROWNUM <= (2*5)
  ) t3 WHERE t3.rn >= ((2-1)*5+1)
) ORDER BY t4.BookKindID;
 
 
SELECT t4.BookKindName,t4.BookKindParent,t4.BookKindID
FROM geovindu.BookKindList t4
WHERE t4.ROWID IN(
  SELECT t3.BookKindID FROM(
    SELECT t2.BookKindID,ROWNUM rn FROM(
      SELECT t1.ROWID BookKindID FROM geovindu.BookKindList t1 ORDER BY t1.BookKindID
    ) t2 WHERE ROWNUM <= (3*5)
  ) t3 WHERE t3.rn >= ((3-1)*5+1)
) ORDER BY t4.BookKindID;
　　用的Toad for Oracle 12.1 编辑，Oracle 10g
posted @ 2019-12-20 13:03 ®Geovin Du Dream Park™ 阅读(396) 评论(0) 编辑收藏举报
®Geovin Du Dream Park™

why we only heard about haves and have-nots, but we did'nt heard about doers and doer-nots. 人生是一种心境,生活是一种艺术,成功是一种心态,幸福是一种感觉,竞争是一种建构,情感是一种容合.学习是一种成长.

Sql: Oracle paging

公告

搜索

常用链接

我的标签

积分与排名

随笔分类

随笔档案

文章分类

相册

捷为工作室

阅读排行榜

评论排行榜

推荐排行榜

最新评论
