数据加载中……
oracle分页查询
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;

posted on 2008-12-10 10:56 有事没事 阅读(75) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航: