Oracle，实现一条记录的上移下移

对于一个元组的上移下移排序

1.今天要和大家分享一个Oracle数据库实现对一条记录的上移下移功能

对于熟悉PLSQL编程的人来说，语法之类自然已经是家常便饭了，在此我就不再赘述，那么我们直接上代码，

表结构代码：

create table STORAGE_LOCATION
(
  id           NUMBER not null,
  storage_code VARCHAR2(100) not null,
  area_code    VARCHAR2(100) not null,
  code         VARCHAR2(100) not null,
  name         VARCHAR2(200) not null,
  shelf_code   VARCHAR2(100),
  state        VARCHAR2(100),
  flag         VARCHAR2(100),
  sort         NUMBER,
  create_time  DATE,
  create_user  VARCHAR2(100),
  update_time  DATE,
  udapte_user  VARCHAR2(100)
)

实现功能代码：

create or replace procedure pro_update_location_sort
(
    StorageCode varchar2,  --仓库代码
    sort_no number,     --sort_no   当前行sort值
    type_char number  --type_char 排序类型 1.置顶 2.上移 3.下移 4.置底
) is        
--实现功能：仓库管理中下架路径的排序  
   other_sort number;
     current_id number;
     other_id number;
     isRun number:=0;
     startTime varchar2(30);
begin

  --判断存储过程的运行状态
 select count(*) into isRun from oversea_run_status s where s.ems_type='pro_update_location_sort';
   if isRun=0 then
          insert into oversea_run_status(ems_type,create_on,status)
          values('pro_update_location_sort',sysdate,'1');
          --插入执行日志记录
          startTime:=to_char(sysdate,'yyyyMMddHH24miss');
          insert into OVERSEA_RUN_LOG(oper_name,oper_type,begin_date,end_date,username)
          values('管理下架路径','pro_update_location_sort',startTime,'','');
          commit;

       --业务逻辑处理

          --置顶   
          if type_char=1 then
            select (nvl(min(sl.sort),0)-1) into other_sort from storage_location sl ;
            update storage_location set sort=other_sort where sort=sort_no and storage_code=StorageCode;
            commit;
          end if;  
          --上移
          if type_char=2 then          
            select t.id into current_id from storage_location t where t.sort=sort_no and storage_code=StorageCode ;
            select t.id, nvl(t.sort,0) into other_id,other_sort from storage_location t
            where t.sort=
                    (select p from 
                                 (select sl.storage_code,sl.area_code, sl.sort,lag(sl.sort,1,0)  over (order by sl.sort) as p 
                                  from storage_location sl  where sl.storage_code=StorageCode order by sl.sort asc) c
                     where c.sort=sort_no 
                     ) ;
               if other_sort!=0 then 
                   update storage_location set sort=other_sort where id=current_id;
                   update storage_location set sort=sort_no where id=other_id;
                   commit;
               end if;
          end if;  
           --下移
          if type_char=3 then             
            select t.id into current_id from storage_location t where t.sort=sort_no and storage_code=StorageCode ;
            select t.id, nvl(t.sort,0) into other_id,other_sort from storage_location t
            where t.sort=
                    (select p from 
                                 (select sl.storage_code,sl.area_code,sl.sort,lead(sl.sort,1,0)  over (order by sl.sort) as p 
                                  from storage_location sl where sl.storage_code=StorageCode order by sl.sort asc) c
                     where c.sort=sort_no  
                     ) ;
               if other_sort!=0 then 
                   update storage_location set sort=other_sort where id=current_id;
                   update storage_location set sort=sort_no where id=other_id;
                   commit;
               end if;
          end if;
          --置底  
          if type_char=4 then
            select (nvl(max(sl.sort),0)+1) into other_sort from storage_location sl ;
            update storage_location set sort=other_sort where sort=sort_no and storage_code=StorageCode ;
            commit;
          end if;
          --更新运行状态、执行日志记录
          delete from oversea_run_status where ems_type='pro_update_location_sort';          
          update OVERSEA_RUN_LOG l set l.RUN_RESULT='成功',l.end_date =to_char(sysdate,'yyyyMMddHH24miss') 
           where l.begin_date=startTime;
          commit;  
  end if;
  --异常处理
exception when others then
   rollback;
   --更新运行状态、执行日志记录
   delete from oversea_run_status where ems_type='pro_update_location_sort';          
   update OVERSEA_RUN_LOG l set l.end_date =to_char(sysdate,'yyyyMMddHH24miss'),l.RUN_RESULT='失败'
   where l.begin_date=startTime;
   commit;  
end pro_update_location_sort;

调用，这个我们就不再讲了，

我们直接看效果吧：

 当然，本人数据库水平有限，如果数据量大的话，可能撑不起来，欢迎批评扔砖。