create function dbo.GETFULLPATHNAME
(
@ArchiveID varchar(15) --参数
)
RETURNS varchar(1000)BEGIN
declare @ParentID varchar(15) --变量父ID
declare @ParentName varchar(500) --变量父名称
declare @ParentVersion varchar(500) --变量父名称
declare @FullPathName varchar(500)
set @FullPathName = '' SELECT @ParentID = PB_PARENT_ID , @ParentName = PB_NAME , @ParentVersion= VERSION FROM PB_ARCHIVES
where PB_ARCHIVES_ID = @ArchiveIDif (@ParentID is not null)--如果不是根节点
begin
set @FullPathName = dbo.GETFULLPATHNAME(@ParentID) + @ParentName+'\'
if( @ParentVersion ='1' or @ParentVersion is null)
set @FullPathName = dbo.GETFULLPATHNAME(@ParentID) + @ParentName+'\'
else
set @FullPathName = dbo.GETFULLPATHNAME(@ParentID) +'V'+ @ParentVersion+'_' + @ParentName+'\'
end return @FullPathName END
go
(
@ArchiveID varchar(15) --参数
)
RETURNS varchar(1000)BEGIN
declare @ParentID varchar(15) --变量父ID
declare @ParentName varchar(500) --变量父名称
declare @ParentVersion varchar(500) --变量父名称
declare @FullPathName varchar(500)
set @FullPathName = '' SELECT @ParentID = PB_PARENT_ID , @ParentName = PB_NAME , @ParentVersion= VERSION FROM PB_ARCHIVES
where PB_ARCHIVES_ID = @ArchiveIDif (@ParentID is not null)--如果不是根节点
begin
set @FullPathName = dbo.GETFULLPATHNAME(@ParentID) + @ParentName+'\'
if( @ParentVersion ='1' or @ParentVersion is null)
set @FullPathName = dbo.GETFULLPATHNAME(@ParentID) + @ParentName+'\'
else
set @FullPathName = dbo.GETFULLPATHNAME(@ParentID) +'V'+ @ParentVersion+'_' + @ParentName+'\'
end return @FullPathName END
go
create or replace function WEBPOWER.GETFULLPATHNAME(ArchiveID IN VARCHAR2) return VARCHAR2
is
FullPathName VARCHAR2(500);
ParentID VARCHAR2(15);
ParentName VARCHAR2(500);
ParentVersion VARCHAR2(500);
beginSELECT PB_PARENT_ID,PB_NAME,VERSION into ParentID,ParentName,ParentVersion FROM PB_ARCHIVES
where PB_ARCHIVES_ID =''' + ArchiveID + ''';
if (ParentID is not null) then
if( ParentVersion ='1' or ParentVersion is null) then
FullPathName := WEBPOWER.GETFULLPATHNAME(ParentID) + ParentName+'\';
else
FullPathName := WEBPOWER.GETFULLPATHNAME(ParentID) +'V'+ ParentVersion+'_' + ParentName+'\';
end if;
end if;
return FullPathName;
end;
/编译没问题,但返回总是为空。好像这个
SELECT PB_PARENT_ID,PB_NAME,VERSION into ParentID,ParentName,ParentVersion FROM PB_ARCHIVES
where PB_ARCHIVES_ID =''' + ArchiveID + ''';
有问题
where PB_ARCHIVES_ID =''' + ArchiveID + ''';
-》varchar2不需要引号了
SELECT PB_PARENT_ID,PB_NAME,VERSION into ParentID,ParentName,ParentVersion FROM PB_ARCHIVES
where PB_ARCHIVES_ID = ArchiveID;
SQL> create table test (name varchar(2));
Table created
SQL> insert into test(name) values('人');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test where name = ''黄'';
select * from test where name = ''黄''
ORA-00933: SQL command not properly ended
SQL> select * from test where name = '"黄"';
NAME
----
SQL> select * from test where name = '"人"'
2 ;
3 ;
select * from test where name = '"人"'
;
ORA-00911: invalid character
SQL> select * from test where name = '"人"';
NAME
----
SQL> select * from test where name = '人';
NAME
----
人
Table created
SQL> insert into test(name) values('人');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test where name = '"人"';
NAME
----
SQL> select * from test where name = '人';
NAME
----
人
我用这个
select GETFULLPATHNAME('2') from dual;执行结果提示:
ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在"WEBPOWER.GETFULLPATHNAME", line 16
直接sql语句select ... connect by 就行