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 book 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.
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> goThe 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> goThe 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 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.
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.
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.
You need to create a database to use these examples. The database MUSIC is created with the following tables:
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.
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 |
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 |
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 |
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> goNow 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:
1> select * from #albums 2> go
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> goAfter 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:
1> select * from #albums 2> goThis time, you get the following results:
artist album_name media_type _______________________________________________________________________________________
The Replacements Pleased To Meet Me 1
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> goThe 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
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:
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.
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:
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:
1> create Artists_Cursor cursor 2> for select * from ARTISTS 3> go
open cursor_nameExecuting the following statement opens Artists_Cursor for use:
1> open Artists_Cursor 2> goNow you can use the cursor to scroll through the result set.
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.
The following statements fetch the data from the Artists_Cursor result set and return the data to the program variables:
NOTE: Transact-SQL enables the programmer to advance more than one row at a time by using the following command: set cursor rows number 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.
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> goYou can use the WHILE loop (see Day 12, "Database Security") to loop through the entire result set. But how do you know when you have reached the end of the records?
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 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:
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
close cursor_nameThis 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:
deallocate cursor cursor_nameExample 13.4 illustrates the complete process of creating a cursor, using it, and then closing it, using Transact-SQL.
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.
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
A cursor can be created within three regions:
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.
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:
create procedure procedure_name [[(]@parameter_name datatype [(length) | (precision [, scale]) [= default][output] [, @parameter_name datatype [(length) | (precision [, scale]) [= default][output]]...[)]] [with recompile] as SQL_statementsThis EXECUTE command executes the procedure:
execute [@return_status = ] procedure_name [[@parameter_name =] value | [@parameter_name =] @variable [output]...]] [with recompile]
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> goYou can now execute the Print_Artists_Name procedure using the EXECUTE statement:
1> execute Print_Artists_Name 2> go
Soul Asylum Maurice Ravel Dave Matthews Band Vince Gill Oingo Boingo Crowded House Mary Chapin-Carpenter Edward MacDowellExample 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.
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.
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> goExecuting this statement would return the following set of records:
NAME Soul Asylum Maurice Ravel Vince Gill Crowded House Mary Chapin-Carpenter
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
Oingo Boingo
drop procedure procedure_nameThe 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:
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.
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.
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
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:
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
create trigger trigger_name on table_name for {insert, update, delete} as SQL_StatementsThe Oracle7 SQL syntax used to create a trigger follows.
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:
1> insert RECORDINGS values (12, "The Cross of Changes", 3, 1994) 2> go
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.
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.
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
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
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
SQL> UPPDATE EMPLOYEE_TBL SET LAST_NAME = 'SMITH' WHERE EXISTS (SELECT EMPLOYEE_ID FROM PAYROLL_TBL WHERE EMPLOYEE_ID = 2);
1 row updated.
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.
SQL> DELETE FROM EMPLOYEE_TBL WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID FROM PAYROLL_TBL WHERE EMPLOYEE_ID = '222222222';
1 row deleted.
SQL> select * from employee_tbl where rownum < 5;
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?
On the other hand, embedded SQL commonly refers to what is technically known as Static SQL.
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 Day 12.
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; }
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.)
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.
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.
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?
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.