Cursor
Last updated
Last updated
Ferramenta que permite percorrer sequencialmente os tuplos retornados por determinada consulta (SELECT).
Tipicamente temos duas abordagem:
Set based query (AR) versus cursor operation.
Soluções set-based são, em geral, bastante mais rápidas do que cursores.
Usualmente os utilizadores sentem-se mais confortáveis a pensar em termos de ciclos e ponteiros do que em consultas baseadas em álgebra relacional.
Défice de formação em base de dados? Álgebra Relacional?
Em SQL Server os cursores são server-side.
Analogia da “Pesca”: podemos ver os cursores como pesca à linha e as operações set-based como pesca com rede.
Iterating over a stored procedure: When a stored procedure must be executed several times, once for each row or value, and the stored procedure can’t be refactored into a set-based solution, or it’s a system stored procedure, then a cursor is the right way to iteratively call the stored procedure.
Iterating over DDL code: When DDL code must be dynamically executed multiple times, using a cursor is the appropriate solution.
Sometimes it’s necessary to iterate over multiple rows or columns, generating a dynamic SQL statement for each row or column.
Cumulative Totals/Running Sums: While there are set-based solutions, a cursor is the best-performing solution in these cases because it only has to add the next row’s value to the cumulative value.
Time-Sensitive Data: Some time-sensitive problems, depending on the database design, can benefit by using a cursor to determine the duration between events. Like the cumulative totals problem, time-sensitive data requires comparing the current row with the last row. Although there are possible set-based solutions, in some cases I’ve seen cursors perform better than set-based solutions.
