This content has been marked as final. Show 13 replies
I don't see the need for two cursors. Why don't you simply select what you want?
No need for PL/SQL and dbms_output too.
SELECT NAME,CLASS FROM LIMBAFK ORDER BY CLASS;
Edited by: Sven W. on Jan 28, 2009 8:04 PM
Well, I was asked to use cursors, and dbms_output in doing my task...
For those that didn't understood, I HAVE to use the cursors, and dbms_output... so please a little help
Can you provide sample output which you desire against your data .
The output i want to be same like my 1st working script does:
something like this:1 person found this helpful
SQL> set serveroutput on SQL> SQL> drop table LIMBAFK 2 / Table dropped SQL> create table LIMBAFK 2 as 3 (select 'JULIAN' name, 1 class from dual 4 union all 5 select 'Michale', 1 from dual 6 union all 7 select 'George', 2 from dual) 8 / Table created SQL> begin 2 3 for v_sel in (select CLASS,NAME, lag(class, 1) over (order by class) prev_class from LIMBAFK) 4 loop 5 if (v_sel.prev_class != v_sel.class or v_sel.prev_class is null) then 6 dbms_output.put_line(v_sel.class); 7 end if; 8 9 dbms_output.put_line(v_sel.name) ; 10 end loop; 11 end; 12 / 1 JULIAN Michale 2 George PL/SQL procedure successfully completed SQL>
First of all, thanks for replying, but im not that advanced regarding pl/sql, so i didn't quite understood all tha coding from there... 2nd, i must use cursors, its a task that i have to complete
LE: just tested it, and it works exactly the way i wanted, the only problem is that i did not understood the code, for example what is "v_sel ", or lag(class, 1) over (order by class) prev_class and so on, if u have time please explain the syntax;
For anyone else, please help me with solving the problem with cursors
Edited by: user10763461 on Jan 29, 2009 4:17 AM
first of all you need to understand what is cursor is, the second there is implicit cursor in example
declare cursor c1 is select CLASS,NAME, lag(class, 1) over (order by class) prev_class from LIMBAFK; l_var C1%ROWTYPE; begin open c1; loop fetch c1 into l_var; exit when c1%notfound; if (l_var.prev_class != l_var.class or l_var.prev_class is null) then dbms_output.put_line(l_var.class); end if; dbms_output.put_line(l_var.name) ; end loop; end;
Thanks for explaining the LAG, but I have never heard of such function, i have to study much more.
I have studied LAG, and its interesting, but what of my 1st try with the 2 cursors, where did i go wrong there? I think at that comparaison with the cursors. Any ideeas?
Despite the fact that this just screams homework, I will comment on your 2 cursor approach.1 person found this helpful
First, in both loops you have you exit condition in the wrong place. You will duplicate your last line of output because when it hits the exit the first time notfound will not be true (it will be null), so it will do the fetch then dbms_output, come back to the top of the loop and find notfound to be true (since the last fetch did return a row) then fetch again. After the last row is fetched, not found will be true, but you are checking before the fetch, so it will output the last row twice. For example Note also the parameterized cursor which you will also need):
Second, you are short an end loop, you have two loops but only one end loop.
SQL> DECLARE 2 CURSOR c1 (p_id NUMBER) IS 3 SELECT 'X' FROM dual 4 WHERE 1 = p_id; 5 l_v varchar2(10); 6 BEGIN 7 OPEN c1 (1); 8 LOOP 9 EXIT WHEN c1%notfound; 10 FETCH c1 INTO l_v; 11 -- EXIT WHEN c1%notfound; should go here not after the loop 12 DBMS_OUTPUT.Put_Line ('got '||l_v); 13 END LOOP; 14 END; 15 / got X got X
Third, in order to make your construct work with explicit cursors like that, your c2 needs to ba a parameterized cursor so you can pass the class obtained form the first cursor to the second so that the second only retrieves rows for the appropriate class.
Finally, if you want the classes to come out in a specific order, then you need to provide an explicit order by in the cursor definition (the same would apply if you want the names to come out in a specific order.
Thank you for replying, but homework was done, it's what i have already done and it works, but i dont want to stop there, i wanted to have another way to solve the problem. And thanks a lot for explaining how things work.