动态sql(2)

[ruide205]

假定有这个的一个表：
table a(id varchar(6),mydesc varchar(80),primary key(id))
设数据如下：
id desc
test1 test1 desc
test2 test2 desc
test3 test3desc
test4 testddd

需要写一个函数list( fieldname)返回以逗号分隔的字符串。如上表：
select list(id) from a where id<>’test4’ , 返回要求结果是：test1,test2,test3
因sql2000下函数中无法用Exec()，exec sp_executesql及动态SQL,请各位高手提供思路或方法.,谢谢!
这个是sqlserver版的问题，请问在oracle中如何实现！？
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
sqlserver解决方案：
create function f_str(@inid varchar(1000),@outid varchar(1000))
returns varchar(8000)
as
begin
declare @r varchar(8000)
select
@r = isnull(@r,'')+','+id
from
a
where
','+isnull(@inid ,id)+',' like '%,'+id+',%'
and
','+isnull(@outid,'')+',' not like '%,'+id+',%'

set @r = isnull(stuff(@r,1,1,''),'')
return @r
end
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

@r = isnull(@r,'')+','+id 可以代替oracle中游标的作用，会累加id???
小弟菜鸟，大家请指教！

[duanduan1122]

help up!

[feng2]

sys_connect_by_path函数
详见
http://community.csdn.net/Expert/topicview.asp?id=4161213

[bobfang]

oracle下的select ... into到变量的语句，所影响的行数必须为1，否则就会引起异常。

[ruide205]

请问在oracle中如何实现！？

[bzszp]

参考：
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