Common SQL has three iteration constructs: a
do
loop, a mapping function, and an extension to the Common Lisp loop macro.
The macros
do-query and
simple-do-query repeatedly execute a piece of code within the scope of variables bound to the attributes of each record resulting from a query.
The function map-query maps a function across the results of a query and returns its result in a sequence of a specified type, like the Common Lisp
map
function.
Common SQL provides an extension to the ANSI Common Lisp macro
loop
which is a clause for iterating over query results. The syntax of the 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)))