Queries in PSM

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.

2. Queries that return a single value can be used as the right sides of assignment statements.

3. A single-row select statement is a legal statement in PSM. Recall this statement has an INTO clause that specifies variables into which the components of the single returned tuple are placed. These variables could be local variables or parameters of a PSM procedure. The general form was discussed in the context of embedded SQL in "Using Shared Variables" > "Single-Row Select Statements".

4. We can declare and use a cursor, essentially as it was described in "Cursors" for embedded SQL. The declaration of the cursor, OPEN, FETCH, and CLOSE statements are all as described there, with the exceptions that:

(a) No EXEC SQL appears in the statements and
(b) The variables, being local, do not use a colon prefix.

A single-row select in PSM

Example 1: In Figure 1 is the single-row select of "Using Shared Variables" Figure 2, redone for PSM and placed in the context of a hypothetical procedure definition. Note that, because the single-row select returns a one-component tuple, we could also get the same effect from an assignment statement, as:

We shall defer examples of cursor use until we learn the PSM loop statements in the next section.