create or replace procedure queryUsers(pageSize in number,
page in number,
userList out cursor) as
begin
select (t3.name, t3.nation, t3.address, t3.id_card_number, t3.born_date,
t3.sex)
into userList
from (select t2.name,
t2.nation,
t2.address,
t2.id_card_number,
t2.born_date,
t2.sex,
rownum rn
from (select t1.name,
t1.nation,
t1.address,
t1.id_card_number,
t1.born_date,
t1.sex
from user_info t1) t2
where rownum <= (page * pageSize)) t3
where rn >= ((page - 1) * pageSize + 1);
end;
create or replace package body pagingQuery as
procedure queryUserinfo(pagesize in number,
page in number,
userlist out userinfo) as
begin
open userlist for
select t3.name,
t3.nation,
t3.address,
t3.id_card_number,
t3.born_date,
t3.sex
from (select t2.name,
t2.nation,
t2.address,
t2.id_card_number,
t2.born_date,
t2.sex,
rownum rn
from (select t1.name,
t1.nation,
t1.address,
t1.id_card_number,
t1.born_date,
t1.sex
from user_info t1) t2
where rownum <= (page * pageSize)) t3
where rn >= ((page - 1) * pageSize + 1);
end queryUserinfo;
end pagingQuery;