Looping in SQL

3 Key excerpts on "Looping in SQL"

• 

  eBook - ePub

  Processing

  An Introduction to Programming

  • Jeffrey L. Nyhoff, Larry R. Nyhoff(Authors)
  • 2017(Publication Date)
  • Chapman and Hall/CRC

    (Publisher)

  5

  Repetition with a Loop: The  while    Statement    

  We have seen in the programs we have written that the order in which the statements are written makes a difference. This illustrates a key principle of computing known as sequence  .

  In Chapter  4, we saw that statements placed inside an if  or switch  statement are only performed if a certain condition is true. Such conditional programming illustrates a second key principle of computing known as selection  .

  In this chapter and the next, we will see that, sometimes, one or more statements need to be performed repeatedly   in a computer program. This will illustrate a third key principle in computing: repetition  .

  Human beings often find repetitive tasks to be tedious. In contrast, computers can provide an ideal way to perform repetitive tasks.  Repetition as Long as a Certain Condition Is True

  Like the if  statement, each repetition structure in this chapter will make use of a condition  . This condition will determine whether the repetition should continue  .

  Once again, let’ s consider a cooking recipe as an analogy to a computer program. Suppose a certain dough recipe says that you are to mix the ingredients and then repeatedly   add ½  cup of flour as long as   you can still stir the mixture with a spoon. A flowchart of this recipe might look like the following:

  Similarly, in computer programming, there are sometimes sets of statements that we would like to perform repeatedly  , but only as long as   a certain condition is true.

  Repetition with the while   Statement

  Processing provides several programming structures that can be used for creating repetition. One of the more versatile of these repetition structures is the while   statement. The basic form of the while

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL in 7 Days

  A Quick Crash Course in Manipulating Data, Databases Operations, Writing Analytical Queries, and Server-Side Programming (English Edition)

  • Alex Bolenok(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  As with many other SQL features, cursors are vestiges of the interactive usage pattern. SQL queries were meant to be entered by a human, interactively, from a simple console, with little to no help from other software. Used this way, cursors make a lot of sense. They give the user ability to run a query and page through its results. Early consoles and terminals had scarce memory. Storing the whole resultset anywhere except the server was just not an option.

  Later, SQL's usage pattern shifted from interactivity to meta-programming. SQL queries are now sent by code, not people. Programs do not need to stare at the data and decide what to do next. They know what they want from the database. They read the next chunk as soon as they are done with the previous one. For a program, a cursor is just a convenient way to loop through the records.

  Programs read the query output, record by record, in a loop, and put some decorations around it: some HTML tags or charting system markup. The data is often nested, and so is its processing. A usual data processing pattern is something like: "For each customer, start a new page. For each order of his customer, print its date. For each item within the order …". Nested loops. Sounds familiar, doesn't it?

  It takes quite a mind shift to learn to formulate these things in terms of joins and filters, rather than nested loops. Most programming languages have built-in support for loops but not for joins. Hence, most programmers are comfortable around loops more than they are around joins.

  If you are a user of an interactive console, you learn joins fast. Nested loops are just not an option for you. You will get sick of typing the same query again and again. But one thing computers are good at is doing a simple repetitive task in a loop. The developer types each query just once, inside the loop. The computer is happy to run it as many times as needed. And the easiest way to loop over records is using cursors.

  Running a single query with joins is oftentimes better than running multiple queries in a loop. It takes less time and resources. There are fewer network roundtrips and fewer queries to parse, and the optimizer is likely to come up with a better plan than nested loops.

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  SQL All-in-One For Dummies

  • Allen G. Taylor(Author)
  • 2019(Publication Date)
  • For Dummies

    (Publisher)

  Notice the one little problem with the code fragment in the preceding example: It’s an infinite loop. No provision is made for leaving the loop, so it will continue inserting rows into the asteroid table until the database management system (DBMS) fills all available storage with asteroid table records. If you’re lucky, the DBMS raises an exception at that time. If you’re unlucky, the system merely crashes.

  For the LOOP statement to be useful, you need a way to exit loops before you raise an exception: the LEAVE statement.

  LEAVE

  The LEAVE statement works just like you might expect it to. When execution encounters a LEAVE statement embedded within a labeled statement, it proceeds to the next statement beyond the labeled statement, as in this example:

  AsteroidPreload: SET vcount = 0 ; LOOP SET vcount = vcount + 1 ; IF vcount > 10000 THEN LEAVE AsteroidPreload ; END IF ; INSERT INTO asteroid (AsteroidID) VALUES (vcount) ; END LOOP AsteroidPreload The preceding code inserts 10,000 sequentially numbered records into the asteroids table and then passes out of the loop.

  WHILE … DO … END WHILE

  The WHILE statement provides another method for executing a series of SQL statements multiple times. While a designated condition is true, the WHILE loop continues to execute. When the condition becomes false, looping stops, as in this example:

  AsteroidPreload2: SET vcount = 0 ; WHILE vcount < 10000 DO SET vcount = vcount + 1 ; INSERT INTO asteroid (AsteroidID) VALUES (vcount) ; END WHILE AsteroidPreload2

  This code does exactly the same thing that AsteroidPreload does in the preceding section. This is just another example of the oft-cited fact that with SQL, you usually have multiple ways to accomplish any given task. Use whichever method you feel most comfortable with, assuming that your implementation allows it.

  REPEAT … UNTIL … END REPEAT

  The REPEAT loop is very much like the WHILE

  Sign up to read

  Learn more about book