Chapter 4: Procedures, Functions, and Control Flow
All kinds of control flow happens in the context of some procedure, though we've already introduced examples of procedures let's now go over some of the additional aspects we have not yet illustrated.
Out Parameters
Consider this procedure:
Here arg2
has been declared out
. CQL out parameters are very similar to "by reference" arguments in other langauges and
indeed they compile into a simple pointer reference in the generated C code. One notable difference is that, in CQL, out
parameters
for reference types and nullable types are always set to NULL by default. This is another way that an otherwise non-null reference
variable can end up with a null in it.
Looking at the one line in the body of this procedure:
The input argument arg1
is unconditionally stored in the output. Note that the in
keyword is entirely optional and does
nothing other than perhaps add some clarity. CQL also supports inout
arguments which are expected to contain non-garbage values on
entry. If the procedure is called from CQL, the compiler will arrange for this to be true.
in
arguments contain a valid valueout
arguments are assumed to contain garbage and are aggressively cleared on entryinout
arguments contain a valid value
These invariants are very important when considering how reference types are handled.
in
reference arguments are borrowed, CQL will not further retain unless they are stored elsewhereout
reference arguments are assumed to be garbage, they are not released on entry, but instead set to NULLinout
reference arguments are assumed valid at entry
If CQL changes an out
or inout
value it first releases the existing value and then retains the new value.
In all cases the caller will ultimately release any non-null out reference either because it was borrowed (in
) or
the caller now/still owns it (inout
or in
).
Aggressively putting NULL
into out
argumetns normalizes pointer handling for all out
types.
Procedure Calls
The usual call
syntax is used to invoke a procedure. It returns no value but it can have any number of out
arguments.
The let's go over the most essential bits of control flow.
The IF statement
The CQL IF
statement has no syntatic ambiguities at the expense of being somewhat more verbose than many other languages.
In CQL the ELSE IF
portion is baked into the IF
statement, so what you see below is logically a single statement.
The WHILE statement
What follows is a simple procedure that counts down its input argument.
The WHILE
loop has additional keywords that can be used within it to better control the loop. A more general
loop might look like this:
Let's go over this peculiar loop:
This is an immediate sign that there will be an unusual exit condition. The loop will never end without one because 1
will never be false.
Now here we've encoded our exit condition a bit strangely we might have done the equivalent job with a normal conditionn in the predicate
part of the while
statement but for illustration anyway, when x becomes negative leave
will cause us to exit the loop. This is like
break
in C.
This bit says that on every 100th iteration we go back to the start of the loop. So the next bit will not run, which is the printing.
Finishing up the control flow, on every 10th iteration we print the value of the loop variable.
The TRY, CATCH, and THROW Statements
This example illustrates catching an error from some DML, and recovering rather than letting the error cascade up.
This is the common "upsert" pattern (insert or update)
Once again, let's go over this section by section:
Normally if the insert
statement fails, the procedure will exit with a failure result code. Here, instead,
we prepare to catch that error.
Now, having failed to insert, presumably because a row with the provided id
already exists, we try to update
that row instead. However that might also fail, so we wrap it in another try. If the update fails, then there is a final catch block:
Here we print a diagnostic message and then use the throw
keyword to rethrow the previous failure. Throw will create a failure in
the current block using the most recent result code from SQLite if it is an error, or else the general SQLITE_ERROR
result code
if there is no such error. In this case the failure code for the update
statement will become the result code of the current procedure.
This leaves only the closing markers:
If control flow reaches the normal end of the procedure it will return SQLITE_OK
.
Procedures as Functions: Motivation and Example
The calling convention for CQL stored procedures often (usually) requires that the procedure returns a result code from SQLite.
This makes it impossible to write a procedure that returns a result like a function, the result position is already used for
the error code. You can get around this problem by using out
arguments as your return codes. So for instance, this version
of the Fibonacci function is possible.
The above works, but the notation is very awkward.
CQL has a "procedures as functions" feature that tries to make this more pleasant by making it possible to use function call notation
on a procedure whose last argument is an out
variable. You simply call the procedure like it was a function and omit the last argument in the call.
A temporary variable is automatically created to hold the result and that temporary becomes the logical return of the function.
For semantic analysis, the result type of the function becomes the type of the out
argument.
This form is allowed when:
- all but the last argument of the procedure was specified
- the formal parameter for that last argument was marked with
out
(neitherin
norinout
are acceptable) - the procedure did not also be returning a result set using a
select
statement orout
statement (more on these later)
If the procedure in question uses SQLite, or calls something that uses SQLite, then it might fail.
If that happens the result code will propagate just like it would have with a the usual call
form.
Any failures can be caught with try/catch
as usual.
This feature is really only syntatic sugar for the "awkward" form above, but it does allow for slightly better generated C code.