PL/SQLite

PL/SQLite Documentation Page

Variables

The name of a variable should begin with a symbol <@> and can contain no more than 31 symbols from the following: a-zA-Z0-9_. Before using variable must be declared.

 ex:
  create table t1(a,b);
  create table t2(a,b);
  create trigger t1 after insert on t1 begin
   insert into t2 values(new.a, new.a+@b);
  end;
  SQL error: trigger: t1(2,37): variable @b undeclared

The type of a variable is used as well as type of a column of the table in comparison operations (see test vars-1.1 in tarball). Variable name can be used in any place where expression can be used (expr ::= var_name). Before access to variable value variable must be assigned.

 ex:
  create trigger t1 after insert on t1 begin
   declare @b;
   insert into t2 values(new.a, new.a+@b);
  end;
  SQL error: trigger: t1(3,37): variable @b not assigned
  
  create trigger t1 after insert on t1 begin
   declare @b;
   @b=new.b+5;
   insert into t2 values(new.a, new.a+@b);
  end;

Cursors

The name of the cursor submits to the same rules as a name of a variable. You must declare a cursor before referencing it in other statements. When you declare a cursor, you name it and associate it with a specific query using the syntax:

 
 DECLARE CURSOR cursor_name FOR select_statement;

Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. The number of the selected records is kept in a system variable @@count.

 ex:
  ...
  declare cursor @t2 for select * from t2;
  declare @t2count int;
  open @t2;
  @t2count=@@count;
  ...

The FETCH statement advances the cursor to the next row in the result set. The FETCH statement must be called before you can access to cursor fields.

 ex:
  create table t3(a,b);
  create trigger t2 after insert on t1 begin
   declare cursor @t2 for select b from t2 where a=1;
   open @t2;
   insert into t3 values(new.a, @t2.b);  -- at this point cursor must be fetched
  end;
  SQL error: trigger: t2: cursor @t2 not closed
  trigger: t2(4,31): cursor @t2 not fetched
  
  create trigger t2 after insert on t1 begin
   declare cursor @t2 for select b from t2 where a=1;
   open @t2;
   fetch @t2;
   insert into t3 values(new.a, @t2.b);
   close @t2;
  end;

The CLOSE statement disables the cursor, and the result set becomes undefined. As you see in example above, you must close opened cursor.

Conditional and iterative control operators

IF Statements

The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are two forms of IF statements: IF-THEN-ENDIF and IF-THEN-ELSE-ENDIF.

IF-THEN-ENDIF

The simplest form of IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and ENDIF, as shown below:

IF condition THEN
 trigger_cmd_list
ENDIF;

The sequence of statements is executed only if the condition is true. If the condition is false or null, the IF statement does nothing. In either case, control passes to the next statement.

IF-THEN-ELSE-ENDIF

The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements:

 IF condition THEN
  trigger_cmd_list
 ELSE
  trigger_cmd_list
 ENDIF;

The sequence of statements in the ELSE clause is executed only if the condition is false or null. Thus, the ELSE clause ensures that a sequence of statements is executed.

WHILE Statement

WHILE statement let you execute a sequence of statements multiple times. The WHILE-DO-ENDO statement associates a condition with a sequence of statements enclosed by the keywords DO and ENDDO, as follows:

 WHILE condition DO
  trigger_cmd_list
 ENDDO;

Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

BREAK Statement

The BREAK statement forces a loop to complete unconditionally. When an BREAK statement is encountered, the loop completes immediately and control passes to the next statement, as shown below:

 WHILE condition DO
  ...
  IF condition THEN
   ...
   BREAK; -- exit loop immediately
  ENDIF;
 ENDDO;
 -- control resumes here

The next example shows that you cannot use the BREAK statement without WHILE:

 create trigger t1 after insert on t1 begin
  if (new.a < 5) then 
   break; -- illegal
  endif;
 end;

CONTINUE Statement

The CONTINUE statement forces a loop to resume at start. The CONTINUE statement cannot be used without WHILE statement:

 create trigger t1 after insert on t1 begin
  if (new.a < 5) then 
   continue; -- illegal
  endif;
 end;

CASE Statement

The CASE statement lets you execute a sequence of statements conditionally. There are two forms of CASE statements: CASE expr-WHEN-THEN-ENDCASE and CASE-WHEN-THEN-ENDCASE.