如何防止Varchar追加重复信息

Question

我正在Oracle SQL Developer中学习PL/SQL存储函数，在创建一个返回不包含重复信息的行的函数时遇到了问题。

架构信息：

1. 有ANUMBER(申请人编号(的申请人
2. SPOSSESSED(拥有技能(，具有SNAME(技能名称(和ANUMBER(将申请人与技能联系起来(
3. 具有PNUMBER的POSITION
4. SNEEDED具有SNAME和PNUMBER，将所需技能与职位联系起来

任务本质上是有一个函数，该函数获取一个编号，并返回一个字符串，其中包含申请人具备的可用职位。

我当前的代码：

CREATE OR REPLACE FUNCTION applicant_position_titles(anum NUMBER) RETURN VARCHAR IS
course_list VARCHAR(300);
first_position POSITION.TITLE%TYPE;
current_position POSITION.TITLE%TYPE;
BEGIN
course_list := '';
FOR spossessed_cursor IN (SELECT sname FROM SPOSSESSED WHERE anumber = anum)
LOOP
FOR sneeded_cursor IN (SELECT PNUMBER FROM SNEEDED WHERE spossessed_cursor.sname = sname)
LOOP
FOR position_cursor IN (SELECT TITLE FROM POSITION WHERE sneeded_cursor.PNUMBER = PNUMBER)         
LOOP
course_list := course_list || ' ' || position_cursor.title;
END LOOP;
END LOOP;
END LOOP;
RETURN course_list;
END applicant_position_titles;
/

我的选择声明：

SELECT anumber, applicant_position_titles(anumber) 
FROM APPLICANT 
WHERE applicant_position_titles(anumber) IS NOT NULL;

结果：

ANUMBER	APPLICATION_POSITION_TITLES(ANUMBER(
1	讲师讲师高级讲师讲师教授教授

@OldProgrammer是对的，你可以在一个select语句中完成。这是我的样品表和数据：

create table SPOSSESSED (sname varchar2(30), anumber number);
create table sneeded (sname varchar2(30), pnumber number);
create table "position" (title varchar2(30), pnumber number);
-------------------------------------
insert into SPOSSESSED values('name',1);
insert into SPOSSESSED values('name2',1);
insert into SPOSSESSED values('name3',1);
--------------------------------------
insert into sneeded values ('name',111);
insert into sneeded values ('name2',222);
insert into sneeded values ('name3',222);
--------------------------------------------
insert into "position" values ('lecturer',111);
insert into "position" values ('professor',222);

这里有一个选择语句：

select sp.anumber, LISTAGG(p.title,' ') WITHIN GROUP (ORDER BY sp.anumber) AS title
from spossessed sp,sneeded sn,"position" p
where
sp.sname=sn.sname and
p.pnumber=sn.pnumber
group by sp.anumber

结果：

ANUMBER | TITLE
1        lecturer professor

删除相同职位标题的编辑：

select anumber, LISTAGG(title,' ')
WITHIN GROUP (ORDER BY anumber) AS TITLE
from (
select distinct sp.anumber, p.title
from spossessed sp,sneeded sn,"position" p
where sp.sname=sn.sname and p.pnumber=sn.pnumber
)
group by anumber;