Home » SQL & PL/SQL » SQL & PL/SQL » cursor
cursor [message #215179] Fri, 19 January 2007 09:52 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all

i want to display deptno, dname, loc and below this display empno, ename, deptno of that dept and at the end of every
deptno display total no of emps of the corresponding deptno.
i.e-
10 H.R DELHI
---- -------- --------
7823 SURAJ 10
7867 ISHIKA 10
7689 SUMAN 10

TOTAL EMP=3

like this i want to display for all deptno.

i have done one query which gives result except total no of emps.

declare
 cursor dept_cur is
  select * from dept;
 cursor emp_cur(v_deptno dept.deptno%type) is
  select * from emp
   where deptno=v_deptno;
 dept_rec dept_cur%rowtype;
 emp_rec emp_cur%rowtype;
begin
 for dept_rec in dept_cur loop
  dbms_output.put_line(rpad(dept_rec.deptno,7)||rpad(dept_rec.dname, 13)||dept_rec.loc);
  for emp_rec in emp_cur(dept_rec.deptno) loop
  dbms_output.put_line(rpad(emp_rec.empno,10)||rpad(emp_rec.ename, 13)||emp_rec.deptno);
 end loop;
 end loop;
end;
/ 


with regards
Re: cursor [message #215202 is a reply to message #215179] Fri, 19 January 2007 12:21 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But of course it doesn't display number of employees per department! How could it? You *forgot* to code it!
Re: cursor [message #215227 is a reply to message #215179] Fri, 19 January 2007 23:09 Go to previous message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

You forget to write code for counting the employees and displaying it as said by littlefoot
Add a counter and display it see below modified code.
declare
 cursor dept_cur is
  select * from dept;
 cursor emp_cur(v_deptno dept.deptno%type) is
  select * from emp
   where deptno=v_deptno;
 dept_rec dept_cur%rowtype;
 emp_rec emp_cur%rowtype;
 n number;
begin
 for dept_rec in dept_cur loop  dbms_output.put_line(rpad(dept_rec.deptno,7)||rpad(dept_rec.dname, 13)||dept_rec.loc);
  n := 0;
  for emp_rec in emp_cur(dept_rec.deptno) loop
     dbms_output.put_line(rpad(emp_rec.empno,10)||rpad(emp_rec.ename, 13)||emp_rec.deptno);
     n := n + 1;
 end loop;
     dbms_output.put_line('Total Emp : '||n);
 end loop;
end;
Previous Topic: Assigning Values within a Function
Next Topic: SQL Query
Goto Forum:
  


Current Time: Sat Dec 03 06:07:10 CST 2016

Total time taken to generate the page: 0.09954 seconds