Writing a stored procedure to do a look-up

Please help me out from this situation

I have to create a function to retrieve the name of the manager for a given employee. (I was trying to join the emp table to itself to do this but not sure.) Also I need to write a SQL select statement that uses the function to produce a list of employee numbers and nam开发者_Python百科es together with the name of their manager

Thanks

---

As you indicated, the solution is to join the emp table with itself.

CREATE FUNCTION fn_get_manager(p_empno IN NUMBER) 
   RETURN VARCHAR2
   IS manager VARCHAR2;
BEGIN 
   SELECT mgr.name 
     INTO manager 
     FROM emp e, emp mgr 
    WHERE e.mgr = mgr.empno
      AND e.empno = p_empno; 
   RETURN(manager); 
 END;

This query you can wrap in a user defined function where the @employee variable becomes a parameter.

To return the list of employees you can call the function in the select statement

SELECT e.empno, e.name, fn_get_manager(e.empno)
  FROM emp e

Although this construction works it is a terrible way of doing it for such a simple request. The function will be executed for every record in the emp table and the result will be very slow compared to the simple join statement.

 SELECT e.empno, e.name, mgr.name
   FROM emp e,emp mgr 
  WHERE e.mgr = mgr.empno (+)

I used a left join here to also handle the edge case of the "big boss" which doesn't have a manager himself.

edit: updated queries a bit for oracle syntax

---

Filip's function is not the best solution for a function which is called from a query of the EMP table. That table has the MGR attribute and that is what should be passed to the function.

CREATE FUNCTION fn_get_manager(p_mgr IN emp.mgr%type) 
   RETURN VARCHAR2
IS 
   manager emp.ename%type;
BEGIN 
   SELECT ename 
     INTO manager 
     FROM emp e 
    WHERE e.empno = p_mgr; 
   RETURN(manager); 
END;

These things perform badly enough without introducing another unnecessary read on the EMP table.

But as Filip days, this properly should be done in SQL, with a JOIN of the EMP table to itself.