There are some SQL operators which may take a single argument (for example
any
,
some
,
all
,
not
,
union
,
intersect
,
except
, and
minus
). 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 an 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]]]
->
#<SQL-QUERY
"(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%"]]]
->
#<SQL-QUERY:
"(SELECT FOO.BAR,BAZ
FROM FOO,QUUX
WHERE ((BAZ > 3)
OR (FOO.BAR LIKE 'SU%')))">
Any non-constant included gets filled in at runtime, for example:
[> [foo] x]
(SQL-> #<SQL-IDENT "FOO"> X)
which constructs the actual SQL string at runtime.
Any arguments to an 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
null
,
exists
,
*
,
+
,
/
,
-
,
like
,
substr
,
and
,
or
,
not
,
in
,
all
,
any
,
some
,
||
,
=
,
<
,
>
,
>=
,
<=
,
<>
,
order-by
,
count
,
max
,
min
,
avg
,
sum
,
minus
,
nvl
,
distinct
,
except
,
intersect
,
union
,
slot-value
,
between
and
userenv
. 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:
(sql:create-table [company]
'(([name] (varchar 20) not-null)))
(loop for company in '("LispWorks Ltd"
"Harlequin"
"Oracle"
"Rover"
"Microsoft")
do
(sql:insert-records :into [company]
:av-pairs `(([name] ,company))))
(sql:create-table [person]
'(([surname] (varchar 20) not-null)
([firstname] (varchar 20) not-null)))
(loop for person in '(("Joe" "Bloggs")
("Fred" "Smith")
("Rover" "the Dog")
("Fido" "the Dog"))
do (sql:insert-records :into [person]
:av-pairs
`(([firstname] ,(car person))
([surname] ,(second person)))))
(sql:select [name]
:from [company]
:where [= [name]
[any [select [surname]
:from [person]]]])
(sql:select [surname]
:from [person]
:set-operation [union [select [firstname]
:from [person]]])