sqlserver2000的函数:create function fFileMenu(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=menuName,@id=parkey from TShareMenuTree where pkey=@id
while @@rowcount<>0
begin
select @str=menuName+'/'+@str,@id=parkey from TShareMenuTree where pkey=@id
end
return isnull(@str,'/')
End我转换过后的oracle,但是不正确,请问怎样才可以.
我想得到的是各列查出的数据转换成字符串,如a/b/c/这样的值CREATE OR REPLACE FUNCTION fFileMenu(id integer)
RETURN varchar2 IS
str varchar2;
BEGIN
str:='';
select str:=menuName,id:=parkey from TShareMenuTree where pkey=id
while @@rowcount<>0
begin
select str:=menuName||'/'||str,id:=parkey from TShareMenuTree where pkey=id
end
return str;
END;
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=menuName,@id=parkey from TShareMenuTree where pkey=@id
while @@rowcount<>0
begin
select @str=menuName+'/'+@str,@id=parkey from TShareMenuTree where pkey=@id
end
return isnull(@str,'/')
End我转换过后的oracle,但是不正确,请问怎样才可以.
我想得到的是各列查出的数据转换成字符串,如a/b/c/这样的值CREATE OR REPLACE FUNCTION fFileMenu(id integer)
RETURN varchar2 IS
str varchar2;
BEGIN
str:='';
select str:=menuName,id:=parkey from TShareMenuTree where pkey=id
while @@rowcount<>0
begin
select str:=menuName||'/'||str,id:=parkey from TShareMenuTree where pkey=id
end
return str;
END;
while @@rowcount<>0
什么意思?想干吗?
returns varchar(8000)
is
mn varchar(8000);
pak varchar(8000);cursor c1 is
select menuName, parkey from TShareMenuTree where pkey=id;begin
OPEN c1;
LOOP
FETCH c1
INTO mn, pak;
EXIT WHEN c1%NOTFOUND;
str := mn || '/' || pak || '/';
END LOOP;
CLOSE c1;
return (str)
End;
是的话要这样。
create function fFileMenu(id in int)
returns varchar(8000)
is
mn varchar(8000);
pak varchar(8000);cursor c1 is
select menuName, parkey from TShareMenuTree where pkey=id;begin
OPEN c1;
LOOP
FETCH c1
INTO mn, pak;
EXIT WHEN c1%NOTFOUND;
str := str || mn || '/' || pak;
str := str || '/';
END LOOP;
CLOSE c1;
return (str)
End
as
str varchar2(8000);
begin
str:='';
for c1 in(select menuname from TShareMenuTree connect by pkey= prior parkey start with pkey=p_id) LOOP
str := str||'/'||c1.menuname;
END LOOP;
str:=substr(str,2,length(str));
return nvl(str,'/');
End;