Chapter 7. Procedural SQL (PSQL) Statements

Procedural SQL (PSQL) is a procedural extension of SQL. This language subset is used for writing PSQL modules: stored procedures, stored functions, triggers, and PSQL blocks.

PSQL provides all the basic constructs of traditional structured programming languages, and also includes DML statements (SELECT, INSERT, UPDATE, DELETE, etc.), with a slightly modified syntax in some cases.

7.1. Elements of PSQL

A PSQL module may contain declarations of local variables, subroutines and cursors, assignments, conditional statements, loops, statements for raising custom exceptions, error handling and sending messages (events) to client applications. DML triggers have access to special context variables, two records that store, respectively, the NEW values for all columns during insert and update activity, and the OLD values during update and delete work, and three Boolean variables — INSERTING, UPDATING and DELETING — to determine the event that fired the trigger.

Statements that modify metadata (DDL) are not available in PSQL.

7.1.1. DML Statements with Parameters

If DML statements (SELECT, INSERT, UPDATE, DELETE, etc.) in the body of a module (procedure, function, trigger or block) use parameters, only named parameters can be used. If DML statements contain named parameters, then they must be previously declared as local variables using DECLARE [VARIABLE] in the declaration section of the module, or as input or output variables in the module header.

When a DML statement with parameters is included in PSQL code, the parameter name must be prefixed by a colon (:) in most situations. The colon is optional in statement syntax that is specific to PSQL, such as assignments and conditionals and the INTO clause. The colon prefix on parameters is not required when calling stored procedures from within another PSQL module.

7.1.2. Transactions

Stored procedures and functions (including those defined in packages) are executed in the context of the transaction in which they are called. Triggers are executed as an intrinsic part of the operation of the DML statement: thus, their execution is within the same transaction context as the statement itself. Individual transactions are launched for database event triggers fired on connect or disconnect.

Statements that start and end transactions are not available in PSQL, but it is possible to run a statement or a block of statements in an autonomous transaction.

7.1.3. Module Structure

PSQL code modules consist of a header and a body. The DDL statements for defining them are complex statements; that is, they consist of a single statement that encloses blocks of multiple statements. These statements begin with a verb (CREATE, ALTER, DROP, RECREATE, CREATE OR ALTER, or EXECUTE BLOCK) and end with the last END statement of the body.

7.1.3.1. The Module Header

The header provides the module name and defines any input and output parameters or — for functions — the return type. Stored procedures and PSQL blocks may have input and output parameters. Functions may have input parameters and must have a scalar return type. Triggers do not have either input or output parameters, but DML triggers do have the NEW and OLD records, and INSERTING, UPDATING and DELETING variables.

The header of a trigger indicates the DML event (insert, update or delete, or a combination) or DDL or database event and the phase of operation (BEFORE or AFTER that event) that will cause it to fire.

7.1.3.2. The Module Body

The module body is either a PSQL module body, or an external module body. PSQL blocks can only have a PSQL module body.

Syntax of a Module Body

   |<module-body> ::=
   |  <psql-module-body> | <external-module-body>
   | 
   |<psql-module-body> ::=
   |  AS
   |    [<forward-declarations>]
   |    [<declarations>]
   |  BEGIN
   |    [<PSQL_statements>]
   |  END
   | 
   |<external-module-body> ::=
   |  EXTERNAL [NAME <extname>] ENGINE engine
   |  [AS '<extbody>']
   | 
   |<forward-declarations> ::=
   |  <forward-declare-item> [<forward-declare-item> ...]
   | 
   |<declarations> ::=
   |  <declare-item> [<declare-item> ...]
   | 
   |<forward-declare-item> ::=
   |    <subfunc-forward>
   |  | <subproc-forward>
   | 
   |<declare-item> ::=
   |    <declare-var>
   |  | <declare-cursor>
   |  | <subfunc-def>
   |  | <subproc-def>
   | 
   |<extname> ::=
   |  '<module-name>!<routine-name>[!<misc-info>]'
   | 
   |<declare-var> ::=
   |  !! See DECLARE VARIABLE !!
   | 
   |<declare-cursor> ::=
   |  !! See DECLARE .. CURSOR !!
   | 
   |<subfunc-forward>, <subfunc-def> ::=
   |  !! See DECLARE FUNCTION !!
   | 
   |<subproc-forward>, <subproc-def> ::=
   |  !! See DECLARE PROCEDURE !!

Table 7.1. Module Body Parameters

Parameter	Description
declarations	Section for declaring local variables, named cursors, and subroutines
PSQL_statements	Procedural SQL statements. Some PSQL statements may not be valid in all types of PSQL. For example, `RETURN <value>;` is only valid in functions.
subfunc-forward	Sub-function forward declaration
subproc-forward	Sub-procedure forward declaration
declare_var	Local variable declaration
declare_cursor	Named cursor declaration
subfunc-def	Sub-function declaration
subproc-def	Sub-procedure declaration
extname	String identifying the external procedure
engine	String identifying the UDR engine
extbody	External procedure body. A string literal that can be used by UDRs for various purposes.
module-name	The name of the module that contains the procedure
routine-name	The internal name of the procedure inside the external module
misc-info	Optional string that is passed to the procedure in the external module

7.1.3.2.1. The PSQL Module Body

The PSQL module body starts with an optional section that declares variables and subroutines, followed by a block of statements that run in a logical sequence, like a program. A block of statements — or compound statement — is enclosed by the BEGIN and END keywords, and is executed as a single unit of code. The main BEGIN…END block may contain any number of other BEGIN…END blocks, both embedded and sequential. Blocks can be nested to a maximum depth of 512 blocks. All statements except BEGIN and END are terminated by semicolons (;). No other character is valid for use as a terminator for PSQL statements.

Switching the Terminator in isql

Here we digress a little, to explain how to switch the terminator character in the isql utility to make it possible to define PSQL modules in that environment without conflicting with isql itself, which uses the same character, semicolon (;), as its own statement terminator.

isql Command `SET TERM`

Sets the terminator character(s) to avoid conflict with the terminator character in PSQL statements

Available inISQL only

Syntax

  |SET TERM new_terminator old_terminator

Table 7.2. SET TERM Parameters

Argument	Description
new_terminator	New terminator
old_terminator	Old terminator

When you write your triggers, stored procedures, stored functions or PSQL blocks in isql — either in the interactive interface or in scripts — running a SET TERM statement is needed to switch the normal isql statement terminator from the semicolon to another character or short string, to avoid conflicts with the non-changeable semicolon terminator in PSQL. The switch to an alternative terminator needs to be done before you begin defining PSQL objects or running your scripts.

The alternative terminator can be any string of characters except for a space, an apostrophe or the current terminator character(s). Any letter character(s) used will be case-sensitive.

ExampleChanging the default semicolon to ^ (caret) and using it to submit a stored procedure definition: character as an alternative terminator character:

   |SET TERM ^;
   | 
   |CREATE OR ALTER PROCEDURE SHIP_ORDER (
   |  PO_NUM CHAR(8))
   |AS
   |BEGIN
   |  /* Stored procedure body */
   |END^
   | 
   |/* Other stored procedures and triggers */
   | 
   |SET TERM ;^
   | 
   |/* Other DDL statements */

7.1.3.2.2. The External Module Body

The external module body specifies the UDR engine used to execute the external module, and optionally specifies the name of the UDR routine to call (<extname>) and/or a string (<extbody>) with UDR-specific semantics.

Configuration of external modules and UDR engines is not covered further in this Language Reference. Consult the documentation of a specific UDR engine for details.

Prev	Up	Next
6.8. `EXECUTE BLOCK`	Home	7.2. Stored Procedures

isql Command SET TERM

isql Command `SET TERM`