CODEWITHSUNDEEP
sqloracle-databaseplsql
sqlovers
sqlovers

Reputation: 67

Return boolean inside a create procedure pl sql

So i need to input the employee's name(parameter), and check if that his salary is lower than the average salary of his' department, than return false, if it's higher than return true. Below the procedure, i need to execute so that if it's false i output "low salary", but if it's true i output "high salary". Any idea how to use the boolean thing? And also i got an error because there cannot be any subqueries inside? I need to use pl/sql and create or replace procedure. This is my code so far, thanks!

CREATE OR REPLACE PROCEDURE get_status
 (v_name IN employees.first_name%TYPE) IS
 v_sal employees.salary%type
 v_status boolean;
BEGIN
 SELECT e1.salary INTO v_sal
 FROM employees e1
 WHERE e1.first_name = v_name;
 if(v_sal<(select avg(salary) from employees e2 where e1.department_id=e2.department_id group by e2.department_id)) then
    v_status:=false;
 else
    v_status:=true;
end if;
return v_status;
END;
/

BEGIN
 if(get_status('Neena')=true) then
 dbms_output.put_line('high salary');
 else
  dbms_output.put_line('low salary');
 end if;
END;
/

Upvotes: 0

Views: 1000

Answers (2)

Dornaut
Dornaut

Reputation: 553

To return some value from PL/SQL you either need to register OUT parameter for procedure, or use function instead of procedure. Also the syntax is incorrect because you use alias from select query(e1) inside pl/sql if statement

In current case I'd suggest to use function like

CREATE OR REPLACE FUNCTION get_status(
    v_name IN employees.first_name%TYPE
) return boolean IS
    v_compare number(1);
BEGIN
    select case when e1.salary < (select avg(salary) from employees e2 where e2.department_id = e1.department_id) then 0 else 1 end
    into v_compare
    from employees e1
    where e1.first_name = v_name;
    
    return v_compare = 1;
END;
/

fiddle

P.S. Also consider returning number(1 or 0) from this kind of PL/SQL funcitons - this will give you an advantage to be able to use this function inside SQL statements.

Upvotes: 0

Andrew Sayer
Andrew Sayer

Reputation: 2336

Procedures don't use the return clause, a function does. If you want output to be given to the caller of a procedure then you can declare an output argument. Also note that boolean is not a valid data type for SQL.

Here's a small example of using an out parameter

create or replace procedure check_number(numberIn in number
  ,booleanOut out boolean)
is
begin
  booleanOut := numberIn > 10;
end;
/
declare
  myBoolean boolean;
begin
  check_number (numberIn => 11, booleanOut => myBoolean);
  if myBoolean then
    dbms_output.put_line('True');
  else
    dbms_output.put_line('False');
  end if;
end;
/

For your comparison with the average salary you can just declare an additional variable to select the average into. You will also need to grab the employees department in the first query into another variable or you can have a subquery to get it.

...
SELECT e1.salary, e1.department_id INTO v_sal, v_dept
FROM employees e1
WHERE e1.first_name = v_name;
select avg(salary) into v_avg
from  employees e2 
where e2.department_id = v_dept;

booleanOut := v_sal< v_avg;

Or (using the subquery)

...
SELECT e1.salary INTO v_sal
FROM employees e1
WHERE e1.first_name = v_name;
select avg(salary) into v_avg
from  employees e2 
where e2.department_id = (SELECT e1.department_id FROM employees e1 WHERE e1.first_name = v_name);

booleanOut := v_sal< v_avg;

See also how I do not need to apply any other logic against the comparison to turn it into a boolean - it already is (otherwise you wouldn't be able to use it in an if statement.

Upvotes: 1

Related Questions