如題：
CREATE TABLE dbo.Dept(
id int identity(1,1),
Parent numeric(2),
Name varchar(1),
Type numeric(1)
)
INSERT INTO Dept(Parent,Name,Type)
select 0, 'A ',1
union all select 1, 'B ',2
union all select 1, 'C ',2
union all select 2, 'D ',3
union all select 2, 'E ',3
union all select 2, 'F ',3
union all select 3, 'G ',2
union all select 7, 'H ',4
union all select 8, 'I ',4
SELECT * FROM Dept
-- drop function dbo.fn_string
create function dbo.fn_string(@id numeric(2))
returns varchar(100)
as
begin
declare @s1 varchar(100)
declare @parent1 numeric(2),@name1 varchar(1),@type1 numeric(2)
set @s1= ' '
select @parent1=parent,@name1=name,@type1=type from Dept where id=@id
if @type1 <=2
begin
select @s1=@name1
end
else
begin
select @s1=dbo.fn_string(@parent1)+ '\ '+@name1
end
return(@s1)
end
SELECT ID,Name,FullPath=dbo.fn_string(id) FROM Dept
ORDER BY ID
這段sqlserver2000函數如何轉換成oracle9i函數？並且如何調用？
謝謝!
------解决方案--------------------
SELECT ID,Name,fn_string(id) FullPath
FROM Dept
ORDER BY ID
--oracle中可以这样调用