-3

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.

KrishnaSaran draksharapu
  • 71
  • 8

1 Answers1

0
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;
/
kfinity
  • 8,581
  • 1
  • 13
  • 20
  • Hi All, addiding to the above requirement,. I have one more requirement. Requirement is I need to check whether a name value exists in a table or not. After that if the name exists, then I need to append .A to that name value. If for that name , if there is already .A is appended then I need to append .B .. Example : Suppose I am passing John as my input parameter to the function, then function should check if John exists in name column or not. If John exists then the output should be like John.A , if John.A is my input parameter then output should be John.B , so on – KrishnaSaran draksharapu Nov 01 '18 at 12:32