FA FB 1 a 1 b 2 c 2 d 3 e 4 f1 a,b 2 c,d 3 e 4 f--函数 create or raplace function fun_test(p_id number) return varchar2 as cursor c is select FB from tablename where Fa=p_id; v_temp varchar2(20); p_out varchar2(200); begin open c; loop fetch c into v_temp; exit when c%notfound; p_out:=p_out||','||v_temp; end loop; close c; return p_out; exception when others then return '出错'; end;--查询 select fa,fun_test(fa) from (select distinct fa from tablename);
参考: SQL > select job,ename from emp;
JOB ENAME --------- ---------- CLERK SMITH SALESMAN ALLEN SALESMAN WARD MANAGER JONES SALESMAN MARTIN MANAGER BLAKE MANAGER CLARK ANALYST SCOTT PRESIDENT KING SALESMAN TURNER CLERK ADAMS
JOB ENAME --------- ---------- CLERK JAMES ANALYST FORD CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2) 2 return varchar2 is 3 4 str_return varchar2(200); 5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno; 6 begin 7 for str_temp in c_ename(jobno) loop 8 str_return:=str_return | |str_temp.ename; 9 end loop; 10 return str_return; 11 end; 12 /
函数已创建。
SQL > select f_ename(job) from ( 2 select distinct job from emp);
F_ENAME(JOB) --------------------------------------- SCOTT FORD SMITH ADAMS JAMES MILLER JONES BLAKE CLARK KING ALLEN WARD MARTIN TURNER
SQL > 加上job列: 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
1 a
1 b
2 c
2 d
3 e
4 f1 a,b
2 c,d
3 e
4 f--函数
create or raplace function fun_test(p_id number) return varchar2 as
cursor c is select FB from tablename where Fa=p_id;
v_temp varchar2(20);
p_out varchar2(200);
begin
open c;
loop
fetch c into v_temp;
exit when c%notfound;
p_out:=p_out||','||v_temp;
end loop;
close c;
return p_out;
exception
when others then
return '出错';
end;--查询
select fa,fun_test(fa) from (select distinct fa from tablename);
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
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