PL/SQL解數獨在簡單優化基礎上的改進

原始程式，改寫自這篇python解數獨的簡單優化 。

create or replace procedure sd3(a in varchar)
as
i int;
r int;
c int;
r0 int;
c0 int;
n int;
e varchar(10);
begin
i:=instr(a,'0');
if (i=0) then
dbms_output.put_line(a);
return;
end if;
e:='a';
r:=floor((i-1)/9)+1;
c:=mod(i-1,9)+1;
r0:=floor((i-1)/27)*3+1;
c0:=floor(mod(i-1,9)/3)*3+1;
--same row
for j in 1..9 loop
n:=(r-1)*9+j;
if instr(e,substr(a,n,1))=0 and substr(a,n,1)<>0 then
   e:=e||substr(a,n,1); 
end if;
end loop;
--same col
for j in 1..9 loop
n:=(j-1)*9+c;
if instr(e,substr(a,n,1))=0 and substr(a,n,1)<>0 then
   e:=e||substr(a,n,1); 
end if;
end loop;
--same blk
for j in 1..9 loop
n:=(r0+floor((j-1)/3)-1)*9+c0+mod(j-1,3);
if instr(e,substr(a,n,1))=0 and substr(a,n,1)<>0 then
   e:=e||substr(a,n,1); 
end if;
end loop;

for m in 1 ..9 loop
if instr(e,m)=0 then
--dbms_output.put_line(substr(a,1,i-1)||'('||m||')'||substr(a,i+1));
sd3(substr(a,1,i-1)||m||substr(a,i+1));
end if;
end loop;
end;
/

雖然把81次遍歷縮短到了27次，其實還是有很多多餘的步驟，以左上角格子為例，在列舉同行和同列時，本身都各被列舉了1次，在列舉第一個九宮時，第一列和第一行的格子又被列舉了。而且這種列舉每次呼叫函式都要重複進行。
改進思路1，把原始佈局中的未知格子的鄰居（同行、同列、同宮）一次性找出來，每次呼叫函式只要列舉這個格子的鄰居即可。每個格子都有20個鄰居。

create or replace procedure sd4(a in varchar)
is
type int_varray is varray(20) of int;
type int_varray2 is varray(81) of int_varray;
sd int_varray2:=int_varray2();

procedure init
is
k int;
begin
--find each one's 20 neibour
sd.extend(81);
for i in 1..81 loop
sd(i):=int_varray();
sd(i).extend(20);
k:=1;
for j in 1..81 loop
if i<>j and  ((floor((i-1)/9) = floor((j-1)/9)) or
   (mod(i-j, 9) = 0) or
   (floor((i-1)/27) = floor((j-1)/27) and floor(mod(i-1,9)/3) = floor(mod(j-1,9)/3))) then
     sd(i)(k):=j;
     k:=k+1;
end if;
end loop;
dbms_output.put_line('');
end loop;

end;


procedure sd4_inner(a in varchar)
as
i int;
n int;
e varchar(10);


begin
i:=instr(a,'0');
if (i=0) then
dbms_output.put_line(a);
return;
end if;
e:='a';
--lookup in 20   neibour  

for j in 1..20 loop
n:=sd(i)(j);
if instr(e,substr(a,n,1))=0 and substr(a,n,1)<>0 then
   e:=e||substr(a,n,1); 
end if;
end loop;


for m in 1 ..9 loop
if instr(e,m)=0 then
--dbms_output.put_line(substr(a,1,i-1)||'('||m||')'||substr(a,i+1));
sd4_inner(substr(a,1,i-1)||m||substr(a,i+1));
end if;
end loop;
end;

begin

init;
sd4_inner(a);
end;
/

改進思路2，上述20個鄰居中，有些是已知的，有些是未知的。已知的不會變化，所以只要記得它們包含的數字，未知的每次嘗試不同的數字時會變化，需要遍歷。

create or replace procedure sd5(a in varchar)
is
type int_varray3 is varray(81) of varchar(10);
ex int_varray3:=int_varray3();
type int_varray is varray(20) of int;
type int_varray2 is varray(81) of int_varray;
sd int_varray2:=int_varray2();

procedure init
is
k int;
begin
--find each one's 20 neibour
sd.extend(81);
ex.extend(81);

for i in 1..81 loop
sd(i):=int_varray();
sd(i).extend(20);
k:=1;
for j in 1..81 loop
if i<>j and  ((floor((i-1)/9) = floor((j-1)/9)) or
   (mod(i-j, 9) = 0) or
   (floor((i-1)/27) = floor((j-1)/27) and floor(mod(i-1,9)/3) = floor(mod(j-1,9)/3))) then
   if substr(a,j,1)='0' then
     sd(i)(k):=j;
     k:=k+1;
   elsif instr(ex(i),substr(a,j,1))=0 or instr(ex(i),substr(a,j,1)) is null then
     ex(i):=ex(i)||substr(a,j,1);
   end if;
end if;
end loop;
dbms_output.put_line('');
end loop;

end;


procedure sd5_inner(a in varchar)
as
i int;
n int;
e varchar(10);


begin
i:=instr(a,'0');
if (i=0) then
dbms_output.put_line(a);
return;
end if;
e:='a'||ex(i);
--lookup in 20   neibour  

for j in 1..20 loop
n:=sd(i)(j);
if n is null then
  exit;
end if;
if instr(e,substr(a,n,1))=0 and substr(a,n,1)<>0 then
   e:=e||substr(a,n,1); 
end if;
end loop;


for m in 1 ..9 loop
if instr(e,m)=0 then
--dbms_output.put_line(substr(a,1,i-1)||'('||m||')'||substr(a,i+1));
sd5_inner(substr(a,1,i-1)||m||substr(a,i+1));
end if;
end loop;
end;

begin

init;
sd5_inner(a);
end;
/

執行結果比較：

SQL> exec sd5('800000000003600000070090200050007000000045700000100030001000068008500010090000400')
812753649943682175675491283154237896369845721287169534521974368438526917796318452

PL/SQL 過程已成功完成。

已用時間:  00: 00: 17.02
SQL> exec sd4('800000000003600000070090200050007000000045700000100030001000068008500010090000400')
812753649943682175675491283154237896369845721287169534521974368438526917796318452

PL/SQL 過程已成功完成。

已用時間:  00: 00: 21.41
SQL> exec sd3('800000000003600000070090200050007000000045700000100030001000068008500010090000400')
812753649943682175675491283154237896369845721287169534521974368438526917796318452

PL/SQL 過程已成功完成。

已用時間:  00: 00: 40.65