--把表里面的name字段的名字连起来: create or replace function fun_test return varchar2 as cursor c is select name from tablename ; p_out varchar2(5000); p_temp varchar2(100); begin open c; loop fetch c into v_temp; exit when c%notfound; p_out:=p_out||','||p_temp; end loop; close c; return p_out; exception when others then return '出错'; end ;
参考: 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
数据量小的时候(比如3条),你可以: SQL> select * from (select a.sid||','||b.sid||','||c.sid from tbname a,tbname b,tbname c where a.sid <>b.sid and b.sid<>c.sid and c.sid <> a.sid order by a.sid ,b.s id, c.sid ) where rownum <2;A.SID||','||B.SID||','||C.SID ----------------------------- 011303001,011303002,011303003 其他方法暂时不知道
17:07:56 SQL> create or replace function get_clause return varchar2 is 17:08:11 2 p_out varchar2(4000); 17:08:11 3 cursor temp is select name from tab_name; 17:08:11 4 begin 17:08:11 5 for tempa in temp loop 17:08:11 6 if p_out is null then 17:08:11 7 p_out:=tempa.name; 17:08:11 8 else 17:08:11 9 p_out:=p_out||','||tempa.name; 17:08:11 10 end if; 17:08:11 11 end loop; 17:08:11 12 return(p_out); 17:08:11 13 end; 17:08:11 14 /函数已创建。已用时间: 00: 00: 00.32 17:08:11 SQL> select * from tab_name; ID NAME ---------- ---------- 1 Tom1 2 Tom2 3 Tom3已用时间: 00: 00: 00.00 17:08:17 SQL> select get_clause from tab_name;GET_CLAUSE ------------------------------ Tom1,Tom2,Tom3 Tom1,Tom2,Tom3 Tom1,Tom2,Tom3已用时间: 00: 00: 00.00 17:08:25 SQL> select get_clause from dual;GET_CLAUSE ------------------------------ Tom1,Tom2,Tom3已用时间: 00: 00: 00.00
create or replace function fun_test return varchar2 as
cursor c is select name from tablename ;
p_out varchar2(5000);
p_temp varchar2(100);
begin
open c;
loop
fetch c into v_temp;
exit when c%notfound;
p_out:=p_out||','||p_temp;
end loop;
close c;
return p_out;
exception
when others then
return '出错';
end ;
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
SQL> select * from (select a.sid||','||b.sid||','||c.sid from tbname a,tbname b,tbname c
where a.sid <>b.sid and b.sid<>c.sid and c.sid <> a.sid order by a.sid ,b.s
id, c.sid ) where rownum <2;A.SID||','||B.SID||','||C.SID
-----------------------------
011303001,011303002,011303003
其他方法暂时不知道
17:08:11 2 p_out varchar2(4000);
17:08:11 3 cursor temp is select name from tab_name;
17:08:11 4 begin
17:08:11 5 for tempa in temp loop
17:08:11 6 if p_out is null then
17:08:11 7 p_out:=tempa.name;
17:08:11 8 else
17:08:11 9 p_out:=p_out||','||tempa.name;
17:08:11 10 end if;
17:08:11 11 end loop;
17:08:11 12 return(p_out);
17:08:11 13 end;
17:08:11 14 /函数已创建。已用时间: 00: 00: 00.32
17:08:11 SQL> select * from tab_name; ID NAME
---------- ----------
1 Tom1
2 Tom2
3 Tom3已用时间: 00: 00: 00.00
17:08:17 SQL> select get_clause from tab_name;GET_CLAUSE
------------------------------
Tom1,Tom2,Tom3
Tom1,Tom2,Tom3
Tom1,Tom2,Tom3已用时间: 00: 00: 00.00
17:08:25 SQL> select get_clause from dual;GET_CLAUSE
------------------------------
Tom1,Tom2,Tom3已用时间: 00: 00: 00.00