SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 320

The typical pattern for using a cursor is as follows:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH07-04 *** */
DECLARE SQLCursor CURSOR FOR (SELECT * FROM CUSTOMER);
/* Opening SQLcursor executes (SELECT * FROM CUSTOMER) */
OPEN SQLcursor;
MOVE SQLcursor to first row of (SELECT * FROM CUSTOMER);
WHILE (SQLcursor not past the last row) LOOP
SET customerLastName = LastName;
. . . other statements . . .
REPEAT LOOP UNTIL DONE;
CLOSE SQLcursor
. . . other processing . . .
In this way, the rows of an SQL SELECT are processed one at a time. You will see many
examples of these techniques and others like them in the chapters that follow.
A typical and useful example of embedding SQL statements in an application is the use of
SQL in Web database applications. We will discuss this topic in detail in Chapter 11, where we
will provide several examples of SQL statements embedded in the PHP scripting language. For
now, try to gain an intuitive understanding of how SQL is embedded in program code as we
discuss how SQL application code is embedded within databases themselves.
SQL/Persistent Stored Modules (SQL/PSM)
As discussed previously in this chapter, each DBMS product has its own variant or extension
of SQL, including features that allow SQL to function similarly to a procedural program-
ming language. The ANSI/ISO standard refers to these as SQL/Persistent Stored Modules
(SQL/PSM). Microsoft SQL Server calls its version of SQL Transact-SQL (T-SQL) , and Oracle
Database calls its version of SQL Procedural Language/SQL (PL/SQL) . The MySQL variant
also includes SQL/PSM components, but it has no special name and is just called SQL in the
MySQL documentation.
SQL/PSM provides the program variables and cursor functionality previously discussed. It
also includes control-of-flow language such as BEGIN . . . END blocks, IF . . . THEN . . . ELSE logic
structures, and LOOPs, as well as the ability to provide usable output to users.
The most important feature of SQL/PSM, however, is that it allows the code that imple-
ments these features in a database to be contained in that database. The SQL code can be
written as one of three module types: user-defined functions, triggers, and stored procedures.
Thus the name: Persistent —the code remains available for use over time— Stored —the code is
stored for reuse in the database— Modules —the code is written as a user-defined function, trig-
ger, or stored procedure.
Using SQL User-Defined Functions
A user-defined function (also known as a stored function ) is a stored set of SQL statements
that:
● is called by name from another SQL statement,
● may have input parameters passed to it by the calling SQL statement, and
● returns an output value to the SQL statement that called the function.
The logical process flow of a user-defined function is illustrated in Figure 7-20. SQL/PSM user-
defined functions are very similar to the SQL built-in functions (COUNT, SUM, AVE, MAX, and