今天学习了一下pl/sql语句,也用pl/sql dev来做一些函数,但不知道如何用语句调试函数。在pl/sql dev的test窗口下边的参数列表中已经为输入参数设置值,再通过F9调试运行,结果执行的结果都是函数异常后返回的值50。
请教:如何用语句调试函数?
如何在pl/sql dev的test窗口中调试函数?附函数代码:
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
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;
dbms_output.put_line(sql_str);
execute immediate sql_str into result;
exception when others then
result:=50;
end;
return result;
exception when others then
raise_application_error('-20001','Error:get_Sum_FSAL004!');
end;需要输入的参数为:"T_temp_2898","201001","201002".
请教:如何用语句调试函数?
如何在pl/sql dev的test窗口中调试函数?附函数代码:
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
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;
dbms_output.put_line(sql_str);
execute immediate sql_str into result;
exception when others then
result:=50;
end;
return result;
exception when others then
raise_application_error('-20001','Error:get_Sum_FSAL004!');
end;需要输入的参数为:"T_temp_2898","201001","201002".
--我一般复杂的函数都是改写成过程来调试的,
--改成过程很简单,只需要去点返回值return 等就可以了
create or replace procedure 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
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;
dbms_output.put_line(sql_str);
execute immediate sql_str into result;
exception when others then
result:=50;
end;
--return result;
exception when others then
raise_application_error('-20001','Error:get_Sum_FSAL004!');
end;
paddy用||把where断开可能是为了格式好看些
我结贴先哦,给分给gelyon