5 Lexical elements

This Clause modifies Clause 5, “Lexical elements”, in ISO/IEC 9075-2.

5.1 <token> and <separator>

Function

Specify lexical units (tokens and separators) that participate in SQL language.

Format
<non-reserved word> ::=
    !! All alternatives from ISO/IEC 9075-2

  | CONDITION_IDENTIFIER

  | EXIT

  | STACKED

  | UNDO
<reserved word> ::=
    !! All alternatives from ISO/IEC 9075-2

  | DO

  | ELSEIF

  | HANDLER

  | IF | ITERATE

  | LEAVE | LOOP

  | REPEAT | RESIGNAL

  | SIGNAL

  | UNTIL

  | WHILE

5.2 Names and identifiers

Function

Specify names.

Format
<SQL-server module name> ::=
  <schema qualified name>
<SQL variable name> ::=
  <identifier>
<condition name> ::=
  <identifier>

6 Scalar expressions

6.1 <value specification> and <target specification>

Function

Specify one or more values, host parameters, SQL parameters, dynamic parameters, host variables, or SQL variables.

Format
<general value specification> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL variable reference>
<simple value specification> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL variable reference>
<target specification> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL variable reference>
<simple target specification> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL variable reference>
<target array reference> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL variable reference>

6.4 <SQL variable reference>

Function

Reference an SQL variable.

Format
<SQL variable reference> ::=
  <basic identifier chain>

8 Persistent Stored Modules (SQL/PSM)

8.2 <sqlstate value>

Function

Specify an SQLSTATE value.

Format
<sqlstate value> ::=
  SQLSTATE [ VALUE ] <character string literal>

9 Schema definition and manipulation

9.1 <schema definition>

Function

Define a schema.

Format
<schema element> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL-server module definition>

9.19 <trigger definition>

Function

Defined triggered SQL-statements.

Format
<triggered SQL statement> ::=
  <SQL procedure statement>
     NOTE 12 — The preceding production defining <triggered SQL statement> completely supersedes the definition in [ISO9075-2].

9.21 <SQL-server module definition>

Function

Define an SQL-server module.

<SQL-server module character set specification> ::=
  NAMES ARE <character set specification>
<SQL-server module schema clause> ::=
  SCHEMA <default schema name>
<default schema name> ::=
  <schema name>
<SQL-server module path specification> ::=
  <path specification>
<SQL-server module contents> ::=
  <SQL-invoked routine> <semicolon>

9.22 <drop module statement>

Function

Destroy an SQL-server module.

Format
<drop module statement> ::=
  DROP MODULE <SQL-server module name> <drop behavior>

9.24 <SQL-invoked routine>

Function

Define an SQL-invoked routine.

Format
<SQL-invoked routine> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <module routine>
<module routine> ::=
    <module procedure>
  | <module function>
<module procedure> ::=
  [ DECLARE ] <SQL-invoked procedure>
<module function> ::=
  [ DECLARE ] <SQL-invoked function>

10 Access control

10.2 <privileges>

This Subclause modifies Subclause 12.3, “<privileges>”, in ISO/IEC 9075-2.

Function

Specify privileges.

Format
<object name> ::=
    !! All alternatives from ISO/IEC 9075-2
  | MODULE <SQL-server module name>

11 SQL-client modules

11.2 <SQL procedure statement>

Function

Define all of the SQL-statements that are <SQL procedure statement>s.

Format
<SQL schema definition statement> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <SQL-server module definition>
<SQL schema manipulation statement> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <drop module statement>
<SQL diagnostics statement> ::=
    !! All alternatives from ISO/IEC 9075-2
  | <signal statement>
  | <resignal statement>

14 Control statements

14.1 <compound statement>

Function

Specify a statement that groups other statements together.

<beginning label> ::=
  <statement label>
<ending label> ::=
  <statement label>
<statement label> ::=
  <identifier>
<local declaration list> ::=
  <terminated local declaration>...
<terminated local declaration> ::=
  <local declaration> <semicolon>
<local cursor declaration list> ::=
  <terminated local cursor declaration>...
<terminated local cursor declaration> ::=
  <declare cursor> <semicolon>
<local handler declaration list> ::=
  <terminated local handler declaration>...
<terminated local handler declaration> ::=
  <handler declaration> <semicolon>
<SQL statement list> ::=
  <terminated SQL statement>...
<terminated SQL statement> ::=
  <SQL procedure statement> <semicolon>

14.2 <handler declaration>

Function

Associate a handler with exception or completion conditions to be handled in a module or compound statement.

Format
<handler declaration> ::=
  DECLARE <handler type> HANDLER FOR <condition value list> <handler action>
<handler type> ::=
    CONTINUE
  | EXIT
  | UNDO
<handler action> ::=
  <SQL procedure statement>
<condition value list> ::=
  <condition value> [ { <comma> <condition value> }... ]
<condition value> ::=
    <sqlstate value>
  | <condition name>
  | SQLEXCEPTION
  | SQLWARNING
  | NOT FOUND

14.3 <condition declaration>

Function

Declare a condition name and an optional corresponding SQLSTATE value.

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

14.4 <SQL variable declaration>

Function

Declare one or more variables.

Format
<SQL variable declaration> ::=
  DECLARE <SQL variable name list> <data type> [ <default clause> ]
<SQL variable name list> ::=
  <SQL variable name> [ { <comma> <SQL variable name> }... ]

14.5 <assignment statement>

Function

Assign a value to an SQL variable, SQL parameter, host parameter, or host variable.

<multiple variable assignment> ::=
  SET <assignment target list> <equals operator> <assigned row>
<singleton variable assignment> ::=
  SET <assignment target> <equals operator> <assignment source>
<modified field reference> ::=
  <modified field target> <period> <field name>

14.6 <case statement>

Function

Provide conditional execution based on truth of <search condition>s or on equality of operands.

Format
<simple case statement> ::=
  CASE <case operand>
      <simple case statement when clause>...
      [ <case statement else clause> ]
      END CASE
<searched case statement> ::=
  CASE <searched case statement when clause>...
      [ <case statement else clause> ]
      END CASE
<simple case statement when clause> ::=
  WHEN <when operand list>
      THEN <SQL statement list>
<searched case statement when clause> ::=
  WHEN <search condition>
      THEN <SQL statement list>
<case statement else clause> ::=
  ELSE <SQL statement list>

14.7 <if statement>

Function

Provide conditional execution based on the truth value of a condition.

<if statement then clause> ::=
  THEN <SQL statement list>
<if statement elseif clause> ::=
  ELSEIF <search condition> THEN <SQL statement list>
<if statement else clause> ::=
  ELSE <SQL statement list>

14.8 <iterate statement>

Function

Terminate the execution of an iteration of an iterated SQL-statement.

Format
<iterate statement> ::=
  ITERATE <statement label>

14.9 <leave statement>

Function

Continue execution by leaving a labeled statement.

Format
<leave statement> ::=
  LEAVE <statement label>

14.10 <loop statement>

Function

Repeat the execution of a statement.

Format
<loop statement> ::=
  [ <beginning label> <colon> ]
      LOOP
      <SQL statement list>
      END LOOP [ <ending label> ]

14.11 <while statement>

Function

While a specified condition is True, repeat the execution of a statement.

Format
<while statement> ::=
  [ <beginning label> <colon> ]
      WHILE <search condition> DO
      <SQL statement list>
      END WHILE [ <ending label> ]

14.12 <repeat statement>

Function

Repeat the execution of a statement.

Format
<repeat statement> ::=
  [ <beginning label> <colon> ]
      REPEAT
      <SQL statement list>
      UNTIL <search condition>
      END REPEAT [ <ending label> ]

14.13 <for statement>

Function

Execute a statement for each row of a table.

<for loop variable name> ::=
  <identifier>

17 Diagnostics management

17.1 <get diagnostics statement>

Function

Get exception or completion condition information from the diagnostics area.

Format
<get diagnostics statement> ::=
  GET [ <which area> ] DIAGNOSTICS <SQL diagnostics information>
<which area> ::=
    CURRENT
  | STACKED
<condition information item name> ::=
    !! All alternatives from [ISO9075-2]
  | CONDITION_IDENTIFIER

17.2 <signal statement>

Function

Signal an exception condition.

Format
<signal statement> ::=
  SIGNAL <signal value> [ <set signal information> ]
<signal value> ::=
    <condition name>
  | <sqlstate value>
<set signal information> ::=
  SET <signal information item list>
<signal information item list> ::=
  <signal information item> [ { <comma> <signal information item> }... ]

17.3 <resignal statement>

Function

Resignal an exception condition.

Format
<resignal statement> ::=
  RESIGNAL [ <signal value> ] [ <set signal information> ]