SQL> select * from ta;MEMBER LEADER
---------- ----------
MA LA
MB LA
MC LA
MD LB
ME LB已用时间: 00: 00: 00.40
SQL> select * from tb;C_USER AGE TITLE
---------- ---------- --------------------
MA 12 Engineer I
MB 13 Engineer I
MC 14 Engineer II
MD 15 Engineer I
ME 16 Engineer II已用时间: 00: 00: 00.30
SQL> create or replace function f_ename(leader in varchar2)
2 return varchar2 is
3 str_return varchar2(200);
4 cursor c_title(v_leader varchar2) is select title
5 from tb,ta where ta.member=tb.c_user and ta.leader=v_leader;
6 begin
7 for c_temp in c_title(leader) loop
8 str_return:=str_return||'/'||c_temp.title;
9 end loop;
10 return str_return;
11 end f_ename;
12 / 函数已创建。已用时间: 00: 00: 00.60
SQL> select distinct leader,f_ename(leader) from ta;LEADER
----------
F_ENAME(LEADER)
----------------------------------------------------------------------
LA
/Engineer I/Engineer I/Engineer IILB
/Engineer I/Engineer II
已用时间: 00: 00: 00.61
SQL>
---------- ----------
MA LA
MB LA
MC LA
MD LB
ME LB已用时间: 00: 00: 00.40
SQL> select * from tb;C_USER AGE TITLE
---------- ---------- --------------------
MA 12 Engineer I
MB 13 Engineer I
MC 14 Engineer II
MD 15 Engineer I
ME 16 Engineer II已用时间: 00: 00: 00.30
SQL> create or replace function f_ename(leader in varchar2)
2 return varchar2 is
3 str_return varchar2(200);
4 cursor c_title(v_leader varchar2) is select title
5 from tb,ta where ta.member=tb.c_user and ta.leader=v_leader;
6 begin
7 for c_temp in c_title(leader) loop
8 str_return:=str_return||'/'||c_temp.title;
9 end loop;
10 return str_return;
11 end f_ename;
12 / 函数已创建。已用时间: 00: 00: 00.60
SQL> select distinct leader,f_ename(leader) from ta;LEADER
----------
F_ENAME(LEADER)
----------------------------------------------------------------------
LA
/Engineer I/Engineer I/Engineer IILB
/Engineer I/Engineer II
已用时间: 00: 00: 00.61
SQL>
那是不是直接用SQL,不用Function和Stored Procedure & Package,不能实现该功能?
===========
function f_ename的返回值有4000个字符的限制啊???
还是返回错误:
ORA-06502:PLS/SQL numeric or value erro:character string buffer is too small
怎么解决?