create function fnTest(@ParentID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + name from tbTest where ParentID = @ParentID
RETURN stuff(@str,1,1, ' ')
end
请问上面的SQL Server语句如何翻译成Oracle语句,谢谢大家
str varchar2(4000) := ' ';
begin
select ' ' || ',' || name
into str
from tbTest
where ParentID = paramParentID;
return str := ' ' || substr(str, 2, length(str));
RETURN str;
end
没测试过你看看行不行
str varchar2(4000) := ' ';
begin
select ' ' || ',' || name
into str
from tbTest
where ParentID = paramParentID;
str := ' ' || substr(str, 2, length(str));
RETURN str;
end
上面的写错了多了个return
create or replace function fnTest(paramParentID number) return varchar2 as
strSql varchar2(4000);
str varchar2(4000) := ' ';
begin
strSql := 'select ' || '''' || str || '''' || '|| '','' ' ||
'|| name from tbTest where parentid=' || paramParentID;
dbms_output.put_line(strsql);
execute immediate strSql
into str;
dbms_output.put_line(str);
str := ' ' || substr(str, 2, length(str));
RETURN str;
end;
那么说明你的表里头有多条parentid为1的。
那么我写的那个当然会报错,因为必须返回一行数据
才好用..
比如有一个表SP_VOYAGE_PORT,表里有字段ID,parentid,PORT_CODE,数据如下:
ID,parentid,PORT_CODE
1 1 AA
2 1 BB
3 3 CC
4 3 DD
5 3 EE
6 4 FF我想用ORACLE写一个过程,通过parentid参数知道有哪些PORT_CODE如:
1 AA, BB
3 CC,DD,EE
4 FF
from
(
select parentid,PORT_CODE,min(PORT_CODE) over(partition by parentid) C_MIN,
(row_number() over(order by parentid,PORT_CODE))+(dense_rank() over (order by parentid)) numid
from SP_VOYAGE_PORT
)
start with PORT_CODE=C_MIN connect by numid-1=prior numid
group by parentid;