CREATE OR REPLACE function print_comb(LEV in number) return varchar2 is cursor cura is select lv,a.i1,a.i2 from (select level lv,a.i i1,b.i i2 from comb a,comb b where a.i < b.i connect by prior b.i = a.i and level < lev) a where exists (select null from(select level lv,a.i i1,b.i i2 from comb a,comb b where a.i < b.i connect by prior b.i = a.i and level < lev) b where (a.lv < b.lv and a.i2 = b.i1) or a.lv = lev-1); cursor curb is select i from comb order by i; val varchar2(4000); wk varchar2(4000); type hoge is table of varchar2(100) index by binary_integer; arr_hoge hoge; begin dbms_output.enable(1000000); if lev = 1 then val := ''; for rec in curb loop if val is not null then val := val||','; end if; val := val||rec.i; end loop; else for rec in cura loop arr_hoge(rec.lv) := rec.i1; if rec.lv = lev - 1 then arr_hoge(rec.lv+1) := rec.i2; wk := ''; for i in 1..lev loop if i > 1 then wk := wk||','; end if; wk := wk||arr_hoge(i); end loop; dbms_output.put_line(wk); val := val||wk||' '; end if; end loop; end if; return val; end; /
select max(cnt) from( select count(*) cnt from (select level lv,a.i i1,b.i i2 from comb a,comb b where a.i < b.i connect by prior b.i = a.i and level < lev) a where lv = lev-1 union all select count(*) from comb where 1 = lev);
>select count(*) from >( >select level lv,i from comb >connect by prior i < i > and level <= LEV >) >where lv = LEV >; > >connect by で同じキーでの不等号条件と >level 関数で縛りを入れているのがポイント。 >(LEV 以下の組合せで終わるものを含む)
#以下iには1から5が入っています。
SQL> select lv,count(*) from 2 (select level lv,i from comb 3 connect by prior i < i) 4 group by lv;
>ちなみに sys_connect_by_path と order sibiling by を使えば >Oracle9i 9.20 では、かなり楽になります。
後だしジャンケンみたいで、ちょっと気が引けますが。。。
問題１
CREATE OR REPLACE function print_comb(LEV in number) return varchar2 is cursor cura is select Ans1 from ( select sys_connect_by_path(to_char(i,'fm09'),'|') || '|' Ans1,level LV from comb connect by prior i < i and level <= LEV order siblings by i ) where LV = LEV; val varchar2(2000); begin val := ''; for rec in cura loop if val is not null then val := val || ','; end if; val := val || rec.Ans1; end loop; return val; end; / show errors
問題２
CREATE OR REPLACE function count_comb(LEV in number) return number is i number; a number; b number; c number; begin
a:=1; b:=1; select count(*) into c from comb;
for i in (c-LEV+1)..c loop a := a * i ; end loop; for i in 1..LEV loop b := b * i ; end loop;
CREATE OR REPLACE function count_comb(LEV in number) return number is n number; r number; C number; begin select count(*) into n from comb; if ((lev < 0) or (lev > n)) then -- エラーパターン return 0; elsif (lev <= n/2) then -- nCr = nCn-r r := lev; else r := n-lev; end if;
C:=1; for i in 1..r loop C := C * n / i ; -- 絶対に割切れるのだ n := n - 1; end loop; return C; end; / show errors
begin for i in 1..10 loop insert into comp values(i); end loop; end; / commit;
create or replace function print_comb(LEV in number) return varchar2 is cursor c_tree(LEV in number) is select level lv,i from comb connect by level <= LEV and prior i < i; -- and ( (prior i<i) -- or (prior i=i and prior rowid><rowid)); --問題3 p_sep1 varchar2(1); p_sep2 varchar2(1); p_ret varchar2(2000); p_tmp varchar2(2000); p_prev number; p_pos number; begin p_sep1 := '/'; p_sep2 := ','; p_ret := ''; p_tmp := ''; p_prev := 0; for p_tree in c_tree(LEV) loop if (p_tree.lv = 1) then p_tmp := to_char(p_tree.i,'fm0999'); -- 4桁決打ち else if (p_tree.lv ><= p_prev) then p_tmp := substrb(p_tmp,1, (p_tree.lv-1)*(4+1)-1); --p_sep1でinstrbした方が柔軟性があって良い end if; p_tmp := p_tmp||p_sep1||to_char(p_tree.i,'fm0999'); end if; -- dbms_output.put_line(p_tmp); if (p_tree.lv = LEV) then if (p_ret is null or p_ret = '') then p_ret := p_tmp; else p_ret := p_ret||p_sep2||p_tmp; end if; end if; p_prev := p_tree.lv; end loop; return p_ret; end; / show error
declare c varchar2(2000); begin c := print_comb(3); for i in 1..lengthb(c)/45+1 loop dbms_output.put_line(substrb(c,(i-1)*45+1,45)); end loop; end; /
