Procedures Stored in the Schema

Procedures Stored in the Schema

In this section, we introduce you to a recent SQL standard called Persistent, Stored Modules (SQL/PSM, or just PSM, or PSM-96). Each commercial DBMS offers a way for the user to store with a database schema some functions or procedures that can be used in SQL queries or other SQL statements. These pieces of code are written in a simple, general-purpose language, and allow us to perform, within the database itself, computations that cannot be expressed in the SQL query language. In this blog, we shall describe the SQL/PSM standard, which captures the major ideas of these facilities, and which should help you understand the language associated with any particular system. In PSM, you define modules, which are collections of function and procedure definitions, temporary relation declarations, and several other optional declarations, here we shall discuss only the functions and procedures of PSM.

Creating PSM Functions and Procedures

The major elements of a procedure declaration are

This form should be familiar from a number of programming languages; it consists of a procedure name, a parenthesized list of parameters, some optional local-variable declarations, and the executable body of code that defines the procedure. A function is defined in almost the same way, except that the keyword FUNCTION is used and there is a return-value type that must be specified. That is, the elements of a function definition are:

The parameters of a procedure are triples of mode-name-type, much like the parameters of ODL methods, which we discussed in Methods in ODL. That is, the parameter name is not only followed by its declared type, as usual in programming languages, but it is preceded by a "mode" which is either IN, OUT, or INOUT. These three keywords indicate that the parameter is input-only, output-only, or both input and output, respectively. IN is the default, and can be omitted. Function parameters, on the other hand, may only be of mode IN. That is, PSM forbids side-effects in functions, so the only way to obtain information from a function is through its return-value. We shall not specify the IN mode for function parameters, although we do so in procedure definitions.

Example 1 : While we have not yet learned the variety of statements that can appear in procedure and function bodies, one kind should not surprise us: an SQL statement. The limitation on these statements is the same as for embedded SQL, as we introduced in Using Shared Variables : only single-row-select statements and cursor-based accesses are permitted as queries. In Figure 1 is a PSM procedure that takes two addresses - an old address and a new address - and changes to the new address the address attribute of every star who lived at the old address.

A procedure to change addresses

Line (1) introduces the procedure and its name, Move. Lines (2) and (3) contain the two parameters, both of which are input parameters whose type is variable-length character strings of length 255. Note that this type is consistent with the type we declared for the attribute address of MovieStar in Simple Table Declarations Figure 1. Lines (4) through (6) are a conventional UPDATE statement. However, notice that the parameter names can be used as if they were constants. Unlike host-language variables, which require a colon prefix when used in SQL (see System Aspects of SQL), parameters and other local variables of PSM procedures and functions require no colon.