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