System Aspects of SQL

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.

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

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

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

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:

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