oracle函数参数与表列名相同的问题.

create or replace function FUNC_GET_SUMCNT(WORKDATE in VARCHAR2, PRODUCT in VARCHAR2,v_department in VARCHAR2) return number is
Result number;
hiscnt number;
datcnt number;
begin
Result := 0;
hiscnt := 0;
datcnt := 0;

select count(*) into hiscnt from wfsuser.wfs_hiscase h1, wfsuser.wfs_hisflow h2
where h1.flowid = h2.flowid(+)
and h1.stagecat = 'C4'
and h1.department=v_department
/*and decode(substrb(h2.cntreasonids, 1, 8)
,'40300624','固话','40300011','固话',
'40300760','小灵通','其他')=PRODUCT*/
and rptdata.PRODUCT_TYPE(h2.cntreasonids)=PRODUCT
and h1.endtime between to_date(WORKDATE||' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date(WORKDATE||' 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

select count(*) into datcnt from wfsuser.wfs_datcase d1, wfsuser.wfs_datflow d2
where d1.flowid = d2.flowid(+)
and d1.stagecat = 'C4'
and d1.department=v_department
/*and decode(substrb(d2.cntreasonids, 1, 8)
,'40300624','固话','40300011','固话',
'40300760','小灵通','其他')=PRODUCT*/
and rptdata.PRODUCT_TYPE(d2.cntreasonids)=PRODUCT
and d1.endtime between to_date(WORKDATE||' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date(WORKDATE||' 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

Result := hiscnt + datcnt;

return(Result);

Exception
when others then
Result := 0;
return(Result);
end FUNC_GET_SUMCNT;

以上过程中,若将函数参数v_department 改为 department 则会造成查询结果错误.

也许oracle编译器不能让函数参数和用作比较的表列名相同,这里为department .