create or replace function f_ename(jobno in varchar2)
return varchar2 is
str_return varchar2(200);
cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
begin
for str_temp in c_ename(jobno) loop
str_return:=str_return||str_temp.ename;
end loop;
return str_return;
end;
/
SQL> select job,f_ename(job) from (
2 select distinct job from emp);JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
SQL>
return varchar2 is
str_return varchar2(200);
cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
begin
for str_temp in c_ename(jobno) loop
str_return:=str_return||str_temp.ename;
end loop;
return str_return;
end;
/
SQL> select job,f_ename(job) from (
2 select distinct job from emp);JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
SQL>
如果建成一个视图,然后以组合后得到的字段为条件查询视图,在原始表很大的情况下,这种方法非常慢。