我有一个管道函数是这样:create or replace function fun1 return table_type1 pipelined as
v row_type1;
begin
for myrow in (select KEYID,HOS_CODE, HOS_NAME from tableTest) loop
v := row_type1(myrow.HOS_CODE, myrow.HOS_NAME);
pipe row (v);
end loop;
return;
end;我想把其中查询的语句换成SQL拼接出来字符串。应该如何写呢?
for myrow in 里面可以放一个字符串吗?还是如何实现?
v row_type1;
begin
for myrow in (select KEYID,HOS_CODE, HOS_NAME from tableTest) loop
v := row_type1(myrow.HOS_CODE, myrow.HOS_NAME);
pipe row (v);
end loop;
return;
end;我想把其中查询的语句换成SQL拼接出来字符串。应该如何写呢?
for myrow in 里面可以放一个字符串吗?还是如何实现?
2 for x in (select regexp_substr('AAA,BB,FF,DDDD','[^,]+',1,level) item
3 from dual
4 connect by rownum <= regexp_count('AAA,BB,FF,DDDD',',')+1
5 ) loop
6
7 dbms_output.put_line(x.item);
8 end loop;
9 end;
10 /
AAA
BB
FF
DDDD
PL/SQL procedure successfully completedSQL>
我是想SQL查询语句是用字符串拼接的。就像下面这个地址一样,然后是用管道函数的方式实现的。
http://k11hao.iteye.com/blog/1101988