Stop and perform a task
SQL is a set-based language. While SQL does provide for cursors, you may research them yourself, as you will find...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments