Managing Rows in a Table in SQL - dummies

Managing Rows in a Table in SQL

By Allen G. Taylor

SQL deals with data a set at a time. Cursors come into play only if you want to violate that principle and grapple with the data a row at a time. Why would you want to do that? It’s very cool that all you have to do is tell SQL what you want to accomplish and it will go off and do it without explicit step-by-step directions from you.

However, there are times when you do want to give those step-by-step directions. Perhaps you want to access a row in a table and, based on its contents, do one thing rather than another. Making decisions on a row-by-row basis is something that ordinary SQL statements cannot perform. To deal with situations such as this, SQL has cursors that you can use to point to individual rows in a table.

The Persistent Stored Modules (SQL/PSM) update that was added in SQL:1999 gives SQL a number of added capabilities:

  • The addition of flow of control structures such as IF…THEN…ELSE, CASE…END CASE, WHILE…DO…END WHILE, and FOR…DO…END FOR. These additions enable you to perform procedural operations without forcing you to revert to a host language.

  • The introduction of compound SQL statements, which execute a sequence of operations.

  • The introduction of variables. Prior to SQL/PSM, SQL did not allow you to store a value in a variable.