Chapter 7: Procedural SQL (PSQL) Statements

Table of Contents

Elements of PSQL

DML Statements with Parameters
Transactions
Module Structure

Stored Procedures

Benefits of Stored Procedures
Types of Stored Procedures
Creating a Stored Procedure
Modifying a Stored Procedure
Deleting a Stored Procedure

Stored Functions

PSQL Blocks

Triggers

Firing Order (Order of Execution)
DML Triggers
Database Triggers
Creating Triggers
Modifying Triggers
Deleting a Trigger

Writing the Body Code

Assignment Statements
DECLARE CURSOR
DECLARE VARIABLE
BEGIN ... END
IF ... THEN ... ELSE
WHILE ... DO
LEAVE
EXIT
SUSPEND
EXECUTE STATEMENT
FOR SELECT
FOR EXECUTE STATEMENT
OPEN
FETCH
CLOSE
IN AUTONOMOUS TRANSACTION
POST_EVENT

Trapping and Handling Errors

System Exceptions
Custom Exceptions
EXCEPTION
WHEN ... DO

	REVIEW STATUS
	All sections from this point forward to the end of the chapter are awaiting technical and editorial review.

Procedural SQL (PSQL) is a procedural extension of SQL. This language subset is used for writing stored procedures, 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 slight modifications to syntax in some cases.

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:

Used for: Changing the terminator character[s] to avoid conflict with the terminator character in PSQL statements

Available in: ISQL only

Syntax:

SET TERM <new_terminator> <old_terminator>

Table 7.1. SET TERM Parameters

Argument	Description
new_terminator	New terminator
old_terminator	Old terminator

When you write your triggers and stored procedures 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 some other character or short string, to avoid conflict 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.

Example: Changing 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 */