14.3.1 Functional Data Manipulation Language (FDML)

14.3.1.4 Iteration

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

do-query

Macro

do-query &rest args query &key database &rest body

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

map-query

Function

map-query result-type function query-expression &key database

: 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 Lispmap function.

loop

Macro

: The Common Lisploop 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 wordtuple is used so that it can also be applied to the object-oriented case. In the functional case,tuple is synonymous withrecord.
: 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 functionmap-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 extendedloop example binds, on each record returned as a result of the query,name andsalary, 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)))