PLSql -- 递归查询的另几种实现方式（函数/存储过程）

问题

这是一个树结构，查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称)

解决此问题的SQL语句见 Sql – 练习3 递归查询

解决1

 declare
  v_tno hand_teacher.teacher_no%type;
  v_mno hand_teacher.teacher_no%type;
begin
  select teacher_no
    into v_tno
    from hand_teacher
   where hand_teacher.teacher_name = '胡明星';

  -- produce
  dbms_output.put_line('------ procedure ----');
  dbms_output.put_line('Start with  ' || v_tno);

  search_manager(v_tno);

  --function
  dbms_output.put_line('------ function -----');
  dbms_output.put_line('Start with  ' || v_tno);

  while v_tno <> '0' loop
    v_mno := v_tno;

    v_tno := search_manager_F(v_tno);
  end loop;

exception
  when no_data_found then
    dbms_output.put_line('1111--no_data_found');
  when others then
    dbms_output.put_line(SQLCODE);
    dbms_output.put_line(SQLERRM);
end;

 -- function
create or replace function search_manager_F(t_no in hand_teacher.manager_no%type)
  return hand_teacher.manager_no%type is
  Result_value hand_teacher.manager_no%type;
begin
  select nvl(manager_no, '0')
    into Result_value
    from hand_teacher
   where teacher_no = t_no;

  if Result_value = '0' then
    dbms_output.put_line('END');
  else
    dbms_output.put_line(t_no || ' reports to ' || Result_value);
  end if;

  return(Result_value);

exception
  when no_data_found then
    dbms_output.put_line('2222--no_data_found');
  when others then
    dbms_output.put_line(SQLCODE);
    dbms_output.put_line(SQLERRM);

end search_manager_F;

--procedure
create or replace procedure search_manager(t_no in hand_teacher.teacher_no%type default 't003') is
  m_no hand_teacher.manager_no%type;
begin

  select nvl(manager_no, '0')
    into m_no
    from hand_teacher
   where teacher_no = t_no;

  if m_no = '0' then
    dbms_output.put_line('END');
  else
    dbms_output.put_line(t_no || ' reports to ' || m_no);
    search_manager(m_no);
  end if;

exception
  when no_data_found then
    dbms_output.put_line('2222--no_data_found');
  when others then
    dbms_output.put_line('2222--others');
end search_manager;

结果1

解决2

利用 Oracle WITH语句的解决