oracle 分页查询

最新推荐文章于 2025-12-02 20:26:41 发布

原创最新推荐文章于 2025-12-02 20:26:41 发布 · 566 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #integer #sql #null #query #join

本文介绍了一种使用PL/SQL实现的分页查询过程和导航元素移动功能的方法。通过创建两个存储过程，一是实现带有计数功能的分页查询，二是实现导航元素的上下移动调整。这些过程可用于网站导航菜单等场景中，以提高用户体验并简化后台操作。

--分页查询
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;