Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL get manager name form employee table
PL/SQL get manager name form employee table [message #427398] Thu, 22 October 2009 07:04 Go to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Hi everybody,

I'm busy with an assignments and I can't get it working.

create a script which asks for a name (with accept) and then puts this name and his/her manager in a testtable.
HINT: the employee number of the manager is the manager number of the employee which works under that manager.

use 2 exceptions:
e_no_emp (the inserted employee doesn't correspond with the names in the eployees table)
e_no_manager (the inserted employee hasn't got a manager)


This is what I got, but I don't know how to check for no_manager. I thougt off manager number is null and how can I insert the managers name (where mgrnr = empnr?)?
accept a_name varchar2 prompt 'Please give employee name: '

declare
  v_name varchar2(20) := '&a_name';
  e_no_emp exception;
  e_no_manager exception;
 
cursor c_employees(b_name varchar2) is 
select name
from employees;
  
begin
  for r_employees in c_employees('&a_name') loop 
  if c_employees%notfound then
  raise  e_no_emp;
 
  elsif v_name = ????????????
  raise e_no_manager;
 
  else insert into testtable
  values(null,v_name, r_employees.???????);
 end if;

end loop;

exception
when e_no_emp then
insert into testtable values(null, v_name,'this employee is not known');

exception
when e_no_manager then
insert into testtable values(null,v_name,'hasn't got a manager');

end;
/

Thank in advantage

[Mod Edit: Added code tags]

[Updated on: Thu, 22 October 2009 07:06] by Moderator

Report message to a moderator

Re: PL/SQL get manager name form employee table [message #427399 is a reply to message #427398] Thu, 22 October 2009 07:26 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well to check the manager number you'd have to select it wouldn't you. You'll be needing a self-join to get the manager details.

This bit:
  for r_employees in c_employees('&a_name') loop 
  if c_employees%notfound then
  raise  e_no_emp;

Will never work. When using cursor for loops the code execution only ever goes inside the loop if the cursor finds records. So that IF will never be true.
Re: PL/SQL get manager name form employee table [message #427400 is a reply to message #427398] Thu, 22 October 2009 07:29 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Use of SELF JOIN or CONNECT BY PRIOR should help to get the result
Re: PL/SQL get manager name form employee table [message #427402 is a reply to message #427398] Thu, 22 October 2009 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since he only ever wants to go up one level, CONNECT BY is a waste of time, self join is all that's needed.
Re: PL/SQL get manager name form employee table [message #427406 is a reply to message #427398] Thu, 22 October 2009 08:04 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Oke,

Thanks

I will try the SELF JOIN

Re: PL/SQL get manager name form employee table [message #428872 is a reply to message #427398] Fri, 30 October 2009 07:30 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Hi,

I'm trying by using self-join and open-fetch, but I can't wrap my head around it.

My exception e_no_emp works fine (%notfound)
but my exception e_no_manager doesn't work, because I don't know how to corperate the self-join with a open-fetch

thanks in advantage

(by the way...im a newbie in sql...but you figured that out already Laughing )
Re: PL/SQL get manager name form employee table [message #428875 is a reply to message #428872] Fri, 30 October 2009 07:38 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
What you have tried so far? Please provide the code you have written


Thanks
Ved
Re: PL/SQL get manager name form employee table [message #429200 is a reply to message #427398] Mon, 02 November 2009 09:09 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Hi,

This is what i got:

accept a_name varchar2 prompt 'Give employees name: '

declare
  cursor c_controle(b_name varchar2) is
   select twr1.name     name_emp
   ,         twr1.empnr   empnr
   ,         twr1.mgr
   ,         twr2.name     name_manager
   ,         twr2.persnr    empnr_manager
   from employees twr1
       ,    employees twr2
   where twr1.name = '&a_name'
   and   twr1.mgr = twr2.empnr;                            
   r_controle c_controle%rowtype; 
  
  twr1.name := varchar2;
  e_no_emp exception;
  e_no_manager exception;
  
begin
   v_name := '&a_name';
   open c_controle(v_name);
   fetch c_controle into r_controle;
   if c_controle%notfound then
   raise e_no_emp;
   
   elsif r_controle.mgr is null then
     raise e_no_manager;
 
   else insert into testtabel
   values(null,twr1.name,twr2.name);
 end if;

exception
when e_no_emp then
insert into testhulptabel values(null,twr1.name,'isn't an employee');

when e_no_manager then
insert into testhulptabel values(null,twr1.name,'hasn't got a manager');
close c_controle;
end;
/



I think I'm on the right track

[Updated on: Mon, 02 November 2009 09:10]

Report message to a moderator

Re: PL/SQL get manager name form employee table [message #429203 is a reply to message #427398] Mon, 02 November 2009 09:25 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Your self join is the wrong way round. You're currently querying the manager then linking to the employee.
2) You've parametrized your cursor but you're not using the parameter.
3) The cursor will only return anything if the employee and manager both exist. You'll need to make the self-join an outer join.
4) If you want to put quotes in strings you need to use 2 quotes.
so:
'isn't an employee'
becomes
'isn''t an employee'

5) Insert statements should always list the columns you're inserting into.
Re: PL/SQL get manager name form employee table [message #429204 is a reply to message #427398] Mon, 02 November 2009 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
6)
twr1.name := varchar2;

That isn't a valid declaration.

Not sure why it's there either as you can just use the record group throughout.

Re: PL/SQL get manager name form employee table [message #429310 is a reply to message #427398] Tue, 03 November 2009 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ignore my point 1, it is the right way round.
Re: PL/SQL get manager name form employee table [message #429329 is a reply to message #427398] Tue, 03 November 2009 04:17 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
OK

I modified a couple of things.

The main error I get now is:

v_name must be declared
man.name must be declared


Haven't I declared the v_name already?
and how can I declare the man.name?

accept a_name varchar2 prompt 'Give employees name: '

declare
  cursor c_controle(b_name varchar2) is
   select emp.name     name_emp
   ,         emp.empnr   empnr
   ,         emp.mgr
   ,         man.name     name_manager
   ,         man.persnr    empnr_manager
   from employees emp
       ,    employees man
   where emp.name = '&a_name'
   and   emp.mgr = man.empnr (+);                            
   r_controle c_controle%rowtype; 
  
  e_no_emp exception;
  e_no_manager exception;
  
begin
   v_name := '&a_name';
   open c_controle(v_name);
   fetch c_controle into r_controle;
   if c_controle%notfound then
   raise e_no_emp;
   
   elsif r_controle.mgr is null then
     raise e_no_manager;
 
   else insert into testtabel
   values(null,v_name,man.name);
 end if;

exception
when e_no_emp then
insert into testable(column1,column2,column3) 
values(null,v_name,'isn''t an employee'');

when e_no_manager then
insert into testable(column1,column2,column3) 
values(null,v_name,'hasn''t got a manager'');
close c_controle;
end;
/
Re: PL/SQL get manager name form employee table [message #429331 is a reply to message #429329] Tue, 03 November 2009 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Haven't I declared the v_name already?

Nope - there is no line in the DECLARE section of that block that defines what datatype and size the v_name variable is.

You want to replace man.name with r_controle.name - man.name is the way you referred to the variable when you selected it in the cursor - you need to provide the nameof the variable it was fetched into.
Re: PL/SQL get manager name form employee table [message #429333 is a reply to message #427398] Tue, 03 November 2009 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also you're still not using the cursor parameter and you've added too many quotes to the last 2 insert statements.
Re: PL/SQL get manager name form employee table [message #429335 is a reply to message #429331] Tue, 03 November 2009 04:31 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
You want to replace man.name with r_controle.name


I believe you mean r_controle.name_manager (since that was the alias it was given in the query)
Re: PL/SQL get manager name form employee table [message #429344 is a reply to message #427398] Tue, 03 November 2009 05:20 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Hi Guys,

Thanks so much....it works

I put in r_controle.name_manager and declared v_name.

it works fine now.

@cookiemonster: which cursor parameter do you mean? If there's something in my script obundant, it doesn't influence the result but it isn't very pretty.
Re: PL/SQL get manager name form employee table [message #429348 is a reply to message #427398] Tue, 03 November 2009 05:27 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's only 1 cursor parameter:
cursor c_controle(b_name varchar2) is

You're not using b_name anywhere, you should be referencing it in the where clause instead of:
'&a_name'
Re: PL/SQL get manager name form employee table [message #429353 is a reply to message #427398] Tue, 03 November 2009 06:19 Go to previous messageGo to next message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Oh, yes.

Your absolutely right. I thought you mend something else.

But my script did absolutely the same thing, so does it make any difference....accept that it is the way to build a script.
Re: PL/SQL get manager name form employee table [message #429363 is a reply to message #427398] Tue, 03 November 2009 06:45 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Make any difference to the result - no.
But as a matter of good coding practice you should never declare anything unless you're going to use it, and the sooner you get in that habit the better.
Re: PL/SQL get manager name form employee table [message #429367 is a reply to message #427398] Tue, 03 November 2009 07:11 Go to previous message
thedutchguy
Messages: 13
Registered: October 2009
Junior Member
Your right


thanks a lot
Previous Topic: why the absence of "partition by" gives diffreent results?
Next Topic: How to use Type to work in a procedure
Goto Forum:
  


Current Time: Tue Sep 27 17:56:00 CDT 2016

Total time taken to generate the page: 0.16524 seconds