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.