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_SEQINCREMENT 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 rowdeclare  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';declaregg nvarchar2(500):='geovindu2';dd nvarchar2(500):='d';beginselect 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;beginpageNumber:=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;beginpageNumber:=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);--2SELECT 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);--3SELECT 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.BookKindIDFROM geovindu.BookKindList t4WHERE 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.BookKindIDFROM geovindu.BookKindList t4WHERE 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.BookKindIDFROM geovindu.BookKindList t4WHERE 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

標籤：Oracle

上一篇：skipping archived logs of thread 1 from sequence 29 to 46; already backed up

下一篇：Error: cannot fetch last explain plan from PLAN_TABLE