Chapter 26 – PSM: Not just Persistent Stored Modules¶

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.

On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.

For more information specific to CrateDB, check out the CrateDB Reference documentation.

The initials PSM refer to the specifications of a document labelled “ISO/IEC 9075-4 Information Technology - Database Languages - SQL: Part 4: Persistent Stored Modules (SQL/PSM)”. Part 4 is one of the standard SQL documents, but it is not essential – that is, it describes SQL features that are optional. A DBMS that complies with part 4 can claim “enhanced SQL conformance” (provided, of course, that it also fully supports Core SQL). We will use the phrase “essential SQL” to mean “SQL without PSM, as defined in Parts 1 and 2 of the SQL Standard”.

In essential SQL, the concept of Modules – that is, SQL-client Modules – is defined and used frequently (in effect, every type of SQL binding style conceptually uses Modules, at least implicitly). However, nobody implements them and nobody cares. What vendors have implemented, and what programmers have used throughout SQL’s history, is some variant of: (a) embedded SQL, (b) SQL/CLI or (c) both. There has been no popular implementation of a complete SQL language which can do all the things that other programming languages offer.

For example, in essential SQL, there is no easy way to do these things:

Declare variables.
Assign to variables.
Control flow of execution with loops or if/then statements.
Write complete program modules.

With PSM, there is a way to overcome those deficiencies – if they are deficiencies. Essentially, PSM is a package of extensions to the essential SQL specification. Since one important extension is the ability to create and destroy program modules, the package name is “Persistent Stored Modules”. However, the other extensions – variable handling and program control – can be implemented independently. For example, some DBMSs allow “assignment” statements to be used within Triggers, even though they have no support for persistent stored modules.

Table of Contents

Persistent Stored Modules ¶

In SQL, the word “persistent” is applied to Schema Objects that survive over SQL-sessions (as “persistent Base tables” do). And the sort of Modules we’re talking about are indeed Schema Objects – they’re stored in Schemas, just as Tables and Domains and other Schema Objects are. (The actual storage is on the server and these Objects are sometimes called “SQL-server Modules”, but their physical location is not important.)

It is, then, reasonable to think of a Persistent Stored Module as a [program] Module which is stored permanently within a Schema of an SQL “database”. As with other Schema Objects, there are CREATE, ALTER and DROP statements for creating, altering and dropping Modules. In this chapter, we’ll briefly describe these Modules, and show you the syntax to use to create, alter and destroy them.

A Schema may contain zero or more Modules. An SQL Module is a named group of SQL statements. Modules are dependent on some Schema – the <Module name> must be unique within the Schema the Module belongs to – and are created, altered and dropped using standard SQL statements. All Modules consist of various identifying elements (e.g.: the Module name, the Module <AuthorizationID> and an associated <Schema name> and <Character set name>) as well as the temporary Table declarations necessary to use the Module. In addition, a Module must contain one or more SQL procedures.

An SQL procedure is a named program procedure that will execute an SQL statement when it is called. It contains the list of parameter declarations necessary to execute the procedure and exactly one SQL statement. Procedures are called from Modules with a call statement that provides the procedure name and the necessary values for the parameters that are declared in the procedure. SQL procedures must reference parameters to pass values between the program and SQL-data. Since parameters must map to host language variables, they are not nullable unless they are coupled with an indicator parameter.

SQL provides a status parameter – SQLSTATE – whose value indicates whether or not an SQL statement was successfully executed. All procedures must contain an SQLSTATE declaration.

A Module is defined by a descriptor that contains six pieces of information:

The <Module name>, qualified by the <Schema name> of the Schema it belongs to.
The name of the Character set that is used to express the names of all Schema Objects mentioned in the Module’s definition.
The Module’s <AuthorizationID> – this is the <AuthorizationID> that owns the Module’s Schema.
The list of <Schema name>s contained in the Module’s path specification.
A descriptor for every declared local temporary Table defined in the Module.
A descriptor for every SQL-invoked routine contained in the Module.

To create a Module, use the CREATE MODULE statement (either as a stand-alone SQL statement or within a CREATE SCHEMA statement). CREATE MODULE specifies the enclosing Schema, names the Module and identifies the Module’s Character set, declared Tables and routines. To change an existing Module, use the ALTER MODULE statement. To destroy a Module, use the DROP MODULE statement.

There is a one-to-many association between Schemas and Modules: one Schema can own multiple Modules.

Module Names¶

A <Module name> identifies a Module. The required syntax for a <Module name> is:

<Module name> ::=
[ <Schema name>. ] unqualified name

A <Module name> is a <regular identifier> or a <delimited identifier> that is unique (for all Modules) within the Schema it belongs to. The <Schema name> which qualifies a <Module name> names the Schema that the Module belongs to and can either be explicitly stated, or a default will be supplied by your DBMS as follows:

If a <Module name> in a CREATE SCHEMA statement isn’t qualified, the default qualifier is the name of the Schema you’re creating.
If the unqualified <Module name> is found in any other SQL statement, the default qualifier is the name of the Schema identified in the SCHEMA clause or AUTHORIZATION clause of the CREATE MODULE statement that defines that Module.

Here are some examples of <Module name>s:

MODULE_1
-- a <Module name>

SCHEMA_1.MODULE_1
-- a simple qualified <Module name>

CATALOG_1.SCHEMA_1.MODULE_1
-- a fully qualified <Module name>

CREATE MODULE Statement ¶

The CREATE MODULE statement creates an SQL-server Module: a Module that belongs to a Schema. The required syntax for the CREATE MODULE statement is:

CREATE MODULE <Module name>
   [ NAMES ARE <Character set name> ]
   [ SCHEMA default <Schema name>]
   [ PATH <Schema name> [ {,<Schema name>}... ] ]
   [ DECLARE TABLE statement(s) ]
   <SQL-invoked routine>; ...
   END MODULE

CREATE MODULE defines a new SQL-server Module – don’t get this mixed up with the simple MODULE statement that is part of essential SQL, it defines an SQL- client Module and, although the two are similar, PSM statements won;t work on anything but a PSM Module. An SQL-server Module is owned by the Schema it belongs to.

The <Module name> identifies the Module and the Schema that it belongs to. A <Module name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Module name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <Module name> must be unique within the Schema that owns it.

If CREATE MODULE is part of a CREATE SCHEMA statement, the <Module name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn’t possible to create a Module belonging to a different Schema from within CREATE SCHEMA.

The optional NAMES ARE clause provides the name of the Character set that is used to express the names of all Schema Objects mentioned in the Module’s definition. If you omit the clause, the Module’s Character set is the default Character set of the Schema it belongs to.

The optional SCHEMA clause names the default Schema for the Module – that is, the name of the Schema that owns the Schema Objects referred to in the Module. If you omit the clause, the default <Schema name> is the name of the Schema that owns the Module.

The optional PATH clause names the Module’s default path: the path used to qualify unqualified <Routine name>s that identify <routine invocation>s that are part of this CREATE MODULE statement. You must include the name of the Schema being created in the PATH clause and, if you include multiple names, all of the Schemas named must belong to the same Catalog. If you omit the clause, your DBMS will give the Module a default path that includes the name of the Schema that the Module belongs to.

The Module can contain zero or more DECLARE TABLE statements, each declaring a local temporary Table that will only be visible to this Module.

The Module can contain one or more SQL-invoked routines – these do the Module’s work. Here’s a simple example:

CREATE MODULE Employees_Module         /* This begins and names the Module */
  DECLARE PROCEDURE Delete_Employees() /* This is an SQL routine */
      MODIFIES SQL DATA
      DELETE FROM Employees;
  DECLARE PROCEDURE Update_Employees() /* This is an SQL routine */
      MODIFIES SQL DATA
      UPDATE Employees SET col=NULL;
  DECLARE PROCEDURE Insert_Employees() /* This is an SQL routine */
      MODIFIES SQL DATA
      INSERT INTO Employees VALUES (5);
  END MODULE

If your memory stretches back to our chapter on SQL routines, you’ll recognize the PROCEDURE statements here – procedures and functions are part of essential SQL. What the CREATE MODULE statement allows you to do is construct a “package” of procedures, in the same way that a C implementation allows the construction of a library. Our example is a rather crude attempt to “package” the main SQL-data change statements that can happen with the EMPLOYEES Table. Note: The DECLARE <keyword> before PROCEDURE is optional.

ALTER MODULE Statement ¶

The ALTER MODULE statement lets you change a Module’s definition. The required syntax for the ALTER MODULE statement is:

ALTER MODULE <Module name>
   {ADD | DROP} <Module contents> ...

ALTER MODULE changes an existing Module. <Module contents> can be a function, a procedure or any of the other things that might be part of a Module (exceptions, handlers, variables, Cursors, declared Tables and so on).

Here’s an example of an ALTER MODULE statement:

ALTER Module Employees_Module
  DROP PROCEDURE Insert_Employees;

After the execution of this ALTER MODULE statement, the EMPLOYEES_MODULE Module will have only two procedures left: Delete_Employees and Update_Employees.

DROP MODULE Statement ¶

The DROP MODULE statement destroys an SQL-server Module. The required syntax for the DROP MODULE statement is:

DROP MODULE <Module name> {RESTRICT | CASCADE}

The <Module name> must identify an existing Module whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Module may drop it.

The effect of DROP MODULE <Module name> RESTRICT, e.g.:

DROP MODULE module_1 RESTRICT;

is that the Module named is destroyed, provided that the Module doesn’t contain the definition of an SQL-invoked routine that is invoked outside of the Module – i.e.: in an SQL routine that isn’t defined in this Module or in any View definition, Trigger definition, Constraint or Assertion definition. That is, RESTRICT ensures that only a Module with no dependent Objects can be destroyed. If the Module is used by any other Object, DROP MODULE ... RESTRICT will fail.

The effect of DROP MODULE <Module name> CASCADE, e.g.:

DROP MODULE module_1 CASCADE;

is that the Module named is destroyed.

Successfully dropping a Module has a three-fold effect:

The Module named is destroyed.
All Privileges held on the Module by the <AuthorizationID> that owns it are revoked (by the SQL special grantor, “_SYSTEM”) with a CASCADE revoke behaviour, so that all Privileges held on the Module by any other <AuthorizationID> are also revoked.
All SQL routines, Triggers, Views and Constraints that depend on the Module are dropped with a CASCADE drop behaviour.

BEGIN … END: compound Statement ¶

Advance warning: BEGIN ... END has several optional clauses. We are going to start with the simplest form, and examine the options in following sections.

In its simplest form, BEGIN ... END in SQL serves the same purpose as “begin…end” in Pascal or “{…}” in C. BEGIN ... END encloses a sequence of statements which are part of the same syntactical unit: a compound statement. The simplest required syntax is:

BEGIN
   [ <SQL statement>; ... ]
END

Here’s a simple example:

BEGIN
    INSERT INTO Table_1 VALUES (5);
    INSERT INTO Table_2 VALUES (6);
END

ATOMIC Statements¶

A slightly more complicated form of a compound statement has one extra optional clause: [NOT] ATOMIC. The required syntax is:

BEGIN [ [ NOT ] ATOMIC ]    /* whether compound statement is atomic */
  [ <SQL statement>; ... ]
END

If ATOMIC is specified, the compound statement may not contain COMMIT or ROLLBACK. If you omit the clause, it defaults to NOT ATOMIC: the compound statement may contain COMMIT or ROLLBACK. Here’s an example:

BEGIN ATOMIC
   INSERT INTO Table_1 VALUES (5);
   INSERT INTO Table_2 VALUES (6);
END

We’ve already discussed the idea that transactions are atomic, and individual SQL statements are atomic. Compound SQL statements can be atomic too, provided that they are explicitly designated by the <keyword> ATOMIC. Thus, in the above example, if the first INSERT statement succeeds but the second INSERT statement fails, then the effects of the first INSERT is cancelled. It’s as if there was a savepoint at the beginning of the compound statement and a ROLLBACK TO SAVEPOINT was executed when the second INSERT failed.

Variables¶

A slightly more complicated form of a compound statement has one more optional clause: a variable declaration list. The required syntax is:

BEGIN [ [ NOT ] ATOMIC ]
  [ <variable declaration>; ... ]     /* variable-declaration list */
  [ <SQL statement>; ... ]
END

<variable declaration> ::=
DECLARE <SQL variable name> <data type> [ DEFAULT default value ]

Here’s an example:

BEGIN ATOMIC
  DECLARE v1 CHAR(5);                          /* variable declaration */
  DECLARE v2,v3,v4 SMALLINT;                   /* variable declaration */
  DECLARE v5 DATE DEFAULT DATE '1993-01-01';   /* variable declaration */
  SELECT * INTO v1,v2,v3,v4 FROM Table_1;      /* statement */
  INSERT INTO Table_2 VALUES (v1,v2,v3,v4,v5); /* statement */
END

Caution

Don’t get confused by the similarity to a <Column definition>. A variable definition can contain ONLY a <data type> and (optionally) a DEFAULT clause. It cannot contain a <Domain name>, a <Constraint> or a COLLATE clause.

In our example we defined five variables: v1, v2, v3, v4, v5. BEGIN ... END defines a “local scope”, which means that (a) these variable names have no meaning outside the compound statement, (b) the values in these variables are not saved when the compound statement ends and (c) the values in these variables are not reset by execution of a ROLLBACK statement, because variables are not part of the database.

The example uses the first four variables as targets in a singleton SELECT statement. It also uses all five variables as sources in an INSERT statement. Variables can be used in all sorts of <value expression>s. Variables are extremely useful for temporary storage, and it’s a wonder that most SQL implementations get along without them. The designers of SQL don’t give us the option of using variables for persistent storage: we’re supposed to use Base tables for that.

Assignment Statements¶

Assignment statements begin with the <keyword> SET – but don’t call them “SET statements”, to avoid confusion with non-PSM statements that also begin with SET. Assignment statements are syntactically similar to the SET clauses used in UPDATE statements. Here is the required syntax:

SET
   <target>     /* where the value goes to; usually a variable */
   =
   <source>     /* where the value comes from; an expression */

In theory the <target> doesn’t have to be a variable – it could be a parameter or a “host variable” – but normal programs will take the form “<variable> = <expression>”. Here are some examples:

SET v1 = 5

SET v1 = (v2+7)/5

SET v1 = NULL

SET v1 = column_1

Cursors¶

A slightly more complicated form of a compound statement has one more optional clause: a Cursor declaration list. The required syntax is:

BEGIN [ [ NOT ] ATOMIC ]
  [ <variable declaration>; ... ]
  [ DECLARE CURSOR statement; ... ]      /* Cursor-declaration list */
  [ <SQL statement>; ... ]
END

The mechanics of Cursors are the same for PSM as they are for embedded SQL and for SQL/CLI. Here’s an example:

BEGIN
  DECLARE v1 SMALLINT;             /* variable-declaration */
  DECLARE cc CURSOR FOR
     SELECT column_1 FROM Table_1; /* Cursor-declaration */
  OPEN cc;                         /* statement */
  FETCH cc INTO v1;                /* statement */
  CLOSE cc;                        /* statement */
  INSERT INTO Table_2 VALUES (v1); /* statement */
END

Objects that you declare in a compound statement have “local scope”, so the <Cursor name> in this example – cc – can only be used by SQL statements within the BEGIN ... END. The example could be replaced with this SQL statement:

INSERT INTO Table_2 SELECT column1 FROM Table_1;

if there is only one row in TABLE_1.

Conditions¶

A slightly more complicated form of a compound statement changes the optional variable declaration clause: instead of a variable declaration list, BEGIN ... END actually allows a variable or condition declaration list, so that you can declare conditions as well as variables. The required syntax is:

BEGIN [ [ NOT ] ATOMIC ]
  [ <variable | condition declaration>; ... ] /* variable-or-condition
declaration list */
  [ DECLARE CURSOR statement; ... ]
  [ <SQL statement>; ... ]
END

   <condition declaration> ::=
      DECLARE <condition name> CONDITION [ FOR <sqlstate value> ]

Quick review: An SQLSTATE value is a 5-character status code string. Upon completion of any SQL statement, there will be a status code in SQLSTATE, which is the main diagnostic field. Typical values are '01006' (warning-privilege not revoked), '22012' (data exception-division by zero), '42000' (syntax error or access violation). You’ll find a complete list of SQLSTATE values in our chapter on SQL/CLI diagnostics.

Here’s an example of the latest form of BEGIN ... END:

BEGIN ATOMIC
  DECLARE v1 SMALLINT;                          /* variable-declaration */
  DECLARE warning_revoke CONDITION FOR '01006'; /* condition declaration */
  DECLARE divide_by_zero CONDITION FOR '22012'; /* condition declaration */
  DECLARE syntax_error CONDITION FOR '42000';   /* condition declaration */
  DECLARE cc CURSOR FOR
     SELECT column_1 FROM Table_1;              /* Cursor-declaration */
  OPEN cc;                                      /* statement */
  FETCH cc INTO v1;                             /* statement */
  CLOSE cc;                                     /* statement */
  INSERT INTO Table_2 VALUES (v1);              /* statement */
  INSERT INTO Table_1 VALUES (0);               /* statement */
  INSERT INTO Table_2 VALUES (1);               /* statement */
END

In this example, we have simply given condition names to three of the possible SQLSTATE values.

Handlers¶

A slightly more complicated form of a compound statement adds another optional clause: a handler declaration list. The required syntax is:

BEGIN [ [ NOT ] ATOMIC ]
  [ <variable | condition declaration>; ... ]
  [ DECLARE CURSOR statement; ... ]
  [ <handler declaration>; ...]               /* handler-declaration list */
  [ <SQL statement>; ... ]
END

  <handler declaration> ::=
  DECLARE <handler type> HANDLER FOR <condition value list> <handler action>

     <handler type> ::= {CONTINUE | EXIT | UNDO }

     <handler action> ::= <SQL statement>

     <condition value list> ::= <condition value> [ {,<condition value>}... ]

         <condition value> ::=
         <sqlstate value>|
         <condition name>|
         SQLEXCEPTION |
         SQLWARNING |
         NOT FOUND

The following example contains three handlers. The first is for an SQLSTATE value, the second is for a condition name and the third is for any warning (i.e.: any SQLSTATE in class '01').

BEGIN
  DECLARE constraint_error CONDITION FOR '23000';/* condition declaration */
  DECLARE v1 CHAR(5) DEFAULT 'Okay!';            /* variable declaration */
  DECLARE CONTINUE HANDLER FOR '22003'           /* handler declaration */
     SET v1 = 'Ovflw';
  DECLARE CONTINUE HANDLER FOR constraint_error  /* handler declaration */
     SET v1 = 'c-err';
  DECLARE CONTINUE HANDLER FOR SQLWARNING        /* handler declaration */
     SET v1 = '?????';
  INSERT INTO Table_1 VALUES (99999);            /* statement */
  INSERT INTO Table_2 VALUES (v1);               /* statement */
END

To see the effect of these handlers, consider what will happen with the SQL statement:

INSERT INTO Table_1 VALUES (99999);

If this SQL statement fails due to overflow, then variable v1 gets 'Ovflw'; if it fails due to an integrity Constraint violation, then variable v1 gets 'c-err'; if it succeeds but there is some warning, then variable v1 gets '?????'. But, regardless, play continues because all the handlers are CONTINUE handlers. So the second INSERT statement will put in one of the values 'Ovflw', 'c-err', '?????' or 'Okay!' ('Okay!' is the default value for v1 so this is what goes in if the result of the first INSERT is success with no warnings).

What if exception '42000' happens? That would be an “unhandled exception” since we did not define a handler for exception '42000'. The result would be that the second INSERT is not attempted – the whole compound statement fails.

The following chart compares the exception-handling features of embedded SQL, the CLI and the PSM.

	EMBEDDED SQL	CLI	PSM
method of declaration	EXEC SQL WHENEVER	none	handler-declaration
what happens	GOTO	N/A	any SQL statement
handles SQLNOTFOUND?	yes	N/A	yes
handles SQLERROR?	yes	N/A	yes
handles SQLWARNING?	yes	N/A	yes
handles specific status codes?	no	N/A	yes

Among the SQL statements that a handler can execute are two new special ones: the SIGNAL statement and the RESIGNAL statement. These SQL statements affect the diagnostics area.

Labels¶

We’re still not done with the BEGIN ... END statement. The final form of a compound statement adds two more optional clauses: a beginning label and an end label. The required syntax for a compound statement is:

[ <beginning_label>: ]
BEGIN [ [ NOT ] ATOMIC ]
  [ <variable | condition declaration>; ... ]
  [ DECLARE CURSOR statement; ... ]
  [ <handler declaration>; ...]
  [ <SQL statement>; ... ]
END [ <end_label> ]

  <beginning_label> ::= <identifier>

  <end_label> ::= <identifier>

If you add labels to your compound statement, they should be equivalent (if both are specified). Labels are useful as referents for various control statements, which we will discuss later. Here’s an example:

full_blown_example:                      /* beginning_label */
BEGIN ATOMIC                             /* compound statement is atomic */
  DECLARE v1 INTEGER DEFAULT 0;          /* variable declaration */
  DECLARE c1 CONDITION FOR '01000';      /* condition declaration */
  DECLARE CONTINUE HANDLER FOR SQLERROR  /* handler declaration */
    SET v1 = 1;                          /* assignment statement */
  INSERT INTO Table_1 VALUES (0);        /* statement */
  INSERT INTO Table_2 VALUES (v1);       /* statement */
END full_blown_example                   /* end_label */

This is our final version of BEGIN .. END. It looks quite imposing. That’s because MOST SYNTACTIC ITEMS ARE LOCAL TO THE COMPOUND STATEMENT. Therefore everything is within the compound statement and, by contrast, the Module definition is trivial.

SIGNAL Statement ¶

The SIGNAL statement is used to clear the diagnostics area. The required syntax for the SIGNAL statement is:

SIGNAL <condition name or sqlstate value>
   SET <signal information item list>

   <signal information item list> ::=
   <signal information item> [ {,<signal information item>}... ]

      <signal information item> ::=
      <condition information item name> = <simple value specification>

The SIGNAL statement clears every record in the diagnostics area. The end result is a record containing the passed condition name or sqlstate value. If you include the optional SET clause, your DBMS effectively executes:

RESIGNAL <signal information item list>;

Note: You’ll find the list of <condition information item name>s in our chapter on embedded SQL – see the GET DIAGNOSTICS statement.

RESIGNAL Statement ¶

The RESIGNAL statement is used to pass conditions on to another handler. The required syntax for the RESIGNAL statement is:

RESIGNAL [ <condition name or sqlstate value> ]
   SET <signal information item list>

The RESIGNAL statement passes the given exception “up the line” to the next appropriate handler (since compound statements may be embedded in compound statements, this next appropriate handler will usually be in some outside context). The current diagnostics area remains unchanged, but – if the optional [<condition name or sqlstate value>] clause is specified – there will be one more diagnostics record, containing this new value. If you include the optional SET clause, the <condition information item name> field in the first condition area in the diagnostics area is changed to the value indicated.

Program Control¶

Essential SQL has almost nothing that can control the program flow (except for the CALL and RETURN statements which are associated with SQL routines). By contrast, a DBMS with PSM support will allow eight control statements. Of these, seven are similar to statements which appear in other languages. The eighth, FOR, depends on Objects which are unique to the SQL environment. Here’s a list of these statements:

CASE – Switch depending on condition.
IF – If (condition) do.
ITERATE – Restart loop.
LOOP – Do statement(s) repeatedly.
LEAVE – Break out of a loop or block.
WHILE – Repeat statement(s) as long as condition is true.
REPEAT – Repeat statement(s) until condition is true.
FOR – Cursor-based FETCH loop.

CASE Statement¶

The CASE statement is useful for switching between possible execution paths. There are two forms – one contains search conditions, the other contains value expressions. The required syntax for the CASE statement is:

searched CASE statement ::=
CASE
  WHEN <search condition> THEN <statement>(s)
  [ WHEN <search condition> THEN <statement>(s) ... ]
  [ ELSE <statement>(s) ]
END CASE

simple CASE statement ::=
CASE <case value>
   WHEN <when value> THEN <statement>(s)
   [ WHEN <when value> THEN <statement>(s) ... ]
   [ ELSE <statement>(s) ]
END CASE

A “simple CASE statement” is merely a shorthand, and may be replaced by a “searched CASE statement” which has the form: “CASE WHEN <when value> = <case value> …”. Thus, the following examples, showing a searched CASE statement on the left and a simple CASE statement on the right, are exactly equivalent:

CASE                                CASE parameter_value
  WHEN parameter_value = 15           WHEN 15
   THEN INSERT INTO t VALUES (15);     THEN INSERT INTO t VALUES (15);
  WHEN parameter_value = 17           WHEN 17
   THEN INSERT INTO t VALUES (17);     THEN INSERT INTO t VALUES (17);
  ELSE INSERT INTO t VALUES (0);      ELSE INSERT INTO t VALUES (0);
END CASE                            END CASE

When executing a CASE statement, the DBMS goes through the WHEN clauses from top to bottom, looking for a TRUE condition. If it finds one, it executes the statement(s) after THEN, and the CASE terminates. If it finds none, it executes the statements(s) after ELSE – or, if there is no ELSE, returns this SQLSTATE error: 20000 "case not found for case statement". For the above example, then, if the value of parameter_value is 5, then the DBMS will execute this SQL statement:

INSERT INTO t VALUES (0);

Caution

The syntax for the CASE statement is somewhat different from the syntax for the SQL CASE expression (see our chapter on simple search conditions). In particular, the CASE statement has no equivalent for the ELSE NULL clause, and the terminator is END CASE rather than END.

IF Statement¶

The IF statement is useful for simple “if (x) then (do this)” situations. The required syntax for the IF statement is:

IF <search condition> THEN <SQL statement>(s)
   ELSEIF <search condition> THEN <SQL statement>(s)
   ELSE <SQL statement>(s)
END IF

Here’s an example:

IF
 5=5 THEN UPDATE Table_1 SET column_1 = column_1 + 1;
END IF

In this example, the search condition is TRUE, so the UPDATE statement will be executed. If the search condition had been FALSE or UNKNOWN, then the UPDATE statement would not have been executed.

LOOP Statement¶

The LOOP statement is useful for repeated execution of SQL statements. The required syntax for the LOOP statement is:

[ <beginning_label>: ]
LOOP
   <SQL statement>(s)
END LOOP [ <end_label> ]

The SQL statements between LOOP and END LOOP are repeated until the loop finishes. The <beginning_label> and the <end_label> must be equivalent, if you use them both. Here’s an example:

LOOP
   SET x = x + 1;
END LOOP

This example shows an infinite loop. The usual way to exit from a loop is with the LEAVE statement.

LEAVE Statement¶

The LEAVE statement is useful for exiting a block or for exiting a loop. The required syntax for the LEAVE statement is:

LEAVE <statement_label>

Here’s an example:

beginning_label:
LOOP
  SET x = x + 1;
  IF x > 1000 THEN LEAVE beginning_label; END IF;
END LOOP beginning_label

In this example, the loop will be exited once the value of x passes 1000.

WHILE statement¶

The WHILE statement is useful for repeated execution of SQL statements, with a built-in equivalent to the LEAVE statement. The required syntax for the WHILE statement is:

[ <beginning_label>: ]
WHILE <search condition> DO
   <SQL statement>(s)
END WHILE [ <end_label> ]

As long as the <search condition> is TRUE, the SQL statements between WHILE and END WHILE are repeatedly executed. The <beginning_label> and the <end_label> must be equivalent, if you use them both. Here’s an example:

WHILE x <= 1000 DO
   SET x = x + 1;
END WHILE

This example will loop, incrementing x, until x <= 1000 is either FALSE or UNKNOWN. If the <search condition> is FALSE or UNKNOWN when the loop begins, then nothing happens.

REPEAT Statement¶

The REPEAT statement is much like the WHILE statement, except that the condition is tested after the execution of the SQL statement(s). The required syntax for the REPEAT statement is:

[ <beginning_label>: ]
REPEAT
   <SQL statement>(s) UNTIL <search condition>
END REPEAT [ <end_label> ]

As long as the <search condition> is FALSE or UNKNOWN, the SQL statements between REPEAT and END REPEAT are repeatedly executed. The <beginning_label> and the <end_label> must be equivalent, if you use them both. Here’s an example:

REPEAT
   DELETE FROM Table_1 WHERE column_1 = x;
   SET x = x + 1;
   UNTIL x > 5
END REPEAT

In this example, the UPDATE statement will be repeated until x is greater than 5 – that is, the loop will repeat until after the condition is TRUE.

Caution

The example is an infinite loop if the initial value of x is NULL.

FOR Statement¶

The FOR statement is useful for simplified FETCH loops. Execution takes place for each row of a result set. The required syntax for the FOR statement is:

[ <beginning_label>: ]
FOR <loop variable name> AS [ <Cursor name>
   [ {ASENSITIVE | INSENSITIVE | SENSITIVE} ] CURSOR FOR ]
   <query expression> [ ORDER BY clause ] [ updatability clause ]
DO
   <SQL statement>(s)
END FOR [ <end_label> ]

Here’s an example:

FOR x AS Cursor_1 CURSOR FOR
   SELECT name, address_1, address_2 FROM Addresses
DO
   UPDATE Addresses SET address_1 = '' WHERE CURRENT OF Cursor_1;
END FOR

Effectively, a Cursor is opened when the loop begins, fetched for each row of the result set, and closed when the loop ends. In this example, the UPDATE statement is executed for each fetched row before the next iteration. SQL’s FOR loop is different, in style and meaning, from FOR loops in other languages.

ITERATE Statement¶

The ITERATE statement is useful for “re-starting”: going back to the beginning of the list of statements inside a loop, and proceeding with the next iteration of the loop. The required syntax for the ITERATE statement is:

ITERATE <statement_label>

The ITERATE statement can appear only within an “iterated SQL statement” – that is, within LOOP, WHILE, REPEAT or FOR). The <statement_label> must be the <beginning_label> of the iterated SQL statement. If the iteration condition for the iterated SQL statement is TRUE, or if the statement doesn’t have an iteration condition, ITERATE causes the next iteration of the loop to start. If the iteration condition is FALSE or UNKNOWN, ITERATE causes the loop to end. Here’s an example:

beginning_of_while:
WHILE (color_of_moon_in_june = 'blue') DO
   ...
   SET spot_remover = 'active';
   IF (birthday_test() IS UNKNOWN)
     THEN ITERATE beginning_of_while;
   END IF
   SET checkout_status = 0;
END WHILE

Should everything be in SQL?¶

PSM is an extension package which makes SQL3 a reasonably complete language. There are still some things you can’t do (such as disk or screen I/O), but anybody could write external-routine libraries which would plug the remaining gaps.

So what?

For several years, programmers have written applications in “host languages” and invoked SQL statements either via embedded SQL or via the CLI. By now there is an awful lot of legacy code in those host languages. It has to be expected, too, that there are good host-language optimizers out there – don’t bother pitting C and SQL head-to-head with a “Sieve of Eratosthenes” benchmark. The SQL code would lose.

On the other hand, we could say that:

Yes, SQL optimizers are inferior for low-level benchmarks, but they’re better at the high level – and you’d be helping SQL optimizers if you could pass them groups of SQL statements, rather than individual SQL statements.
A lot of programming effort is spent solving the “impedance mismatch” problem – the fact that host languages don’t store data the SQL way, or process sets the SQL way, or have the same ideas of access control. With Modules inside the SQL environment, DBMSs can act in a consistent way across platforms.
Remote Data Access is feasible with SQL, but not with a host language.

In the end, the world’s SQL developers will decide which arguments are the most convincing. At the moment, PSM is not (yet) the popular way to go.

Dialects ¶

PSM’s features are vaguely similar to Oracle’s PL/SQL, which also has: BEGIN ... END, LOOP, WHILE, and (using different keywords) assignment statements and handlers. Informix SQL has a FOREACH statement, which does the same thing as the standard FOR statement.

Even if a vendor does not support any form of PSM, you may find that some of the above-described features have been added individually into essential SQL. For example, a DBMS which fully supports SQL routines will probably allow compound statements too, in at least a limited way.