𝗗𝗼 𝗜 𝗸𝗻𝗼𝘄 𝗺𝘆 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲?
Part 3 : Cursors across Database Systems
𝘞𝘩𝘢𝘵 𝘦𝘷𝘦𝘯 𝘪𝘴 𝘢 𝘤𝘶𝘳𝘴𝘰𝘳?
A cursor is a short-term memory space formed in the computer's memory when a SQL statement runs. It contains details about a select statement and the data rows it traverses. This temporary space is employed to keep and adjust the retrieved data.
𝘞𝘩𝘺 𝘪𝘴 𝘢 𝘤𝘶𝘳𝘴𝘰𝘳 !?
There's implicit and explicit cursors, and they are just as the name suggests, for internal functioning of your DBS, and for you to explicitly declare and use.
It makes most sense, that since an implicit cursor was implemented for running SQL queries, an option for explicit cursors was provided for developers to work with. As in making use of a particular function instead of using the whole module for some work. 𝘉𝘶𝘵 𝘴𝘩𝘰𝘶𝘭𝘥 𝘺𝘰𝘶 𝘶𝘴𝘦 𝘵𝘩𝘦𝘮?
𝑷𝒓𝒐𝒔 :)
Cursors allow you to get a record from multiple tables and perform complex queries, also allows you to work with data in real-time. Though the specifics of these things depend from DBS to DBS.
Using a cursor will not require loading the whole database to memory.
𝑪𝒐𝒏𝒔 :(
The abilities of cursors vary hugely across different databases, mysql does not allow updates, does not allow bi-directional scroll, and cannot be used outside a procedure, but that's not the case in PL/SQL. Also, somewhat of a skill issue, loading rows into memory and locking them, can create potential blocks, causing performance downgrade.
Also, the overhead of copying each row to memory will be to too much (the system calls to access data repeatedly) rather than doing the task with the data laoded to memory (as in when using a DB-connector, fetching all related rows, and writing your logic in some programming language).
𝑴𝒚 𝒕𝒂𝒌𝒆 🤔
You probably do not need to use a cursor if your database was designed keeping the requirements in mind. Foreign key constraints and as such will be enough to allow you to write complex SQL queries (or ORM) to get the work done.
The real-time updates point is surely something to keep in mind.
But then again, I'd love to see a case where an explicit cursor was a better/only option.
