CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2, Form_FPeriodNum IN VARCHAR2, To_FPeriodNum IN VARCHAR2) RETURN NUMBER IS result NUMBER; sql_str VARCHAR2(2000); BEGIN BEGIN
if Form_FPeriodNum is null and To_FPeriodNum is not null then sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' || 'WHERE b.FPeriodNum <='||To_FPeriodNum; else if Form_FPeriodNum is not null and To_FPeriodNum is null then sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' || 'WHERE b.FPeriodNum >='||Form_FPeriodNum; else sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID '; 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_FYF001 !'); END;
CREATE OR REPLACE FUNCTION get_sum_FYF001 (table_name IN VARCHAR2, Form_FPeriodNum IN VARCHAR2 default null, --设置默认值 To_FPeriodNum IN VARCHAR2 default null) RETURN NUMBER IS result NUMBER; sql_str VARCHAR2(2000); BEGIN BEGIN --通过if进行判断组合不同的sql语句 if Form_FPeriodNum is null and To_FPeriodNum is not null then sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' || 'WHERE b.FPeriodNum <='||To_FPeriodNum; else if Form_FPeriodNum is not null and To_FPeriodNum is null then sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' || 'WHERE b.FPeriodNum >='||Form_FPeriodNum; else sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID '; 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_FYF001 !'); END;
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2 ) RETURN NUMBER IS result NUMBER; sql_str VARCHAR2(2000); BEGIN if Form_FPeriodNum is null and To_FPeriodNum is null then sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID ';elsif Form_FPeriodNum is not null and To_FPeriodNum is null then sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '|| 'WHERE b.FPeriodNum>='||Form_FPeriodNum; elsif To_FPeriodNum is not null and Form_FPeriodNum is null then sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '|| 'WHERE b.FPeriodNum<='||To_FPeriodNum; else sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '|| 'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum; end if; --Dbms_Output.put_line(sql_str); EXECUTE IMMEDIATE sql_str INTO result; EXCEPTION WHEN OTHERS THEN result:=0; RETURN result; EXCEPTION WHEN OTHERS THEN Raise_Application_Error('-20001','Error: get_sum_FYF001 !') ; END;
嗯嗯,我怎么都没有想到用if else就行了呢。非常感谢wkc168和小L。
CREATE OR REPLACE FUNCTION get_sum_FYF001 (table_name IN VARCHAR2, Form_FPeriodNum IN VARCHAR2 default null, --设置默认值 To_FPeriodNum IN VARCHAR2 default null) RETURN NUMBER IS result NUMBER; sql_str VARCHAR2(2000); BEGIN BEGIN --通过if进行判断组合不同的sql语句 if Form_FPeriodNum is null and To_FPeriodNum is not null then sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' || 'WHERE b.FPeriodNum <='||To_FPeriodNum; else if Form_FPeriodNum is not null and To_FPeriodNum is null then sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' || 'WHERE b.FPeriodNum >='||Form_FPeriodNum; else sql_str := 'SELECT Sum(FYF001) FROM ' || table_name || ' a left join T_cmpPeriod b on a.FPeriodID=b.FID '; else sql_str:='SELECT Sum(FYF001) FROM '||table_name|| ' a left join T_cmpPeriod b on a.FPeriodID=b.FID '|| 'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum; 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_FYF001 !'); END;
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2) RETURN NUMBER IS result NUMBER; sql_str VARCHAR2(2000); BEGIN BEGIN sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '|| 'WHERE (b.FPeriodNum>'||Form_FPeriodNum||' and '||To_FPeriodNum||') or b.FPeriodNum>'||Form_FPeriodNum||' or b.FPeriodNum<'||To_FPeriodNum ; --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_FYF001 !') ; END;
然后再函数里对这两个参数进行判断
拼接不同的sql语句
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,
Form_FPeriodNum IN VARCHAR2,
To_FPeriodNum IN VARCHAR2)
RETURN NUMBER IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
BEGIN
if Form_FPeriodNum is null and To_FPeriodNum is not null then
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' ||
'WHERE b.FPeriodNum <='||To_FPeriodNum;
else if Form_FPeriodNum is not null and To_FPeriodNum is null then
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' ||
'WHERE b.FPeriodNum >='||Form_FPeriodNum;
else
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ';
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_FYF001 !');
END;
CREATE OR REPLACE FUNCTION get_sum_FYF001
(table_name IN VARCHAR2,
Form_FPeriodNum IN VARCHAR2 default null, --设置默认值
To_FPeriodNum IN VARCHAR2 default null)
RETURN NUMBER IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
BEGIN
--通过if进行判断组合不同的sql语句
if Form_FPeriodNum is null and To_FPeriodNum is not null then
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' ||
'WHERE b.FPeriodNum <='||To_FPeriodNum;
else if Form_FPeriodNum is not null and To_FPeriodNum is null then
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' ||
'WHERE b.FPeriodNum >='||Form_FPeriodNum;
else
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ';
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_FYF001 !');
END;
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2 )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
if Form_FPeriodNum is null and To_FPeriodNum is null then
sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID ';elsif Form_FPeriodNum is not null and To_FPeriodNum is null then sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
'WHERE b.FPeriodNum>='||Form_FPeriodNum;
elsif To_FPeriodNum is not null and Form_FPeriodNum is null then
sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
'WHERE b.FPeriodNum<='||To_FPeriodNum;
else
sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
end if; --Dbms_Output.put_line(sql_str);
EXECUTE IMMEDIATE sql_str INTO result;
EXCEPTION WHEN OTHERS THEN
result:=0; RETURN result;
EXCEPTION WHEN OTHERS THEN
Raise_Application_Error('-20001','Error: get_sum_FYF001 !') ;
END;
CREATE OR REPLACE FUNCTION get_sum_FYF001
(table_name IN VARCHAR2,
Form_FPeriodNum IN VARCHAR2 default null, --设置默认值
To_FPeriodNum IN VARCHAR2 default null)
RETURN NUMBER IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
BEGIN
--通过if进行判断组合不同的sql语句
if Form_FPeriodNum is null and To_FPeriodNum is not null then
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' ||
'WHERE b.FPeriodNum <='||To_FPeriodNum;
else if Form_FPeriodNum is not null and To_FPeriodNum is null then
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ' ||
'WHERE b.FPeriodNum >='||Form_FPeriodNum;
else
sql_str := 'SELECT Sum(FYF001) FROM ' || table_name ||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID ';
else
sql_str:='SELECT Sum(FYF001) FROM '||table_name||
' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum; 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_FYF001 !');
END;
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2)
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
BEGIN
sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
'WHERE (b.FPeriodNum>'||Form_FPeriodNum||' and '||To_FPeriodNum||') or b.FPeriodNum>'||Form_FPeriodNum||' or b.FPeriodNum<'||To_FPeriodNum ; --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_FYF001 !') ;
END;