Results 1 to 12 of 12
  1. #1
    Join Date
    May 2011
    Posts
    7

    Unanswered: Counting the rows returneds of a cursor

    Hi im coding a procidement that first uses explicit cursors and im having some problems.

    First i need a way to know how many rows return a select in a cursor. I know that exist %Rowcount but that isnt what i need, i want a function that extracts from the cursor without having to fetch or loop anything.

    Code:
      CURSOR c_multes_cotxe is
      SELECT id_multa INTO cuant_multes FROM MULTA WHERE id_cotxe = id_cotxe ;
    Here is the cursor code. Im trying too in put into the variable directly in the cursor using the INTO. This is correct? i get the number of total rows?

    Second im having a problem with the cursor. It says that its invalid. Here is my actual code that open and close it.r
    Code:
      open c_multes_cotxe;
               close c_multes_cotxe;
    The complete code:

    Code:
    create or replace
    PROCEDURE pagar_multa(
      id_cotxe NUMBER,
      dia_multa VARCHAR2,
      dia_pagament VARCHAR2
    )
    
    IS -- Obligat, o in.
    
    
      ---Variables declarats extra
      cantitat_multes_erronea EXCEPTION; --Excepcio propia tractada apart
      pagar INTEGER;
      -- Declarar abans
      cuant_multes INTEGER := 0;
      CURSOR c_multes_cotxe is
      SELECT id_multa INTO cuant_multes FROM MULTA WHERE id_cotxe = id_cotxe ;
    
    BEGIN
    
      open c_multes_cotxe;
       close c_multes_cotxe;
      DBMS_OUTPUT.PUT_LINE ('VaAAAAAAA' || cuant_multes);
       
     if cuant_multes = 1 then
     
        DBMS_OUTPUT.PUT_LINE ('Va');
      else 
      
        -- Si falla la lectura, excepio propia
        raise cantitat_multes_erronea;
        
      end if;
      
    --Controla les excepcions  
    EXCEPTION
      
      when  cantitat_multes_erronea then
        DBMS_OUTPUT.PUT_LINE ('Error. El cotxe no te multes o en te mases.');
      
      --Faltaria afegir el others, per defecte  
    END pagar_multa;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Uh, oh ...

    I strongly believe that you should read some PL/SQL documentation.

    You opened and closed a cursor, but never fetched anything.

    Cursor definition is awkward and won't return what you meant. A procedure parameter name is equal to a table column name so this "... where id_cotxe = id_cotxe" is plain wrong. You should follow certain coding conventions (Google!) and, for example, name procedure parameters as "par_id_cotxe" which would, in turn, make your code
    Code:
    where id_cotxe = par_id_cotxe
    and confusion would be avoided.

    Also, pay attention to cursor FOR loops, as they make use of cursors and loops easier (as you just "use" a cursor, while the way you put it, you have to declare a cursor, cursor variable(s), open a cursor, fetch from it, exit the loop, close a cursor - too many things to take care of).

  3. #3
    Join Date
    May 2011
    Posts
    7
    Thanks for all the comments, but im having anyway a problem with extractin the number of rows returned by a cursor. How i can do that?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2011
    Posts
    7
    I readed that. My problem is the same because it dont say that, no? If i only want to know the number of rows returned and anything else...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >CURSOR c_multes_cotxe is
    > SELECT id_multa INTO cuant_multes FROM MULTA WHERE id_cotxe = id_cotxe ;

    In declaration above "INTO" clause should not exist. as below

    CURSOR c_multes_cotxe is
    SELECT id_multa FROM MULTA WHERE id_cotxe = id_cotxe ;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by wepnop View Post
    i want a function that extracts from the cursor without having to fetch or loop anything.
    This is not possible. The number of rows that a cursor would return is not known until all of them are fetched.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Which, finally, means the following: you have a cursor:
    Code:
    declare
      cursor c_emp is
        select empno, ename 
        from emp
        where deptno = 20;
    You would like to know how many records it would return, but without actually opening it, fetching from it or doing anything with it. Right?

    If so, the only way is to actually run that select statement:
    Code:
    declare
      cursor c_emp is 
        select empno, ename
        from emp
        where deptno = 20;
      l_cnt number;
    begin
      select count(*)
        into l_cnt
        from (select empno, ename  -- this is the cursor query
              from emp
              where deptno = 20
             );
      dbms_output.put_line('Cursor would fetch ' || to_char(l_cnt) || ' records');
      
      -- the rest of your code goes here
    end;
    Is this what you are looking for?

  9. #9
    Join Date
    May 2011
    Posts
    7
    Code:
      
      CURSOR c_multes_cotxe is
      SELECT id_multa FROM MULTA WHERE id_cotxe = id_cotxe_par ;
      vreg c_multes_cotxe%ROWTYPE;
    
      open c_multes_cotxe;
    -
      WHILE c_multes_cotxe%FOUND 
        LOOP
            /* Procesamiento de los registros recuperados */
             fetch c_multes_cotxe into vreg;
        END LOOP;
     DBMS_OUTPUT.PUT_LINE ('QWEEEE' || c_multes_cotxe%Rowcount);
    Now im trying this but it isnt working. My idea was to loop the entire cursor (dont care about if you really need or not, just for knowing the rownumber)

    But it just dont work. It says 1 always.

    I will try that youu say.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> @cursor.sql
    SQL> select count(*) from emp;
    
      COUNT(*)
    ----------
    	19
    
    SQL> DECLARE
      2  CURSOR MY_CUR IS SELECT * FROM EMP;
      3  MY_REC EMP%ROWTYPE;
      4  CNT NUMBER :=0;
      5  BEGIN
      6  FOR MY_REC IN MY_CUR LOOP
      7  CNT := CNT + 1;
      8  END LOOP;
      9  DBMS_OUTPUT.PUT_LINE(' Row Count = '|| CNT);
     10  END;
     11  /
    Row Count = 19
    
    PL/SQL procedure successfully completed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by wepnop View Post
    My idea was to loop the entire cursor (dont care about if you really need or not, just for knowing the rownumber)
    That might be a VERY lengthy operation. If I may, I'd say that this is a rather stupid way to do that.

    Why do you need that information, anyway?

  12. #12
    Join Date
    May 2011
    Posts
    7
    Im doing a exercice that just need that. I need to check for the count of rows in sql...

    Anyway, the last for loop works great.

    Thanks to all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •