16 The SQL Package

loop

Macro

Summary

Extendsloop to provide a clause for iterating over query results.

Package

cl

Signature

loop {for|as} var [type-spec] being {the|each} {records|record} {in|of} query-expression => result

Arguments

var: A variable.
query-expression: An SQL query statement.

Values

result: Aloop return value.

Description

The Common Lisploop 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 Lisploop facility, including the possible return values, see Steele, Common Lisp: the Language, second edition.

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 theloop macro, the new iteration clause provides an integrated report generation facility.

Example

This extendedloop example performs the following on each record returned as a result of a query: bindname 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)))

See also
: do-query
map-query
query
select