18.5.1.2 SQL strings representing symbolic expressions

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:

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:

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:

when macroexpanded reads as

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's also the pseudo operator function (see Calling database functions).

The general syntax is: [<operator> <operand> ...] , for instance:

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