PL/SQL 数独 九宫图

drop table temp;
create table temp(row_id   char(1), col_id   char(1),  rol_id   char(2), qty      varchar2(9), back_qty varchar2(9), set_qty  varchar2(9), rmk      char(1));

begin
   delete from temp;
   for i in 1..9 loop
      for j in 1..9 loop
          insert into temp values(i,j,null,null,null,null,'N');
      end loop;
   end loop;
   update temp set qty = 9,rmk = 'Y'  where row_id = 1 and  col_id = 1;
   update temp set qty = 2,rmk = 'Y'  where row_id = 1 and  col_id = 3;
   update temp set qty = 8,rmk = 'Y'  where row_id = 1 and  col_id = 9;
   update temp set qty = 5,rmk = 'Y'  where row_id = 2 and  col_id = 2;
   update temp set qty = 3,rmk = 'Y'  where row_id = 2 and  col_id = 8;
   update temp set qty = 1,rmk = 'Y'  where row_id = 2 and  col_id = 9;
   update temp set qty = 9,rmk = 'Y'  where row_id = 3 and  col_id = 5;
   update temp set qty = 7,rmk = 'Y'  where row_id = 3 and  col_id = 6;
   update temp set qty = 1,rmk = 'Y'  where row_id = 4 and  col_id = 1;
   update temp set qty = 2,rmk = 'Y'  where row_id = 4 and  col_id = 5;
   update temp set qty = 4,rmk = 'Y'  where row_id = 4 and  col_id = 8;
   update temp set qty = 9,rmk = 'Y'  where row_id = 5 and  col_id = 3;
   update temp set qty = 3,rmk = 'Y'  where row_id = 5 and  col_id = 4;
   update temp set qty = 4,rmk = 'Y'  where row_id = 5 and  col_id = 6;
   update temp set qty = 7,rmk = 'Y'  where row_id = 5 and  col_id = 7;
   update temp set qty = 7,rmk = 'Y'  where row_id = 6 and  col_id = 2;
   update temp set qty = 1,rmk = 'Y'  where row_id = 6 and  col_id = 5;
   update temp set qty = 5,rmk = 'Y'  where row_id = 6 and  col_id = 9;
   update temp set qty = 8,rmk = 'Y'  where row_id = 7 and  col_id = 4;
   update temp set qty = 4,rmk = 'Y'  where row_id = 7 and  col_id = 5;
   update temp set qty = 6,rmk = 'Y'  where row_id = 8 and  col_id = 1;
   update temp set qty = 1,rmk = 'Y'  where row_id = 8 and  col_id = 2;
   update temp set qty = 8,rmk = 'Y'  where row_id = 8 and  col_id = 8;
   update temp set qty = 2,rmk = 'Y'  where row_id = 9 and  col_id = 1;
   update temp set qty = 1,rmk = 'Y'  where row_id = 9 and  col_id = 7;
   update temp set qty = 3,rmk = 'Y'  where row_id = 9 and  col_id = 9;
     
   update temp set rol_id = 1 where row_id <=3;
   update temp set rol_id = 2 where row_id > 3 and row_id <= 6;
   update temp set rol_id = 3 where row_id > 6 and row_id <= 9;
   
   update temp set rol_id = trim(rol_id) || 'A' where col_id <=3;
   update temp set rol_id = trim(rol_id) || 'B' where col_id > 3 and col_id <= 6;
   update temp set rol_id = trim(rol_id) || 'C' where col_id > 6 and col_id <= 9;   
end ;

create or replace view tmep_v
as
select row_id,sum(decode(col_id,1,qty,null)) col1,
              sum(decode(col_id,2,qty,null)) col2,
              sum(decode(col_id,3,qty,null)) col3,
              sum(decode(col_id,4,qty,null)) col4,
              sum(decode(col_id,5,qty,null)) col5,
              sum(decode(col_id,6,qty,null)) col6,
              sum(decode(col_id,7,qty,null)) col7,
              sum(decode(col_id,8,qty,null)) col8,
              sum(decode(col_id,9,qty,null)) col9
from temp where row_id = 1 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 2 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 3 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 4 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 5 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 6 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 7 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 8 group by row_id
union all
select row_id,sum(decode(col_id,1,qty,null)) ,
              sum(decode(col_id,2,qty,null)) ,
              sum(decode(col_id,3,qty,null)) ,
              sum(decode(col_id,4,qty,null)) ,
              sum(decode(col_id,5,qty,null)) ,
              sum(decode(col_id,6,qty,null)) ,
              sum(decode(col_id,7,qty,null)) ,
              sum(decode(col_id,8,qty,null)) ,
              sum(decode(col_id,9,qty,null))
from temp where row_id = 9 group by row_id

create or replace function f_set return number
is
  v_qty   varchar2(9);   v       char(1);  v_count int;  v_row   int;
  function get_qty(i_row_id char,  i_col_id char,  i_rol_id char) return char is   
  begin
     v_qty := '123456789';
     for rec_1 in( select QTY from temp where row_id = i_row_id and rmk = 'Y'
                   union
                   select QTY from temp where col_id = i_col_id and rmk = 'Y'
                   union
                   select QTY from temp where rol_id = i_rol_id and rmk = 'Y'
                  ) loop
          v_qty :=  replace(v_qty,rec_1.qty,'');        
     end loop;   
     return v_qty;           
  end;
   
begin
  v_row := 0;
  for rec_1 in (select * from temp where  rmk = 'N'   ) loop
      v_qty := get_qty(rec_1.row_id,rec_1.col_id,rec_1.rol_id);   
      update temp
         set qty = v_qty,
             rmk = decode(length(v_qty),1,'Y',rmk)
       where row_id = rec_1.row_id and col_id = rec_1.col_id
         and nvl(qty,'AA') <> v_qty;
       if sql%found then
          v_row := v_row + 1;
       end if;
  end loop;
  
  for rec_1 in(select *from temp where rmk = 'N' ) loop
        for i in 1 .. length(rec_1.qty) loop
            v := substr(rec_1.qty,i,1);            
            select count(*) into v_count from temp where row_id = rec_1.row_id
            and qty like '%'||v||'%';
            if v_count = 1 then
               update temp
                  set qty = v,
                      rmk = 'Y'
                where row_id = rec_1.row_id and col_id = rec_1.col_id;
                if sql%found then
                   v_row := v_row + 1;
                end if;
                exit;
            end if;               
            select count(*) into v_count from temp where col_id  = rec_1.col_id
            and qty like '%'||v||'%';
            if v_count = 1 then
               update temp
                  set qty = v,
                      rmk = 'Y'
                where row_id = rec_1.row_id and col_id = rec_1.col_id;
                if sql%found then
                   v_row := v_row + 1;
                end if;
                exit;
            end if;            
            select count(*) into v_count from temp where rol_id  = rec_1.rol_id
            and qty like '%'||v||'%';
            if v_count = 1 then
               update temp
                  set qty = v,
                      rmk = 'Y'
                where row_id = rec_1.row_id and col_id = rec_1.col_id;
                if sql%found then
                   v_row := v_row + 1;
                end if;
                exit;
            end if;            
        end loop;
    end loop;
    return v_row;
end;