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 ?
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 ?
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.
I believe it is not possible directly, face the "philosophy" of Cursor.
– Motta