check if select statement is true in a function in pl/sql-CodePudding

so i have a table called cars designed like this: ID, Model, Category, Availability. The categories are standard and luxury. im trying to write a function that returns either a boolean or an int like this :

function(id)
if category == luxury;
return 1;
else return 0;

i did this select statement but i dont know if i can use it

SELECT (CASE WHEN Category = 'Luxury' THEN 1 ELSE 0 END) AS is_equal
FROM CARS
WHERE ID = 'TM63DTI';

I have to do it in pl/sql. its for a school project and im stuck at this point. I dont have an error with this select statement, i just dont really know how to use it. Maybe suggest an idea of how i can approach this.

CodePudding user response：

PL/SQL means "Oracle", not "MySQL". I suggest you fix tags.

So, if it really is Oracle, then one option might be this:

create or replace function f_category
  (par_id in cars.id%type)
return boolean
is
  l_is_equal number;
begin
  select case when category = 'Luxury' then 1
              else 0
         end 
  into l_is_equal
  from cars
  where id = par_id;
  
  return l_is_equal = 1;
end;
/

Though, it is kind of difficult to use a function that returns Boolean in SQL, so - perhaps you'd also want to consider option that returns a number instead. It is simple to convert previous function to a new version:

create or replace function f_category
  (par_id in cars.id%type)
return number
is
  l_is_equal number;
begin
  select case when category = 'Luxury' then 1
              else 0
         end 
  into l_is_equal
  from cars
  where id = par_id;
  
  return l_is_equal;
end;
/

Now you can use it in SQL as e.g.

select f_category(par_id => 123) from dual;

select *
from cars
where f_category(id) = 1;