New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Advanced SQL Topics


    This 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 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.


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:

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:

create table #albums ( artist char(30), album-name char(50), media-type int) 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:

drop table #albums 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.


    The temporary tables are indeed temporary, using the two different forms of syntax. 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: Artists, Media, and Recordings

INPUT:

create table ARTISTS(name char(30),homebase char(40),style char(20),artist-id int) go

create table MEDIA ( media-type int, description char(30), price float) go

create table RECORDINGS ( artist-id int, media-type int, title char(50), year int) go

    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:

create table #albums ( artist char(30), album-name char(50), media-type int) go

insert #albums values ("The Replacements", "Pleased To Meet Me", 1) 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:

select * from #albums go

ANALYSIS:

    This table does not exist in the current database.

    Now create the table with syntax:

INPUT:

create table tempdb..albums (artist char(30), album-name char(50), media-type int) go

insert #albums values ("The Replacements", "Pleased To Meet Me", 1) 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:

select * from #albums go

    This time, you get the following results:

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

INPUT:

create table #temp (name char(30), homebase char(40), style char(20), artist-id int)
insert #temp-info select * from ARTISTS where homebase = "Nashville"
select RECORDINGS.* from RECORDINGS, ARTISTS
where RECORDINGS.artist-id = #temp-info.artist-id 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.

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