Sql: Oracle paging「建议收藏」

--书分类目录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;

代码100分