oracle 分页查询

 

--分页查询
create or replace procedure AISITE_QUERY_NAVIGATION
(p_sql IN VARCHAR,
p_page_size IN INTEGER,
p_page_number IN INTEGER,
p_count OUT NUMBER,
p_cursor OUT RECORDSET.SELECT_CURSOR)
 is
v_sql VARCHAR(4000);
v_page_lower_bound INTEGER;
v_page_upper_bount INTEGER ;
begin
v_page_lower_bound := (p_page_number - 1) * p_page_size + 1;
  v_page_upper_bount := p_page_number * p_page_size;

  v_sql := 'select count(*) from (' || p_sql || ')' ;
  EXECUTE IMMEDIATE v_sql INTO p_count ;

 

  v_sql := 'SELECT DISTINCT AISITE_NAVIGATION.*' ||
        ' FROM AISITE_NAVIGATION ' ||
        ' INNER JOIN ' ||
        ' (SELECT B.* '||
        '  FROM (SELECT A.* ,rownum rn' ||
               ' FROM (' || p_sql || ') A ' ||
               ' WHERE rownum <= ' || TO_CHAR(v_page_upper_bount) || ') B ' ||
        '  WHERE rn >= ' || TO_CHAR(v_page_lower_bound) || ') C' ||
        '  ON C.NAVIGATION_ID = AISITE_NAVIGATION.NAVIGATION_ID';

OPEN p_cursor FOR v_sql;
 
end AISITE_QUERY_NAVIGATION;

--导航移动
create or replace procedure AISITE_Move_Navigation
(
n_id in VARCHAR2 ,/*μ?o?id*/
p_type in  NUMBER,/*ààDí*/
p_id in VARCHAR2,/*???úμ?id*/
p_seq  in NUMBER, /*μ±?°μ?DòáD*/
IsMoveUp in NUMBER /*ê?·??òé?ò??ˉ*/
)
is 
 max_sequence number  ;
begin
 
  /*?òé?ò??ˉ*/
  if  IsMoveUp = 1 and  p_seq != 1
  then 
    if   p_id != '00000000-0000-0000-0000-000000000000'
    then
      /*ò??ˉ????*/
      update aisite_navigation   
      set aisite_navigation.sequence  = p_seq  
      where   
      aisite_navigation.positiontype = p_type 
      and aisite_navigation.parentid = p_id
      and aisite_navigation.sequence =  p_seq  - 1 
      and aisite_navigation.is_valid = 1 ;     
    else
      /*ò??ˉ????*/
      update aisite_navigation   
      set aisite_navigation.sequence  = p_seq  
      where    aisite_navigation.positiontype = p_type 
      and (aisite_navigation.parentid = '' or aisite_navigation.parentid  is null )
      and  aisite_navigation.sequence =  p_seq  -1
      and aisite_navigation.is_valid = 1 ; 
     end if ;
   
      /*ò??ˉμ?o?*/
      Update aisite_navigation 
      set  aisite_navigation.sequence  = p_seq  -1
      where aisite_navigation.navigation_id= n_id  ;
    
   end if ; 
  
    /*??μ?×?′óDòáD*/
   
   if     p_id != '00000000-0000-0000-0000-000000000000'
   then  
     select max(sequence )
     into  max_sequence
     from aisite_navigation
     where aisite_navigation.parentid = p_id
     and aisite_navigation.positiontype = p_type
     and  aisite_navigation.is_valid = 1   ;
   else    
     select max(sequence )
     into  max_sequence
     from aisite_navigation
     where  (aisite_navigation.parentid = '' or aisite_navigation.parentid  is null )
     and  aisite_navigation.positiontype = p_type
     and  aisite_navigation.is_valid = 1   ;
   end if ;
     
   /*?ò??ò??ˉ*/
  if  IsMoveUp = 0   and  p_seq != max_sequence 
  then   
     if   p_id != '00000000-0000-0000-0000-000000000000'
      then
        /*ò??ˉ????*/
        update aisite_navigation   
        set aisite_navigation.sequence  = p_seq  
        where   
         aisite_navigation.positiontype = p_type 
        and aisite_navigation.parentid = p_id
        and  aisite_navigation.sequence =  p_seq   + 1 
         and aisite_navigation.is_valid = 1 ; 
      else
        /*ò??ˉ????*/
        update aisite_navigation   
        set aisite_navigation.sequence  = p_seq  
        where   aisite_navigation.positiontype = p_type 
        and (aisite_navigation.parentid = '' or aisite_navigation.parentid  is null )
        and  aisite_navigation.sequence =  p_seq   + 1 
        and aisite_navigation.is_valid = 1 ;    
      end if ;
    
      /*ò??ˉμ?o?*/
      Update aisite_navigation 
      set  aisite_navigation.sequence  =  p_seq   + 1 
      where aisite_navigation.navigation_id= n_id  ; 

   end if ; 
 
end AISITE_Move_Navigation;