如题,代码如下,如果把if..end if这串去掉,编译不会出问题的。不知道哪里写错了??我用pl/sql dev来编译,定位在倒数第7行语句:exception when others then
提示PLS-00103:Encountered the symbol 'exception' when expection one of the following:begin case declare end exit for goto if loop mod null pragma raise returen select ....
create or replace function get_Sum_FSAL004(v_tableName in varchar2,v_FbegCmpPrd in varchar2,v_FendCmpPrd in varchar2) return number
is
result number;
sql_str varchar2(2000);
begin
begin
if v_FbegCmpPrd is null and v_FendCmpPrd is null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid';
else if v_FbegCmpPrd is not null and v_FendCmpPrd is null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber>='||v_FbegCmpPrd;
else if v_FbegCmpPrd is null and v_FendCmpPrd is not null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber<='||v_FendCmpPrd;
else
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber between '||v_FbegCmpPrd||' and '||v_FendCmpPrd;
end if; --dbms_output.put_line(sql_str); execute immediate sql_str into result;
exception when others then
result:=0;
end;
return result;
exception when others then
raise_application_error('-20001','Error:get_Sum_FSAL004!');
end;
提示PLS-00103:Encountered the symbol 'exception' when expection one of the following:begin case declare end exit for goto if loop mod null pragma raise returen select ....
create or replace function get_Sum_FSAL004(v_tableName in varchar2,v_FbegCmpPrd in varchar2,v_FendCmpPrd in varchar2) return number
is
result number;
sql_str varchar2(2000);
begin
begin
if v_FbegCmpPrd is null and v_FendCmpPrd is null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid';
else if v_FbegCmpPrd is not null and v_FendCmpPrd is null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber>='||v_FbegCmpPrd;
else if v_FbegCmpPrd is null and v_FendCmpPrd is not null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber<='||v_FendCmpPrd;
else
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber between '||v_FbegCmpPrd||' and '||v_FendCmpPrd;
end if; --dbms_output.put_line(sql_str); execute immediate sql_str into result;
exception when others then
result:=0;
end;
return result;
exception when others then
raise_application_error('-20001','Error:get_Sum_FSAL004!');
end;
is
result number;
sql_str varchar2(2000);
begin
begin
if v_FbegCmpPrd is null and v_FendCmpPrd is null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid';
elsif v_FbegCmpPrd is not null and v_FendCmpPrd is null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber>='||v_FbegCmpPrd;
elsif v_FbegCmpPrd is null and v_FendCmpPrd is not null then
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber<='||v_FendCmpPrd;
else
sql_str:='select sum(FSAL004) from '||v_tableName||' a left join T_HR_CMPPERIOD b on a.FPeriod=b.Fid'||
' where b.FNumber between '||v_FbegCmpPrd||' and '||v_FendCmpPrd;
end if; --dbms_output.put_line(sql_str); execute immediate sql_str into result;
exception when others then
result:=0;
end;
return result;
exception when others then
raise_application_error('-20001','Error:get_Sum_FSAL004!');
end;
oracle中的else if 要这样写 elsif
回楼上,我将else if 改成elseif 不行,也还会提示PLS-00103错误!而且错误记录更多!
感觉elseif 不像是关键字,其颜色和if/else/end if不一样,但查看PL/sql的语法,是好像有elseif的用法,蛮奇怪的!
回楼上,我将else if 改成elseif 不行,也还会提示PLS-00103错误!而且错误记录更多!
感觉elseif 不像是关键字,其颜色和if/else/end if不一样,但查看PL/sql的语法,是好像有elseif的用法,蛮奇怪的!
晕~~多了一个字符.原来oracle的是elsif关键词呀!怎么不是elseif呢?习惯害死人~