Question

Oracle函数中对变量赋值不起作用，用的是oracle11g

create or replace
FUNCTION toTestReturn
(
costcenterId varchar2
)
RETURN int
IS
rootId varchar2 (32);
sumnum int;
BEGIN
rootId := nvl(costcenterId, '0');
--select count(pid) into rootId from costcenterinfo where ID=rootId;
select count(pid) into sumnum from costcenterinfo where id=costcenterId;

RETURN sumnum;
END toTestReturn;

当我用select count(pid) into sumnum from costcenterinfo where id=costcenterId;时，sql调用函数是能返回sumnum=1的，但是当我使用注释的select count(pid) into rootId from costcenterinfo where ID=rootId;，sql调用函数返回的是null

不知道什么原因
上面的函数有点错误，应该是这样的：
create or replace
FUNCTION toTestReturn
(
costcenterId varchar2
)
RETURN varchar2
is
rootId varchar2 (32);
temp varchar2 (32);
BEGIN
rootId := nvl(costcenterId, '0');
select pid into temp from costcenterinfo where ID=rootId;
RETURN temp;

END toTestReturn;

现在调用函数时传的值肯定是不为空且pid也肯定有值，但是返回temp为null

Answer 1

--select count(pid) into rootId from costcenterinfo where ID=rootId;
select count(pid) into sumnum from costcenterinfo where id=costcenterId;

RETURN sumnum;

因脊蚂为你返回的旅辩是sumnum，使拆野缺用注释的你赋值给了rootId 所有返回NULL。