LispWorks User Guide and Reference Manual > 19 Common SQL > 19.3 Functional interface > 19.3.1 Functional Data Manipulation Language (FDML)

NextPrevUpTopContentsIndex

19.3.1.5 Iteration

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

LispWorks User Guide and Reference Manual - 22 Dec 2009

NextPrevUpTopContentsIndex