Skip to content
Advertisement

how to create pl sql function to Checking if data exists in database or not

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.

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; 
/
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement