sql数据库的函数CREATE function [dbo].[get_staff_id](@rolegroup varchar(6))
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r=isnull(@r+',','')+staff_id from staff where defaultrolegroup=@rolegroup and workstatus='ONLINE' order by
staff_id
return @r
end
想变成oracle里面的函数 怎么改?
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r=isnull(@r+',','')+staff_id from staff where defaultrolegroup=@rolegroup and workstatus='ONLINE' order by
staff_id
return @r
end
想变成oracle里面的函数 怎么改?
is
r varchar2(1000);
begin
select isnull(@r+',','')+staff_id into r from staff where defaultrolegroup=rolegroup and workstatus='ONLINE' order by staff_id ;
RETURN r;
end get_staff_id;
create or replace function get_staff_id(rolegroup varchar2)
return varchar2
as
r varchar2(8000);
select r=nvl(r||',','')||staff_id from staff where defaultrolegroup=rolegroup and workstatus='ONLINE' order by staff_id;
return r;
end get_staff_id;但是发现还是报错了
error如下
FUNCTION FLEX2011.GET_STAFF_ID 编译错误错误:PLS-00103: 出现符号 "SELECT"在需要下列之一时:
begin
function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
行:5
文本:select r=nvl(r||',','')||staff_id from staff where defaultrolegroup=rolegroup and workstatus='ONLINE' order by staff_id;
什么意思
select前加上begin
第一 变量r没有初始化 直接用了 select nvl(r||',','')||staff_id into r from staff r既然没有初始化 你就直接into r 就行了 nvl后边的r就没必要了 肯定是空的
第二 这样的select into 只能获取第一条记录 你的order by 是多余的
如果想要查询多条记录 连接到一块 要用游标来循环的
至于游标的具体用法 你百度一下 好好理解 记得更清楚 都告诉你了 印象反而不深刻
CREATE function get_staff_id(v_rolegroup varchar2)
return varchar2
as
v_r varchar2(8000);
begin
select wm_concat(staff_id) into v_r
from staff
where defaultrolegroup=v_rolegroup and workstatus='ONLINE';
return v_r;
end