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:

create procedure echo (in arg1 integer not null, out arg2 integer not null)
begin
set arg2 := arg1;
end;

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:

set arg2 := arg1;

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 value
  • out arguments are assumed to contain garbage and are aggressively cleared on entry
  • inout 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 elsewhere
  • out reference arguments are assumed to be garbage, they are not released on entry, but instead set to NULL
  • inout 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 arguments 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.

declare scratch integer not null;
call echo(12, scratch);
scratch == 12; -- true

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.

create proc checker(foo integer, out result integer not null)
begin
if foo = 1 then
set result := 1;
else if foo = 2 then
set result := 3;
else
set result := 5;
end if;
end;

The WHILE statement

What follows is a simple procedure that counts down its input argument.

create proc looper(x integer not null)
begin
while x > 0
begin
call printf('%d\n', x);
set x := x - 1;
end;
end;

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:

create proc looper(x integer not null)
begin
while 1
begin
set x := x - 1;
if x < 0 then
leave;
else if x % 100 = 0 then
continue;
else if x % 10 = 0
call printf('%d\n', x);
end if;
end;
end;

Let's go over this peculiar loop:

while 1
begin
...
end;

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.

if x < 0 then
leave;

Now here we've encoded our exit condition a bit strangely we might have done the equivalent job with a normal condition 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.

else if x % 100 = 0 then
continue;

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.

else if x % 10 = 0
call printf('%d\n', x);
end if;

Finishing up the control flow, on every 10th iteration we print the value of the loop variable.

The SWITCH Statement

The CQL SWITCH is designed to map to the C switch statement for better codegen and also to give us the opportunity to do better error checking. SWITCH is statement like IF not an expression like CASE..WHEN..END so it combines with other statements. The general form looks like this:

SWITCH switch-expression [optional ALL VALUES]
WHEN expr1, expr2, ... THEN
[statement_list]
WHEN expr3, ... THEN
[statement_list]
WHEN expr4 NOTHING
ELSE
[statement_list]
END;
  • the switch-expression must be a not-null integral type (integer not null or long integer not null)
  • the WHEN expressions [expr1, expr2, etc.] are made from constant integer expressions (e.g. 5, 1+7, 1<<2, or my_enum.thing)
  • the WHEN expressions must be compatible with the switch expression (long constants cannot be used if the switch expression is an integer)
  • the values in the WHEN clauses must be unique (after evaluation)
  • within one of the interior statement lists the LEAVE keyword exits the SWITCH prematurely, just like break in C
    • a LEAVE is not required before the next WHEN
    • there are no fall-through semantics as you can find in C, if fall-through ever comes to SWITCH it will be explicit
  • if the keyword NOTHING is used instead of THEN it means there is no code for that case, this is useful with ALL VALUES see below
  • the ELSE clause is optional and works just like default in C, covering any cases not otherwise explicitly listed
  • If you add ALL VALUES then:
    • the expression be an from an enum type
    • the WHEN values must cover every value of the enum
      • enum members that start with a leading _ are by convention considered pseudo values and do not need to be covered
    • there can be no extra WHEN values not in the enum
    • there can be no ELSE clause (it would defeat the point of listing ALL VALUES which is to get an error if new values come along)

Some more complete examples:

let x := get_something();
switch x
when 1,1+1 then -- constant expressions ok
set y := 'small';
-- other stuff
when 3,4,5 then
set y := 'medium';
-- other stuff
when 6,7,8 then
set y := 'large';
-- other stuff
else
set y := 'zomg enormous';
end;
declare enum item integer (
pen = 0, pencil, brush,
paper, canvas,
_count
);
let x := get_item(); -- returns one of the above
switch x all values
when item.pen, item.pencil then
call write_something();
when item.brush then nothing
-- itemize brush but it needs no code
when item.paper, item.canvas then
call setup_writing();
end;

Using THEN NOTHING allows the compiler to avoid emitting a useless break in the C code. Hence that choice is better/clearer than when brush then leave;

Note that the presence of _count in the enum will not cause an error in the above because it starts with _.

The C output for this statement will be a direct mapping to a C switch statement.

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)

create procedure upsert_foo(id_ integer, t_ text)
begin
begin try
insert into foo(id, t) values(id_, t_)
end try;
begin catch
begin try
update foo set t = t_ where id = id_;
end try;
begin catch
call printf("Error code %d!\n", @rc);
throw;
end catch;
end catch;
end;

Once again, let's go over this section by section:

begin try
insert into foo(id, t) values(id_, t_)
end try;

Normally if the insert statement fails, the procedure will exit with a failure result code. Here, instead, we prepare to catch that error.

begin catch
begin try
update foo set t = t_ where id = id_;
end try;

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:

begin catch
call printf("Error code %d!\n", @rc);
throw;
end catch;

Here we see a usage of the @rc variable to observe the failed error code. In this case we simply print a diagnostic message and then use the throw keyword to rethrow the previous failure (exactly what is stored in @rc). In general, throw will create a failure in the current block using the most recent failed result code from SQLite (@rc) 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:

end catch;
end;

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.

-- this works, but it is awkward
create procedure fib (in arg integer not null, out result integer not null)
begin
if (arg <= 2) then
set result := 1;
else
declare t integer not null;
call fib(arg - 1, result);
call fib(arg - 2, t);
set result := t + result;
end if;
end;

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.

-- rewritten with function call syntax
create procedure fib (in arg integer not null, out result integer not null)
begin
if (arg <= 2) then
set result := 1;
else
set result := fib(arg - 1) + fib(arg - 2);
end if;
end;

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 (neither in nor inout are acceptable)
  • the procedure does not return a result set using a select statement or out 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.

Last updated on by Raoul Foaleng