22.6 SQL commands

SML# accept the following subset of SQL commands $\,\langle$ sqlcommand $\rangle\,$ as well as SELECT queries:

$\,\langle$ sqlcommand $\rangle\,$	::=	$\,\langle$ sqlinsert $\rangle\,$	INSERT commands
	$\|$	$\,\langle$ sqlupdate $\rangle\,$	UPDATE commands
	$\|$	$\,\langle$ sqldelete $\rangle\,$	DELETE commands
	$\|$	$\,\langle$ sqltransaction $\rangle\,$	transaction management commands
	$\|$	( $\,\langle$ sqlfn $\rangle\,$ ; $\cdots$ ; $\,\langle$ sqlfn $\rangle\,$ )	command sequence
	$\|$	... ( $\,\langle$ exp $\rangle\,$ )	embedded SQL commands

The type of INSERT, UPDATE, DELETE, and transaction commands is

(unit, $w$ ) SQL.command

Details of these commands are shown in the following subsections.

( $\,\langle$ sqlfn ${}_{1}$ $\rangle\,$ ; $\cdots$ ; $\,\langle$ sqlfn ${}_{n}$ $\rangle\,$ ) ( $n$ must be greater than 1) is a command consisting of a sequence of commands separated by semicolons. Its type is equal to the type of the last command $\,\langle$ sqlfn ${}_{n}$ $\rangle\,$ .

...( $\,\langle$ exp $\rangle\,$ ) embeds the result of $\,\langle$ exp $\rangle\,$ as a command.

22.6.1 INSERT commands

The syntax of the INSERT command is the following:

$\,\langle$ sqlinsert $\rangle\,$	::=	insert into # $\,\langle$ vid $\rangle\,$ . $\,\langle$ lab $\rangle\,$ ( $\,\langle$ lab $\rangle\,$ , $\cdots$ , $\,\langle$ lab $\rangle\,$ )
		values $\,\langle$ insertRow $\rangle\,$ , $\cdots$ , $\,\langle$ insertRow $\rangle\,$
	$\|$	insert into # $\,\langle$ vid $\rangle\,$ . $\,\langle$ lab $\rangle\,$ ( $\,\langle$ lab $\rangle\,$ , $\cdots$ , $\,\langle$ lab $\rangle\,$ )
		values $\,\langle$ insertVar $\rangle\,$
	$\|$	insert into # $\,\langle$ vid $\rangle\,$ . $\,\langle$ lab $\rangle\,$ $($ ( $\,\langle$ lab $\rangle\,$ , $\cdots$ , $\,\langle$ lab $\rangle\,$ ) $)?$ $\,\langle$ sqlselect $\rangle\,$
$\,\langle$ insertRow $\rangle\,$	::=	( $\,\langle$ insertVal $\rangle\,$ , $\cdots$ , $\,\langle$ insertVal $\rangle\,$ )
$\,\langle$ insertVal $\rangle\,$	::=	$\,\langle$ sqlexp $\rangle\,$ $\|$ default
$\,\langle$ insertVar $\rangle\,$	::=	$\,\langle$ vid $\rangle\,$ $\|$ op $\,\langle$ longvid $\rangle\,$

The syntax is restricted by the following rules:

•

The labels of ( $\,\langle$ lab $\rangle\,$ , $\cdots$ , $\,\langle$ lab $\rangle\,$ ) must be distinct.
•

The number of $\,\langle$ insertVal $\rangle\,$ in $\,\langle$ insertRow $\rangle\,$ must be equal to the number of labels in ( $\,\langle$ lab $\rangle\,$ , $\cdots$ , $\,\langle$ lab $\rangle\,$ ).

An INSERT command inserts the rows in either the values subclause or the result of the $\,\langle$ sqlselect $\rangle\,$ query into the designated table. If an SML# variable $\,\langle$ vid $\rangle\,$ or op $\,\langle$ longvid $\rangle\,$ is written in the values subclause, the list of the records consisting of labels $\,\langle$ lab ${}_{1}$ $\rangle\,$ , $\cdots$ , $\,\langle$ lab ${}_{n}$ $\rangle\,$ denoted by the variable is inserted into the table. If the rows to be inserted does not cover all the columns in the designated table, the columns not covered are filled with the default values, which is specified when the table is created. If a column is specified as default in a row of the values clause, the default value is also inserted into that column. If a default value is needed but no default value is specified, the database server causes an runtime error and the SQL.Exec exception is raised in SML#.

The typing rules are the following:

•

$\,\langle$ vid $\rangle\,$ must be of the SQL.db type that has at least the table name $\,\langle$ lab $\rangle\,$ .
•

If there is ( $\,\langle$ lab ${}_{1}$ $\rangle\,$ , $\cdots$ , $\,\langle$ lab ${}_{n}$ $\rangle\,$ ), either the table # $\,\langle$ vid $\rangle\,$ . $\,\langle$ lab $\rangle\,$ refers to or the $\,\langle$ sqlselect $\rangle\,$ query must have at least columns $\mbox{\mbox{$\,\langle$}{\it lab${}_{1}$}\mbox{$\rangle\,$}},\ldots,\mbox{% \mbox{$\,\langle$}{\it lab${}_{n}$}\mbox{$\rangle\,$}}$ . Other columns may be included in it. for $\,\langle$ sqlselect $\rangle\,$ , the column sets of its result and the designated table may be different.
•

If no ( $\,\langle$ lab ${}_{1}$ $\rangle\,$ , $\cdots$ , $\,\langle$ lab ${}_{n}$ $\rangle\,$ ) appears, the column set of the result of the $\,\langle$ sqlselect $\rangle\,$ query and the designated table must be identical.
•

The type of the $i$ -th expression $\,\langle$ sqlexp ${}_{i}$ $\rangle\,$ in the values columns must be $(\mbox{\tt\frenchspacing\char 123\char 125},w)\triangleright\tau_{i}$ where $\tau_{i}$ is the type of the $\,\langle$ lab ${}_{i}$ $\rangle\,$ column of the designated table.

22.6.2 UPDATE Commands

The syntax of the UPDATE command is the following:

$\,\langle$ sqlupdate $\rangle\,$	::=	update # $\,\langle$ vid $\rangle\,$ . $\,\langle$ lab $\rangle\,$
		set $\,\langle$ updateRow $\rangle\,$ , $\cdots$ , $\,\langle$ updateRow $\rangle\,$
		$($ $\,\langle$ sqlWhereClause $\rangle\,$ $)?$
$\,\langle$ updateRow $\rangle\,$	::=	$\,\langle$ lab $\rangle\,$ = $\,\langle$ sqlexp $\rangle\,$

The syntax is restricted by the following rules:

•

All the $\,\langle$ lab $\rangle\,$ s of $\,\langle$ updateRows $\rangle\,$ must be distinct.

An UPDATE command updates the rows in the designated table and matched with the condition of the WHERE clause with the values of the SET clause. The columns not specified in the SET clause are left original. The expressions in the SET clauses may include column references of the table $\,\langle$ lab $\rangle\,$ in order to refer to the original value.

The typing rules are the following:

•

$\,\langle$ vid $\rangle\,$ must be of the SQL.db type that has at least the table name $\,\langle$ lab $\rangle\,$ .
•

The column $\,\langle$ lab ${}_{i}$ $\rangle\,$ of each $\,\langle$ updateRow ${}_{i}$ $\rangle\,$ must be a column in the designated table. The table may have other columns.
•

The type of $\,\langle$ sqlexp ${}_{i}$ $\rangle\,$ of each $\,\langle$ updateRow ${}_{i}$ $\rangle\,$ must be $(\mbox{\tt\frenchspacing\char 123\mbox{$\,\langle$}{\it lab}\mbox{$\rangle\,$}% : $\tau$\char 125},w)\triangleright\tau_{i}$ where $\tau$ is the type of the designated table and $\tau_{i}$ is the type of the $\,\langle$ lab ${}_{i}$ $\rangle\,$ column in $\tau$ .
•

If there exists $\,\langle$ sqlWhereClause $\rangle\,$ , it must be of type ( $\tau$ -> $\tau$ , $w$ ) SQL.whr where $\tau$ is the type of the designated table.

22.6.3 DELETE commands

The following is the syntax of the DELETE commands:

\,\langle

sqldelete

\rangle\,

::=

delete from #

\,\langle

vid

\rangle\,

\,\langle

lab

\rangle\,

(

\,\langle

sqlWhereClause

\rangle\,

)?

An DELETE commands deletes the rows matched with the condition of the WHERE clause from the designated table.

The typing rules are the following:

•

$\,\langle$ vid $\rangle\,$ must be of the SQL.db type that has at least the table name $\,\langle$ lab $\rangle\,$ .
•

If there exists $\,\langle$ sqlWhereClause $\rangle\,$ , its type must be of ( $\tau$ -> $\tau$ , $w$ ) SQL.whr where $\tau$ is the type of the designated table.

22.6.4 BEGIN, COMMIT, and ROLLBACK commands

The following transaction control commands are available in SML#:

$\,\langle$ sqltransaction $\rangle\,$	::=	begin	start a transaction
	$\|$	commit	commit a transaction
	$\|$	rollback	abort a transaction