13 Replies Latest reply: Jan 29, 2009 5:33 PM by 682167 RSS

    Oracle script using cursors

    682167
      Hi all. I am trying to make a script that selects NAME from a table, and orders the result by CLASS, using cursors. The 1st cursor, is selecting NAME from CLASS where CLASS='1'; while the 2nd cursor is SELECTING NAME FROM CLASS where CLASS ='2';, and after this, using LOOP, the script does the job, but i have to write the same code for the 2 cursors like the following:
      If i have a table named LIMBAFK with NAME and CLASS as columns, and the folowing data:

      NAME CLASS
      JULIAN 1
      Michale 1
      George 2


      DECLARE
      CURSOR C1 IS SELECT NAME FROM LIMBAFK WHERE CLASS='1';
      CURSOR C2 IS SELECT NAME FROM LIMBAFK WHERE CLASS='2';
      MYD C1%ROWTYPE;
      MY2D C2%ROWTYPE;
      BEGIN
      OPEN C1;
      DBMS_OUTPUT.PUT_LINE('1');
      LOOP
      FETCH C1 INTO MYD;
      EXIT WHEN C1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(MYD.NAME);

      END LOOP;
      CLOSE C1;
      OPEN C2;
      DBMS_OUTPUT.PUT_LINE('2');
      LOOP
      FETCH C2 INTO MY2D;
      EXIT WHEN C2%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(MY2D.NAME);

      END LOOP;
      CLOSE C2;
      END;


      Then, using this script, the result would be:
      1
      Julian
      Michale
      2
      George

      This is good, and works, but i dont want to write the same code for both the cursors, so I had in mind that the first cursor selects DISTINCT CLASS from table, and the 2nd cursor selects NAME, CLASS from the same table, and then by using a comparison between the cursors, do the same thing. I wrote some of the code, but it doesn't work at all, so I'm hoping someone can help me.
      Here's what i have done so far, but it;s not finished at all:

      DECLARE

      CURSOR C1 IS SELECT DISTINCT CLASS FROM LIMBAFK;
      CURSOR C2 IS SELECT NAME,CLASS FROM LIMBAFK;

      MYD C1%ROWTYPE;
      MY2D C2%ROWTYPE;

      BEGIN
      OPEN C1;
      LOOP
      EXIT WHEN C1%NOTFOUND;
      FETCH C1 INTO MYD;
      DBMS_OUTPUT.PUT_LINE(MYD.CLASS);
           OPEN C2;
           LOOP
           EXIT WHEN C2%NOTFOUND;
           FETCH C2 INTO MY2D;
           IF C2.CLASS=C1.CLASS
                DBMS_OUTPUT.PUT_LINE(MYD.CLASS);
           


      END LOOP;
      CLOSE C1;
      END;
        • 1. Re: Oracle script using cursors
          Sven W.
          I don't see the need for two cursors. Why don't you simply select what you want?

          Like
           SELECT NAME,CLASS 
           FROM LIMBAFK
           ORDER BY CLASS;
          No need for PL/SQL and dbms_output too.

          Edited by: Sven W. on Jan 28, 2009 8:04 PM
          • 2. Re: Oracle script using cursors
            682167
            Well, I was asked to use cursors, and dbms_output in doing my task...
            • 3. Re: Oracle script using cursors
              682167
              For those that didn't understood, I HAVE to use the cursors, and dbms_output... so please a little help
              • 4. Re: Oracle script using cursors
                shishu
                Hi,
                Can you provide sample output which you desire against your data .


                Thanks
                Shishu Paul
                • 5. Re: Oracle script using cursors
                  682167
                  The output i want to be same like my 1st working script does:

                  1
                  Julian
                  Michale
                  2
                  George
                  • 6. Re: Oracle script using cursors
                    636403
                    something like this:
                    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> 
                    • 7. Re: Oracle script using cursors
                      682167
                      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
                      • 8. Re: Oracle script using cursors
                        636403
                        first of all you need to understand what is cursor is, the second there is implicit cursor in example
                        • 9. Re: Oracle script using cursors
                          636403
                          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;
                          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions070.htm#SQLRF00652
                          • 10. Re: Oracle script using cursors
                            682167
                            Thanks for explaining the LAG, but I have never heard of such function, i have to study much more.
                            • 11. Re: Oracle script using cursors
                              682167
                              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?
                              • 12. Re: Oracle script using cursors
                                John Spencer
                                Despite the fact that this just screams homework, I will comment on your 2 cursor approach.

                                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):
                                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
                                Second, you are short an end loop, you have two loops but only one end loop.

                                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.

                                HTH
                                John
                                • 13. Re: Oracle script using cursors
                                  682167
                                  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.