以下是我用sql server写的函数,转到 oracle不适用了。
老是报:
Compilation errors for FUNCTION SL.F_GETCHILDError: PLS-00103: 出现符号 "@"在需要下列之一时:
<an identifier>
<a double-quoted delimited-identifier> current
Line: 1
Text: CREATE OR REPLACE Function F_getChild(@PID int)
这些错误。请有经验者不吝指教。
CREATE OR REPLACE Function F_getChild(@PID int)Return @tbTree table(FMID NUMBER,PMID NUMBER,FMNAME VARCHAR2(100),[LEVEL] INT)
as
begin
--临时表
declare @tbTemp table(FMID NUMBER,PMID NUMBER VARCHAR2(100),[LEVEL] INT)
insert into @tbTemp
select t.fmid,
t.pfmid,
lpad(' --',8*level-1)||t.fmname||' ' as FMNAME,
level
from SYS_FUNCTION t
start with t.pfmid=@PID
connect by nocycle prior t.fmid= t.pfmid
order by t.fmid,level declare @PidTemp int
set @PidTemp = @PID++
loop
while(@PidTemp<1000)
begin
if(!Exit(Select * from @tbTemp where FMID=@PidTemp))
begin
insert into @tbTemp
select t.fmid,
t.pfmid,
lpad(' --',8*level-1)||t.fmname||' ' as FMNAME,
level
from SYS_FUNCTION t
start with t.pfmid=@PidTemp
connect by nocycle prior t.fmid= t.pfmid
order by t.fmid,level
end
end
end loop
insert into @tbTree select * from @tbTemp
return
end
go
老是报:
Compilation errors for FUNCTION SL.F_GETCHILDError: PLS-00103: 出现符号 "@"在需要下列之一时:
<an identifier>
<a double-quoted delimited-identifier> current
Line: 1
Text: CREATE OR REPLACE Function F_getChild(@PID int)
这些错误。请有经验者不吝指教。
CREATE OR REPLACE Function F_getChild(@PID int)Return @tbTree table(FMID NUMBER,PMID NUMBER,FMNAME VARCHAR2(100),[LEVEL] INT)
as
begin
--临时表
declare @tbTemp table(FMID NUMBER,PMID NUMBER VARCHAR2(100),[LEVEL] INT)
insert into @tbTemp
select t.fmid,
t.pfmid,
lpad(' --',8*level-1)||t.fmname||' ' as FMNAME,
level
from SYS_FUNCTION t
start with t.pfmid=@PID
connect by nocycle prior t.fmid= t.pfmid
order by t.fmid,level declare @PidTemp int
set @PidTemp = @PID++
loop
while(@PidTemp<1000)
begin
if(!Exit(Select * from @tbTemp where FMID=@PidTemp))
begin
insert into @tbTemp
select t.fmid,
t.pfmid,
lpad(' --',8*level-1)||t.fmname||' ' as FMNAME,
level
from SYS_FUNCTION t
start with t.pfmid=@PidTemp
connect by nocycle prior t.fmid= t.pfmid
order by t.fmid,level
end
end
end loop
insert into @tbTree select * from @tbTemp
return
end
go
临时表也不是那样用的。
变量赋值语句也不对。
sqlserver中用的是t-sql,而oracle中是pl/sql