• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL PL–The LOOP Loop

    November 15, 2016 Ted Holt

    In the previous episode of this exciting series, I wrote about FOR, arguably the most useful of the SQL PL looping structures. Next in line of usefulness, in my opinion, is the LOOP structure. Like the rest of SQL PL, this is an easy structure to master, and I am pleased to write about it.

    In its simplest form, the syntax of the LOOP structure is as follows:

    LOOP - body - END LOOP
    

    Here is a more realistic version of the syntax:

    label: LOOP - body - END LOOP label
    

    The body contains any assortment of simple statements and/or compound statements.

    Something in the body must force an exit from the loop. The recommended way to exit a loop is the LEAVE statement. You can also use RETURN, which exits a procedure or function, and GOTO, which I don’t recommend.

    SQL PL allows you to label executable statements. That is, you can give a statement a name. A label is a group of alphanumeric characters. As with CL, a label is followed by a colon when used to identify a statement.

    You may use a label to name a LOOP loop if you wish. In fact, if you use LEAVE to exit the loop, you must give the loop a label because LEAVE, unlike the RPG and CL counterparts, requires a label.

    Let’s look at a simple example:

    create or replace procedure Billing (in p_Company dec (3))
    
    begin atomic
    
       declare   SqlState     char(5);
       declare   v_Company    dec (3);
       declare   v_Order      dec (9);
       declare   v_Line       dec (3);
       declare   v_Item       char(6);
       declare   v_Qty        dec (3);
    
       declare c_Bill cursor for
          select shp.Company, shp.SalesOrder, shp.Line,
                 shp.Item, shp.Quantity
            from Shipments as shp
           where shp.Company = p_Company
             for update;
    
       open c_Bill;
    
    Loop1:
       Loop
          fetch c_Bill into v_Company, v_Order, v_Line,
                         v_Item, v_Qty;
          if SqlState = '02000'
             then leave Loop1;
          end if; 
    
          update SalesOrderDetails as d
             set d.QtyShipped = d.QtyShipped + v_Qty
           where (d.company, d.order, d.line) =
                    (v_Company, v_Order, v_Line);
    
          delete from Shipments
           where current of c_Bill;
    
       end loop;
    
       close c_Bill;
    
    end
    

    This example uses a cursor to bill customers for the company specified in the only parameter, p_Company. Since today’s topic is LOOP, I won’t explain the workings of the cursor. However, if you’ve used SQL cursors in other languages, you’ll readily understand this example.

    The relevant parts of the LOOP structure are in red. The loop begins with the unimaginative label “Loop1” and ends with END LOOP. I could have included the loop name after END LOOP.

       end loop Loop1;
    

    In a large routine, adding the loop name to LOOP might have contributed to greater clarity. Since the routine is so small, the loop name seemed to me to be unnecessary clutter.

    SQL, like RPG, CL, COBOL, and some other languages, cannot determine the end of a data set without attempting to read. This is the classic case for a middle-tested loop. Part of the loop (the fetch) must be executed at least once, while the code that processes the fetched data may not be executed at all. The LOOP structure handles this requirement nicely.

    When there is no more data to fetch, the database manager loads the value 02000 into the SQL state variable. This is the cue to exit the loop. Notice the LEAVE statement references the loop name.

    I included two statements in the body of the loop: an UPDATE and a DELETE. Notice that the DELETE references the current row. This is the reason that I was not able to use a FOR loop. FOR loops are read-only.

    I have written about my great fondness of middle-tested loops. RPG and CL let me fake them. COBOL has no structured way to implement them. (I could implement them with GO TO, but I won’t.) SQL PL directly supports them. Chalk up another one for SQL PL.

    Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

     

    RELATED STORIES

    SQL PL–The FOR Loop

    A Brief Introduction To The SQL Procedures Language

    Book Excerpt: DB2 SQL Procedural Language for Linux, UNIX, and Windows

     

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  NOW ON DEMAND! Webinar: Agile Modernization with Node.js. Fresche:  IBM i staffing for all of your IT needs. Request a FREE estimate. 1-800-361-6782 Chrono-Logic:  Simplify deployment with LANSA-based Change Management and Deployment solutions.

    You’re Hired! Finding Your Next IBM i Pro What Koch’s $2.5 Billion Infor Investment Means For IBM i

    Leave a Reply Cancel reply

Volume 16, Number 25 -- November 15, 2016
THIS ISSUE SPONSORED BY:

ProData Computer Services
T.L. Ashford
WorksRight Software

Table of Contents

  • Calling SQL Functions From RPG, A Service Program
  • SQL PL–The LOOP Loop
  • QTEMP Is A Different Animal

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM Pulls The Curtain Back A Smidge On Project Bob
  • IBM Just Killed Merlin. Here’s Why
  • Guru: Playing Sounds From An RPG Program
  • A Bit More Insight Into IBM’s “Spyre” AI Accelerator For Power
  • IBM i PTF Guide, Volume 27, Number 42
  • What You Will Find In IBM i 7.6 TR1 and IBM i 7.5 TR7
  • Three Things For IBM i Shops To Consider About DevSecOps
  • Big Blue Converges IBM i RPG And System Z COBOL Code Assistants Into “Project Bob”
  • As I See It: Retirement Challenges
  • IBM i PTF Guide, Volume 27, Number 41

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle