Database Reference
In-Depth Information
used in SQL Server T-SQL rather than the more Oracle Database PL/SQL style that was used
for the trigger examples in the prior section. If you compare the pseudocode examples in both
sections, you can gain a sense of the differences between code written in PL/SQL and T-SQL.
The WORK_addWorkTransaction procedure receives five input parameters and returns
none. In a more realistic example, a return parameter would be passed back to the caller to
indicate the success or failure of the operation. That discussion takes us away from database
concepts, however, and we will omit it here. This code does not assume that the value of
ArtistID that is passed to it is a valid ID. Instead, the first step in the stored procedure is to
check whether the ArtistID value is valid. To do this, the first block of statements counts the
number of rows that have the given ArtistID value. If the count is zero, then the ArtistID value
is invalid, and the procedure writes an error message and returns.
Otherwise,
3
the procedure then checks to determine if the work has been in the View
Ridge Gallery before. If so, the WORK table will already contain a row for this ArtistID, Title,
and Copy. If no such row exists, the procedure creates a new WORK row. Once that has been
done, it then uses a SELECT to obtain a value for the WorkID value. If the WORK row was just
created, this statement is necessary to obtain the new value of the WorkID surrogate key. If
the work was not created, the SELECT on WorkID is necessary to obtain the WorkID of the
existing row. Once a value of WorkID has been obtained, the new row is inserted into TRANS.
Notice that the system function GetDate() is used to supply a value for DateAcquired in the
new row.
This procedure illustrates how SQL is embedded in stored procedures. It is not complete
because we need to do something to ensure that either all updates are made to the database or
none of them are. You will learn how to do this in Chapter 9. For now, just concentrate on how
SQL can be used as part of a database application.
3
This code does not check for more than one row having the given ArtistID because ArtistID is a surrogate key.
SQL DDL statements are used to manage the structure
of tables. This chapter presented four SQL DDL state-
ments: CREATE TABLE, ALTER TABLE, DROP TABLE, and
TRUNCATE TABLE. SQL is preferred over graphical tools for
creating tables because it is faster, it can be used to create
the same table repeatedly, tables can be created from pro-
gram code, and it is standardized and DBMS independent.
The IDENTITY (
N, M
) data type is used to create surro-
gate key columns, where
N
is the starting value and
M
is the
increment to be added. The SQL CREATE TABLE statement
is used to define the name of the table, its columns, and
constraints on columns. There are five types of constraints:
PRIMARY KEY, UNIQUE, NULL/NOT NULL, FOREIGN KEY,
and CHECK.
The purposes of the first three constraints are obvious.
FOREIGN KEY is used to create referential integrity con-
straints; CHECK is used to create data constraints. Figure 7-11
summarizes techniques for creating relationships using SQL
constraints.
Simple default values can be assigned using the DEFAULT
keyword. Data constraints are defined using CHECK
constraints. Domain, range, and intratable constraints can be
defined. Although SQL-92 defined facilities for interrelation
CHECK constraints, those facilities were not implemented by
DBMS vendors. Instead, interrelation constraints are enforced
using triggers.
The ALTER statement is used to add and remove col-
umns and constraints. The DROP statement is used to drop
tables. In SQL DDL, parents need to be created first and
dropped last.
The DML SQL statements are INSERT, UPDATE,
DELETE, and MERGE. Each statement can be used on a
single row, on a group of rows, or on the entire table. Because
of their power, both UPDATE and DELETE need to be used
with care.
An SQL view is a virtual table that is constructed from
other tables and views. SQL SELECT statements are used
to define views. The only restriction is that a view definition
may not include an ORDER BY clause.
Views are used to hide columns or rows and to show
the results of computed columns. They also can hide com-
plicated SQL syntax, such as that used for joins and GROUP
Search WWH ::
Custom Search