I am trying to create a function that tells whether data exist in database or not. Trying to solve this using cursor if data exist then data will be fetched from database and value of temp will be TRUE
otherwise data would not be fetched from DB and it will set value of temp to false
.
But when I compile this function it gives warning that
Warning: Function created with compilation errors.
x
CREATE OR REPLACE FUNCTION CHECK_DATA(Eid IN varchar2(5))
RETURN boolean
IS
cursor cemp is select EMPNO from employee where EMPNO = Eid ;
eno employee.EMPNO%TYPE ;
temp number;
BEGIN
open cemp ;
fetch cemp into eno ;
if(cemp%notfound)then
temp := FALSE ;
else
temp := TRUE ;
End if ;
close cemp ;
return temp ;
END;
/
Advertisement
Answer
I recommended to use an implicit cursor and don’t bother for open/closed;
CREATE OR REPLACE FUNCTION CHECK_DATA(Eid IN varchar2(5)) RETURN boolean IS
n number;
BEGIN
select count(*) into n
from employee where EMPNO = Eid ;
if (n > 0) then
return true;
else
return false;
end if;
END;
/