CREATE function F_GetITABB(VAR_TYPE as varchar(50),VAR_FLAG as varchar(50))
returns varchar(8000)
as
temp nvarchar(4000) --中间变量
begin
temp := ''; --说明:if VAR_TYPE =1 then
select temp+rtrim(ltrim(ITABB))+',' into temp
from (
select distinct itabb
from WQ_EVGR_E
where itabb in
(
select COLUMNS_CODE
from TB_APPRAISE_ITABB
where smtp='GB3838-2002'
and COLUMNS_VISIBLE='√'
and columns_code<>'TPHK'
and FLAG=VAR_FLAG
)
) AA
where rownum =1 ;
end if ;--水域类型未知道if temp='' then
select null into temp from dual;
end if; temp:=substr(temp,len(temp)-1)
return temp
end
returns varchar(8000)
as
temp nvarchar(4000) --中间变量
begin
temp := ''; --说明:if VAR_TYPE =1 then
select temp+rtrim(ltrim(ITABB))+',' into temp
from (
select distinct itabb
from WQ_EVGR_E
where itabb in
(
select COLUMNS_CODE
from TB_APPRAISE_ITABB
where smtp='GB3838-2002'
and COLUMNS_VISIBLE='√'
and columns_code<>'TPHK'
and FLAG=VAR_FLAG
)
) AA
where rownum =1 ;
end if ;--水域类型未知道if temp='' then
select null into temp from dual;
end if; temp:=substr(temp,len(temp)-1)
return temp
end
两个错误:
Compilation errors for FUNCTION SHUIBJ.F_GETITABB
Error: PLS-00103: 出现符号 "AS"在需要下列之一时:
in out <an identifier>
<a double-quoted delimited-identifier> LONG_ double ref char
time timestamp interval date binary national character nchar
Line: 1
Text: CREATE function F_GetITABB(VAR_TYPE as varchar(50),VAR_FLAG as varchar(50))Error: Hint: Comparison with NULL in 'F_GetITABB'
Line: 30
Text: if temp='' then
CREATE function F_GetITABB(VAR_TYPE as varchar(50),VAR_FLAG as varchar(50))
returns varchar(8000)
as
temp nvarchar(4000); --中间变量
begin
temp := ''; --说明:if VAR_TYPE =1 then
select temp+rtrim(ltrim(ITABB))+',' into temp
from (
select distinct itabb
from WQ_EVGR_E
where itabb in
(
select COLUMNS_CODE
from TB_APPRAISE_ITABB
where smtp='GB3838-2002'
and COLUMNS_VISIBLE='√'
and columns_code<>'TPHK'
and FLAG=VAR_FLAG
)
) AA
where rownum =1 ;
end if ;--水域类型未知道if temp='' then
select null into temp from dual;
end if; temp:=substr(temp,len(temp)-1);
return temp;
end
Compilation errors for FUNCTION SHUIBJ.F_GETITABBError: PLS-00103: 出现符号 "AS"在需要下列之一时:
in out <an identifier>
<a double-quoted delimited-identifier> LONG_ double ref char
time timestamp interval date binary national character nchar
Line: 23
Text: from TB_APPRAISE_ITABBError: Hint: Comparison with NULL in 'F_GetITABB'
Line: 100
CREATE function F_GetITABB(VAR_TYPE in varchar(50),VAR_FLAG in varchar(50))
returns varchar
as
temp nvarchar(4000); --中间变量
begin
temp := ''; --说明:if VAR_TYPE =1 then
select temp+rtrim(ltrim(ITABB))+',' into temp
from (
select distinct itabb
from WQ_EVGR_E
where itabb in
(
select COLUMNS_CODE
from TB_APPRAISE_ITABB
where smtp='GB3838-2002'
and COLUMNS_VISIBLE='√'
and columns_code<>'TPHK'
and FLAG=VAR_FLAG
)
) AA
where rownum =1 ;
end if ;--水域类型未知道if temp='' then
select null into temp from dual;
end if; temp:=substr(temp,len(temp)-1);
return temp;
end
to: baojianjun(包子) 还是不对...
参数是不是也不能分配大小???
return varchar --改动
as
temp varchar2(4000); --中间变量 --改动
begin
temp := ''; --说明:if VAR_TYPE =1 then
select temp+rtrim(ltrim(ITABB))+',' into temp
from (
select distinct itabb
from WQ_EVGR_E
where itabb in
(
select COLUMNS_CODE
from TB_APPRAISE_ITABB
where smtp='GB3838-2002'
and COLUMNS_VISIBLE='√'
and columns_code<>'TPHK'
and FLAG=VAR_FLAG
)
) AA
where rownum =1 ;
end if ;--水域类型未知道if temp='' then
select null into temp from dual;
end if; temp:=substr(temp,len(temp)-1);
return temp;
end
都是一些基本的东西哦~~~
你看看我说的有没有用1:字符串的合并不能用 + ,用 ||
如:temp := 'aaa' || temp ;2:取字符串的长度用 length(temp) 而不是 len;3:取子串substr(temp,1,3); 意思是从temp中第 1 个字符开始取长度为 3 的子串4:temp := ''; 此时temp 已经是 NULL 了,再置空就没有必要了。
并且与 NULL 进行比较是没有意义的,肯定是 FALSE
baojianjun(包子) 的书写风格看起来好舒服~~受教了~~
谢谢指教!