Macro

Summary

Extends loop to provide a clause for iterating over query results.

Package

common-lisp

Signature

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

Arguments

var

A variable.

query-expression

A SQL query statement or a prepared-statement containing a query.

not-inside-transaction

A generalized boolean.

get-all

A generalized boolean.

Values

result

A loop return value.

Description

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 for details.

Example

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)))

See also

do-query
map-query
prepare-statement
query
select
simple-do-query