几年前写的一个oracle 函数

pkoqplnfgw

　　create or replace function getClassTheacher1(id in varchar2)
  return varchar2 is str  varchar2(2000);
  str1  varchar2(2000);
  str2  varchar2(2000);
  n number(10);
  m number(10);
  begin  
  select count(a.userid) into n from bjxx_class_teacher a,bjxx_user b
   where a.userid = b.userid AND a.classno = id;
   if n = 0 then
    str := '未关联老师';
   ELSIF n=1 then
    select b.truename,e.schoolname  into str1,str2 from bjxx_class_teacher a,bjxx_user b,bjxx_school e
   where a.userid = b.userid AND a.classno = id AND e.schoolcode = b.school;
   str :=CONCAT(str1,'(');
   str :=CONCAT(str,str2);
   str :=CONCAT(str,')');
   ELSE
       m :=1;
       str :='';
   WHILE m<=n LOOP
    SELECT c.truename,c.schoolname into str1,str2 FROM (SELECT ROWNUM as num,b.truename,e.schoolname from bjxx_class_teacher a,bjxx_user b,bjxx_school e
           where a.userid = b.userid AND a.classno = id AND e.schoolcode = b.school order by b.school) c where num =m;
      str :=CONCAT(str,str1);
      str :=CONCAT(str,'(');
      str :=CONCAT(str,str2);
      str :=CONCAT(str,'),');
      m :=m+1;
     END LOOP;
  end if;
  str :=RTRIM(str,',');
  return str;
end getClassTheacher1;