How to reorder a primary key

No PK violation. Updates only those values that need to be changed.

See Nested tables and Arrays
The table
create table test (
  id number primary key,
  rank number not null unique --We will reorder this columnn
)

 

The package head
CREATE OR REPLACE package reorder is
 type tRank is record (id number, rank number);
 type ttableRank is table of tRank index by binary_integer;  
  procedure newOrder(tableRank ttableRank);
end reorder;
/

 

The package body
CREATE OR REPLACE package body reorder is
  
  procedure exchange(id1 number, id2 number) is
    old1 number;
    old2 number;
  begin
    if(id1 != id2) then
      select rank into old1 from test where id = id1;
      select rank into old2 from test where id = id2;
      update test set rank = -1 where id = id1;
      update test set rank = old1 where id = id2;
      update test set rank = old2 where id = id1;
    end if;
  end;

  procedure newOrder(tableRank ttableRank) is
    type tRankId is record(id1 number, rank number, id2 number);
    type tTableRankId is table of tRankId index by binary_integer;
    tableRankId tTableRankId;
    i number;
    dummyId number;
    dummyRankId tRankId;
    found boolean := true;
  begin
    FOR i IN tableRank.first..tableRank.last loop
      tableRankId(i).id1 := tableRank(i).id;
      tableRankId(i).rank := tableRank(i).rank;
      select id into tableRankId(i).id2 from test where rank = tableRank(i).rank;
    end loop;
    --minor id is id1
    FOR i IN tableRankId.first..tableRankId.last loop
      if(tableRankId(i).id2 < tableRankId(i).id1) then
        dummyId := tableRankId(i).id2;
        tableRankId(i).id2 := tableRankId(i).id1;
        tableRankId(i).id1 := dummyId;
      end if;
    end loop;
    --sort by id1
    while found loop
      FOR i IN tableRankId.first..tableRankId.last-1 loop
        found := false;
        if(tableRankId(i+1).id1 < tableRankId(i).id1) then
          dummyRankId:= tableRankId(i);
          tableRankId(i) := tableRankId(i+1);
          tableRankId(i+1) := dummyRankId;
          found := true;
          exit;
        end if;
      end loop;
    end loop;
    --print, debug only
    /*FOR i IN tableRank.first..tableRank.last loop
      dbms_output.put_line('Printing ' || i || ' ' || tableRankId(i).id1 || ' ' || 
        tableRankId(i).rank || ' ' || tableRankId(i).id2);
    end loop;*/
    begin
      dummyId := null;
      FOR i IN tableRankId.first..tableRankId.last loop
        if((dummyId is null) or (tableRankId(i).id1 != dummyId)) then
          exchange(tableRankId(i).id1, tableRankId(i).id2);
          dummyId := tableRankId(i).id1;
        end if;
      end loop;
    end;
  end;
end reorder;
/

 

The testcase
begin
  insert into test values(1, 7);
  insert into test values(2, 8);
  insert into test values(3, 9);
  insert into test values(4, 10);
  insert into test values(5, 11);
  commit;
end;

select * from test

Now we want to change from    [1, 7] [2, 8] [3, 9] [4, 10] [5, 11]
                       to     [1, 9] [2, 8] [3, 7] [4, 11] [5, 10]
We need to exchange only       ****                 *****

declare
  tableRank reorder.ttableRank;
  i number := 0;
begin
  tableRank(i).id := 1;
  tableRank(i).rank := 9;
  i := i+1;
  tableRank(i).id := 2;
  tableRank(i).rank := 8;
  i := i+1;
  tableRank(i).id := 3;
  tableRank(i).rank := 7;
  i := i+1;
  tableRank(i).id := 4;
  tableRank(i).rank := 11;
  i := i+1;
  tableRank(i).id := 5;
  tableRank(i).rank := 10;
  reorder.newOrder(tableRank);
end;

select * from test