15.3.1.4 Iteration

Common SQL has three iteration constructs: a do- loop, a mapping function, and an extension to the Common Lisp loop macro.

do-query

Macro

Repeatedly executes body within a binding of args to the attributes of each record resulting from query .

map-query

Function

Returns the result of mapping function across the results of query-expression . The result-type argument specifies the type of the result sequence as per the Common Lisp map function.

loop

Macro

The Common Lisp loop macro has been extended with a clause for iterating over query results. The syntax of the new clause is:

{for|as} var
 [type-spec] being

             {the|each}{tuples|tuple}

             {in|of} query-expression

The more general word tuple is used so that it can also be applied to the object-oriented case. In the functional case, tuple is synonymous with record .

Each iteration of the loop assigns the next record of the table to the variable var . The record is represented in Lisp as a list. Destructuring can be used in var to bind variables to specific attributes of the records resulting from query-expression . In conjunction with the panoply of existing clauses available from the loop macro, the new iteration clause provides an integrated report generation facility.

Suppose the name of everyone in an employee table is required. This simple query is shown below using the different iteration method. The function map-query requires flatp to be specified; otherwise each name would be wrapped in a list.

(do-query ((name)[select [ename] :from [emp]])

          (print name))

(map-query

     nil

     #
'(lambda (name) (print name))

     [select [ename] :from [emp] :flatp t])

(loop for (name)

          being each tuple in

             [select [ename] :from [emp]]

          do

(print name))

The following extended loop example binds, on each record returned as a result of the query, name and salary , accumulates the salary, and for salaries greater than 2750 increments a count, and prints the details. Finally, the average salary is printed.

(loop for (name salary) being each record in

   [select [ename] [sal] :from [emp]]

   initially (format t "~&~20A~10D" 'name 'salary)

   when (and salary (> salary 2750))

     count salary into salaries

     and sum salary into total

     and do (format t "~&~20A~10D" name salary)

   else

     do (format t "~&~20A~10D" name "N/A")

   finally

   (format t "~2&Av Salary:  ~10D" (/ total salaries)))