Latest Posts

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

Cursors

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

System Aspects of SQL

We now consider the question of how SQL fits into a complete programming environment. In "SQL in a Programming Environment" we see how to embed SQL in programs that are written in an ordinary programming language, such as C. A critical issue is how we move data

Instead-Of Triggers

There is a useful feature of triggers that did not make the SQL-99 standard, but figured into the discussion of the standard and is supported by some commercial systems. This extension allows BEFORE or AFTER to be replaced by INSTEAD OF; the meaning is that when an event

Triggers in SQL

The SQL trigger statement gives the user a number of different options in the event, condition, and action parts. Here are the principal features. 1. The action may be executed either before or after the triggering event. 2. The action can refer to both old and/or new values of tuples that were inserted, deleted, or updated in the event that triggered the action.

Schema-Level Constraints and Triggers

The most powerful forms of active elements in SQL are not associated with specific tuples or components of tuples. These elements, called "triggers" and "assertions," are part of the database schema, on a par with the relations and views themselves.