Common SQL has three iteration constructs: a
do-
loop, a mapping function, and an extension to the Common Lisp
loop
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
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 Lisp
map
function.
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)))