create function group_concat(doc_id in varchar2,v_sign in varchar2)
return varchar is l_text varchar2(32767) :=null;
begin
for cur_rel in (select hio.opinionid from hkgt_iso_document hid,hkgt_iso_opinion hio where hid.docid=hio.docid and hid.docid=doc_id) loop
l_text :=l_text ¦ ¦v_sign ¦ ¦ cur_rel.(hio.opinionid);
end loop;
return ltrim(l_text,v_sign);
end;
oracle 改成sql server函数怎么写
return varchar is l_text varchar2(32767) :=null;
begin
for cur_rel in (select hio.opinionid from hkgt_iso_document hid,hkgt_iso_opinion hio where hid.docid=hio.docid and hid.docid=doc_id) loop
l_text :=l_text ¦ ¦v_sign ¦ ¦ cur_rel.(hio.opinionid);
end loop;
return ltrim(l_text,v_sign);
end;
oracle 改成sql server函数怎么写
@doc_id nvarchar(30),
@v_sign nvarchar(30)
)
returns nvarchar(8000) as
begin
declare @l_text nvarchar(8000)
set @l_text=''
declare @opinionid nvarchar(100)
DECLARE Type_Cursor CURSOR FOR
select hio.opinionid from hkgt_iso_document hid,hkgt_iso_opinion hio where hid.docid=hio.docid and hid.docid=@doc_id
OPEN Type_Cursor
FETCH NEXT FROM Type_Cursor INTO @opinionid
WHILE @@FETCH_STATUS=0
BEGIN
set @l_text = @l_text+@v_sign+@opinionid
FETCH NEXT FROM Type_Cursor into @opinionid
END
CLOSE Type_Cursor
DEALLOCATE Type_Cursor declare @stmp nvarchar(8000)
set @stmp = ''
if substring(@l_text,1,len(@v_sign)) = @v_sign
begin
set @stmp = substring(@l_text,len(@v_sign),len(@l_text)-len(@v_sign))
end
else
set @stmp = @l_text return @stmp
endvarchar和nvarchar最多8000,不能上万的