I want to Create a function named VALIDATE_EMP which accepts employeeNumber as a parameter, Returns TRUE or FALSE depending on existence

user_16365428 0
Viewed 271

Sample Data

create table Employees (emp_id number, emp_name varchar2(50), salary number, department_id number) ;

insert into Employees values(1,'ALex',10000,10);
insert into Employees values(2,'Duplex',20000,20);
insert into Employees values(3,'Charles',30000,30);
insert into Employees values(4,'Demon',40000,40);

Code :

    create or replace function validate_emp(empno in number)
    return boolean
    is lv_count number
    begin
    
    select count(employee_id) into lv_count from hr.employees where employee_id = empno;
    if lv_count >1 then
    return true;
    else
    return false;
    end;

I want to Create a function named VALIDATE_EMP which accepts empno as a parameter, Returns TRUE if the specified employee exists in the table name “Employeee” else FALSE.

plsql function oracle
1 Answers
  • missing semi-colon as terminator of the local variable declaration
  • if user you're connected to isn't hr, remove it (otherwise, leave it as is)
  • column name is emp_id, not employee_id
  • missing end if

When fixed, code compiles:

SQL> CREATE OR REPLACE FUNCTION validate_emp (empno IN NUMBER)
  2     RETURN BOOLEAN
  3  IS
  4     lv_count  NUMBER;
  5  BEGIN
  6     SELECT COUNT (emp_id)
  7       INTO lv_count
  8       FROM employees
  9      WHERE emp_id = empno;
 10
 11     IF lv_count > 1
 12     THEN
 13        RETURN TRUE;
 14     ELSE
 15        RETURN FALSE;
 16     END IF;
 17  END;
 18  /

Function created.

SQL>

How to call it? Via PL/SQL as Oracle's SQL doesn't have the Boolean datatype.

SQL> set serveroutput on
SQL> declare
  2    result boolean;
  3  begin
  4    result := validate_emp(1);
  5
  6    dbms_output.put_line(case when result then 'employee exists'
  7                                 else 'employee does not exist'
  8                            end);
  9  end;
 10  /
employee does not exist

PL/SQL procedure successfully completed.

SQL>

Maybe you'd rather return VARCHAR2; then you'd mimic Boolean, but you'd be able to use it in plain SQL:

SQL> CREATE OR REPLACE FUNCTION validate_emp (empno IN NUMBER)
  2     RETURN VARCHAR2
  3  IS
  4     lv_count  NUMBER;
  5  BEGIN
  6     SELECT COUNT (emp_id)
  7       INTO lv_count
  8       FROM employees
  9      WHERE emp_id = empno;
 10
 11     IF lv_count > 1
 12     THEN
 13        RETURN 'TRUE';
 14     ELSE
 15        RETURN 'FALSE';
 16     END IF;
 17  END;
 18  /

Function created.

SQL> select validate_emp(1) from dual;

VALIDATE_EMP(1)
-------------------------------------------------------------------
FALSE

SQL>
user_90979060
Related