Functions in Oracle. Need to search and find the passed paramater using Oracle Function

Question

I have a requirement like , I need to search and find a value of a table column using Oracle Parameterized function.

Suppose, if my table has a column called 'Name' which has 2 records like 'mike', 'steve'.

I need to create a function where I will pass name as input parameter and this parameter should be compared or iterated with the existing name values of the table column 'Name'.

If the passed parameter matches with the column value , then then function should return the passed parameter

Otherwise wise the function should return a statement saying that the "passed name value doesn't exist".

For example, if in a table there is Name column which has 2 records like 'mike', 'steve'.

Now if I passed 'mike' as a input parameter to the function then the function should return 'mike' In other case, if I passed 'john' as a input parameter to the function, then the function should return saying that "passed name value doesn't exist".

Please do advise me.

Answer 1

create or replace function does_name_exist(p_name in varchar2)
    return varchar2 is
    v_exists varchar2(1);
begin
    -- validate parameter
    select 'Y' into v_exists from my_table where name = p_name;

    -- if it exists
    return p_name;

    -- if it doesn't exist
    exception when NO_DATA_FOUND then
        return 'passed name value doesn''t exist';
end;
/