This discussion is archived
2 Replies Latest reply: Dec 31, 2012 1:40 AM by newbi_egy RSS

cursor for loops

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2
hi all ,
i am trying to create a tree with "cursor for loops" technique , and i've seen an example for this ,
there is something i can not understand in the code , i will write it , and please try to demonstrate it to me :

the code should create a tree with departments names as roots of the tree , and under each department , there is it's employees :



declare
cursor dept_cur is
select deptno , dname
from dept;

cursor emp_cur (N number) is
Select empno , ename
from emp
where deptno=N;

Htree item;

parent_node varchar2(200);
child_node varchar2(200);
begin
Htree:=Find_item('tree');
For dept_R in Dept_cur loop
parent_node:=Ftree.Add_Tree_Node(htree,
Ftree.ROOT_NODE,
Ftree.PARENT_OFFSET,
Ftree.LAST_CHILD,
Ftree.EXPANDED_NODE,
dept_r.deptno||' '||dept_r.dname,
'insrec',
dept_r.deptno);

Htree:=Find_item('tree');
For emp_R in emp_cur(dept_r.deptno) loop -- "this the line i do not understand "
child_node:=Ftree.Add_Tree_Node(htree,
parent_node,
Ftree.PARENT_OFFSET,
Ftree.LAST_CHILD,
Ftree.EXPANDED_NODE,
emp_r.empno||' '||emp_r.ename,
'insrec',
emp_r.empno);
end loop;
end loop;
end;


the "dept_r.deptno" values are all deptno column's values , the both return the same values , then why can not i do
this :
rathar than writing this cursor like so :
cursor emp_cur (N number) is
Select empno , ename
from emp
where deptno=N;

, why not to write it like so :
cursor emp_cur is
select empno , ename from emp

what is the difference between the both ? the cursor with parameter return the query where all deptno
values are in , and the cursor i've written does the same , but when i use the cursor as i've written ,
i get every department as a root but rathar than getting the dnames' employees under each department ,
i get all employees "14" under each dname , and sure that is not good , because there is dname has "4" employees ,
and another has "3" and so on , but i am getting the "14" employees under each dname ?

thanks in advance
  • 1. Re: cursor for loops
    Andreas Weiden Guru
    Currently Being Moderated
    dept_r.deptno does not contain all departments. in a for-loop, the record-variable contains only one record, and that record is different for every roundtrip of the loop. And for sure the two cursors you show are not the same, as one selects all emps, and the other one has a restriction on the dept_no.
  • 2. Re: cursor for loops
    newbi_egy Explorer
    Currently Being Moderated
    the cursor i've written will retrieve all deptno values
    10
    20
    30
    40

    , and the cursor for loop with the parameter will retrieve in the first loop of the cursor
    10
    then it will iterate to retrieve
    20
    then
    30
    then 40

    are not these values the same as the above mentioned ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points