Latest Posts

Queries in PSM

There are many ways that select-from-where queries are used in PSM. 1. Subqueries can be used in conditions, or in general, any place a subquery is legal in SQL. We saw two examples of subqueries in lines (3) and (6) of "Branching Statements" Figure 2, for instance.

Branching Statements

For our first complicated PSM statement type, let us examine the if-statement. The form is only a little strange; it is different from C or similar languages in that:

Some Simple Statement Forms in PSM

Let us begin with a collection of statement forms that are easy to master. 1. The call-statement: The form of a procedure call is:

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

Dynamic SQL

Our model of SQL embedded in a host language has been that of specific SQL queries and commands within a host-language program. An alternative style

Scrolling Cursors

Cursors give us a choice of how we move through the tuples of the relation. The default, and most common choice is to start at the beginning and fetch the tuples in order, until the end. On the other hand, there are other orders in which tuples may be fetched, and tuples could be

Protecting Against Concurrent Updates

Assume that as we observe the net worths of movie executives using the function worthRanges of Cursors Figure 1, some other process is modifying the underlying MovieExec relation. We shall have more to say about a number of processes accessing a single database at the

Modifications by Cursor

When a cursor ranges over the tuples of a base table (i.e., a relation that is stored in the database, rather than a view or a relation constructed by a query), then one can not only read and process the value of each tuple, but one can update or delete tuples. The syntax of


The most handy way to connect SQL queries to a host language is with a cursor that runs through the tuples of a relation. This relation can be a stored table, or it can be something that is created by a query. To create and use a cursor, we need the following statements: 1. A cursor declaration. The simplest form of a cursor declaration consists of:

Using Shared Variables

A shared variable can be used in SQL statements in places where we expect or allow a constant. Remember that shared variables are preceded by a colon when so used. Here is an example in which we use the variables of "System Aspects of SQL" Example 1, as components