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 orAUTHORIZATION
clause of theCREATE 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 aCASCADE
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-basedFETCH
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.