There are some SQL operators which may take a single argument (for example
). These are read as calls to the appropriate SQL operator. For example:
[any '(3 4)] -> #<SQL-VALUE-EXP "(ANY (3,4))">
This causes no conflict, however, as it is illegal to use these reserved words as identifiers in SQL. Similarly with two argument operators:
[> [baz] [beep]]
-> #<SQL-RELATIONAL-EXP "(BAZ > BEEP)">
The select statement itself may be prepared for later query execution using the
syntax. For example:
[select [person_id] [surname] :from [person]]
This form results in a SQL expression, which could be bound to a Lisp variable and later given to query to execute. For example:
[select [foo] [bar *]
:from '([baz] [bar])
:where [or [= [foo] 3]
[> [baz.quux] 10]]]
"(SELECT FOO,BAR.* FROM BAZ,BAR
WHERE ((FOO = 3)
OR (BAZ.QUUX > 10)))">
Strings can be inserted in place of database identifiers within a select:
[select [foo bar] [baz]
:from '([foo] [quux])
:where [or [> [baz] 3]
[like [foo bar] "SU%"]]]
WHERE ((BAZ > 3)
OR (FOO.BAR LIKE 'SU%')))">
Any non-constant included gets filled in at runtime, for example:
[> [foo] x]
when macroexpanded reads as
(SQL-> #<SQL-IDENT "FOO"> X)
which constructs the actual SQL string at runtime.
Any arguments to a SQL operator that are Lisp constants are translated to the matching SQL construct at compile-time, for example:
"foo" -> "'foo'"
3 -> "3"
'("this" 5 "that") -> "('this', 5, 'that')"
'xyz -> "XYZ"
SQL operators which are supported are
. There are also pseudo operators for calling database functions (see Calling database functions).
The general syntax is:
[<operator> <operand> ...]
, for instance:
(select [count [*]] :from [emp])
The operand can itself be a SQL expression, as in the following example:
'(([name] (varchar 20) not-null)))
(loop for company in '("LispWorks Ltd"
(sql:insert-records :into [company]
:av-pairs `(([name] ,company))))
'(([surname] (varchar 20) not-null)
([firstname] (varchar 20) not-null)))
(loop for person in '(("Joe" "Bloggs")
("Rover" "the Dog")
("Fido" "the Dog"))
do (sql:insert-records :into [person]
`(([firstname] ,(car person))
([surname] ,(second person)))))
:where [= [name]
[any [select [surname]
:set-operation [union [select [firstname]
LispWorks User Guide and Reference Manual - 21 Dec 2011