SQL LOOP Statements in Sybase SQL Anywhere
Handling Sybase Events with Triggers and Procedures Using SQL – Part 12
Division 5
Forward: In this part of the series, we look at the LOOP SQL statement in Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 28 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Illustration
Try the following code (see explanation below):
CREATE VARIABLE n INT = 1;
WHILE n <= 5 LOOP
INSERT INTO Invoice () VALUES ();
SET n = n + 1;
END LOOP;
Now execute the following statement:
SELECT * FROM Invoice;
You should realize that 5 new rows have been inserted into the Invoice table.
Explanation
A variable, n is created and initialized to 1. After that you have the LOOP statement. The syntax of the LOOP statement is:
WHILE search-condition LOOP
statement-list
END LOOP;
You have the reserved words, WHILE and LOOP. The LOOP statement ends with END LOOP and a semicolon. statement-list is a block of SQL statements. So while the search-condition is true, the block of statements are executed repeatedly until the search condition is false.
In the first run of the LOOP statement above, the two SQL statements are executed. The first one inserts a row in the Invoice table. The second one increments the variable by 1. In this second statement, the right hand side of the assignment operator (=) takes whatever value n had and adds 1 to it. Because of the assignment operator and the left hand side, the sum becomes the new value of n. So after the first run of the LOOP, the value of n becomes 2.
In the second run of the LOOP a second row is inserted into the Invoice table again; the value of n becomes 3. The LOOP continues to repeat until the 5th run. At the 5th run the 5th row is inserted and the value of n is 6.
Before the loop is executed each time, the search-condition checks if n is less than or equal to5 (n<=5). After n becomes 6 in the 5th run, the search condition is checked again. This time it is false and the loop will not be executed anymore. The search-condition is always checked until it is false. Each time it is true, the loop is executed.
Note that by first assigning the value of 1 to the variable n, and making the search-condition, (n<=5), we made the loop to repeat 5 times. You can make the loop to repeat any number of times following the same ideas. The search-condition can be anything that you know will finally be set to false within the loop.
Well, we shall look at another example in one of the following parts of the series. Let us take a break now and continue in the next part.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT