Web based School

- Chapter 13 -
Advanced SQL Topics

Objectives

Over the course of the past 12 days, you have examined every major topic used to write powerful queries to retrieve data from a database. You have also briefly explored aspects of database design and database security. Today's purpose is to cover advanced SQL topics, which include the following:

Temporary tables
Cursors
Stored procedures
Triggers
Embedded SQL

NOTE: Today's examples use Oracle7's PL/SQL and Microsoft/Sybase SQL Server's Transact-SQL implementations. We made an effort to give examples using both flavors of SQL wherever possible. You do not need to own a copy of either the Oracle7 or the SQL Server database product. Feel free to choose your database product based on your requirements. (If you are reading this to gain enough knowledge to begin a project for your job, chances are you won't have a choice.)

NOTE: Although you can apply most of the examples within this guide to any popular database management system, this statement does not hold for all the material covered today. Many vendors still do not support temporary tables, stored procedures, and triggers. Check your documentation to determine which of these features are included with your favorite database system.

Temporary Tables

The first advanced topic we discuss is the use of temporary tables, which are simply tables that exist temporarily within a database and are automatically dropped when the user logs out or their database connection ends. Transact-SQL creates these temporary tables in the tempdb database. This database is created when you install SQL Server. Two types of syntax are used to create a temporary table.

SYNTAX:

SYNTAX 1:
create table #table_name (
field1 datatype,
.
.
.
fieldn datatype)

Syntax 1 creates a table in the tempdb database. This table is created with a unique name consisting of a combination of the table name used in the CREATE TABLE command and a date-time stamp. A temporary table is available only to its creator. Fifty users could simultaneously issue the following commands:

1> create table #albums (
2> artist char(30),
3> album_name char(50),
4> media_type int)
5> go

The pound sign (#) before the table's name is the identifier that SQL Server uses to flag a temporary table. Each of the 50 users would essentially receive a private table for his or her own use. Each user could update, insert, and delete records from this table without worrying about other users invalidating the table's data. This table could be dropped as usual by issuing the following command:

1> drop table #albums
2> go

The table could also be dropped automatically when the user who created it logs out of the SQL Server. If you created this statement using some type of dynamic SQL connection (such as SQL Server's DB-Library), the table will be deleted when that dynamic SQL connection is closed.

Syntax 2 shows another way to create a temporary table on an SQL Server. This syntax produces a different result than the syntax used in syntax 1, so pay careful attention to the syntactical differences.

SYNTAX:

SYNTAX 2:
create table tempdb..tablename (
field1 datatype,
.
.
.
fieldn datatype)

Creating a temporary table using the format of syntax 2 still results in a table being created in the tempdb database. This table's name has the same format as the name for the table created using syntax 1. The difference is that this table is not dropped when the user's connection to the database ends. Instead, the user must actually issue a DROP TABLE command to remove this table from the tempdb database.

TIP: Another way to get rid of a table that was created using the create table tempdb..tablename syntax is to shut down and restart the SQL Server. This method removes all temporary tables from the tempdb database.

Examples 13.1 and 13.2 illustrate the fact that temporary tables are indeed temporary, using the two different forms of syntax. Following these two examples, Example 13.3 illustrates a common usage of temporary tables: to temporarily store data returned from a query. This data can then be used with other queries.

You need to create a database to use these examples. The database MUSIC is created with the following tables:

ARTISTS
MEDIA
RECORDINGS

Use the following SQL statements to create these tables:

INPUT:

1> create table ARTISTS (
2> name char(30),
3> homebase char(40),
4> style char(20),
5> artist_id int)
6> go
1> create table MEDIA (
2> media_type int,
3> description char(30),
4> price float)
5> go
1> create table RECORDINGS (
2> artist_id int,
3> media_type int,
4> title char(50),
5> year int)
6> go

NOTE: Tables 13.1, 13.2, and 13.3 show some sample data for these tables.

Table 13.1. The ARTISTS table.

Name	Homebase	Style	Artist_ID
Soul Asylum	Minneapolis	Rock	1
Maurice Ravel	France	Classical	2
Dave Matthews Band	Charlottesville	Rock	3
Vince Gill	Nashville	Country	4
Oingo Boingo	Los Angeles	Pop	5
Crowded House	New Zealand	Pop	6
Mary Chapin-Carpenter	Nashville	Country	7
Edward MacDowell	U.S.A.	Classical	8

Table 13.2. The MEDIA table.

Media_Type	Description	Price
1	Record	4.99
2	Tape	9.99
3	CD	13.99
4	CD-ROM	29.99
5	DAT	19.99

Table 13.3. The RECORDINGS table.

Artist_Id	Media_Type	Title	Year
1	2	Hang Time	1988
1	3	Made to Be Broken	1986
2	3	Bolero	1990
3	5	Under the Table and Dreaming	1994
4	3	When Love Finds You	1994
5	2	Boingo	1987
5	1	Dead Man's Party	1984
6	2	Woodface	1990
6	3	Together Alone	1993
7	5	Come On, Come On	1992
7	3	Stones in the Road	1994
8	5	Second Piano Concerto	1985

Example 13.1

You can create a temporary table in the tempdb database. After inserting a dummy record into this table, log out. After logging back into SQL Server, try to select the dummy record out of the temporary table. Note the results:

INPUT:

1> create table #albums (
2> artist char(30),
3> album_name char(50),
4> media_type int)
5> go
1> insert #albums values ("The Replacements", "Pleased To Meet Me", 1)
2> go

Now log out of the SQL Server connection using the EXIT (or QUIT) command. After logging back in and switching to the database you last used, try the following command:

INPUT:

1> select * from #albums
2> go

ANALYSIS:

This table does not exist in the current database.

Example 13.2

Now create the table with syntax 2:

INPUT:

1> create table tempdb..albums (
2> artist char(30),
3> album_name char(50),
4> media_type int)
5> go
1> insert #albums values ("The Replacements", "Pleased To Meet Me", 1)
2> go

After logging out and logging back in, switch to the database you were using when create table tempdb..albums() was issued; then issue the following command:

INPUT:

1> select * from #albums
2> go

This time, you get the following results:

OUTPUT:

artist                         album_name              media_type
_______________________________________________________________________________________

The Replacements               Pleased To Meet Me      1

Example 13.3

This example shows a common usage of temporary tables: to store the results of complex queries for use in later queries.

INPUT:

1> create table #temp_info (
2> name char(30),
3> homebase char(40),
4> style char(20),
5> artist_id int)
6> insert #temp_info
7> select * from ARTISTS where homebase = "Nashville"
8> select RECORDINGS.* from RECORDINGS, ARTISTS
9> where RECORDINGS.artist_id = #temp_info.artist_id
10> go

The preceding batch of commands selects out the recording information for all the artists whose home base is Nashville.

The following command is another way to write the set of SQL statements used in Example 13.3:

1> select ARTISTS.* from ARTISTS, RECORDINGS where ARTISTS.homebase = "Nashville"
2> go

Cursors

A database cursor is similar to the cursor on a word processor screen. As you press the Down Arrow key, the cursor scrolls down through the text one line at a time. Pressing the Up Arrow key scrolls your cursor up one line at a time. Hitting other keys such as Page Up and Page Down results in a leap of several lines in either direction. Database cursors operate in the same way.

Database cursors enable you to select a group of data, scroll through the group of records (often called a recordset), and examine each individual line of data as the cursor points to it. You can use a combination of local variables and a cursor to individually examine each record and perform any external operation needed before moving on to the next record.

One other common use of cursors is to save a query's results for later use. A cursor's result set is created from the result set of a SELECT query. If your application or procedure requires the repeated use of a set of records, it is faster to create a cursor once and reuse it several times than to repeatedly query the database. (And you have the added advantage of being able to scroll through the query's result set with a cursor.)

Follow these steps to create, use, and close a database cursor:

1. Create the cursor.

2. Open the cursor for use within the procedure or application.

3. Fetch a record's data one row at a time until you have reached the end of the cursor's records.

4. Close the cursor when you are finished with it.

5. Deallocate the cursor to completely discard it.

Creating a Cursor

To create a cursor using Transact-SQL, issue the following syntax:

SYNTAX:

declare cursor_name cursor
     for select_statement
     [for {read only | update [of column_name_list]}]

The Oracle7 SQL syntax used to create a cursor looks like this:

SYNTAX:

DECLARE cursor_name CURSOR
     FOR {SELECT command | statement_name | block_name}

By executing the DECLARE cursor_name CURSOR statement, you have defined the cursor result set that will be used for all your cursor operations. A cursor has two important parts: the cursor result set and the cursor position.

The following statement creates a cursor based on the ARTISTS table:

INPUT:

1> create Artists_Cursor cursor
2> for select * from ARTISTS
3> go

ANALYSIS:

You now have a simple cursor object named Artists_Cursor that contains all the records in the ARTISTS table. But first you must open the cursor.

Opening a Cursor

The simple command to open a cursor for use is

SYNTAX:

open cursor_name

Executing the following statement opens Artists_Cursor for use:

1> open Artists_Cursor
2> go

Now you can use the cursor to scroll through the result set.

Scrolling a Cursor

To scroll through the cursor's result set, Transact-SQL provides the following FETCH command.

SYNTAX:

fetch cursor_name [into fetch_target_list]

Oracle SQL provides the following syntax:

FETCH cursor_name {INTO : host_variable
     [[INDICATOR] : indicator_variable]
        [,   : host_variable
        [[INDICATOR] : indicator_variable] ]...
     | USING DESCRIPTOR descriptor }

Each time the FETCH command is executed, the cursor pointer advances through the result set one row at a time. If desired, data from each row can be fetched into the fetch_target_list variables.

NOTE: Transact-SQL enables the programmer to advance more than one row at a time by using the following command: set cursor rowsnumber for cursor_name. This command cannot be used with the INTO clause, however. It is useful only to jump forward a known number of rows instead of repeatedly executing the FETCH statement.

The following statements fetch the data from the Artists_Cursor result set and return the data to the program variables:

INPUT:

1> declare @name char(30)
2> declare @homebase char(40)
3> declare @style char(20)
4> declare @artist_id int
5> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
6> print @name
7> print @homebase
8> print @style
9> print char(@artist_id)
10> go

You can use the WHILE loop (see Chapter 12, "Database Security") to loop through the entire result set. But how do you know when you have reached the end of the records?

Testing a Cursor's Status

Transact-SQL enables you to check the status of the cursor at any time through the maintenance of two global variables: @@sqlstatus and @@rowcount.

The @@sqlstatus variable returns status information concerning the last executed FETCH statement. (The Transact-SQL documentation states that no command other than the FETCH statement can modify the @@sqlstatus variable.) This variable contains one of three values. The following table appears in the Transact-SQL reference manuals:

Status	Meaning
`0`	Successful completion of the `FETCH` statement.
`1`	The `FETCH` statement resulted in an error.
`2`	There is no more data in the result set.

The @@rowcount variable contains the number of rows returned from the cursor's result set up to the previous fetch. You can use this number to determine the number of records in a cursor's result set.

The following code extends the statements executed during the discussion of the FETCH statement. You now use the WHILE loop with the @@sqlstatus variable to scroll the cursor:

INPUT:

1> declare @name char(30)
2> declare @homebase char(40)
3> declare @style char(20)
4> declare @artist_id int
5> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
6> while (@@sqlstatus = 0)
7> begin
8>      print @name
9>      print @homebase
10>     print @style
11>     print char(@artist_id)
12>     fetch Artists_Cursor into @name, @homebase, @style, @artist_id
13> end
14> go

ANALYSIS:

Now you have a fully functioning cursor! The only step left is to close the cursor.

Closing a Cursor

Closing a cursor is a very simple matter. The statement to close a cursor is as follows:

SYNTAX:

close cursor_name

This cursor still exists; however, it must be reopened. Closing a cursor essentially closes out its result set, not its entire existence. When you are completely finished with a cursor, the DEALLOCATE command frees the memory associated with a cursor and frees the cursor name for reuse. The DEALLOCATE statement syntax is as follows:

SYNTAX:

deallocate cursor cursor_name

Example 13.4 illustrates the complete process of creating a cursor, using it, and then closing it, using Transact-SQL.

Example 13.4

INPUT:

1> declare @name char(30)
2> declare @homebase char(40)
3> declare @style char(20)
4> declare @artist_id int
5> create Artists_Cursor cursor
6> for select * from ARTISTS
7> open Artists_Cursor
8> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
9> while (@@sqlstatus = 0)
10> begin
11>      print @name
12>      print @homebase
13>      print @style
14>      print char(@artist_id)
15>      fetch Artists_Cursor into @name, @homebase, @style, @artist_id
16> end
17> close Artists_Cursor
18> deallocate cursor Artists_Cursor
19> go

NOTE: The following is sample data only.

OUTPUT:

Soul Asylum             Minneapolis        Rock        1
Maurice Ravel           France             Classical   2
Dave Matthews Band      Charlottesville    Rock        3
Vince Gill              Nashville          Country     4
Oingo Boingo            Los Angeles        Pop         5
Crowded House           New Zealand        Pop         6
Mary Chapin-Carpenter   Nashville          Country     7
Edward MacDowell        U.S.A.             Classical   8

The Scope of Cursors

Unlike tables, indexes, and other objects such as triggers and stored procedures, cursors do not exist as database objects after they are created. Instead, cursors have a limited scope of use.

WARNING: Remember, however, that memory remains allocated for the cursor, even though its name may no longer exist. Before going outside the cursor's scope, the cursor should always be closed and deallocated.

A cursor can be created within three regions:

In a session--A session begins when a user logs on. If the user logged on to an SQL Server and then created a cursor, then cursor_name would exist until the user logged off. The user would not be able to reuse cursor_name during the current session.
Stored procedure--A cursor created inside a stored procedure is good only during the execution of the stored procedure. As soon as the stored procedure exits, cursor_name is no longer valid.
Trigger--A cursor created inside a trigger has the same restrictions as one created inside a stored procedure.

Creating and Using Stored Procedures

The concept of stored procedures is an important one for the professional database programmer to master. Stored procedures are functions that contain potentially large groupings of SQL statements. These functions are called and executed just as C, FORTRAN, or Visual Basic functions would be called. A stored procedure should encapsulate a logical set of commands that are often executed (such as a complex set of queries, updates, or inserts). Stored procedures enable the programmer to simply call the stored procedure as a function instead of repeatedly executing the statements inside the stored procedure. However, stored procedures have additional advantages.

Sybase, Inc., pioneered stored procedures with its SQL Server product in the late 1980s. These procedures are created and then stored as part of a database, just as tables and indexes are stored inside a database. Transact SQL permits both input and output parameters to stored procedure calls. This mechanism enables you to create the stored procedures in a generic fashion so that variables can be passed to them.

One of the biggest advantages to stored procedures lies in the design of their execution. When executing a large batch of SQL statements to a database server over a network, your application is in constant communication with the server, which can create an extremely heavy load on the network very quickly. As multiple users become engaged in this communication, the performance of the network and the database server becomes increasingly slower. The use of stored procedures enables the programmer to greatly reduce this communication load.

After the stored procedure is executed, the SQL statements run sequentially on the database server. Some message or data is returned to the user's computer only when the procedure is finished. This approach improves performance and offers other benefits as well. Stored procedures are actually compiled by database engines the first time they are used. The compiled map is stored on the server with the procedure. Therefore, you do not have to optimize SQL statements each time you execute them, which also improves performance.

Use the following syntax to create a stored procedure using Transact-SQL:

SYNTAX:

create procedure procedure_name
     [[(]@parameter_name
         datatype [(length) | (precision [, scale])
         [= default][output]
     [, @parameter_name
         datatype [(length) | (precision [, scale])
         [= default][output]]...[)]]
     [with recompile]
     as SQL_statements

This EXECUTE command executes the procedure:

SYNTAX:

execute [@return_status = ]
     procedure_name
     [[@parameter_name =] value |
         [@parameter_name =] @variable [output]...]]
     [with recompile]

Example 13.5

This example creates a simple procedure using the contents of Example 13.4.

INPUT:

1> create procedure Print_Artists_Name
2> as
3> declare @name char(30)
4> declare @homebase char(40)
5> declare @style char(20)
6> declare @artist_id int
7> create Artists_Cursor cursor
8> for select * from ARTISTS
9> open Artists_Cursor
10> fetch Artists_Cursor into @name, @homebase, @style, @artist_id
11> while (@@sqlstatus = 0)
12> begin
13>      print @name
14>      fetch Artists_Cursor into @name, @homebase, @style, @artist_id
15> end
16> close Artists_Cursor
17> deallocate cursor Artists_Cursor
18> go

You can now execute the Print_Artists_Name procedure using the EXECUTE statement:

INPUT:

1> execute Print_Artists_Name
2> go

OUTPUT:

Soul Asylum
Maurice Ravel
Dave Matthews Band
Vince Gill
Oingo Boingo
Crowded House
Mary Chapin-Carpenter
Edward MacDowell

Example 13.5 was a small stored procedure; however, a stored procedure can contain many statements, which means you do not have to execute each statement individually.

Using Stored Procedure Parameters

Example 13.5 was an important first step because it showed the use of the simplest CREATE PROCEDURE statement. However, by looking at the syntax given here, you can see that there is more to the CREATE PROCEDURE statement than was demonstrated in Example 13.5. Stored procedures also accept parameters as input to their SQL statements. In addition, data can be returned from a stored procedure through the use of output parameters.

Input parameter names must begin with the @ symbol, and these parameters must be a valid Transact-SQL data type. Output parameter names must also begin with the @ symbol. In addition, the OUTPUT keyword must follow the output parameter names. (You must also give this OUTPUT keyword when executing the stored procedure.)

Example 13.6 demonstrates the use of input parameters to a stored procedure.

Example 13.6

The following stored procedure selects the names of all artists whose media type is a CD:

1> create procedure Match_Names_To_Media @description char(30)
2> as
3>    select ARTISTS.name from ARTISTS, MEDIA, RECORDINGS
4>    where MEDIA.description = @description and
5>    MEDIA.media_type = RECORDINGS.media_type and
6>    RECORDINGS.artist_id = ARTISTS.artist_id
7> go
1> execute Match_Names_To_Media "CD"
2> go

Executing this statement would return the following set of records:

OUTPUT:

NAME
Soul Asylum
Maurice Ravel
Vince Gill
Crowded House
Mary Chapin-Carpenter

Example 13.7

This example demonstrates the use of output parameters. This function takes the artist's homebase as input and returns the artist's name as output:

INPUT:

1> create procedure Match_Homebase_To_Name @homebase char(40), @name char(30) output
2> as
3>      select @name = name from ARTISTS where homebase = @homebase
4> go
1> declare @return_name char(30)
2> execute Match_Homebase_To_Name "Los Angeles", @return_name = @name output
3> print @name
4> go

OUTPUT:

Oingo Boingo

Removing a Stored Procedure

By now, you can probably make an educated guess as to how to get rid of a stored procedure. If you guessed the DROP command, you are absolutely correct. The following statement removes a stored procedure from a database:

SYNTAX:

drop procedure procedure_name

The DROP command is used frequently: Before a stored procedure can be re-created, the old procedure with its name must be dropped. From personal experience, there are few instances in which a procedure is created and then never modified. Many times, in fact, errors occur somewhere within the statements that make up the procedure. We recommend that you create your stored procedures using an SQL script file containing all your statements. You can run this script file through your database server to execute your desired statements and rebuild your procedures. This technique enables you to use common text editors such as vi or Windows Notepad to create and save your SQL scripts. When running these scripts, however, you need to remember to always drop the procedure, table, and so forth from the database before creating a new one. If you forget the DROP command, errors will result.

The following syntax is often used in SQL Server script files before creating a database object:

SYNTAX:

if exists (select * from sysobjects where name = "procedure_name")
begin
     drop procedure procedure_name
end
go
create procedure procedure_name
as
.
.
.

These commands check the SYSOBJECTS table (where database object information is stored in SQL Server) to see whether the object exists. If it does, it is dropped before the new one is created. Creating script files and following the preceding steps saves you a large amount of time (and many potential errors) in the long run.

Nesting Stored Procedures

Stored procedure calls can also be nested for increased programming modularity. A stored procedure can call another stored procedure, which can then call another stored procedure, and so on. Nesting stored procedures is an excellent idea for several reasons:

Nesting stored procedures reduces your most complex queries to a functional level. (Instead of executing 12 queries in a row, you could perhaps reduce these 12 queries to three stored procedure calls, depending on the situation.)
Nesting stored procedures improves performance. The query optimizer optimizes smaller, more concise groups of queries more effectively than one large group of statements.

When nesting stored procedures, any variables or database objects created in one stored procedure are visible to all the stored procedures it calls. Any local variables or temporary objects (such as temporary tables) are deleted at the end of the stored procedure that created these elements.

When preparing large SQL script files, you might run into table or database object referencing problems. You must create the nested stored procedures before you can call them. However, the calling procedure may create temporary tables or cursors that are then used in the called stored procedures. These called stored procedures are unaware of these temporary tables or cursors, which are created later in the script file. The easiest way around this problem is to create the temporary objects before all the stored procedures are created; then drop the temporary items (in the script file) before they are created again in the stored procedure. Are you confused yet? Example 13.8 should help you understand this process.

Example 13.8

INPUT:

1> create procedure Example13_8b
2> as
3>     select * from #temp_table
4> go
1> create procedure Example13_8a
2> as
3>     create #temp_table (
4>     data char(20),
5>     numbers int)
6>     execute Example13_8b
7>     drop table #temp_table
8> go

ANALYSIS:

As you can see, procedure Example13_8b uses the #temp_table. However, the #temp_table is not created until later (in procedure Example13_8a). This results in a procedure creation error. In fact, because Example13_8b was not created (owing to the missing table #temp_table), procedure Example13_8a is not created either (because Example13_8b was not created).

The following code fixes this problem by creating the #temp_table before the first procedure is created. #temp_table is then dropped before the creation of the second procedure:

INPUT:

1> create #temp_table (
2> data char(20),
3> numbers int)
4> go
1> create procedure Example13_8b
2> as
3>     select * from #temp_table
4> go
1> drop table #temp_table
2> go
1> create procedure Example13_8a
2> as
3>     create #temp_table (
4>     data char(20),
5>     numbers int)
6>     execute Example13_8b
7>     drop table #temp_table
8> go

Designing and Using Triggers

A trigger is essentially a special type of stored procedure that can be executed in response to one of three conditions:

An UPDATE
An INSERT
A DELETE

The Transact-SQL syntax to create a trigger looks like this:

SYNTAX:

create trigger trigger_name
   on table_name
   for {insert, update, delete}
   as SQL_Statements

The Oracle7 SQL syntax used to create a trigger follows.

SYNTAX:

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
  {BEFORE | AFTER}
  {DELETE | INSERT | UPDATE [OF column[, column]...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}]...
   ON [schema.]table
[[REFERENCING { OLD [AS] old [NEW [AS] new]
       | NEW [AS] new [OLD [AS] old]}]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql statements...

Triggers are most useful to enforce referential integrity, as mentioned on Day 9, "Creating and Maintaining Tables," when you learned how to create tables. Referential integrity enforces rules used to ensure that data remains valid across multiple tables. Suppose a user entered the following command:

INPUT:

1> insert RECORDINGS values (12, "The Cross of Changes", 3, 1994)
2> go

ANALYSIS:

This perfectly valid SQL statement inserts a new record in the RECORDINGS table. However, a quick check of the ARTISTS table shows that there is no Artist_ID = 12. A user with INSERT privileges in the RECORDINGS table can completely destroy your referential integrity.

NOTE: Although many database systems can enforce referential integrity through the use of constraints in the CREATE TABLE statement, triggers provide a great deal more flexibility. Constraints return system error messages to the user, and (as you probably know by now) these error messages are not always helpful. On the other hand, triggers can print error messages, call other stored procedures, or try to rectify a problem if necessary.

Triggers and Transactions

The actions executed within a trigger are implicitly executed as part of a transaction. Here's the broad sequence of events:

1. A BEGIN TRANSACTION statement is implicitly issued (for tables with triggers).

2. The insert, update, or delete operation occurs.

3. The trigger is called and its statements are executed.

4. The trigger either rolls back the transaction or the transaction is implicitly committed.

Example 13.9

This example illustrates the solution to the RECORDINGS table update problem mentioned earlier.

INPUT:

1> create trigger check_artists
2> on RECORDINGS
3> for insert, update as
4>      if not exists (select * from ARTISTS, RECORDINGS
5>      where ARTISTS.artist_id = RECORDINGS.artist_id)
6>      begin
7>         print "Illegal Artist_ID!"
8>         rollback transaction
9>      end
10> go

ANALYSIS:

A similar problem could exist for deletes from the RECORDINGS table. Suppose that when you delete an artist's only record from the RECORDINGS table, you also want to delete the artist from the ARTISTS table. If the records have already been deleted when the trigger is fired, how do you know which Artist_ID should be deleted? There are two methods to solve this problem:

Delete all the artists from the ARTISTS table who no longer have any recordings in the RECORDINGS table. (See Example 13.10a.)

Examine the deleted logical table. Transact-SQL maintains two tables: DELETED and INSERTED. These tables, which maintain the most recent changes to the actual table, have the same structure as the table on which the trigger is created. Therefore, you could retrieve the artist IDs from the DELETED table and then delete these IDs from the ARTISTS table. (See Example 13.10b.)

Example 13.10a

INPUT:

1> create trigger delete_artists
2> on RECORDINGS
3> for delete as
4> begin
5>      delete from ARTISTS where artist_id not in
6>      (select artist_id from RECORDINGS)
7> end
8> go

Example 13.10b

1> create trigger delete_artists
2> on RECORDINGS
3> for delete as
4> begin
5>      delete ARTISTS from ARTISTS, deleted
6>      where ARTIST.artist_id  = deleted.artist_id
7> end
8> go

Restrictions on Using Triggers

You must observe the following restrictions when you use triggers:

Triggers cannot be created on temporary tables.
Triggers must be created on tables in the current database.
Triggers cannot be created on views.
When a table is dropped, all triggers associated with that table are automatically dropped with it.

Nested Triggers

Triggers can also be nested. Say that you have created a trigger to fire on a delete, for instance. If this trigger itself then deletes a record, the database server can be set to fire another trigger. This approach would, of course, result in a loop, ending only when all the records in the table were deleted (or some internal trigger conditions were met). Nesting behavior is not the default, however. The environment must be set to enable this type of functionality. Consult your database server's documentation for more information on this topic.

Using SELECT Commands with UPDATE and DELETE

Here are some complex SQL statements using UPDATE and DELETE:

INPUT:

SQL> UPPDATE EMPLOYEE_TBL
     SET LAST_NAME = 'SMITH'
     WHERE EXISTS (SELECT EMPLOYEE_ID
     FROM PAYROLL_TBL
     WHERE EMPLOYEE_ID = 2);

OUTPUT:

1 row updated.

ANALYSIS:

The EMPLOYEE table had an incorrect employee name. We updated the EMPLOYEE table only if the payroll table had the correct ID.

INPUT/OUTPUT:

SQL> UPDATE EMPLOYEE_TABLE
     SET HOURLY_PAY = 'HOURLY_PAY * 1.1
     WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID
     FROM PAYROLL_TBL
     WHERE EMPLOYEE_ID = '222222222');

1 row updated.

ANALYSIS:

We increased the employee's hourly rate by 10 percent.

INPUT/OUTPUT:

SQL> DELETE FROM EMPLOYEE_TBL
	WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID
	FROM PAYROLL_TBL
	WHERE EMPLOYEE_ID = '222222222';

1 row deleted.

ANALYSIS:

Here we deleted an employee with the ID of 222222222.

Testing SELECT Statements Before Implementation

If you are creating a report (using SQL*PLUS for an example) and the report is rather large, you may want to check spacing, columns, and titles before running the program and wasting a lot of time. A simple way of checking is to add where rownum < 3 to your SQL statement:

SYNTAX:

SQL> select *
     from employee_tbl
     where rownum < 5;

ANALYSIS:

You get the first four rows in the table from which you can check the spelling and spacing to see if it suits you. Otherwise, your report may return hundreds or thousands of rows before you discover a misspelling or incorrect spacing.

TIP: A major part of your job--probably 50 percent--is to figure out what your customer really wants and needs. Good communication skills and a knowledge of the particular business that you work for will complement your programming skills. For example, suppose you are the programmer at a car dealership. The used car manager wants to know how many vehicles he has for an upcoming inventory. You think (to yourself): Go count them. Well, he asked for how many vehicles he has; but you know that for an inventory the manager really wants to know how many types (cars, trucks), models, model year, and so on. Should you give him what he asked for and waste your time, or should you give him what he needs?

Embedded SQL

This guide uses the term embedded SQL to refer to the larger topic of writing actual program code using SQL--that is, writing stored procedures embedded in the database that can be called by an application program to perform some task. Some database systems come with complete tool kits that enable you to build simple screens and menu objects using a combination of a proprietary programming language and SQL. The SQL code is embedded within this code.

On the other hand, embedded SQL commonly refers to what is technically known as Static SQL.

Static and Dynamic SQL

Static SQL means embedding SQL statements directly within programming code. This code cannot be modified at runtime. In fact, most implementations of Static SQL require the use of a precompiler that fixes your SQL statement at runtime. Both Oracle and Informix have developed Static SQL packages for their database systems. These products contain precompilers for use with several languages, including the following:

C
Pascal
Ada
COBOL
FORTRAN

Some advantages of Static SQL are

Improved runtime speed

Compile-time error checking

The disadvantages of Static SQL are that

It is inflexible.
It requires more code (because queries cannot be formulated at runtime).
Static SQL code is not portable to other database systems (a factor that you should always consider).

If you print out a copy of this code, the SQL statements appear next to the C language code (or whatever language you are using). Program variables are bound to database fields using a precompiler command. See Example 13.11 for a simple example of Static SQL code.

Dynamic SQL, on the other hand, enables the programmer to build an SQL statement at runtime and pass this statement off to the database engine. The engine then returns data into program variables, which are also bound at runtime. This topic is discussed thoroughly on Chapter 12.

Example 13.11

This example illustrates the use of Static SQL in a C function. Please note that the syntax used here does not comply with the ANSI standard. This Static SQL syntax does not actually comply with any commercial product, although the syntax used is similar to that of most commercial products.

INPUT:

BOOL Print_Employee_Info (void)
{
int Age = 0;
char Name[41] = "\0";
char Address[81] = "\0";
/* Now Bind Each Field We Will Select To a Program Variable */
#SQL BIND(AGE, Age)
#SQL BIND(NAME, Name);
#SQL BIND(ADDRESS, Address);
/* The above statements "bind" fields from the database to variables from the program.
 After we query the database, we will scroll the records returned
and then print them to the screen */

#SQL SELECT AGE, NAME, ADDRESS FROM EMPLOYEES;

#SQL FIRST_RECORD
if (Age == NULL)
{
     return FALSE;
}
while (Age != NULL)
{
     printf("AGE = %d\n, Age);
     printf("NAME = %s\n, Name);
     printf("ADDRESS = %s\n", Address);
     #SQL NEXT_RECORD
}
return TRUE;

}

ANALYSIS:

After you type in your code and save the file, the code usually runs through some type of precompiler. This precompiler converts the lines that begin with the #SQL precompiler directive to actual C code, which is then compiled with the rest of your program to accomplish the task at hand.

If you have never seen or written a C program, don't worry about the syntax used in Example 13.11. (As was stated earlier, the Static SQL syntax is only pseudocode. Consult the Static SQL documentation for your product's actual syntax.)

Programming with SQL

So far, we have discussed two uses for programming with SQL. The first, which was the focus of the first 12 days of this guide, used SQL to write queries and modify data. The second is the capability to embed SQL statements within third- or fourth-generation language code. Obviously, the first use for SQL is essential if you want to understand the language and database programming in general. We have already discussed the drawbacks to using embedded or Static SQL as opposed to Dynamic SQL. Chapter 18, "PL/SQL: An Introduction," and Chapter 19 "Transact-SQL: An Introduction," cover two extensions to SQL that you can use instead of embedded SQL to perform the same types of functions discussed in this section.

Summary

The popularity of programming environments such as Visual Basic, Delphi, and PowerBuilder gives database programmers many tools that are great for executing queries and updating data with a database. However, as you become increasingly involved with databases, you will discover the advantages of using the tools and topics discussed today. Unfortunately, concepts such as cursors, triggers, and stored procedures are recent database innovations and have a low degree of standardization across products. However, the basic theory of usage behind all these features is the same in all database management systems.

Temporary tables are tables that exist during a user's session. These tables typically exist in a special database (named tempdb under SQL Server) and are often identified with a unique date-time stamp as well as a name. Temporary tables can store a result set from a query for later usage by other queries. Performance can erode, however, if many users are creating and using temporary tables all at once, owing to the large amount of activity occurring in the tempdb database.

Cursors can store a result set in order to scroll through this result set one record at a time (or several records at a time if desired). The FETCH statement is used with a cursor to retrieve an individual record's data and also to scroll the cursor to the next record. Various system variables can be monitored to determine whether the end of the records has been reached.

Stored procedures are database objects that can combine multiple SQL statements into one function. Stored procedures can accept and return parameter values as well as call other stored procedures. These procedures are executed on the database server and are stored in compiled form in the database. Using stored procedures, rather than executing standalone queries, improves performance.

Triggers are special stored procedures that are executed when a table undergoes an INSERT, a DELETE, or an UPDATE operation. Triggers often enforce referential integrity and can also call other stored procedures.

Embedded SQL is the use of SQL in the code of an actual program. Embedded SQL consists of both Static and Dynamic SQL statements. Static SQL statements cannot be modified at runtime; Dynamic SQL statements are subject to change.

Q&A

Q If I create a temporary table, can any other users use my table?

A No, the temporary table is available only to its creator.

Q Why must I close and deallocate a cursor?

A Memory is still allocated for the cursor, even though its name may no longer exist.

Workshop

The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."

Quiz

1. True or False: Microsoft Visual C++ allows programmers to call the ODBC API directly.

2. True or False: The ODBC API can be called directly only from a C program.

3. True or False: Dynamic SQL requires the use of a precompiler.

4. What does the # in front of a temporary table signify?

5. What must be done after closing a cursor to return memory?

6. Are triggers used with the SELECT statement?

7. If you have a trigger on a table and the table is dropped, does the trigger still exist?

Exercises

1. Create a sample database application. (We used a music collection to illustrate these points today.) Break this application into logical data groupings.

2. List the queries you think will be required to complete this application.

3. List the various rules you want to maintain in the database.

4. Create a database schema for the various groups of data you described in step 1.

5. Convert the queries in step 2 to stored procedures.

6. Convert the rules in step 3 to triggers.

7. Combine steps 4, 5, and 6 into a large script file that can be used to build the database and all its associated procedures.

8. Insert some sample data. (This step can also be a part of the script file in step 7.)

9. Execute the procedures you have created to test their functionality.