u014532964
一筐猪
采纳率100%
2017-04-19 08:29 阅读 1.9k

oracle自定义函数中执行查询语句与直接执行查询语句不同,请问是怎么回事

50

自定义函数:

create or replace function uf_get_docsenduser(activityname in varchar2,
pid in number)
return varchar2 is
result varchar2(255);
begin
select to_char(Wmsys.Wm_Concat(to_Char(username)))
into result
from (select ope.docid,
ope.username,
row_number() over(partition by ope.docid order by ope.endtime desc) as rId
from gzyth_instance.fd_docoperate ope
where ope.opttype in ('提交', '追加', '创建')
and instr(ope.content, activityname || ']') > 0)
where rid = 1
and docid = pid
group by docid;
return(result);
end uf_get_docsenduser;

直接执行sql:

select to_char(Wmsys.Wm_Concat(to_Char(username)))
as result
from (select ope.docid,
ope.username,
row_number() over(partition by ope.docid order by ope.endtime desc) as rId
from gzyth_instance.fd_docoperate ope
where ope.opttype in ('提交', '追加', '创建')
and instr(ope.content, activityname || ']') > 0)
where rid = 1
and docid = pid
group by docid;

两种方式查询结果不一样,请问是什么原因,如何修改函数可以达到一致的效果

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

相关推荐