数据库教程: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;