1.說明
要分頁系統操作日志表，條件是根據操作日期，用戶名，操作模塊，采用降序顯示
2.不多說，看代碼吧
create or replace procedure get_sys_log(
iv_begindate in system_log.operate_datetime%type,
iv_enddate in system_log.operate_datetime%type,
iv_username in system_log.user_id%type,
iv_model in system_log.operate_model%type,
iv_pageIndex in number,
iv_pageTotal in number,
ov_totalnum out number,
ov_cur out system_manage.refcursor)is
begin
select count(rownum) into ov_totalnum
from system_log a,
system_user b
where a.user_id = b.user_id
and a.operate_datetime >= iv_begindate
and a.operate_datetime <= iv_enddate+1
and b.user_name like '%'||nvl(iv_username, b.user_name)||'%'
and a.operate_model like '%' || nvl(iv_model, a.operate_model) || '%';
open ov_cur for
select *
from (
select c.*,
rownum tempnum
from (select b.user_name user_name,
b.user_id user_id,
a.operate_datetime operate_datetime,
a.operate_model operate_model,
a.operate_event operate_event,
a.old_content old_content,
a.new_content new_content
from system_log a,
system_user b
where a.user_id = b.user_id
and a.operate_datetime >= iv_begindate
and a.operate_datetime <= iv_enddate+1
and b.user_name like '%'||nvl(iv_username, b.user_name)||'%'
and a.operate_model like '%' || nvl(iv_model, a.operate_model) || '%'
order by a.operate_datetime desc, b.user_name, a.operate_model
) c
where rownum <=(iv_pageIndex)*iv_pageTotal)
where tempnum >(iv_pageIndex-1)*iv_pageTotal;
end;