SqlOraclepaging
--书分类目录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;
以上是 SqlOraclepaging 的全部内容， 来源链接： utcz.com/z/531852.html
