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;
几年前写的一个oracle 函数
最新推荐文章于 2022-02-25 20:00:37 发布