2 Replies Latest reply: Dec 31, 2012 3:40 AM by salute-Salem RSS

    cursor for loops

    salute-Salem
      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
          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
            salute-Salem
            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 ?