The cursor and usage in embedded SQL

The SQL language, by default, uses set-at-a-time processing. While this is used in most SQL queries, sometimes there is a need to loop through the set and execute a stored procedure or many different SQL statements for each row fetched. SELECT statements that return more than one row can present a problem when embedding them in a program, as Harrington (2010) identified. The host language variables can only handle one value at a time, and the SQL command processor cannot work with the host language arrays. So, the cursor allows the host language to extract a row at a time for processing.

To understand the concept of a cursor, Harrington (2010) describes the commands needed to execute a basic cursor. They are DECLARE, OPEN, FETCH, and CLOSE. The DECLARE of the cursor defines the SQL SELECT statement to be executed but does not perform the retrieval from the database. The OPEN of the cursor then executes the SQL statement and stores the result set in memory so the cursor can process the result set. The cursor is now positioned above the first row, awaiting the next cursor command. Next, the FETCH command is executed to retrieve a row one at a time, looping through the set retrieved in the OPEN statement and processed in whatever way is needed. Once all rows in the set are processed, the CLOSE command is executed, and the cursor of the transaction is complete.

Another way to describe the cursor is to loop through a set of rows one at a time and change, calculate values of the attribute(s), or execute SQL as the looping routine processes each row in the set selected. Cursor processing is slower than set-at-a-time processing, but row-at-a-time processing is sometimes needed. Many row-at-a-time SQL processes can be converted to set-at-time processing.

As a Database Administrator, sometimes the cursor is used while performing database maintenance.

If a new column is added to the database and needs to be populated, a cursor could be used to loop through each row and update the new column value. A database I worked with had one schema per company. That means we had the same tables, indexes, and functions defined within each company schema. So, the cursor worked well for writing database patches. If we needed to add an index to a table within each company schema, we would write a query against the database’s metadata pulling each table name from each company schema and using a cursor to create the new index on each table of the companies schema.

References

Elmasri, R., Navathe, S. B. (2015-06-01). Fundamentals of Database Systems, 7th Edition

[VitalSource Bookshelf version]. Retrieved from (https://bookshelf.vitalsource.com/books/9781323139899)

Harrington, J. L. (2010). SQL clearly explained (3rd ed.). NL: Morgan Kaufmann Publishers Inc.