• 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
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225

    Email: Sales@racksquared.com

    www.racksquared.com/ibmsolutions

    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 Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.