上面說的第三步必須保證每個點不能重復分配，有些難度，還是用過程代碼吧。

CREATE TABLE t_offset as

select 1? ?? ?? ???id,1.001??x,1.002? ?y, 10 mark from dual

union all

select 2011? ?? ?id,1.001??x,1.012? ?y, 31 mark from dual

union all

select 34213? ???id,2.009??x,2.504? ?y, 51 mark from dual

union all

select 42? ?? ???id,1.001??x,1.002? ?y, 92 mark from dual

union all

select 555? ?? ? id,1.001??x,1.002? ?y, 71 mark from dual

union all

select 678? ?? ? id,3.122??x,4.998? ?y, 43 mark from dual

union all

select 730? ?? ? id,7.010??x,1.111? ?y, 11 mark from dual

;

CREATE INDEX t_offset_idx ON t_offset(x,y);

CREATE TYPE obj_dots AS OBJECT (id NUMBER,x number,y NUMBER);

/

CREATE TYPE t_obj_dots AS TABLE OF obj_dots;

/

DECLARE

lv_dots t_obj_dots:=t_obj_dots();

lv_level NUMBER;

BEGIN

FOR lv_dup IN

(? ?SELECT *

FROM (SELECT t_offset.*

,ROW_NUMBER() OVER(PARTITION BY x,y ORDER BY id) rn

FROM t_offset

)

WHERE rn>1

)

LOOP

lv_level :=1;

WHILE lv_level>0 LOOP

FOR lv_new IN

(? ?SELECT *

FROM (SELECT off_x

,off_y

,lv_dup.x + off_x*0.001 x

,lv_dup.y + off_y*0.001 y

FROM (SELECT LEVEL-lv_level-1 off_x FROM DUAL CONNECT BY LEVEL<=lv_level*2+1)

,(SELECT LEVEL-lv_level-1 off_y FROM DUAL CONNECT BY LEVEL<=lv_level*2+1)

WHERE off_x IN (lv_level,-lv_level) OR off_y IN (lv_level,-lv_level)

)

WHERE (x,y) NOT IN (SELECT x,y FROM t_offset)

AND (x,y) NOT IN (SELECT x,y FROM TABLE(lv_dots))

ORDER BY off_x*off_x+off_y*off_y

)

LOOP

lv_dots.EXTEND;

lv_dots(lv_dots.COUNT):=obj_dots(lv_dup.id,lv_new.x,lv_new.y);

lv_level := -999;

EXIT;

END LOOP;

lv_level := lv_level+1;

END LOOP;

END LOOP;

FOR i IN 1..lv_dots.COUNT LOOP

DBMS_OUTPUT.PUT_LINE('id:'||lv_dots(i).id||' x:'||lv_dots(i).x||' y:'||lv_dots(i).y);

END LOOP;

END;

/