networker
networker

Reputation: 31

counting rows from a cursor in pl/sql

I'm trying to count the number of rows that will be returned from an sql statement . This statement is in a cursor

My code is like this

DECLARE

v_counter int := 0 ;

select count(*) into v_counter from (
cursor get_sth is select * from table1 where condit..) ;


BEGIN

DBMS_OUTPUT.PUT_LINE (v_counter);


END ;
/

and it doesn't work

Is there any other solution that makes me counting the number of rows of a cursor result , I'm really noob

thanks helpers

Upvotes: 2

Views: 20573

Answers (3)

user9865188
user9865188

Reputation: 11

to optimize runnign time take de cursor code and get into the body code


  CURSOR MOVIMIENTO_ACTIV IS
   SELECT  X.CODMODFUE
         , X.COD_DEP
         , X.CODTIPREC
         , SUM(X.VLR_COSTO)
         , X.COD_ACTIVIDAD
         , X.PERIODO
         , Y.CLASE
         , Y.ESTADO
         , Y.redistri
         , X.recurso
         , ROWNUM NUMERO
   FROM COS_MOVIMIENTO X
      , COS_NIVELES Y
   WHERE X.EMPRESA         = PEMP AND 
         X.EMPRESA         = Y.EMPRESA AND
         X.COD_ACTIVIDAD     = Y.COD_NIVEL AND
         X.PERIODO         = PPER AND 
         Y.CLASE = 'G' AND
         Y.ESTADO='A' AND
         Y.redistRI = 'S'
   GROUP BY X.CODMODFUE
         , X.COD_DEP
         , X.CODTIPREC
         , X.COD_ACTIVIDAD
         , X.PERIODO
         , Y.CLASE
         , Y.ESTADO
         , Y.redistri
         , X.recurso      
         ;




begin

SELECT COUNT(*) FROM (SELECT  X.CODMODFUE
         , X.COD_DEP
         , X.CODTIPREC
         , SUM(X.VLR_COSTO)
         , X.COD_ACTIVIDAD
         , X.PERIODO
         , Y.CLASE
         , Y.ESTADO
         , Y.redistri
         , X.recurso
   FROM COS_MOVIMIENTO X
      , COS_NIVELES Y
   WHERE X.EMPRESA         = '01' AND 
         X.EMPRESA         = Y.EMPRESA AND
         X.COD_ACTIVIDAD     = Y.COD_NIVEL AND
         X.PERIODO         = '201803' AND 
         Y.CLASE = 'G' AND
         Y.ESTADO='A' AND
         Y.redistRI = 'S'
GROUP BY X.CODMODFUE
         , X.COD_DEP
         , X.CODTIPREC
         , X.COD_ACTIVIDAD
         , X.PERIODO
         , Y.CLASE
         , Y.ESTADO
         , Y.redistri
         , X.recurso      


  )W
    end;

Upvotes: -1

Alex Poole
Alex Poole

Reputation: 191275

If your aim is to reuse an existing cursor definition and not have to repeat the query it's based on, you could loop over its results to get a count:

set serveroutput on
declare
  v_counter pls_integer := 0;
  cursor get_sth is select * from all_tables where owner = user; -- your query
begin
  for sth in get_sth loop
    v_counter := v_counter + 1;
  end loop;
  dbms_output.put_line (v_counter);
end;
/

You can't count the rows in the result set without fetching them, which the cursor loop does. (@MarcinWroblewski shows another way to that, with explicit fetches). Either way the cursor is consumed by the process. If you want to do anything with the returned data after counting it you'd have to re-execute and re-fetch the cursor.

Upvotes: 5

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

What about

DECLARE
     v_counter INT := 0;
BEGIN
     SELECT COUNT(*) INTO v_counter FROM table1 WHERE condit..;
     dbms_output.put_line(v_counter);
END;
/

?

If you already have cursor and just want to know how many records it returns, you have to fetch them all

DECLARE
     CURSOR get_sth IS
          SELECT * FROM table1 WHERE condit..;
     sth       get_sth%ROWTYPE;
     v_counter NUMBER;
BEGIN
     OPEN get_sth;
     LOOP
          FETCH get_sth
               INTO sth;
          EXIT WHEN get_sth%NOTFOUND;
     END LOOP;
     v_counter := get_sth%ROWCOUNT;
     dbms_output.put_line(v_counter);
     CLOSE get_sth;
END;
/

Upvotes: 4

Related Questions