Home » SQL & PL/SQL » SQL & PL/SQL » for loop (11g)
for loop [message #609110] Sun, 02 March 2014 11:34 Go to next message
ggggg
Messages: 4
Registered: February 2014
Junior Member
Hello!

I've problem with correct displaying expected results.

The result follow code is:
employee_id,employee_id,[...] department_id.


But it's not what I mean because I expect the result as follow:
department_id. employee_id,employee_id,[...]

declare
cursor cursor1 is select department_id,department_name from departments order by department_id;
variable1 varchar2(256);
variable2 varchar2(256);

begin
for x in cursor1 loop
  for t in (  select
              employees.employee_id
              from employees
              where x.department_id=employees.DEPARTMENT_ID ) loop
              
              variable1 := t.employee_id;
              dbms_output.put(variable1||', ');
  end loop;
variable2 := x.DEPARTMENT_ID||'. ';
dbms_output.put_line(variable2);
end loop;
end;
/ 
 

Please help with solving this problem.

*BlackSwan added {code} tags. Please do so yourself in the future;as described in URL below.
http://www.orafaq.com/forum/t/174502/

[Updated on: Sun, 02 March 2014 11:59] by Moderator

Report message to a moderator

Re: for loop [message #609112 is a reply to message #609110] Sun, 02 March 2014 11:39 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How much would it cost if you actually read what you've written? Take piece of a paper and a pencil and run that code "manually" on that paper, writing down everything this code does.

You might notice that relocating VARIABLE2 output would solve that "problem", i.e.
for x in cursor1 loop
  dbms_output.put_line(x.department_id);
  for t in (...)
Re: for loop [message #609114 is a reply to message #609110] Sun, 02 March 2014 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

[Out of scope for learning purpose] Note that you can have the result in SQL using:
SQL> select d.deptno||'. '||listagg(e.empno,', ') within group (order by e.empno) res
  2  from dept d, emp e
  3  where e.deptno (+) = d.deptno
  4  group by d.deptno
  5  order by d.deptno
  6  /
RES
--------------------------------------------------------------------------------------
10. 7782, 7839, 7934
20. 7369, 7566, 7788, 7876, 7902
30. 7499, 7521, 7654, 7698, 7844, 7900
40.

4 rows selected.

Re: for loop [message #612421 is a reply to message #609114] Fri, 18 April 2014 04:04 Go to previous messageGo to next message
krishna@6268
Messages: 3
Registered: October 2013
Location: chennai
Junior Member
[code]
declare
cursor cur_emp_det is select * from emp;
begin
for i in cur_emp_det loop
dbms_output.put_line(i.department_id||' '||i.employee_id);
end loop;
end;
Re: for loop [message #612422 is a reply to message #612421] Fri, 18 April 2014 04:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Krishna,

That's not what OP wants. Your code will return all employees one by one, however, OP wants all of employees of each department to be grouped, just like the output shown by Michel above using LISTAGG. So, you need to modify your loop. See Littlefoot's post.

And use [/code] at the end to enclose the code.

[Updated on: Fri, 18 April 2014 04:26]

Report message to a moderator

Re: for loop [message #612423 is a reply to message #609110] Fri, 18 April 2014 04:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
ggggg wrote on Sun, 02 March 2014 23:04

declare
cursor cursor1 is select department_id,department_name from departments order by department_id;


No need to declare a cursor explicitly, just use a CURSOR FOR LOOP.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/cursor_for_loop_statement.htm#LNPLS1155
Re: for loop [message #612424 is a reply to message #612423] Fri, 18 April 2014 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
No need to declare a cursor explicitly, just use a CURSOR FOR LOOP.


Thing he has done in the inner loop, so he knows it.

Re: for loop [message #612425 is a reply to message #612424] Fri, 18 April 2014 05:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Fri, 18 April 2014 15:15

Thing he has done in the inner loop, so he knows it.


Aah... you are correct, thanks for correcting me. I saw Krishna's post and blindly quoted OP.
Re: for loop [message #612427 is a reply to message #609110] Fri, 18 April 2014 06:12 Go to previous messageGo to next message
oralover2006
Messages: 113
Registered: January 2010
Location: India
Senior Member
hi all,

Michel's solution is Excellent because it is only SQL. Just to explore another ways for the same, here is a PL/SQL code. ( I am also in learning stage and respect my seniors )

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    empnos  varchar2(1000);
  3  begin
  4  for rec in (select deptno from dept order by 1)
  5  loop
  6  select wm_concat(empno) res
  7    into empnos
  8    from emp
  9   where deptno (+) = rec.deptno;
 10  dbms_output.put_line (rec.deptno||', '||empnos);
 11  end loop;
 12* end;
SQL> /
10, 7782,7839,7934
20, 7369,7566,7788,7876,7902
30, 7499,7521,7654,7698,7844,7900
40,

PL/SQL procedure successfully completed.

SQL> 


another SQL solution from Michel's given solution.

SQL> select a.deptno, wm_concat(empno) res
  2    from dept a, emp b
  3   where b.deptno (+) = a.deptno
  4   group by a.deptno
  5   order by 1;

   DEPTNO RES
--------- -----------------------------------
       10 7782,7934,7839
       20 7566,7788,7369,7876,7902
       30 7521,7654,7698,7900,7499,7844
       40

4 rows selected.

SQL> 


regards.

[Updated on: Fri, 18 April 2014 06:19]

Report message to a moderator

Re: for loop [message #612429 is a reply to message #612427] Fri, 18 April 2014 06:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
wm_concat is an undocumented feature and should not be used(practically). Oracle does not support it for user applications/production systems.

[Updated on: Fri, 18 April 2014 07:03]

Report message to a moderator

Re: for loop [message #612430 is a reply to message #612427] Fri, 18 April 2014 07:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1962
Registered: January 2010
Senior Member
Please don't suggest using undocumented wm_concat.

SY.
Re: for loop [message #612431 is a reply to message #612430] Fri, 18 April 2014 07:09 Go to previous messageGo to next message
oralover2006
Messages: 113
Registered: January 2010
Location: India
Senior Member
Solomon Yakobson wrote on Fri, 18 April 2014 17:30
Please don't suggest using undocumented wm_concat.

SY.


Please accept my apologize.
as I mentioned Michel's solution is Excellent and I just want to explore another ways.

regards.
Re: for loop [message #612433 is a reply to message #612431] Fri, 18 April 2014 07:32 Go to previous message
Solomon Yakobson
Messages: 1962
Registered: January 2010
Senior Member
There are plenty "another ways": XMLAGG, hierarchical query, recursive subquery factoring, MODEL...
You can find examples of pretty-much all of them on this forum or at least on the web.

SY.
Previous Topic: sp_log_entry
Next Topic: Transpose query help
Goto Forum:
  


Current Time: Mon Jul 28 22:57:38 CDT 2014

Total time taken to generate the page: 0.14207 seconds