While Loops

Kevin Feasel

2016-08-08

Syntax

Lukas Eder discovers that Oracle’s PL/SQL has WHILE loops:

In SQL, everything is a table (see SQL trick #1 in this article), just like in relational algebra, everything is a set.

Now, PL/SQL is a useful procedural language that “builds around” the SQL language in the Oracle database. Some of the main reasons to do things in PL/SQL (rather than e.g. in Java) are:

  • Performance (the most important reason), e.g. when doing ETL or reporting
  • Logic needs to be “hidden” in the database (e.g. for security reasons)
  • Logic needs to be reused among different systems that all access the database

Much like Java’s foreach loop, PL/SQL has the ability to define implicit cursors (as opposed to explicit ones)

The WHILE loop is a little more helpful in the SQL Server world for doing things like deleting lots of rows in small batches, but I agree with Lukas’s sentiment:  if you start writing a WHILE loop, it’s best to sit back and think about whether this is the best decision.

Related Posts

Using DISTINCT With XML Data

Dave Bland has a workaround for a limitation with processing XML in SQL Server: Since I was working on an example for my next performance class, I decided to use an example from that session.  In the query below the qp.* part of the column list will return a column named query_plan which is an […]

Read More

Indexed View Matching

Erik Darling has a series of posts on indexed views, with the latest covering query matching even when using a keyword in creation of the indexed view itself: There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031