代码如下,在PL/sql dev 中选择生成的存储过程,点击右键菜单的“test”,打开调试窗口,输入参数依次是 01.02.07,201001,201002
运行调试后弹出一个错误提示框,提示内容为:
ora-00904:"get_cmpSum_FSAL004":invalid identifier
ora-06512:at "GXLQ.Get_SchemeItemSum_Info",line 43
ora-06512: at line 3
view program sources of error starck?代码如下,请问都是什么错误呢?
create or replace procedure get_schemeItemSum_Info (v_ForgNumber in varchar2,
v_FbegCmpPrd in varchar2,v_FendCmpPrd in varchar2,
resultSet out sys_refcursor)
--获取所选组织及薪资期间下薪酬方案的各个薪酬项目
is
SQLStr varchar2(2048);
function get_cmpSum_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';
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;
begin
SQLStr:= 'select a.FNumber,a.FName_L2,b.FNumber,b.FDisplayName_l2,a.FHisTableName, get_cmpSum_FSAL004(a.FHisTableName,'||v_FbegCmpPrd||','||v_FendCmpPrd||') FSAL004
from T_HR_CMPSCHEME a left join T_org_admin b
on a.FAdminOrgID=b.fid
where ((a.FEnable=1 or a.FEnable=3) and (a.FIsDelete=0)) and a.FHisTableName is not null
and b.fnumber like '''||v_ForgNumber||'%''';
open resultSet for SQLStr;end;
运行调试后弹出一个错误提示框,提示内容为:
ora-00904:"get_cmpSum_FSAL004":invalid identifier
ora-06512:at "GXLQ.Get_SchemeItemSum_Info",line 43
ora-06512: at line 3
view program sources of error starck?代码如下,请问都是什么错误呢?
create or replace procedure get_schemeItemSum_Info (v_ForgNumber in varchar2,
v_FbegCmpPrd in varchar2,v_FendCmpPrd in varchar2,
resultSet out sys_refcursor)
--获取所选组织及薪资期间下薪酬方案的各个薪酬项目
is
SQLStr varchar2(2048);
function get_cmpSum_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';
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;
begin
SQLStr:= 'select a.FNumber,a.FName_L2,b.FNumber,b.FDisplayName_l2,a.FHisTableName, get_cmpSum_FSAL004(a.FHisTableName,'||v_FbegCmpPrd||','||v_FendCmpPrd||') FSAL004
from T_HR_CMPSCHEME a left join T_org_admin b
on a.FAdminOrgID=b.fid
where ((a.FEnable=1 or a.FEnable=3) and (a.FIsDelete=0)) and a.FHisTableName is not null
and b.fnumber like '''||v_ForgNumber||'%''';
open resultSet for SQLStr;end;
如下面一样,在动态SQL中调用嵌套局部函数是不行的。但可以直接调用完成后得到结果再拼结是可以的。
CREATE OR REPLACE PROCEDURE testouter(o OUT SYS_REFCURSOR) IS
FUNCTION innerfunc RETURN VARCHAR2 IS
BEGIN
RETURN 'abc';
END;
BEGIN
OPEN o FOR 'select innerfunc from dual'; --错误
OPEN o FOR 'select ' || innerfunc || ' from dual'; --OK
END;
/
2、如果你一定要在动态SQL内部调用,则将函数写在外部,作为全局函数。
CREATE OR REPLACE FUNCTION innerfunc RETURN VARCHAR2 IS
BEGIN
RETURN 'abc';
END;
/
CREATE OR REPLACE PROCEDURE testouter(o OUT SYS_REFCURSOR) IS
BEGIN
OPEN o FOR 'select innerfunc from dual'; --OK
END;
/
我测试的时候没问题,估计是我原先就在外部先创建了4个函数,你可以按此贴的处理方法,将那4个函数创建在外部,不用创建到procedure中,然后就避免了open cur for sql_str动态情况
不知道你明白没?
--存储过程只需要这样:另外的4个函数单独创建
CREATE OR REPLACE PROCEDURE get_sum_info1(v_ForgNumb IN VARCHAR2 ,
Form_FPeriodNum IN VARCHAR2 ,
To_FPeriodNum IN VARCHAR2 ,
resultSet OUT sys_refcursor )
IS
BEGIN
OPEN resultSet FOR
'SELECT a.Fnumber,a.Fname,b.ForgNumber,b.ForgName,
get_sum_FYF001(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FYF001,
get_sum_FJX002(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FJX002,
get_sum_FGW003(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FGW003,
get_sum_FtmpFld(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FtmpFld
FROM T_cmsScheme a left join T_OrgAdmin b
ON a.ForgID=b.FID
WHERE b.ForgNumber like '''||v_ForgNumb||'%''';
END;