Oracle Applications

Oracle for Beginners

Function to fetch Employee information

Posted by shameemakhtar on June 29, 2010

CREATE OR REPLACE function ztd_get_employee_info_fn 
    p_emp_id in varchar2,
    p_choice in varchar2
return varchar2
    p_emp_num varchar2(250);
    p_emp_name varchar2(250);
    p_emp_email varchar2(250);

    select employee_number, 
    into p_emp_num,
    from per_people_f
    where person_id = p_emp_id;
        when upper(p_choice) not in ('NUM', 'NAME', 'EMAIL') then
            return 'ORA-user error: Wrong Parameter';
        when upper(p_choice) = 'NUM' then
            return p_emp_num;
        when upper(p_choice) = 'NAME' then
            return p_emp_name;
        when upper(p_choice) = 'EMAIL' then
            return p_emp_email;            
    end case;
when no_data_found then
    return null;
when others then
    return nvl(sqlerrm, null);

The above function takes in two parameters, namely employee_id and a parameter for choice. Based on the choice, the function will return either the employee number, his full name or his email address.

In case no data is found, it will return null, and in case of error, it will return the error message.

Note the following:
a. The additional parameter ‘p_choice’ allows me the flexibility of using the same function to return different columns from the same select statement as required. You could have added more choices to enable you to return other fields. This gives me two advantage: I do not need to build a separate function for each and it simplifies my joins in eventual select statements as I have one table less to join.
b. Instead of making p_choice a number, I have made it a character. You can argue that I could have put remarks besides each number, but as preferred varchar2 as this makes its usage more intuitive.
c. I used the standard function ‘upper’ to further allows for any combination of cases when passing parameter p_choice.
d. Before checking for actual cases, I check whether there is an error in entering parameters
e. In case of no data found, I am returning null instead of the error code. I am returning the error messages in case of other errors. You could have opted otherwise.

I am using this structure and logic to build a series of simple functions that will allow me simplifies my eventual queries in the future.

Table Used:
per_people_f (You can use per_people_x as well).

You can check your function as follows:

select ztd_get_employee_info_fn(4018, 'NUM')
from dual;

Shameem Bauccha
28 June 2010

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s