日期:2014-05-17  浏览次数:20780 次

sqlserver2000函數如何轉換成oracle9i函數?
如題:
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中可以这样调用