loop {for|as} var [ type-spec ] being {the|each} {records|record} {in|of} query-expression [not-inside-transaction not-inside-transaction ] [get-all get-all ] => result
The Common Lisp
loop
macro has been extended with a clause for iterating over query results. This extension is available only when Common SQL has been loaded. For a full description of the rest of the Common Lisp
loop
facility, including the possible return values, see the ANSI Common Lisp specification.
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.
The additional loop keywords
not-inside-transaction
and
get-all
may be useful when fetching many records through a connection with
database-type
:mysql
. See the section "Special considerations for iteration functions and macros" in the
LispWorks User Guide
for details.
This extended
loop
example performs the following on each record returned as a result of a query: bind
name
to the query result, find the salary (if any) from an associated hash-table, increment a count for salaries greater than 20000, accumulate the salary, and print the details. Finally, it prints the average salary.
(loop
for (name) being each record in
[select [ename] :from [emp]]
as salary = (gethash name *salary-table*)
initially (format t "~&~20A~10D" 'name 'salary)
when (and salary (> salary 20000))
count salary into salaries
and sum salary into total
and do (format t "~&~20A~10D" name salary)
else
do (format t "~&~20A~10A" name "N/A")
finally
(format t "~2&Av Salary: ~10D" (/ total salaries)))