Database Reference
In-Depth Information
some DBMS products, a candidate key) is present in the view. Multi-table views may be updat-
able on the most subordinate table. Again, this can only be done if the primary key or candi-
date key for that table is in the view. We will revisit this topic for Microsoft SQL Server 2012 in
Chapter 10A, Oracle Database 11
g
Release 2 in Chapter 10B, and MySQL 5.6 in Chapter 10C.
SQL statements can be embedded in application programs, triggers, and stored procedures.
Before we discuss those subjects, however, we need to explain the placement of SQL state-
ments in program code.
In order to embed SQL statements in program code, two problems must be solved. The
first problem is that some means of assigning the results of SQL statements to program vari-
ables must be available. Many different techniques are used. Some involve object-oriented pro-
grams, whereas others are simpler. For example, in Oracle's PL/SQL the following statement
assigns the count of the number of rows in the CUSTOMER table to the user-defined variable
named
rowCount
:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH07-01 *** */
SELECT
Count(*) INTO rowCount
FROM
CUSTOMER;
MySQL SQL uses the same syntax. In SQL Server T-SQL, all user-defined variables must use
the @ (“at” symbol) as the first character, and therefore the code in T-SQL uses the user-
defined variable named @
rowCount
:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH07-02 *** */
SELECT
@rowCount = Count(*)
FROM
CUSTOMER;
In either case, the execution of this code will place the number of rows in CUSTOMER into the
program variable rowCount or @rowCount.
The second problem to solve concerns a paradigm mismatch between SQL and applica-
tion programming languages. SQL is table oriented; SQL SELECT statements start with one
or more tables and produce a table as output. Programs, however, start with one or more vari-
ables, manipulate them, and store the result in a variable. Because of this difference, an SQL
statement like the following makes no sense:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH07-03 *** */
SELECT
LastName INTO CustomerLastName
FROM
CUSTOMER;
If there are 100 rows in the CUSTOMER table, there will be 100 values of LastName. The
program variable customerLastName, however, is expecting to receive just one value.
To avoid this problem, the results of SQL statements are treated as
pseudofiles
. When
an SQL statement returns a set of rows, a
cursor
, which is a pointer to a particular row, is es-
tablished. The application program can then place the cursor on the first, last, or some other
row of the SQL statement output table. With the cursor placed, values of columns for that row
can be assigned to program variables. When the application is finished with a particular row, it
moves the cursor to the next, prior, or some other row and continues processing.
Search WWH ::
Custom Search