百分悬赏标值函数或存储过程

Question

本帖最后由 grn0515 于 2014-06-20 23:14:23 编辑

Answer 1

create or replace procedure test(pi_start_num number) is
  l_count    number;
  l_state    number;
  l_to_state number;
  l_a        number;
  l_b        number;
  l_c        number;
  l_d        number;
begin
  dbms_output.put_line('id' || ' a' || ' b' || ' c' || ' d');
  for c1 in (select id
               from a
              start with id = pi_start_num
             connect by pid = prior id
                    and (level = 1 or level = 2)
              order by id) loop
    for c2 in (select 'a' typ
                 from dual
               union all
               select 'b' typ
                 from dual
               union all
               select 'c' typ
                 from dual
               union all
               select 'd' typ from dual) loop
      if c2.typ = 'a' then
        l_state    := 1;
        l_to_state := 0;
      elsif c2.typ = 'b' then
        l_state    := 1;
        l_to_state := 2;
      elsif c2.typ = 'c' then
        l_state    := 2;
        l_to_state := 0;
      else
        l_state    := 2;
        l_to_state := 1;
      end if;
    
      select count(*)
        into l_count
        from (select b1.id,
                     b1.state,
                     lead(state) over(partition by b1.id order by rn) to_state
                from (select b.*, rownum rn from b) b1,
                     (select *
                        from a
                       start with id = c1.id
                      connect by pid = prior id) a1
               where b1.id = a1.id)
       where state = l_state
         and to_state = l_to_state;
    
      if c2.typ = 'a' then
        l_a := l_count;
      elsif c2.typ = 'b' then
        l_b := l_count;
      elsif c2.typ = 'c' then
        l_c := l_count;
      else
        l_d := l_count;
      end if;
    
    end loop;
    dbms_output.put_line(c1.id || ' ' || l_a || ' ' || l_b || ' ' || l_c || ' ' || l_d);
  end loop;
end test;