SQL is a set-based language. While SQL does provide for cursors, you may research them yourself, as you will find...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
no help from me on using them. Most database systems also provide for stored procedures, which would allow you both to write procedural code to "move on" from one row of a table to the next, and to "perform a task" when a condition is detected. However, you will again find no help from me on writing stored procedures.
A set-based solution is characterized by an approach in which you specify which rows you want, and typically this set of rows all satisfy a specific condition. This is the way SQL was designed to work.
An example which involves "performing a task" is an UPDATE. Suppose your input variable is 500, which represents an amount of five hundred dollars, and you want to compare this to a particular column called CustomerReturns in a table called StaffSalesTotals, and if 500 is greater than the value in the CustomerReturns column, this indicates that the sales person represented by this row has not exceeded the threshold for customer returns, and therefore the action that you wish to take is to set the value of the LowReturnsBonus column to 50, representing a special 50 dollar bonus amount for this salesperson.
update StaffSalesTotals set LowReturnsBonus = 50 where CustomerReturns < 500
Note in particular that there is nothing in this solution which involves "stopping" or "moving on to the next row" -- these concepts are meaningless in a set-based solution. All rows which meet the specified condition are updated simultaneously.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.