PLSQL know the amount of records within a "for cursor"

Asked

Viewed 3,093 times

2

Inside the example below :

Begin
  For r in (select * from tabela)
  Loop
    ..... ;
  End loop;
End;

Is there any direct way to know inside the loop how many records the cursor has ?

oracle pl-sql

  • I believe it is not possible directly, face the "philosophy" of Cursor.

2 answers

3

Try it like this:

BEGIN
  FOR r IN (SELECT COUNT(*) OVER() total_de_registros
                  ,t.*
              FROM tabela t)
  LOOP
    .. .. .;
  END LOOP;
END;

In the above example, the clause COUNT(*) OVER() counts all records that return in the query without the need to add a group by.

  • Yes! In this case, using query Partition clause: http://oracle-base.com/articles/misc/analytic-functions.php#query_partition_clause, you can group values in a column without affecting the query itself. In the above case I used a Count(*) with an OVER(), inside the OVER() I could have a grouping clause using Partition by, but then I just counted all the records that return in the query.

  • Got it! Next time I’ll explain better!

  • OK, you can erase

1

I don’t know if that’s the idea.

But it can be done that way.

declare
vtabela tabela%rowtype;
Begin
  select t.* into vtabela from tabela;
  For r in 1..vtabela.count loop
  Loop
    ..... ;
  End loop;
End;

  • declare vuser_tables user_tables%rowtype; VN_ROWS NUMBER; Begin select X.* into vUSER_TABLES from USER_TABLES X; For r in 1.. vUSER_TABLES0.Count loop VN_ROWS := VN_ROWS + R.NUM_ROWS; end loop; End; gave error in this adaptation...

  • Doing it this way will make a mistake anyway. SELECT INTO gives error if the number of results is different from 1. You would have to use a BULK COLLECT INTO, with a totally different declaration for vtabela (e.g.: TABLE OF table%rowtype), but then you would be bringing all the results into a collection, what I don’t know if I would be desired.

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.