Sql: Oracle paging_ZenDei技術網路在線

用的Toad for Oracle 12.1 編輯，Oracle 10g ...
--書分類目錄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