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
|